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,963
    1,184
    113
    May 25, 2014
    Ratings:
    +1,798
    Local Time:
    11:32 AM
    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,963
    1,184
    113
    May 25, 2014
    Ratings:
    +1,798
    Local Time:
    11:32 AM
    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

    44,804
    10,216
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,834
    Local Time:
    1:32 PM
    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

    405
    49
    28
    Apr 11, 2015
    Ratings:
    +96
    Local Time:
    6:32 AM
    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,963
    1,184
    113
    May 25, 2014
    Ratings:
    +1,798
    Local Time:
    11:32 AM
    Mainline
    10.2
    This.
    No, just default.
     
  6. rdan

    rdan Well-Known Member

    4,963
    1,184
    113
    May 25, 2014
    Ratings:
    +1,798
    Local Time:
    11:32 AM
    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 “It always seems impossible until it’s done.” Premium Member

    1,297
    350
    83
    Apr 29, 2016
    Ratings:
    +1,056
    Local Time:
    5:32 AM
    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)
     
  8. Jay Chen

    Jay Chen Active Member

    133
    37
    28
    Sep 10, 2017
    Ratings:
    +72
    Local Time:
    11:32 PM
    Wow, that's some impressive numbers
     
  9. negative

    negative Active Member

    405
    49
    28
    Apr 11, 2015
    Ratings:
    +96
    Local Time:
    6:32 AM
    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.
     
  10. Xon

    Xon Active Member

    170
    61
    28
    Nov 16, 2015
    Ratings:
    +223
    Local Time:
    11:32 AM
    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)
     
  11. eva2000

    eva2000 Administrator Staff Member

    44,804
    10,216
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,834
    Local Time:
    1:32 PM
    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
     
  12. negative

    negative Active Member

    405
    49
    28
    Apr 11, 2015
    Ratings:
    +96
    Local Time:
    6:32 AM
    1.9.10
    10.1.11
    So we should think to disable the join_cache_level as 0 (disable) on Mariadb 10.4 ?
     
  13. Xon

    Xon Active Member

    170
    61
    28
    Nov 16, 2015
    Ratings:
    +223
    Local Time:
    11:32 AM
    1.15.x
    MariaDB 10.3.x
    @eva2000 differences in the optimizer switches;
    Code:
    condition_pushdown_for_subquery=on
    condition_pushdown_from_having=on
    optimize_join_buffer_size=on
    rowid_filter=on
    
    Adjusting optimizer flags (the above to off or mrr/mrr_sort_keys to on) doesn't appear to matter.

    Code:
    SELECT `xf_thread`.*, `xf_deletion_log_DeletionLog_1`.*, `xf_user_User_2`.*, `xf_user_LastPoster_3`.*, `xf_thread_read_Read_4`.*, `xf_thread_user_post_UserPosts_5`.*, `xf_thread_watch_Watch_6`.*, `xf_reaction_content_FirstPostReactions_7`.*, `xf_thread_reply_ban_ReplyBans_8`.*, `xf_sv_collaboration_thread_user_CollaborativeUsers_9`.*, `xf_sv_collaboration_thread_group_Group_10`.*
    FROM `xf_thread`
    LEFT JOIN `xf_deletion_log` AS `xf_deletion_log_DeletionLog_1` ON (`xf_deletion_log_DeletionLog_1`.`content_type` = 'thread' AND `xf_deletion_log_DeletionLog_1`.`content_id` = `xf_thread`.`thread_id`)
    LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_thread`.`user_id`)
    LEFT JOIN `xf_user` AS `xf_user_LastPoster_3` ON (`xf_user_LastPoster_3`.`user_id` = `xf_thread`.`last_post_user_id`)
    LEFT JOIN `xf_thread_read` AS `xf_thread_read_Read_4` ON (`xf_thread_read_Read_4`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_read_Read_4`.`user_id` = '7')
    LEFT JOIN `xf_thread_user_post` AS `xf_thread_user_post_UserPosts_5` ON (`xf_thread_user_post_UserPosts_5`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_user_post_UserPosts_5`.`user_id` = '7')
    LEFT JOIN `xf_thread_watch` AS `xf_thread_watch_Watch_6` ON (`xf_thread_watch_Watch_6`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_watch_Watch_6`.`user_id` = '7')
    LEFT JOIN `xf_reaction_content` AS `xf_reaction_content_FirstPostReactions_7` ON (`xf_reaction_content_FirstPostReactions_7`.`content_type` = 'thread' AND `xf_reaction_content_FirstPostReactions_7`.`content_id` = `xf_thread`.`thread_id` AND `xf_reaction_content_FirstPostReactions_7`.`reaction_user_id` = '7')
    LEFT JOIN `xf_thread_reply_ban` AS `xf_thread_reply_ban_ReplyBans_8` ON (`xf_thread_reply_ban_ReplyBans_8`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_reply_ban_ReplyBans_8`.`user_id` = '7')
    LEFT JOIN `xf_sv_collaboration_thread_user` AS `xf_sv_collaboration_thread_user_CollaborativeUsers_9` ON (`xf_sv_collaboration_thread_user_CollaborativeUsers_9`.`thread_id` = `xf_thread`.`thread_id` AND `xf_sv_collaboration_thread_user_CollaborativeUsers_9`.`user_id` = '7')
    LEFT JOIN `xf_sv_collaboration_thread_group` AS `xf_sv_collaboration_thread_group_Group_10` ON (`xf_sv_collaboration_thread_group_Group_10`.`thread_id` = `xf_sv_collaboration_thread_user_CollaborativeUsers_9`.`thread_id` AND `xf_sv_collaboration_thread_group_Group_10`.`group_id` = `xf_sv_collaboration_thread_user_CollaborativeUsers_9`.`group_id`)
    WHERE ((`xf_thread`.`discussion_state` IN ('visible', 'deleted', 'moderated'))) AND (`xf_thread`.`node_id` = 4) AND (`xf_thread`.`sticky` = 0)
    ORDER BY `xf_thread`.`last_post_date` DESC
    limit 30;
    

    Code:
    {
      "query_block": {
        "select_id": 1,
        "r_loops": 1,
        "r_total_time_ms": 2.0729,
        "table": {
          "table_name": "xf_thread",
          "access_type": "ref",
          "possible_keys": [
            "node_id_last_post_date",
            "node_id_sticky_state_last_post"
          ],
          "key": "node_id_last_post_date",
          "key_length": "4",
          "used_key_parts": ["node_id"],
          "ref": ["const"],
          "r_loops": 1,
          "rows": 249467,
          "r_rows": 30,
          "r_total_time_ms": 0.1693,
          "filtered": 100,
          "r_filtered": 100,
          "attached_condition": "xf_thread.node_id <=> 4 and xf_thread.sticky = 0 and xf_thread.discussion_state in ('visible','deleted','moderated')"
        },
        "table": {
          "table_name": "xf_deletion_log_DeletionLog_1",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "31",
          "used_key_parts": ["content_type", "content_id"],
          "ref": ["const", "sbforums.xf_thread.thread_id"],
          "r_loops": 30,
          "rows": 1,
          "r_rows": 0,
          "r_total_time_ms": 0.1015,
          "filtered": 100,
          "r_filtered": 100,
          "attached_condition": "trigcond(xf_deletion_log_DeletionLog_1.content_type = 'thread')"
        },
        "table": {
          "table_name": "xf_user_User_2",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["user_id"],
          "ref": ["sbforums.xf_thread.user_id"],
          "r_loops": 30,
          "rows": 1,
          "r_rows": 1,
          "r_total_time_ms": 0.1439,
          "filtered": 100,
          "r_filtered": 100
        },
        "table": {
          "table_name": "xf_user_LastPoster_3",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["user_id"],
          "ref": ["sbforums.xf_thread.last_post_user_id"],
          "r_loops": 30,
          "rows": 1,
          "r_rows": 1,
          "r_total_time_ms": 0.1333,
          "filtered": 100,
          "r_filtered": 100
        },
        "table": {
          "table_name": "xf_thread_read_Read_4",
          "access_type": "eq_ref",
          "possible_keys": ["user_id_thread_id", "thread_id"],
          "key": "user_id_thread_id",
          "key_length": "8",
          "used_key_parts": ["user_id", "thread_id"],
          "ref": ["const", "sbforums.xf_thread.thread_id"],
          "r_loops": 30,
          "rows": 1,
          "r_rows": 0,
          "r_total_time_ms": 0.0498,
          "filtered": 100,
          "r_filtered": 100
        },
        "table": {
          "table_name": "xf_thread_user_post_UserPosts_5",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "user_id"],
          "key": "PRIMARY",
          "key_length": "8",
          "used_key_parts": ["thread_id", "user_id"],
          "ref": ["sbforums.xf_thread.thread_id", "const"],
          "r_loops": 30,
          "rows": 1,
          "r_rows": 0,
          "r_total_time_ms": 0.1216,
          "filtered": 100,
          "r_filtered": 100
        },
        "table": {
          "table_name": "xf_thread_watch_Watch_6",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "thread_id_email_subscribe"],
          "key": "PRIMARY",
          "key_length": "8",
          "used_key_parts": ["user_id", "thread_id"],
          "ref": ["const", "sbforums.xf_thread.thread_id"],
          "r_loops": 30,
          "rows": 1,
          "r_rows": 0,
          "r_total_time_ms": 0.0746,
          "filtered": 100,
          "r_filtered": 100
        },
        "table": {
          "table_name": "xf_reaction_content_FirstPostReactions_7",
          "access_type": "eq_ref",
          "possible_keys": [
            "content_type_id_user_id",
            "user_like_date",
            "reaction_user_id_reaction_date",
            "content_type_id_reaction_date"
          ],
          "key": "content_type_id_user_id",
          "key_length": "35",
          "used_key_parts": ["content_type", "content_id", "reaction_user_id"],
          "ref": ["const", "sbforums.xf_thread.thread_id", "const"],
          "r_loops": 30,
          "rows": 1,
          "r_rows": 0,
          "r_total_time_ms": 0.0381,
          "filtered": 7.5e-7,
          "r_filtered": 100,
          "attached_condition": "trigcond(xf_reaction_content_FirstPostReactions_7.content_type = 'thread')"
        },
        "table": {
          "table_name": "xf_thread_reply_ban_ReplyBans_8",
          "access_type": "ref",
          "possible_keys": ["thread_id", "user_id"],
          "key": "user_id",
          "key_length": "4",
          "used_key_parts": ["user_id"],
          "ref": ["const"],
          "r_loops": 30,
          "rows": 1,
          "r_rows": 0,
          "r_total_time_ms": 0.0368,
          "filtered": 100,
          "r_filtered": 100,
          "attached_condition": "trigcond(xf_thread_reply_ban_ReplyBans_8.thread_id = xf_thread.thread_id)"
        },
        "table": {
          "table_name": "xf_sv_collaboration_thread_user_CollaborativeUsers_9",
          "access_type": "ref",
          "possible_keys": ["PRIMARY", "thread_id_group_id"],
          "key": "thread_id_group_id",
          "key_length": "4",
          "used_key_parts": ["thread_id"],
          "ref": ["sbforums.xf_thread.thread_id"],
          "r_loops": 30,
          "rows": 1,
          "r_rows": 0,
          "r_total_time_ms": 0.046,
          "filtered": 100,
          "r_filtered": 100,
          "attached_condition": "trigcond(xf_sv_collaboration_thread_user_CollaborativeUsers_9.user_id = '7')"
        },
        "table": {
          "table_name": "xf_sv_collaboration_thread_group_Group_10",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY", "thread_id"],
          "r_loops": 30,
          "rows": 32,
          "r_rows": 32,
          "r_total_time_ms": 0.8304,
          "filtered": 100,
          "r_filtered": 0,
          "attached_condition": "trigcond(xf_sv_collaboration_thread_group_Group_10.thread_id = xf_sv_collaboration_thread_user_CollaborativeUsers_9.thread_id and xf_sv_collaboration_thread_group_Group_10.group_id = xf_sv_collaboration_thread_user_CollaborativeUsers_9.group_id)"
        }
      }
    }
    

    Code:
    {
      "query_block": {
        "select_id": 1,
        "r_loops": 1,
        "r_total_time_ms": 8702.5,
        "filesort": {
          "sort_key": "xf_thread.last_post_date desc",
          "r_loops": 1,
          "r_total_time_ms": 226.74,
          "r_limit": 30,
          "r_used_priority_queue": true,
          "r_output_rows": 31,
          "temporary_table": {
            "table": {
              "table_name": "xf_thread",
              "access_type": "ref",
              "possible_keys": [
                "node_id_last_post_date",
                "node_id_sticky_state_last_post"
              ],
              "key": "node_id_last_post_date",
              "key_length": "4",
              "used_key_parts": ["node_id"],
              "ref": ["const"],
              "r_loops": 1,
              "rows": 249467,
              "r_rows": 152730,
              "r_total_time_ms": 274.59,
              "filtered": 100,
              "r_filtered": 99.999,
              "attached_condition": "xf_thread.sticky = 0 and xf_thread.discussion_state in ('visible','deleted','moderated')"
            },
            "table": {
              "table_name": "xf_deletion_log_DeletionLog_1",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "31",
              "used_key_parts": ["content_type", "content_id"],
              "ref": ["const", "sbforums.xf_thread.thread_id"],
              "r_loops": 152729,
              "rows": 1,
              "r_rows": 0.0046,
              "r_total_time_ms": 160.97,
              "filtered": 100,
              "r_filtered": 100,
              "attached_condition": "trigcond(xf_deletion_log_DeletionLog_1.content_type = 'thread')"
            },
            "table": {
              "table_name": "xf_user_User_2",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["user_id"],
              "ref": ["sbforums.xf_thread.user_id"],
              "r_loops": 152729,
              "rows": 1,
              "r_rows": 0.9977,
              "r_total_time_ms": 206.94,
              "filtered": 100,
              "r_filtered": 100
            },
            "table": {
              "table_name": "xf_user_LastPoster_3",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["user_id"],
              "ref": ["sbforums.xf_thread.last_post_user_id"],
              "r_loops": 152729,
              "rows": 1,
              "r_rows": 0.9995,
              "r_total_time_ms": 194.38,
              "filtered": 100,
              "r_filtered": 100
            },
            "table": {
              "table_name": "xf_thread_read_Read_4",
              "access_type": "eq_ref",
              "possible_keys": ["user_id_thread_id", "thread_id"],
              "key": "user_id_thread_id",
              "key_length": "8",
              "used_key_parts": ["user_id", "thread_id"],
              "ref": ["const", "sbforums.xf_thread.thread_id"],
              "r_loops": 152729,
              "rows": 1,
              "r_rows": 0,
              "r_total_time_ms": 87.127,
              "filtered": 100,
              "r_filtered": 100
            },
            "table": {
              "table_name": "xf_thread_user_post_UserPosts_5",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "user_id"],
              "key": "PRIMARY",
              "key_length": "8",
              "used_key_parts": ["thread_id", "user_id"],
              "ref": ["sbforums.xf_thread.thread_id", "const"],
              "r_loops": 152729,
              "rows": 1,
              "r_rows": 0,
              "r_total_time_ms": 308.18,
              "filtered": 100,
              "r_filtered": 100
            },
            "table": {
              "table_name": "xf_thread_watch_Watch_6",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "thread_id_email_subscribe"],
              "key": "PRIMARY",
              "key_length": "8",
              "used_key_parts": ["user_id", "thread_id"],
              "ref": ["const", "sbforums.xf_thread.thread_id"],
              "r_loops": 152729,
              "rows": 1,
              "r_rows": 0,
              "r_total_time_ms": 167.67,
              "filtered": 100,
              "r_filtered": 100
            },
            "table": {
              "table_name": "xf_reaction_content_FirstPostReactions_7",
              "access_type": "eq_ref",
              "possible_keys": [
                "content_type_id_user_id",
                "user_like_date",
                "reaction_user_id_reaction_date",
                "content_type_id_reaction_date"
              ],
              "key": "content_type_id_user_id",
              "key_length": "35",
              "used_key_parts": ["content_type", "content_id", "reaction_user_id"],
              "ref": ["const", "sbforums.xf_thread.thread_id", "const"],
              "r_loops": 152729,
              "rows": 1,
              "r_rows": 0,
              "r_total_time_ms": 83.233,
              "filtered": 7.5e-7,
              "r_filtered": 100,
              "attached_condition": "trigcond(xf_reaction_content_FirstPostReactions_7.content_type = 'thread')"
            },
            "table": {
              "table_name": "xf_thread_reply_ban_ReplyBans_8",
              "access_type": "ref",
              "possible_keys": ["thread_id", "user_id"],
              "key": "user_id",
              "key_length": "4",
              "used_key_parts": ["user_id"],
              "ref": ["const"],
              "r_loops": 152729,
              "rows": 1,
              "r_rows": 0,
              "r_total_time_ms": 73.133,
              "filtered": 100,
              "r_filtered": 100,
              "attached_condition": "trigcond(xf_thread_reply_ban_ReplyBans_8.thread_id = xf_thread.thread_id)"
            },
            "table": {
              "table_name": "xf_sv_collaboration_thread_user_CollaborativeUsers_9",
              "access_type": "ref",
              "possible_keys": ["PRIMARY", "thread_id_group_id"],
              "key": "thread_id_group_id",
              "key_length": "4",
              "used_key_parts": ["thread_id"],
              "ref": ["sbforums.xf_thread.thread_id"],
              "r_loops": 152729,
              "rows": 1,
              "r_rows": 0,
              "r_total_time_ms": 81.822,
              "filtered": 100,
              "r_filtered": 100,
              "attached_condition": "trigcond(xf_sv_collaboration_thread_user_CollaborativeUsers_9.user_id = '7')"
            },
            "block-nl-join": {
              "table": {
                "table_name": "xf_sv_collaboration_thread_group_Group_10",
                "access_type": "ALL",
                "possible_keys": ["PRIMARY", "thread_id"],
                "r_loops": 189,
                "rows": 32,
                "r_rows": 32,
                "r_total_time_ms": 4.7888,
                "filtered": 100,
                "r_filtered": 100
              },
              "buffer_type": "flat",
              "buffer_size": "512Kb",
              "join_type": "BNL",
              "attached_condition": "trigcond(xf_sv_collaboration_thread_group_Group_10.thread_id = xf_sv_collaboration_thread_user_CollaborativeUsers_9.thread_id and xf_sv_collaboration_thread_group_Group_10.group_id = xf_sv_collaboration_thread_user_CollaborativeUsers_9.group_id)",
              "r_filtered": 3.125
            }
          }
        }
      }
    }
    

    Code:
    +------+-------------+------------------------------------------------------+--------+-----------------------------------------------------------------------------------------------------+-------------------------+---------+------------------------------------------+--------+--------+----------+------------+-------------+
    | id   | select_type | table                                                | type   | possible_keys                                                                                       | key                     | key_len | ref                                      | rows   | r_rows | filtered | r_filtered | Extra       |
    +------+-------------+------------------------------------------------------+--------+-----------------------------------------------------------------------------------------------------+-------------------------+---------+------------------------------------------+--------+--------+----------+------------+-------------+
    |    1 | SIMPLE      | xf_thread                                            | ref    | node_id_last_post_date,node_id_sticky_state_last_post                                               | node_id_last_post_date  | 4       | const                                    | 249467 | 30.00  |   100.00 |     100.00 | Using where |
    |    1 | SIMPLE      | xf_deletion_log_DeletionLog_1                        | eq_ref | PRIMARY                                                                                             | PRIMARY                 | 31      | const,sbforums.xf_thread.thread_id       | 1      | 0.00   |   100.00 |     100.00 | Using where |
    |    1 | SIMPLE      | xf_user_User_2                                       | eq_ref | PRIMARY                                                                                             | PRIMARY                 | 4       | sbforums.xf_thread.user_id               | 1      | 1.00   |   100.00 |     100.00 |             |
    |    1 | SIMPLE      | xf_user_LastPoster_3                                 | eq_ref | PRIMARY                                                                                             | PRIMARY                 | 4       | sbforums.xf_thread.last_post_user_id     | 1      | 1.00   |   100.00 |     100.00 |             |
    |    1 | SIMPLE      | xf_thread_read_Read_4                                | eq_ref | user_id_thread_id,thread_id                                                                         | user_id_thread_id       | 8       | const,sbforums.xf_thread.thread_id       | 1      | 0.00   |   100.00 |     100.00 |             |
    |    1 | SIMPLE      | xf_thread_user_post_UserPosts_5                      | eq_ref | PRIMARY,user_id                                                                                     | PRIMARY                 | 8       | sbforums.xf_thread.thread_id,const       | 1      | 0.00   |   100.00 |     100.00 |             |
    |    1 | SIMPLE      | xf_thread_watch_Watch_6                              | eq_ref | PRIMARY,thread_id_email_subscribe                                                                   | PRIMARY                 | 8       | const,sbforums.xf_thread.thread_id       | 1      | 0.00   |   100.00 |     100.00 |             |
    |    1 | SIMPLE      | xf_reaction_content_FirstPostReactions_7             | eq_ref | content_type_id_user_id,user_like_date,reaction_user_id_reaction_date,content_type_id_reaction_date | content_type_id_user_id | 35      | const,sbforums.xf_thread.thread_id,const | 1      | 0.00   |     0.00 |     100.00 | Using where |
    |    1 | SIMPLE      | xf_thread_reply_ban_ReplyBans_8                      | ref    | thread_id,user_id                                                                                   | user_id                 | 4       | const                                    | 1      | 0.00   |   100.00 |     100.00 | Using where |
    |    1 | SIMPLE      | xf_sv_collaboration_thread_user_CollaborativeUsers_9 | ref    | PRIMARY,thread_id_group_id                                                                          | thread_id_group_id      | 4       | sbforums.xf_thread.thread_id             | 1      | 0.00   |   100.00 |     100.00 | Using where |
    |    1 | SIMPLE      | xf_sv_collaboration_thread_group_Group_10            | ALL    | PRIMARY,thread_id                                                                                   | NULL                    | NULL    | NULL                                     | 32     | 32.00  |   100.00 |       0.00 | Using where |
    +------+-------------+------------------------------------------------------+--------+-----------------------------------------------------------------------------------------------------+-------------------------+---------+------------------------------------------+--------+--------+----------+------------+-------------+
    
    
    vs
    Code:
    +------+-------------+------------------------------------------------------+--------+-----------------------------------------------------------------------------------------------------+-------------------------+---------+------------------------------------------+--------+-----------+----------+------------+-------------------------------------------------+
    | id   | select_type | table                                                | type   | possible_keys                                                                                       | key                     | key_len | ref                                      | rows   | r_rows    | filtered | r_filtered | Extra                                           |
    +------+-------------+------------------------------------------------------+--------+-----------------------------------------------------------------------------------------------------+-------------------------+---------+------------------------------------------+--------+-----------+----------+------------+-------------------------------------------------+
    |    1 | SIMPLE      | xf_thread                                            | ref    | node_id_last_post_date,node_id_sticky_state_last_post                                               | node_id_last_post_date  | 4       | const                                    | 249467 | 152730.00 |   100.00 |     100.00 | Using where; Using temporary; Using filesort    |
    |    1 | SIMPLE      | xf_deletion_log_DeletionLog_1                        | eq_ref | PRIMARY                                                                                             | PRIMARY                 | 31      | const,sbforums.xf_thread.thread_id       | 1      | 0.00      |   100.00 |     100.00 | Using where                                     |
    |    1 | SIMPLE      | xf_user_User_2                                       | eq_ref | PRIMARY                                                                                             | PRIMARY                 | 4       | sbforums.xf_thread.user_id               | 1      | 1.00      |   100.00 |     100.00 |                                                 |
    |    1 | SIMPLE      | xf_user_LastPoster_3                                 | eq_ref | PRIMARY                                                                                             | PRIMARY                 | 4       | sbforums.xf_thread.last_post_user_id     | 1      | 1.00      |   100.00 |     100.00 |                                                 |
    |    1 | SIMPLE      | xf_thread_read_Read_4                                | eq_ref | user_id_thread_id,thread_id                                                                         | user_id_thread_id       | 8       | const,sbforums.xf_thread.thread_id       | 1      | 0.00      |   100.00 |     100.00 |                                                 |
    |    1 | SIMPLE      | xf_thread_user_post_UserPosts_5                      | eq_ref | PRIMARY,user_id                                                                                     | PRIMARY                 | 8       | sbforums.xf_thread.thread_id,const       | 1      | 0.00      |   100.00 |     100.00 |                                                 |
    |    1 | SIMPLE      | xf_thread_watch_Watch_6                              | eq_ref | PRIMARY,thread_id_email_subscribe                                                                   | PRIMARY                 | 8       | const,sbforums.xf_thread.thread_id       | 1      | 0.00      |   100.00 |     100.00 |                                                 |
    |    1 | SIMPLE      | xf_reaction_content_FirstPostReactions_7             | eq_ref | content_type_id_user_id,user_like_date,reaction_user_id_reaction_date,content_type_id_reaction_date | content_type_id_user_id | 35      | const,sbforums.xf_thread.thread_id,const | 1      | 0.00      |     0.00 |     100.00 | Using where                                     |
    |    1 | SIMPLE      | xf_thread_reply_ban_ReplyBans_8                      | ref    | thread_id,user_id                                                                                   | user_id                 | 4       | const                                    | 1      | 0.00      |   100.00 |     100.00 | Using where                                     |
    |    1 | SIMPLE      | xf_sv_collaboration_thread_user_CollaborativeUsers_9 | ref    | PRIMARY,thread_id_group_id                                                                          | thread_id_group_id      | 4       | sbforums.xf_thread.thread_id             | 1      | 0.00      |   100.00 |     100.00 | Using where                                     |
    |    1 | SIMPLE      | xf_sv_collaboration_thread_group_Group_10            | ALL    | PRIMARY,thread_id                                                                                   | NULL                    | NULL    | NULL                                     | 32     | 32.00     |   100.00 |       3.12 | Using where; Using join buffer (flat, BNL join) |
    +------+-------------+------------------------------------------------------+--------+-----------------------------------------------------------------------------------------------------+-------------------------+---------+------------------------------------------+--------+-----------+----------+------------+-------------------------------------------------+
    
    For some insane reason it is doing a full-table scan of xf_thread table, which in MariaDB 10.4 is ignoring the limit clause

    The problem appears to be the xf_sv_collaboration_thread_group table, which isn't using an index but resulting to a full-table scan. Adding force index(PRIMARY) makes things behave with MariaDB 10.4, but I can't actually do that with XenForo 2's finder & relations :(

    Note; setting join_cache_level > 2 causes the force index on the xf_sv_collaboration_thread_group join to no longer work, and things are even slower

    mrr=off,mrr_sort_keys=off; no forced indexes;

    join_cache_level=0; 0.003 sec
    join_cache_level=2; 8.612 sec
    join_cache_level=3; 1 min 9.761 sec
    join_cache_level=4; 20.065 sec
    join_cache_level=5; 8.766 sec
    join_cache_level=6; 8.689 sec
    join_cache_level=7; 8.612 sec
    join_cache_level=8; 8.746 sec


    mrr=on,mrr_sort_keys=on; no forced indexes;
    join_cache_level=0; 0.003 sec
    join_cache_level=1; 8.596 sec
    join_cache_level=2; 8.876 sec
    join_cache_level=3; 1 min 10.893 sec
    join_cache_level=4; 20.135 sec
    join_cache_level=5; 10.653 sec
    join_cache_level=6; 9.839 sec
    join_cache_level=7; 10.588 sec
    join_cache_level=8; 10.046 sec

    Increasing join_buffer_space_limit from ~2mb to 128mb helps when mrr is on, but helps is shave a second or so off. Not dropping the query down to sub 5 milliseconds range.
     
    Last edited: Oct 22, 2019
  14. eva2000

    eva2000 Administrator Staff Member

    44,804
    10,216
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,834
    Local Time:
    1:32 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    Interesting indeed thanks for sharing that. I see what you mean now, was browsing MariaDB Jira bug tracker and seems like a few regressions and bugs in MariaDB 10.4 Optimizer have crept in.

    Curious what's your server's settings for join_buffer_space_limit and join_buffer_size ? Tried playing with those values as well with optimize_join_buffer_size=off and on ?
     
  15. Xon

    Xon Active Member

    170
    61
    28
    Nov 16, 2015
    Ratings:
    +223
    Local Time:
    11:32 AM
    1.15.x
    MariaDB 10.3.x
    A modded version* of my Xenforo2 add-on Optimized List Queries can rewrite queries to;

    *the released version has a design where it never applies to the first page, which doesn't help here
    Code:
    analyze
    SELECT `xf_thread`.*, `xf_deletion_log_DeletionLog_1`.*, `xf_user_User_2`.*, `xf_user_LastPoster_3`.*, `xf_thread_read_Read_4`.*, `xf_thread_user_post_UserPosts_5`.*, `xf_thread_watch_Watch_6`.*, `xf_reaction_content_FirstPostReactions_7`.*, `xf_thread_reply_ban_ReplyBans_8`.*, `xf_sv_collaboration_thread_user_CollaborativeUsers_9`.*, `xf_sv_collaboration_thread_group_Group_10`.*
    FROM (
    select thread_id
    from `xf_thread`
    WHERE ((`xf_thread`.`discussion_state` = 'visible')) AND (`xf_thread`.`node_id` = 4) AND (`xf_thread`.`sticky` = 0)
    ORDER BY `xf_thread`.`last_post_date` DESC
    limit 30
    ) a
    join `xf_thread` on `xf_thread`.thread_id = a.thread_id
    LEFT JOIN `xf_deletion_log` AS `xf_deletion_log_DeletionLog_1` ON (`xf_deletion_log_DeletionLog_1`.`content_type` = 'thread' AND `xf_deletion_log_DeletionLog_1`.`content_id` = `xf_thread`.`thread_id`)
    LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_thread`.`user_id`)
    LEFT JOIN `xf_user` AS `xf_user_LastPoster_3` ON (`xf_user_LastPoster_3`.`user_id` = `xf_thread`.`last_post_user_id`)
    LEFT JOIN `xf_thread_read` AS `xf_thread_read_Read_4` ON (`xf_thread_read_Read_4`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_read_Read_4`.`user_id` = '7')
    LEFT JOIN `xf_thread_user_post` AS `xf_thread_user_post_UserPosts_5` ON (`xf_thread_user_post_UserPosts_5`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_user_post_UserPosts_5`.`user_id` = '7')
    LEFT JOIN `xf_thread_watch` AS `xf_thread_watch_Watch_6` ON (`xf_thread_watch_Watch_6`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_watch_Watch_6`.`user_id` = '7')
    LEFT JOIN `xf_reaction_content` AS `xf_reaction_content_FirstPostReactions_7` ON (`xf_reaction_content_FirstPostReactions_7`.`content_type` = 'thread' AND `xf_reaction_content_FirstPostReactions_7`.`content_id` = `xf_thread`.`thread_id` AND `xf_reaction_content_FirstPostReactions_7`.`reaction_user_id` = '7')
    LEFT JOIN `xf_thread_reply_ban` AS `xf_thread_reply_ban_ReplyBans_8` ON (`xf_thread_reply_ban_ReplyBans_8`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_reply_ban_ReplyBans_8`.`user_id` = '7')
    LEFT JOIN `xf_sv_collaboration_thread_user` AS `xf_sv_collaboration_thread_user_CollaborativeUsers_9` ON (`xf_sv_collaboration_thread_user_CollaborativeUsers_9`.`thread_id` = `xf_thread`.`thread_id` AND `xf_sv_collaboration_thread_user_CollaborativeUsers_9`.`user_id` = '7')
    LEFT JOIN `xf_sv_collaboration_thread_group` AS `xf_sv_collaboration_thread_group_Group_10` force index(primary) ON (`xf_sv_collaboration_thread_group_Group_10`.`thread_id` = `xf_sv_collaboration_thread_user_CollaborativeUsers_9`.`thread_id` AND `xf_sv_collaboration_thread_group_Group_10`.`group_id` = `xf_sv_collaboration_thread_user_CollaborativeUsers_9`.`group_id`)
    WHERE ((`xf_thread`.`discussion_state` = 'visible')) AND (`xf_thread`.`node_id` = 4) AND (`xf_thread`.`sticky` = 0)
    ORDER BY `xf_thread`.`last_post_date` DESC
    limit 30;
    
    Which gives this query plan;
    Code:
    +------+-------------+------------------------------------------------------+--------+-----------------------------------------------------------------------------------------------------+--------------------------------+---------+------------------------------------------------------------------------------------------------------------------------------------------------+--------+--------+----------+------------+-------------------------------------------------+
    | id   | select_type | table                                                | type   | possible_keys                                                                                       | key                            | key_len | ref                                                                                                                                            | rows   | r_rows | filtered | r_filtered | Extra                                           |
    +------+-------------+------------------------------------------------------+--------+-----------------------------------------------------------------------------------------------------+--------------------------------+---------+------------------------------------------------------------------------------------------------------------------------------------------------+--------+--------+----------+------------+-------------------------------------------------+
    |    1 | PRIMARY     | <derived2>                                           | ALL    | NULL                                                                                                | NULL                           | NULL    | NULL                                                                                                                                           | 30     | 30.00  |   100.00 |     100.00 | Using temporary; Using filesort                 |
    |    1 | PRIMARY     | xf_thread                                            | eq_ref | PRIMARY,node_id_last_post_date,node_id_sticky_state_last_post                                       | PRIMARY                        | 4       | a.thread_id                                                                                                                                    | 1      | 1.00   |    50.00 |     100.00 | Using where                                     |
    |    1 | PRIMARY     | xf_deletion_log_DeletionLog_1                        | eq_ref | PRIMARY                                                                                             | PRIMARY                        | 31      | const,a.thread_id                                                                                                                              | 1      | 0.00   |   100.00 |     100.00 | Using where                                     |
    |    1 | PRIMARY     | xf_user_User_2                                       | eq_ref | PRIMARY                                                                                             | PRIMARY                        | 4       | sbforums.xf_thread.user_id                                                                                                                     | 1      | 1.00   |   100.00 |     100.00 |                                                 |
    |    1 | PRIMARY     | xf_user_LastPoster_3                                 | eq_ref | PRIMARY                                                                                             | PRIMARY                        | 4       | sbforums.xf_thread.last_post_user_id                                                                                                           | 1      | 1.00   |   100.00 |     100.00 |                                                 |
    |    1 | PRIMARY     | xf_thread_read_Read_4                                | eq_ref | user_id_thread_id,thread_id                                                                         | user_id_thread_id              | 8       | const,a.thread_id                                                                                                                              | 1      | 0.00   |   100.00 |     100.00 |                                                 |
    |    1 | PRIMARY     | xf_thread_user_post_UserPosts_5                      | eq_ref | PRIMARY,user_id                                                                                     | PRIMARY                        | 8       | a.thread_id,const                                                                                                                              | 1      | 0.00   |   100.00 |     100.00 |                                                 |
    |    1 | PRIMARY     | xf_thread_watch_Watch_6                              | range  | PRIMARY,thread_id_email_subscribe                                                                   | PRIMARY                        | 4       | NULL                                                                                                                                           | 1      | 0.00   |   100.00 |     100.00 | Using where; Using join buffer (flat, BNL join) |
    |    1 | PRIMARY     | xf_reaction_content_FirstPostReactions_7             | eq_ref | content_type_id_user_id,user_like_date,reaction_user_id_reaction_date,content_type_id_reaction_date | content_type_id_user_id        | 35      | const,a.thread_id,const                                                                                                                        | 1      | 0.00   |     0.00 |     100.00 | Using where                                     |
    |    1 | PRIMARY     | xf_thread_reply_ban_ReplyBans_8                      | ref    | thread_id,user_id                                                                                   | user_id                        | 4       | const                                                                                                                                          | 1      | 0.00   |   100.00 |     100.00 | Using where                                     |
    |    1 | PRIMARY     | xf_sv_collaboration_thread_user_CollaborativeUsers_9 | ref    | PRIMARY,thread_id_group_id                                                                          | thread_id_group_id             | 4       | a.thread_id                                                                                                                                    | 1      | 0.00   |   100.00 |     100.00 | Using where                                     |
    |    1 | PRIMARY     | xf_sv_collaboration_thread_group_Group_10            | eq_ref | PRIMARY                                                                                             | PRIMARY                        | 8       | sbforums.xf_sv_collaboration_thread_user_CollaborativeUsers_9.group_id,sbforums.xf_sv_collaboration_thread_user_CollaborativeUsers_9.thread_id | 1      | 0.00   |   100.00 |     100.00 | Using where                                     |
    |    2 | DERIVED     | xf_thread                                            | ref    | node_id_last_post_date,node_id_sticky_state_last_post                                               | node_id_sticky_state_last_post | 6       | const,const,const                                                                                                                              | 249469 | 30.00  |   100.00 |     100.00 | Using where; Using index                        |
    +------+-------------+------------------------------------------------------+--------+-----------------------------------------------------------------------------------------------------+--------------------------------+---------+------------------------------------------------------------------------------------------------------------------------------------------------+--------+--------+----------+------------+-------------------------------------------------+
    
    The subquery makes the query planned realize it will only be joining ~30 rows; not the table size. Bit more expensive at 0.004 seconds than without the rewrite and join_cache_level=0

    join_buffer_size=512kb
    join_buffer_space_limit=2mb

    Changing to;
    join_buffer_size=512kb
    join_buffer_space_limit=128mb

    Shaves a second off. Changing the optimizer switch settings don't help either :(

    But this is fuzzying around the edges; Mariadb 10.4 is ignoring the limit clause before fetching from the starting table. 30 rows vs 249469 rows (filtered down to 152732 rows) is such a dominating performance issue :(
     
  16. eva2000

    eva2000 Administrator Staff Member

    44,804
    10,216
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,834
    Local Time:
    1:32 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    Fingers crossed then that MariaDB does eventually fix this.
     
  17. Xon

    Xon Active Member

    170
    61
    28
    Nov 16, 2015
    Ratings:
    +223
    Local Time:
    11:32 AM
    1.15.x
    MariaDB 10.3.x
    I ended up rolling back from Mariadb 10.4 => 10.3. Using mydumper to create a logical dump then myloader to reload it.

    Major issue I ran into is that the 3-node galera cluster had a node reboot, and then that instance just refused to rejoin the cluster. It would refuse todo the delta state transfer, and try todo a full state transfer. Worse, once a node did do a full state transfer; the joining process would get stuck in the applying delta stats catchup but never actually catch up. Interrupting this process would trigger another full state transfer.

    During testing of trying to get the failing node back; the reseeding node crashed. And crashed the doner node; trigger both nodes to want todo a full-reseed from the remaining node.

    Restarting the cluster from scratch off the surviving node didn't help either.

    And finally I encountered a bug where proxysql would send queries to a galera cluster node with wresp_ready=off but everything else looking good. But those queries would get black-holed as the node wasn't actually ready...

    Keep in mind; beyond patching the kernel nothing else had changed after a reboot!
     
  18. eva2000

    eva2000 Administrator Staff Member

    44,804
    10,216
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,834
    Local Time:
    1:32 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    Thanks for sharing! Seems a messy situation when you throw MariaDB Galera Clustering into that mix for downgrades !

    Guess my personal rule of thumb when it comes to MariaDB major upgrades still holds true - that is wait until the minor version increments to at least .1x or .2x i.e MariaDB 10.4.10+ or 10.4.20+ :D Though MariaDB 10.3 was an exception :)

    Though MariaDB Jira bug tracker for Optimizer component is very revealing in that there's still show many queued bug reports yet to be resolved some going back to MariaDB 10.0/10.1 all the way to 10.4 and even their 10.5 development.
     
  19. diy

    diy Premium Member Premium Member

    44
    28
    18
    Jan 14, 2019
    USA
    Ratings:
    +43
    Local Time:
    11:32 PM
    nginx 1.17.x
    MariaDB 10.4.x
    This is scary! Think I'm gonna roll back to 10.3 right away too. :-(
     
  20. BamaStangGuy

    BamaStangGuy Premium Member Premium Member

    650
    188
    43
    May 25, 2014
    Ratings:
    +263
    Local Time:
    10:32 PM
    Running 10.4 with a Wordpress blog and haven't had any issues.