We've noticed that during high loads scenarios (ie: cache busting attacks) the 10.6 hosts are the ones that suffer the most and the symptoms were always the same: high number of connections running and lag. Part of the investigation was done at T308380: 2022-05-14 Databases but let's use this task to coordinate and simulate scenarios.
After all the 10.6 testing and tweaking the configuration difference was:
41 config differences Variable db1118 db1132 ========================= ========================= ========================= basedir /opt/wmf-mariadb104 /opt/wmf-mariadb106 binlog_format STATEMENT ROW character_set_system utf8 utf8mb3 character_sets_dir /opt/wmf-mariadb104/sh... /opt/wmf-mariadb106/sh... expire_logs_days 30 30.000000 general_log_file db1118.log db1132.log gtid_binlog_pos 171970572-171970572-26... 171970572-171970572-26... gtid_binlog_state 171970572-171970572-26... 171970572-171970572-26... gtid_current_pos 0-171970637-5484646134... 0-171970637-5484646134... gtid_domain_id 171970572 171970595 gtid_slave_pos 0-171970637-5484646134... 0-171970637-5484646134... hostname db1118 db1132 innodb_buffer_pool_size 405874409472 404800667648 innodb_lru_scan_depth 1024 1536 innodb_max_dirty_pages... 75.000000 90.000000 innodb_stats_transient... 16 8 innodb_version 10.4.22 10.6.8 log_bin_basename /srv/sqldata/db1118-bin /srv/sqldata/db1132-bin log_bin_index /srv/sqldata/db1118-bi... /srv/sqldata/db1132-bi... max_recursive_iterations 4294967295 1000 old_mode UTF8_IS_UTF8MB3 optimizer_switch index_merge=on,index_m... index_merge=on,index_m... performance_schema_max... 50 80 performance_schema_max... 3077 -1 performance_schema_max... 200 210 performance_schema_max... 40 50 performance_schema_max... 202 222 performance_schema_max... 4000 -1 performance_schema_set... 100 -1 performance_schema_set... 100 -1 performance_schema_use... 100 -1 pid_file /srv/sqldata/db1118.pid /srv/sqldata/db1132.pid plugin_dir /opt/wmf-mariadb104/li... /opt/wmf-mariadb106/li... report_host db1118.eqiad.wmnet db1132.eqiad.wmnet server_id 171970572 171970595 slave_parallel_mode conservative optimistic slow_query_log_file db1118-slow.log db1132-slow.log version 10.4.22-MariaDB-log 10.6.8-MariaDB-log version_source_revision a19ab67318760f8f155ef7... b2187662bcba12b66667bc... version_ssl_library OpenSSL 1.1.1n 15 Mar... OpenSSL 1.1.1k 25 Mar... wsrep_node_name db1118 db1132
Most of the configuration differences didn't seem relevant to cause this sort of behaviour.
However, they were changed to 100% mimic what we currently have on 10.4 with the following changes:
# for i in db1111 db1132 db1127 db1137 db1143; do echo $i; db-mysql $i -e "set global innodb_stats_transient_sample_pages = 16" ; done db1111 db1132 db1127 db1137 db1143 # for i in db1111 db1132 db1127 db1137 db1143; do echo $i; db-mysql $i -e "set global innodb_lru_scan_depth = 1024" ; done db1111 db1132 db1127 db1137 db1143
The optimizer switch differences:
10.6:
optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=off,condition_pushdown_from_having=on,not_null_range_scan=off
The main difference is 10.6 having not_null_range_scan=off
10.4
optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=off,condition_pushdown_from_having=on That option, as can be seen, is disabled by default anyways (https://mariadb.com/kb/en/not_null_range_scan-optimization/) and it is new on 10.5 (and higher) so it should be noop in this case.
10.6 full my.cnf: https://phabricator.wikimedia.org/P31391
The other changes were made on puppet already.
The hist of hosts we have serving in sX sections are:
s1: db1132
s4: db1143
s7: db1127
s8: db1111
We have others on esX, x1 and pc1 but those are not expected to have issues for now, as the load there is a lot smaller.
I would like to request @jcrespo and @Ladsgroup to help with this stress testing to be able to simulate load and especially mediawiki load to try to catch what the issue could be.
Update - September 2022:
Most likely caused by:
The fix: https://github.com/MariaDB/server/commit/92032499874259bae7455130958ea7f38c4d53a3