mysqldump – Wait state: Statistics

We have 200 databases in one instance of MariaDB with a total of 370.000 tables, since we upgraded to MariaDB 10.5.11, we see most of time passed by mysql is in wait states: Statistics.

According MariaDB, it should be a brief state:
Calculating statistics as part of deciding on a query execution plan. Usually a brief state unless the server is disk-bound.
But we don’t see particular high usage of the disk.

Trying to help this situation, we generated all Engine-Independent Table Statistics by analyzing all tables(370.000), our use_stat_tables is set to preferably_for_queries to use those stats instead of innodb one’s. No real improvement.

We also tried to switch optimizer_search_depth to 0, no improvement either.

And another consequence of this huge time passed calculating statistics, it increased by a lot our daily backup time with mysqldump of the 200 databases (before 30 min, now around 5 hours!) which crashes the server most of time, because the memory increased after each database backup, until the server swap.

Some more details, after 11 hours running

With mariadb 10.1 – 2 weeks
Statistics in Mariadb 10.1 for 2 weeks are really low

With MariaDB 10.5 – 2 weeks
Statistics in Mariadb 10.5 for 2 weeks

We just received some advice to increase the open-files-limit, we still have to do it in production, and then raise table_open_cache (for now it’s table_open_cache = 20000, table_definition-cache = 40000)
Now max open files for mariadb is set to 32768:

cat /proc/$(pidof mariadbd)/limits
Limit Soft Limit Hard Limit Units
Max cpu time unlimited unlimited seconds
Max file size unlimited unlimited bytes
Max data size unlimited unlimited bytes
Max stack size 8388608 unlimited bytes
Max core file size 0 unlimited bytes
Max resident set unlimited unlimited bytes
Max processes 160172 160172 processes
Max open files 32768 32768 files
Max locked memory 65536 65536 bytes
Max address space unlimited unlimited bytes
Max file locks unlimited unlimited locks
Max pending signals 160172 160172 signals
Max msgqueue size 819200 819200 bytes
Max nice priority 0 0
Max realtime priority 0 0
Max realtime timeout unlimited unlimited us

We are running out of ideas.
Any help will be really appreciated.

Here is the output of ‘show global status’

| Aborted_clients                                        | 613                                              |
| Aborted_connects                                       | 1                                                |
| Aria_pagecache_blocks_not_flushed                      | 0                                                |
| Aria_pagecache_blocks_unused                           | 4                                                |
| Aria_pagecache_blocks_used                             | 15647                                            |
| Aria_pagecache_read_requests                           | 23111163                                         |
| Aria_pagecache_reads                                   | 190134                                           |
| Aria_pagecache_write_requests                          | 1085362                                          |
| Aria_pagecache_writes                                  | 162904                                           |
| Aria_transaction_log_syncs                             | 108                                              |
| Binlog_commits                                         | 884378                                           |
| Binlog_group_commits                                   | 884287                                           |
| Binlog_group_commit_trigger_count                      | 0                                                |
| Binlog_group_commit_trigger_lock_wait                  | 0                                                |
| Binlog_group_commit_trigger_timeout                    | 0                                                |
| Binlog_snapshot_file                                   | mysql-bin.000038                                 |
| Binlog_snapshot_position                               | 552835703                                        |
| Binlog_bytes_written                                   | 1626601753                                       |
| Binlog_cache_disk_use                                  | 4811                                             |
| Binlog_cache_use                                       | 882274                                           |
| Binlog_stmt_cache_disk_use                             | 18                                               |
| Binlog_stmt_cache_use                                  | 2104                                             |
| Busy_time                                              | 0.000000                                         |
| Bytes_received                                         | 8740754817                                       |
| Bytes_sent                                             | 80380675660                                      |
| Compression                                            | OFF                                                                                           |
| Connections                                            | 143465                                           |
| Cpu_time                                               | 0.000000                                         |
| Created_tmp_disk_tables                                | 43263                                            |
| Created_tmp_files                                      | 4545                                             |
| Created_tmp_tables                                     | 961351                                                                                         |
| Delete_scan                                            | 2230                                             |
| Empty_queries                                          | 19369816                                         
| Handler_commit                                         | 42120185                                         |
| Handler_delete                                         | 451478                                           |
| Handler_discover                                       | 80                                               |
| Handler_external_lock                                  | 0                                                |
| Handler_icp_attempts                                   | 666402417                                        |
| Handler_icp_match                                      | 666047991                                        |
| Handler_mrr_init                                       | 0                                                |
| Handler_mrr_key_refills                                | 0                                                |
| Handler_mrr_rowid_refills                              | 0                                                |
| Handler_prepare                                        | 2037194                                          |
| Handler_read_first                                     | 1982567                                          |
| Handler_read_key                                       | 209265796                                        |
| Handler_read_last                                      | 37519                                            |
| Handler_read_next                                      | 1432606756                                       |
| Handler_read_prev                                      | 31759933                                         |
| Handler_read_retry                                     | 0                                                |
| Handler_read_rnd                                       | 41884968                                         |
| Handler_read_rnd_deleted                               | 17169                                            |
| Handler_read_rnd_next                                  | 1168367173                                       |
| Handler_rollback                                       | 13                                               |
| Handler_savepoint                                      | 7376                                             |
| Handler_savepoint_rollback                             | 0                                                |
| Handler_tmp_delete                                     | 0                                                |
| Handler_tmp_update                                     | 3760231                                          |
| Handler_tmp_write                                      | 108494398                                        |
| Handler_update                                         | 723675                                           |
| Handler_write                                          | 468014                                           |
| Innodb_adaptive_hash_hash_searches                     | 0                                                |
| Innodb_adaptive_hash_non_hash_searches                 | 1124586899                                       |
| Innodb_background_log_sync                             | 39208                                            |
| Innodb_buffer_pool_dump_status                         |                                                  |
| Innodb_buffer_pool_load_status                         | Buffer pool(s) load completed at 210719  4:50:16 |
| Innodb_buffer_pool_resize_status                       |                                                  |
| Innodb_buffer_pool_load_incomplete                     | OFF                                              |
| Innodb_buffer_pool_pages_data                          | 840319                                           |
| Innodb_buffer_pool_bytes_data                          | 13767786496                                      |
| Innodb_buffer_pool_pages_dirty                         | 45657                                            |
| Innodb_buffer_pool_bytes_dirty                         | 748044288                                        |
| Innodb_buffer_pool_pages_flushed                       | 206518                                           |
| Innodb_buffer_pool_pages_free                          | 321041                                           |
| Innodb_buffer_pool_pages_made_not_young                | 12973877                                         |
| Innodb_buffer_pool_pages_made_young                    | 634254                                           |
| Innodb_buffer_pool_pages_misc                          | 0                                                |
| Innodb_buffer_pool_pages_old                           | 310176                                           |
| Innodb_buffer_pool_pages_total                         | 1161360                                          |
| Innodb_buffer_pool_pages_lru_flushed                   | 0                                                |
| Innodb_buffer_pool_read_ahead_rnd                      | 0                                                |
| Innodb_buffer_pool_read_ahead                          | 79                                               |
| Innodb_buffer_pool_read_ahead_evicted                  | 0                                                |
| Innodb_buffer_pool_read_requests                       | 4634798929                                       |
| Innodb_buffer_pool_reads                               | 799144                                           |
| Innodb_buffer_pool_wait_free                           | 0                                                |
| Innodb_buffer_pool_write_requests                      | 12861326                                         |
| Innodb_checkpoint_age                                  | 406077659                                        |
| Innodb_checkpoint_max_age                              | 434155992                                        |
| Innodb_data_fsyncs                                     | 55314                                            |
| Innodb_data_pending_fsyncs                             | 0                                                |
| Innodb_data_pending_reads                              | 0                                                |
| Innodb_data_pending_writes                             | 0                                                |
| Innodb_data_read                                       | 13094846464                                      |
| Innodb_data_reads                                      | 871143                                           |
| Innodb_data_writes                                     | 1116053                                          |
| Innodb_data_written                                    | 2917482773                                       |
| Innodb_dblwr_pages_written                             | 131349                                           |
| Innodb_dblwr_writes                                    | 1077                                             |
| Innodb_deadlocks                                       | 0                                                |
| Innodb_history_list_length                             | 3                                                |
| Innodb_ibuf_discarded_delete_marks                     | 0                                                |
| Innodb_ibuf_discarded_deletes                          | 0                                                |
| Innodb_ibuf_discarded_inserts                          | 0                                                |
| Innodb_ibuf_free_list                                  | 1436                                             |
| Innodb_ibuf_merged_delete_marks                        | 381882                                           |
| Innodb_ibuf_merged_deletes                             | 4726                                             |
| Innodb_ibuf_merged_inserts                             | 39635                                            |
| Innodb_ibuf_merges                                     | 7900                                             |
| Innodb_ibuf_segment_size                               | 1943                                             |
| Innodb_ibuf_size                                       | 506                                              |
| Innodb_log_waits                                       | 0                                                |
| Innodb_log_write_requests                              | 341852                                           |
| Innodb_log_writes                                      | 936910                                           |
| Innodb_lsn_current                                     | 69175355827                                      |
| Innodb_lsn_flushed                                     | 69175354210                                      |
| Innodb_lsn_last_checkpoint                             | 68769278168                                      |
| Innodb_master_thread_active_loops                      | 30283                                            |
| Innodb_master_thread_idle_loops                        | 8926                                             |
| Innodb_max_trx_id                                      | 96623855                                         |
| Innodb_mem_adaptive_hash                               | 0                                                |
| Innodb_mem_dictionary                                  | 211733568                                        |
| Innodb_os_log_fsyncs                                   | 32811                                            |
| Innodb_os_log_pending_fsyncs                           | 0                                                |
| Innodb_os_log_pending_writes                           | 0                                                |
| Innodb_os_log_written                                  | 3097520128                                       |
| Innodb_page_size                                       | 16384                                            |
| Innodb_pages_created                                   | 82283                                            |
| Innodb_pages_read                                      | 799195                                           |
| Innodb_pages_written                                   | 178087                                           |
| Innodb_row_lock_current_waits                          | 0                                                |
| Innodb_row_lock_time                                   | 23594                                            |
| Innodb_row_lock_time_avg                               | 291                                              |
| Innodb_row_lock_time_max                               | 20972                                            |
| Innodb_row_lock_waits                                  | 81                                               |
| Innodb_rows_deleted                                    | 451067                                           |
| Innodb_rows_inserted                                   | 466012                                           |
| Innodb_rows_read                                       | 2767518874                                       |
| Innodb_rows_updated                                    | 291502                                           |
| Innodb_system_rows_deleted                             | 0                                                |
| Innodb_system_rows_inserted                            | 0                                                |
| Innodb_system_rows_read                                | 205025                                           |
| Innodb_system_rows_updated                             | 0                                                |
| Innodb_num_open_files                                  | 16144                                            |
| Innodb_secondary_index_triggered_cluster_reads         | 733372971                                                                                 |
| Key_blocks_not_flushed                                 | 0                                                |
| Key_blocks_unused                                      | 106026                                           |
| Key_blocks_used                                        | 2073                                             |
| Key_blocks_warm                                        | 2                                                |
| Key_read_requests                                      | 954974                                           |
| Key_reads                                              | 15590                                            |
| Key_write_requests                                     | 25758                                            |
| Key_writes                                             | 7625                                             |
| Last_query_cost                                        | 0.000000                                         |
| Master_gtid_wait_count                                 | 0                                                |
| Master_gtid_wait_time                                  | 0                                                |
| Master_gtid_wait_timeouts                              | 0                                                |
| Max_statement_time_exceeded                            | 0                                                |
| Max_used_connections                                   | 306                                              |
| Memory_used                                            | 1232810768                                       |
| Memory_used_initial                                    | 33626248                                         |
| Not_flushed_delayed_rows                               | 0                                                |
| Open_files                                             | 238                                              |
| Open_streams                                           | 4                                                |
| Open_table_definitions                                 | 40000                                            |
| Open_tables                                            | 16144                                            |
| Opened_files                                           | 292123                                           |
| Opened_plugin_libraries                                | 0                                                |
| Opened_table_definitions                               | 80968                                            |
| Opened_tables                                          | 288311                                           |
| Opened_views                                           | 1                                                |
| Queries                                                | 41505265                                         |
| Questions                                              | 41505265                                         |
| Rows_read                                              | 2238016094                                       |
| Rows_sent                                              | 363638953                                        |
| Rows_tmp_read                                          | 21299678                                         |
| Select_full_join                                       | 20989                                            |
| Select_full_range_join                                 | 25263                                            |
| Select_range                                           | 19735506                                         |
| Select_range_check                                     | 0                                                |
| Select_scan                                            | 3619795                                                                                       |
| Slow_queries                                           | 91                                               |
| Sort_merge_passes                                      | 875                                              |
| Sort_priority_queue_sorts                              | 210279                                           |
| Sort_range                                             | 4010080                                          |
| Sort_rows                                              | 34727539                                         |
| Sort_scan                                              | 1066118                                          |
| Subquery_cache_hit                                     | 717168                                           |
| Subquery_cache_miss                                    | 1571758                                          |
| Syncs                                                  | 3347                                             |
| Table_locks_immediate                                  | 291843                                           |
| Table_locks_waited                                     | 60                                               |
| Table_open_cache_active_instances                      | 1                                                |
| Table_open_cache_hits                                  | 46645351                                         |
| Table_open_cache_misses                                | 2666806                                          |
| Table_open_cache_overflows                             | 271551                                                                                      |
| Threadpool_idle_threads                                | 0                                                |
| Threadpool_threads                                     | 0                                                |
| Threads_cached                                         | 4                                                |
| Threads_connected                                      | 9                                                |
| Threads_created                                        | 19305                                            |
| Threads_running                                        | 1                                                | Update_scan                                            | 2604                                             |
| Uptime                                                 | 39677                                            

My.cnf

(server)
binlog_format=mixed
query_cache_size       = 0
query_cache_type       = 0
query_cache_limit      = 8M
max_connections           = 450
wait_timeout              = 120
interactive_timeout       = 19200
tmp_table_size            = 24M
max_heap_table_size       = 24M
thread_cache_size         = 4
max_allowed_packet = 256M
table_open_cache                        = 20000
table_definition_cache                  = 40000
open_files_limit                        = 0
innodb_lock_wait_timeout  = 50
innodb_file_per_table
innodb_buffer_pool_size   = 18G
innodb_log_buffer_size    = 256M
innodb_log_file_size      = 512M
innodb_flush_log_at_trx_commit          = 2
innodb_flush_method                     = O_DIRECT
innodb_buffer_pool_dump_at_shutdown     = ON
innodb_buffer_pool_load_at_startup      = ON
innodb_write_io_threads                 = 16
analyze_sample_percentage               = 0
optimizer_search_depth=                 = 0