We use a 3 node Galera cluster with version 5.5.41-MariaDB-wsrep
MariaDB [(none)]> show engine innodb mutex;
+--------+---------------------------------+------------------+
| Type | Name | Status |
+--------+---------------------------------+------------------+
| InnoDB | &table->autoinc_mutex | os_waits=1 |
| InnoDB | &table->autoinc_mutex | os_waits=1 |
| InnoDB | &table->autoinc_mutex | os_waits=1 |
| InnoDB | &purge_sys->bh_mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=4 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=6 |
| InnoDB | &rseg->mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=6 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=4 |
| InnoDB | &rseg->mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=4 |
| InnoDB | &rseg->mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=6 |
| InnoDB | &rseg->mutex | os_waits=5 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=4 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=4 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=4 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=5 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=4 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=4 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=4 |
| InnoDB | &rseg->mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=4 |
| InnoDB | &rseg->mutex | os_waits=4 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=4 |
| InnoDB | &rseg->mutex | os_waits=4 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=4 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &rseg->mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=1 |
| InnoDB | &rseg->mutex | os_waits=2 |
| InnoDB | &ibuf_mutex | os_waits=2548 |
| InnoDB | &ibuf_pessimistic_insert_mutex | os_waits=38 |
| InnoDB | &dict_sys->mutex | os_waits=3 |
| InnoDB | &trx_doublewrite->mutex | os_waits=809 |
| InnoDB | &recv_sys->mutex | os_waits=38 |
| InnoDB | &log_sys->log_flush_order_mutex | os_waits=79 |
| InnoDB | &log_sys->mutex | os_waits=21578 |
| InnoDB | &buf_pool->flush_list_mutex | os_waits=3833 |
| InnoDB | &buf_pool->free_list_mutex | os_waits=3 |
| InnoDB | &buf_pool->LRU_list_mutex | os_waits=4667 |
| InnoDB | &buf_pool->mutex | os_waits=22 |
| InnoDB | &fil_system->mutex | os_waits=435 |
| InnoDB | &kernel_mutex | os_waits=19340 |
| InnoDB | combined &block->mutex | os_waits=846 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=14 |
| InnoDB | &new_index->lock | os_waits=27 |
| InnoDB | &new_index->lock | os_waits=13 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=4 |
| InnoDB | &new_index->lock | os_waits=2 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=2 |
| InnoDB | &new_index->lock | os_waits=2 |
| InnoDB | &new_index->lock | os_waits=16 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=4 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=14 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=3 |
| InnoDB | &new_index->lock | os_waits=9 |
| InnoDB | &new_index->lock | os_waits=3 |
| InnoDB | &new_index->lock | os_waits=7 |
| InnoDB | &new_index->lock | os_waits=4 |
| InnoDB | &new_index->lock | os_waits=17 |
| InnoDB | &new_index->lock | os_waits=47 |
| InnoDB | &new_index->lock | os_waits=92 |
| InnoDB | &new_index->lock | os_waits=2 |
| InnoDB | &new_index->lock | os_waits=36 |
| InnoDB | &new_index->lock | os_waits=4 |
| InnoDB | &new_index->lock | os_waits=4 |
| InnoDB | &new_index->lock | os_waits=4 |
| InnoDB | &new_index->lock | os_waits=100 |
| InnoDB | &new_index->lock | os_waits=8 |
| InnoDB | &new_index->lock | os_waits=2 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=2 |
| InnoDB | &new_index->lock | os_waits=3 |
| InnoDB | &new_index->lock | os_waits=3 |
| InnoDB | &new_index->lock | os_waits=2 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=15 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=7 |
| InnoDB | &new_index->lock | os_waits=25 |
| InnoDB | &new_index->lock | os_waits=11 |
| InnoDB | &new_index->lock | os_waits=3 |
| InnoDB | &new_index->lock | os_waits=1 |
| InnoDB | &new_index->lock | os_waits=242 |
| InnoDB | &purge_sys->latch | os_waits=5 |
| InnoDB | &new_index->lock | os_waits=21739 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=61 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=2 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=27 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=1 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=7 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=1 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=4 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=4 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=30 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=2 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=2 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=3 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=17 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=3 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=1 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=76 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=1 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=25 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=3 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=63 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=1 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=16 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=2 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=1 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=6 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=11 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=17 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=2 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=17 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=13 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=2 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=2 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=9 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=4 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=11 |
| InnoDB | &dict_operation_lock | os_waits=7 |
| InnoDB | &space->latch | os_waits=261 |
| InnoDB | &log_sys->checkpoint_lock | os_waits=463904 |
| InnoDB | &btr_search_latch_arr[i] | os_waits=1051300 |
| InnoDB | &buf_pool->page_hash_latch | os_waits=5543 |
| InnoDB | combined &block->lock | os_waits=390124 |
+--------+---------------------------------+------------------+
219 rows in set (0.01 sec)
Howto interpret and diagnose this output? Is this a healthy DB? I found no explanations howto read this table.
With what other data I need to correlate this values?
memory on all 3 nodes
# free -h
total used free shared buff/cache available
Mem: 3.7G 529M 272M 155M 2.9G 2.8G
Swap: 2.0G 134M 1.9G
We use HAProxy that writes and read only to one node.
See Gist for SHOW VARIABLES and SHOW GLOBAL STATUS output.
Best Answer
(Sorry for the delay; there are some important issues.)
The following things won't explain your mutex questions, but may improve performance such that the questions go away.
Observations:
The More Important Issues
Increase
innodb_buffer_pool_sizeto either the total size of your data and indexes, or to 1500M, whichever is smaller.Max_used_connections = 432-- this is high, and needs to be addressed. But, max_connections is set dangerously high, and should be lowered. Suggest 500. (Note that many memory allocations can be 'per-connection', so a high number of connections could run out of RAM.)Set
long_query_time = 1and turn on the slowlog. After a day, look at the slowlog (preferrably withpt-query-digest) to see what queries need to be improved. (Don't changetmp_table_size, instead fix the queries.)You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.
innodb_log_file_sizeshould be something like 64M. (This is not easy to do in 5.5, so give it low priority.)Because you are using Galera, you could depend on it for robustness, and change to these:
innodb_flush_log_at_trx_commit = 2, andinnodb_doublewrite = OFFInvestigate why Innodb_rows_deleted / Innodb_rows_inserted = 60%.
max_allowed_packet(andmax_long_data_size) (at 1GB) is using a lot of RAM. Lower it unless you have a good reason for having it so high.There are a lot of
SHOWcommands; why?Details and other observations
( Key_blocks_used * 1024 / key_buffer_size ) = 20 * 1024 / 16M = 0.12% -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size to avoid unnecessary memory usage.
( innodb_buffer_pool_size / _ram ) = 128M / 3972844748.8 = 3.4% -- % of RAM used for InnoDB buffer_pool
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (16M / 0.20 + 128M / 0.70) / 3972844748.8 = 6.9% -- Most of available ram should be made available for caching. -- http://mysql.rjweb.org/doc.php/memory
( open_files_limit ) = 1,024 -- ulimit -n -- To allow more files, change ulimit or /etc/security/limits.conf or in sysctl.conf (kern.maxfiles & kern.maxfilesperproc) or something else (OS dependent)
( max_connections ) = 15,360 -- Maximum number of connections (threads). Impacts various allocations. -- If max_connections is too high and various memory settings are high, you could run out of RAM.
( max_allowed_packet / _ram ) = 1024M / 3972844748.8 = 27.0%
( Created_tmp_disk_tables ) = 23,007,187 / 5348440 = 4.3 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs -- increase tmp_table_size and max_heap_table_size. Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM. Better indexes and reformulation of queries are more likely to help.
( Created_tmp_disk_tables / Questions ) = 23,007,187 / 336770452 = 6.8% -- Pct of queries that needed on-disk tmp table. -- Better indexes / No blobs / etc.
( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 23,007,187 / (23007187 + 83449306) = 21.6% -- Percent of temp tables that spilled to disk -- maybe increase tmp_table_size and max_heap_table_size; avoid blobs, etc.
( Select_scan ) = 71,311,555 / 5348440 = 13 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)
( Select_scan / Com_select ) = 71,311,555 / 276169795 = 25.8% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries
( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2
( back_log / max_connections ) = 50 / 15360 = 0.33%
( Connections ) = 15,901,325 / 5348440 = 3 /sec -- Connections -- Increase wait_timeout; use pooling?
( thread_cache_size / max_connections ) = 8 / 15360 = 0.05% -- (0 for Windows)
199