1

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.

Improve this question
  • Some of those values need SHOW VARIABLES; and/or SHOW GLOBAL STATUS; to understand the scale. Can you provide such in a postit/postbin/whatever page? Also, how much RAM? Are you writing to all nodes? Or pretending it is Master+Slaves? How far apart (in milliseconds) are the nodes? – Rick James May 31 '16 at 2:10
  • @RickJames thanks I added the requested info – Ivanov Jun 3 '16 at 5:42
3

(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:

Version: 5.5.41-MariaDB-wsrep
3.7 GB of RAM
Uptime = 61d 21:40:40
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues

Increase innodb_buffer_pool_size to 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 = 1 and turn on the slowlog. After a day, look at the slowlog (preferrably with pt-query-digest) to see what queries need to be improved. (Don't change tmp_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_size should 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, and innodb_doublewrite = OFF

Investigate why Innodb_rows_deleted / Innodb_rows_inserted = 60%.

max_allowed_packet (and max_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 SHOW commands; 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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.