Get the most out of your Centmin Mod LEMP stack
Become a Member

MariaDB Anyone using MariaDB 10.4.8 on live and busy site?

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by rdan, Sep 18, 2019.

  1. rdan

    rdan Well-Known Member

    4,671
    1,121
    113
    May 25, 2014
    Ratings:
    +1,663
    Local Time:
    11:08 PM
    Mainline
    10.2
    • CentOS Version: CentOS 7.7 64bit
    • Centmin Mod Version Installed: 123.09beta01
    • MariaDB MySQL Version Installed: 10.4.8
    • When was last time updated Centmin Mod code base ? : today
    • Persistent Config:
      Code (Text):
      ENABLEMOTD='n'
      CUSTOMSERVERNAME='y'
      CUSTOMSERVERSTRING='nginx'
      AUTO_GITUPDATE='y'
      DISABLE_IPVSIX='y'
      PHP_PGO='y'
      DEVTOOLSETEIGHT='y'
      GENERAL_DEVTOOLSETGCC='y'
      CRYPTO_DEVTOOLSETGCC='y'
      NSD_DISABLED='y'
      MEMCACHED_DISABLED='y'
      PUREFTPD_DISABLED='y'
      NGINX_UPDATEMAINTENANCE='n'
      PHP_UPDATEMAINTENANCE='n'
      MARIADB_UPDATEMAINTENANCE='n'
      MARIADB_INSTALLTENFOUR='y'
      AUTOTUNE_CLIENTMAXBODY='n'
      NSD_INSTALL='n'              # Install NSD (DNS Server)
      NGXMODULE_ALTORDER='n'       # nginx configure module ordering alternative order
      NGINX_DEBUG='n'              # Enable & reinstall Nginx debug log nginx.org/en/docs/debugging_log.html & wiki.nginx.org/Debugging
      NGINX_HTTP2='y'              # Nginx http/2 patch https://community.centminmod.com/threads/4127/
      NGINX_MODSECURITY=n          # modsecurity module support https://github.com/SpiderLabs/ModSecurity/wiki/Reference-Manual#Installation_for_NGINX
      NGINX_REALIP='y'
      NGINX_RDNS='n'               # https://github.com/flant/nginx-http-rdns
      NGINX_NJS='n'                # nginScript https://www.nginx.com/blog/launching-nginscript-and-looking-ahead/
      NGINX_GEOIP='n'              # Nginx GEOIP module install
      NGINX_STUBSTATUS='n'         # http://nginx.org/en/docs/http/ngx_http_stub_status_module.html required for nginx statistics
      NGINX_SUB='n'                # http://nginx.org/en/docs/http/ngx_http_sub_module.html
      NGINX_ADDITION='n'           # http://nginx.org/en/docs/http/ngx_http_addition_module.html
      NGINX_IMAGEFILTER='n'        # http://nginx.org/en/docs/http/ngx_http_image_filter_module.html
      NGINX_PERL='n'               # http://nginx.org/en/docs/http/ngx_http_perl_module.html
      NGINX_XSLT='n'               # http://nginx.org/en/docs/http/ngx_http_xslt_module.html
      NGINX_CACHEPURGE='n'         # https://github.com/FRiCKLE/ngx_cache_purge/
      NGINX_ACCESSKEY='n'          #
      NGINX_HTTPCONCAT='n'         # https://github.com/alibaba/nginx-http-concat
      NGINX_THREADS='n'            # https://www.nginx.com/blog/thread-pools-boost-performance-9x/
      NGINX_STREAM='n'             # http://nginx.org/en/docs/stream/ngx_stream_core_module.html
      NGINX_STREAMGEOIP='n'        # nginx 1.11.3+ option http://hg.nginx.org/nginx/rev/558db057adaa
      NGINX_STREAMREALIP='n'       # nginx 1.11.4+ option http://hg.nginx.org/nginx/rev/9cac11efb205
      NGINX_STREAMSSLPREREAD='n'   # nginx 1.11.5+ option https://nginx.org/en/docs/stream/ngx_stream_ssl_preread_module.html
      NGINX_RTMP='n'               # Nginx RTMP Module support https://github.com/arut/nginx-rtmp-module
      NGINX_FLV='n'                # http://nginx.org/en/docs/http/ngx_http_flv_module.html
      NGINX_MP4='n'                # Nginx MP4 Module http://nginx.org/en/docs/http/ngx_http_mp4_module.html
      NGINX_AUTHREQ='n'            # http://nginx.org/en/docs/http/ngx_http_auth_request_module.html
      NGINX_SECURELINK='n'         # http://nginx.org/en/docs/http/ngx_http_secure_link_module.html
      NGINX_FANCYINDEX='n'         # http://wiki.nginx.org/NgxFancyIndex
      NGINX_VHOSTSTATS='n'         # https://github.com/vozlt/nginx-module-vts
      NGINX_LIBBROTLI='y'          # https://github.com/google/ngx_brotli
      NGXDYNAMIC_BROTLI='y'
      NGINX_BROTLIDEP_UPDATE='y'
      NGINX_LIBBROTLISTATIC='n'
      NGINX_PAGESPEED='n'          # Install ngx_pagespeed
      NGINX_PAGESPEEDGITMASTER='n' # Install ngx_pagespeed from official github master instead 
      NGINX_PASSENGER='n'          # Install Phusion Passenger requires installing addons/passenger.sh before hand
      NGINX_WEBDAV='n'             # Nginx WebDAV and nginx-dav-ext-module
      NGINX_HTTPREDIS='n'          # Nginx redis http://wiki.nginx.org/HttpRedisModule
      ORESTY_HEADERSMORE='n'       # openresty headers more https://github.com/openresty/headers-more-nginx-module
      NGINX_STICKY='n'             # nginx sticky module https://bitbucket.org/nginx-goodies/nginx-sticky-module-ng
      NGINX_UPSTREAMCHECK='n'      # nginx upstream check https://github.com/yaoweibin/nginx_upstream_check_module
      NGINX_OPENRESTY='n'          # Agentzh's openresty Nginx modules
      ORESTY_SETMISCGIT='n'        # use git master instead of version specific
      ORESTY_ECHOGIT='n'           # use git master instead of version specific
      LUAJIT_GITINSTALL='n'        # opt to install luajit 2.1 from dev branch http://repo.or.cz/w/luajit-2.0.git/shortlog/refs/heads/v2.1
      ORESTY_LUANGINX='n'             # enable or disable or ORESTY_LUA* nginx modules below
      NGX_LUAUPSTREAM='n'                # disable https://github.com/openresty/lua-upstream-nginx-module
      NGX_LUAUPSTREAMCACHE='n'           # disable https://github.com/cloudflare/lua-upstream-cache-nginx-module
      AUTODETECPHP_OVERRIDE='y'
      PHPGEOIP_ALWAYS='n'          # GeoIP php extension is always reinstalled on php recompiles
      PHPDEBUGMODE='n'             # --enable-debug PHP compile flag
      PHPFINFO='n'                 # Disable or Enable PHP File Info extension
      PHPPCNTL='n'                 # Disable or Enable PHP Process Control extension
      PHPINTL='n'                  # Disable or Enable PHP intl extension
      PHPRECODE=n                  # Disable or Enable PHP Recode extension
      PHPSNMP='n'                  # Disable or Enable PHP SNMP extension
      PHPIMAGICK='y'               # Disable or Enable PHP ImagicK extension
      PHPMAILPARSE='n'             # Disable or Enable PHP mailparse extension
      PHPIONCUBE='n'               # Disable or Enable Ioncube Loader via addons/ioncube.sh
      POSTGRESQL='n'               # set to =y to install PostgreSQL 9.4 server, devel packages and pdo-pgsql PHP extension
      NGINX_VHOSTSSL='n'           # enable centmin.sh menu 2 prompt to create self signed SSL vhost 2nd vhost conf
      NGINXBACKUP='n'
      NGINX_DYNAMICTLS='y'
      NGINX_HPACK='y'
      MEMCACHED_INSTALL='n'         # Install Memcached
      PHPREDIS='y'                # redis PHP extension install
      PHPMONGODB='n'              # MongoDB PHP extension install
      PHP_MCRYPTPECL='n'
      PHP_FTPEXT='n'              # ftp PHP extension
      PHP_MEMCACHE='n'            # memcache PHP extension 
      PHP_MEMCACHED='n'           # memcached PHP extension
      PHPZOPFLI='n'
      PHPZOPFLI_ALWAYS='n'
      PHP_OVERWRITECONF='n'
      PHP_VERSION='7.3.9'        # Use this version of PHP
      ZOPCACHEDFT='y'
      PHP_EXTRAOPTS=""
      
      
      


     
  2. rdan

    rdan Well-Known Member

    4,671
    1,121
    113
    May 25, 2014
    Ratings:
    +1,663
    Local Time:
    11:08 PM
    Mainline
    10.2
    Mysql consumes a lot of CPU until server hangs up (use more than 100% cpu power), need to restart mysql to quickly resolve it.
     
  3. eva2000

    eva2000 Administrator Staff Member

    41,725
    9,395
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +14,431
    Local Time:
    1:08 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    MariaDB 10.4 has drastic changes which make it no longer a MySQL drop-in replacement as outlined at MariaDB - Interesting look at what's new in MariaDB 10.4 and MariaDB - MariaDB 10.1 upgrades to MariaDB 10.2, 10.3 and 10.4.. So really I haven't tested MariaDB 10.4 in production yet myself.

    You'd need to investigate where that load within MariaDB 10.4 is coming from to sure. Is this a MariaDB 10.3 upgraded to 10.4 server or fresh Centmin Mod 123.09beta01 with MariaDB 10.4 default ? Modified your /etc/my.cnf post install ? could need tuning for your specific loads as MariaDB 10.4 isn't same as 10.3 or earlier.
     
  4. negative

    negative Active Member

    346
    39
    28
    Apr 11, 2015
    Ratings:
    +81
    Local Time:
    6:08 PM
    1.9.10
    10.1.11
    Currently i'm using MariaDB 10.4.8 with a Xenforo Board.
    Our database size is about 10GB and we have about 600K threads, 2.5m members, 7m posts.

    We have no problem even mysql ram usage decreased after upgrade the mariadb to 10.4 from 10.1
     
  5. rdan

    rdan Well-Known Member

    4,671
    1,121
    113
    May 25, 2014
    Ratings:
    +1,663
    Local Time:
    11:08 PM
    Mainline
    10.2
    This.
    No, just default.
     
  6. rdan

    rdan Well-Known Member

    4,671
    1,121
    113
    May 25, 2014
    Ratings:
    +1,663
    Local Time:
    11:08 PM
    Mainline
    10.2
    That's nice.
    Mine at 13m+ post, 1.5m members, 700k threads.

    I don't have time to investigate earlier so I just roll back to 10.3.
    I do have a lot of addons, maybe some query isn't running well, not so sure.

    But the database is exported from 10.3 database then imported to 10.4, does it matter?
     
  7. buik

    buik Well-Known Member

    1,197
    320
    83
    Apr 29, 2016
    Ratings:
    +939
    Local Time:
    5:08 PM
    Nice.... you have already taken action yourself.
    Let it be clear to everyone.

    When you have a successful site, money site or its critical.
    Never ever use fresh software on the fly.
    And certainly not if you use customized software or heavily modified software with addons.

    There is absolutely no reason for that.
    The site is really not going to be faster (There are more in-depth limiting factors)
     
    • Agree Agree x 1
    • Friendly Friendly x 1
  8. Jay Chen

    Jay Chen Active Member

    100
    32
    28
    Sep 10, 2017
    Ratings:
    +56
    Local Time:
    11:08 AM
    Wow, that's some impressive numbers
     
    • Like Like x 1
  9. negative

    negative Active Member

    346
    39
    28
    Apr 11, 2015
    Ratings:
    +81
    Local Time:
    6:08 PM
    1.9.10
    10.1.11
    Yes it may related with some add-ons right but if you want to fix that problem, you need go upgrade back to 10.4 and check the logged queries and mysql logs.

    Anyway, my advice is upgrade 10.4 then disable all add-ons on xenforo, then enable back one by one the add-ons. So you can find the which add-on dramatically increasing the mysql load.
     
    • Like Like x 1
    • Agree Agree x 1
  10. Xon

    Xon Active Member

    154
    60
    28
    Nov 16, 2015
    Ratings:
    +195
    Local Time:
    11:08 PM
    1.15.x
    MariaDB 10.3.x
    I've found that the having the join_cache_level > 0 setting causes massive performance regressions on Xenforo forums with some add-ons (basically joining a very large table with a very small table utterly tanks performance)
     
    • Informative Informative x 2
  11. eva2000

    eva2000 Administrator Staff Member

    41,725
    9,395
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +14,431
    Local Time:
    1:08 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    Interesting info. This for specifically for MariaDB 10.4 ? what are the default optimizer_switch configuration for your MariaDB 10.4 install

    example on MariaDB 10.3.18 used on Centmin Mod installs
    Code (Text):
    mysqladmin var | awk '/optimizer_switch/ {print $4}' | sed -e 's|,|\n|g' | sort
    
    condition_pushdown_for_derived=on
    derived_merge=on
    derived_with_keys=on
    engine_condition_pushdown=off
    exists_to_in=on
    extended_keys=on
    firstmatch=on
    index_condition_pushdown=on
    index_merge_intersection=on
    index_merge=on
    index_merge_sort_intersection=off
    index_merge_sort_union=on
    index_merge_union=on
    in_to_exists=on
    join_cache_bka=on
    join_cache_hashed=on
    join_cache_incremental=on
    loosescan=on
    materialization=on
    mrr_cost_based=off
    mrr=off
    mrr_sort_keys=off
    optimize_join_buffer_size=off
    orderby_uses_equalities=on
    outer_join_with_cache=on
    partial_match_rowid_merge=on
    partial_match_table_scan=on
    semijoin=on
    semijoin_with_cache=on
    split_materialized=on
    subquery_cache=on
    table_elimination=on
    

    I ask, as Mariadb 10.4.3 and higher changed defaults for optimize_join_buffer_size from off to on. And if you use join_cache_level => 5 then might need to enable mrr_sort_keys and mrr optimizer_switch too
     
    • Like Like x 1