MySQL 5.6 — How to tell if I need to increase innodb io thread setting

connectionsMySQLmysql-5.6

I am trying to tune my mysql server for the high end system. We are using 16–32 core hosts with memory > 100GB.

I have looked into a bunch of existing setting, but not sure if I need to tune up the innodb_read/write_io_threads. It is currently only set to 4 each.

| InnoDB |      | 
=====================================
2019-12-17 07:54:43 7f711dd6d700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 9 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2400188 srv_active, 0 srv_shutdown, 265 srv_idle
srv_master_thread log flush and writes: 894
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 806244840
OS WAIT ARRAY INFO: signal count 1019059878
Mutex spin waits 10940244001, rounds 62338475851, OS waits 590825553
RW-shared spins 566475375, rounds 5765275559, OS waits 128474028
RW-excl spins 19695565, rounds 1326338619, OS waits 15787881
Spin rounds per wait: -32.06 mutex, 10.18 RW-shared, 67.34 RW-excl
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
29500680050 OS file reads, 1193269217 OS file writes, 193602148 OS fsyncs
117.54 reads/s, 19527 avg bytes/read, 342.96 writes/s, 37.77 fsyncs/s

mysql> show status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 174   |
+----------------------+-------+

It looks like the innodb io threads are sitting idle, but it is a bit counter intuitive there are a high number of threads connected and waiting to be executed.

Am I just reading the metrics wrong? Should I be bumping up the io thread settings?

Edit:

mysql> SHOW GLOBAL STATUS;
+-----------------------------------------------+--------------------------------------------------+
| Variable_name                                 | Value                                            |
+-----------------------------------------------+--------------------------------------------------+
| Aborted_clients                               | 106863                                           |
| Aborted_connects                              | 70                                               |
| Binlog_cache_disk_use                         | 0                                                |
| Binlog_cache_use                              | 0                                                |
| Binlog_stmt_cache_disk_use                    | 0                                                |
| Binlog_stmt_cache_use                         | 0                                                |
| Bytes_received                                | 1625547525701                                    |
| Bytes_sent                                    | 2286003139405                                    |
| Com_admin_commands                            | 8499339                                          |
| Com_change_db                                 | 1                                                |
| Com_commit                                    | 590982562                                        |
| Com_delete                                    | 107601707                                        |
| Com_help                                      | 0                                                |
| Com_insert                                    | 92217681                                         |
| Com_rollback                                  | 471568007                                        |
| Com_rollback_to_savepoint                     | 0                                                |
| Com_savepoint                                 | 0                                                |
| Com_select                                    | 2839611448                                       |
| Com_set_option                                | 1393872258                                       |
| Com_signal                                    | 0                                                |
| Com_show_databases                            | 17                                               |
| Com_show_engine_logs                          | 0                                                |
| Com_show_engine_mutex                         | 0                                                |
| Com_show_engine_status                        | 4                                                |
| Com_show_events                               | 0                                                |
| Com_show_errors                               | 0                                                |
| Com_show_fields                               | 369                                              |
| Com_show_function_code                        | 0                                                |
| Com_show_function_status                      | 0                                                |
| Com_show_grants                               | 0                                                |
| Com_show_keys                                 | 111                                              |
| Com_show_status                               | 23                                               |
| Com_show_storage_engines                      | 0                                                |
| Com_show_table_status                         | 0                                                |
| Com_show_tables                               | 21                                               |
| Com_show_triggers                             | 0                                                |
| Com_show_variables                            | 342355                                           |
| Com_update                                    | 841346886                                        |
| Compression                                   | OFF                                              |
| Connection_errors_accept                      | 0                                                |
| Connection_errors_internal                    | 0                                                |
| Connection_errors_max_connections             | 0                                                |
| Connection_errors_peer_address                | 0                                                |
| Connection_errors_select                      | 0                                                |
| Connection_errors_tcpwrap                     | 0                                                |
| Connections                                   | 342450                                           |
| Created_tmp_disk_tables                       | 2334                                             |
| Created_tmp_files                             | 8                                                |
| Created_tmp_tables                            | 356072                                           |
| Delayed_errors                                | 0                                                |
| Delayed_insert_threads                        | 0                                                |
| Delayed_writes                                | 0                                                |
| Flush_commands                                | 1                                                |
| Handler_commit                                | 4470737313                                       |
| Handler_delete                                | 82094865                                         |
| Handler_discover                              | 0                                                |
| Handler_external_lock                         | 7759509648                                       |
| Handler_mrr_init                              | 0                                                |
| Handler_prepare                               | 0                                                |
| Handler_read_first                            | 1653297601                                       |
| Handler_read_key                              | 6492652384                                       |
| Handler_read_last                             | 0                                                |
| Handler_read_next                             | 5142902623                                       |
| Handler_read_prev                             | 0                                                |
| Handler_read_rnd                              | 3366143                                          |
| Handler_read_rnd_next                         | 3314986049                                       |
| Handler_rollback                              | 471332451                                        |
| Handler_savepoint                             | 0                                                |
| Handler_savepoint_rollback                    | 0                                                |
| Handler_update                                | 841346880                                        |
| Handler_write                                 | 183314643                                        |
| Innodb_buffer_pool_dump_status                | not started                                      |
| Innodb_buffer_pool_load_status                | Buffer pool(s) load completed at 191121 19:04:32 |
| Innodb_buffer_pool_pages_data                 | 509389                                           |
| Innodb_buffer_pool_bytes_data                 | 8345829376                                       |
| Innodb_buffer_pool_pages_dirty                | 51724                                            |
| Innodb_buffer_pool_bytes_dirty                | 847446016                                        |
| Innodb_buffer_pool_pages_flushed              | 831165527                                        |
| Innodb_buffer_pool_pages_free                 | 6243                                             |
| Innodb_buffer_pool_pages_misc                 | 8652                                             |
| Innodb_buffer_pool_pages_total                | 524284                                           |
| Innodb_buffer_pool_read_ahead_rnd             | 0                                                |
| Innodb_buffer_pool_read_ahead                 | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted         | 18274                                            |
| Innodb_buffer_pool_read_requests              | 116508588463                                     |
| Innodb_buffer_pool_reads                      | 2534236537                                       |
| Innodb_buffer_pool_wait_free                  | 0                                                |
| Innodb_buffer_pool_write_requests             | 9553501411                                       |
| Innodb_data_fsyncs                            | 100038238                                        |
| Innodb_data_pending_fsyncs                    | 0                                                |
| Innodb_data_pending_reads                     | 0                                                |
| Innodb_data_pending_writes                    | 0                                                |
| Innodb_data_read                              | 46554305269760                                   |
| Innodb_data_reads                             | 2840557270                                       |
| Innodb_data_writes                            | 772657004                                        |
| Innodb_data_written                           | 28407884982784                                   |
| Innodb_dblwr_pages_written                    | 831165527                                        |
| Innodb_dblwr_writes                           | 19505074                                         |
| Innodb_have_atomic_builtins                   | ON                                               |
| Innodb_log_waits                              | 0                                                |
| Innodb_log_write_requests                     | 2932109671                                       |
| Innodb_log_writes                             | 2358858                                          |
| Innodb_os_log_fsyncs                          | 2534641                                          |
| Innodb_os_log_pending_fsyncs                  | 0                                                |
| Innodb_os_log_pending_writes                  | 0                                                |
| Innodb_os_log_written                         | 1172130567680                                    |
| Innodb_page_size                              | 16384                                            |
| Innodb_pages_created                          | 9089369                                          |
| Innodb_pages_read                             | 2841543370                                       |
| Innodb_pages_written                          | 831165527                                        |
| Innodb_row_lock_current_waits                 | 0                                                |
| Innodb_row_lock_time                          | 0                                                |
| Innodb_row_lock_time_avg                      | 0                                                |
| Innodb_row_lock_time_max                      | 0                                                |
| Innodb_row_lock_waits                         | 0                                                |
| Innodb_rows_deleted                           | 82094865                                         |
| Innodb_rows_inserted                          | 175266694                                        |
| Innodb_rows_read                              | 10220852902                                      |
| Innodb_rows_updated                           | 841346880                                        |
| Innodb_num_open_files                         | 24                                               |
| Innodb_truncated_status_writes                | 0                                                |
| Innodb_available_undo_logs                    | 128                                              |
| Key_blocks_not_flushed                        | 0                                                |
| Key_blocks_unused                             | 319666                                           |
| Key_blocks_used                               | 0                                                |
| Key_read_requests                             | 0                                                |
| Key_reads                                     | 0                                                |
| Key_write_requests                            | 0                                                |
| Key_writes                                    | 0                                                |
| Last_query_cost                               | 0.000000                                         |
| Last_query_partial_plans                      | 0                                                |
| Max_used_connections                          | 42                                               |
| Not_flushed_delayed_rows                      | 0                                                |
| Open_files                                    | 17                                               |
| Open_streams                                  | 0                                                |
| Open_table_definitions                        | 86                                               |
| Open_tables                                   | 246                                              |
| Opened_files                                  | 9475                                             |
| Opened_table_definitions                      | 86                                               |
| Opened_tables                                 | 253                                              |
| Performance_schema_accounts_lost              | 0                                                |
| Performance_schema_cond_classes_lost          | 0                                                |
| Performance_schema_cond_instances_lost        | 0                                                |
| Performance_schema_digest_lost                | 0                                                |
| Performance_schema_file_classes_lost          | 0                                                |
| Performance_schema_file_handles_lost          | 0                                                |
| Performance_schema_file_instances_lost        | 0                                                |
| Performance_schema_hosts_lost                 | 0                                                |
| Performance_schema_locker_lost                | 0                                                |
| Performance_schema_mutex_classes_lost         | 0                                                |
| Performance_schema_mutex_instances_lost       | 0                                                |
| Performance_schema_rwlock_classes_lost        | 0                                                |
| Performance_schema_rwlock_instances_lost      | 0                                                |
| Performance_schema_session_connect_attrs_lost | 0                                                |
| Performance_schema_socket_classes_lost        | 0                                                |
| Performance_schema_socket_instances_lost      | 0                                                |
| Performance_schema_stage_classes_lost         | 0                                                |
| Performance_schema_statement_classes_lost     | 0                                                |
| Performance_schema_table_handles_lost         | 0                                                |
| Performance_schema_table_instances_lost       | 0                                                |
| Performance_schema_thread_classes_lost        | 0                                                |
| Performance_schema_thread_instances_lost      | 0                                                |
| Performance_schema_users_lost                 | 0                                                |
| Prepared_stmt_count                           | 0                                                |
| Qcache_free_blocks                            | 1                                                |
| Qcache_free_memory                            | 33536848                                         |
| Qcache_hits                                   | 0                                                |
| Qcache_inserts                                | 0                                                |
| Qcache_lowmem_prunes                          | 0                                                |
| Qcache_not_cached                             | 2832134371                                       |
| Qcache_queries_in_cache                       | 0                                                |
| Qcache_total_blocks                           | 1                                                |
| Queries                                       | 6346278252                                       |
| Questions                                     | 6337778912                                       |
| Rsa_public_key                                |                                                  |
| Select_full_join                              | 111                                              |
| Select_full_range_join                        | 0                                                |
| Select_range                                  | 111074254                                        |
| Select_range_check                            | 0                                                |
| Select_scan                                   | 1062657891                                       |
| Slave_heartbeat_period                        | 0.000                                            |
| Slave_last_heartbeat                          |                                                  |
| Slave_open_temp_tables                        | 0                                                |
| Slave_received_heartbeats                     | 0                                                |
| Slave_retried_transactions                    | 0                                                |
| Slave_running                                 | OFF                                              |
| Slow_launch_threads                           | 0                                                |
| Slow_queries                                  | 14115                                            |
| Sort_merge_passes                             | 48                                               |
| Sort_range                                    | 5128                                             |
| Sort_rows                                     | 1833044                                          |
| Sort_scan                                     | 1                                                |
| Table_locks_immediate                         | 3878704926                                       |
| Table_locks_waited                            | 0                                                |
| Table_open_cache_hits                         | 3879758717                                       |
| Table_open_cache_misses                       | 253                                              |
| Table_open_cache_overflows                    | 0                                                |
| Tc_log_max_pages_used                         | 0                                                |
| Tc_log_page_size                              | 0                                                |
| Tc_log_page_waits                             | 0                                                |
| Threads_cached                                | 0                                                |
| Threads_connected                             | 41                                               |
| Threads_created                               | 167939                                           |
| Threads_running                               | 2                                                |
| Uptime                                        | 2246778                                          |
| Uptime_since_flush_status                     | 2246778                                          |
+-----------------------------------------------+--------------------------------------------------+

Best Answer

According to the Paragraph 3 of "Configuring the Number of Background InnoDB I/O Threads":

If you have a high end I/O subsystem and you see more than 64 × innodb_read_io_threads pending read requests in SHOW ENGINE INNODB STATUS output, you might improve performance by increasing the value of innodb_read_io_threads.

Given the above SHOW ENGINE INNODB STATUS\G, you have

Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,

Everything has 0 for each I/O threads, so no threads were pending at that moment. If you ever start seeing 2 or 3 digit numbers instead of 0, then you will pending reads and writes in InnoDB.

This can also be seen in your global status

| Innodb_data_pending_reads                     | 0
| Innodb_data_pending_writes                    | 0

As shown, you have experienced no pending reads or writes in the InnoDB Storage Engine since mysqld last started.

You can periodically monitor Innodb_data_pending_reads or Innodb_data_pending_writes

You could also poll SHOW ENGINE INNODB STATUS and look for nonzeros in Pending normal aio.

Personally, I raise both innodb_read_io_threads and innodb_write_io_threads it to 8 to match the default values for Percona Server and call it a day.

SUGGESTIONS

You probably need to tune your queries so they do not request so much data.
You should also avoid doing mysqldumps in the middle of the business day and this tends to load every data page and its grandmother into the Buffer Pool, only to have those pages quietly evicted. If you have to run mysqldumps, setup a MySQL Slave and o your dump from the Slave.
You could experiment with disabling the change buffering (set innodb_change_buffering to 0).

UPDATE 2014-08-11 16:45 EDT

Please look back at the comment section. Derek Downey gave you a link to my 3 year old post. Based on the 2012 update in my post, you need to set innodb_buffer_pool_instances = 2. This will help curb mysqld's swap behavior.

From your SHOW ENGINE INNODB STATUS\G, you have too few read and write threads

--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)

You only have 4 read and 4 write I/O threads. Bump up your threads

innodb_read_io_threads = 16
innodb_write_io_threads = 16

If you had more that 2 cores, I would use 32 or 64. You can try 16 for now.

InnoDB is always at odds with the Query Cache. Please disable it.

query_cache_size = 0

You need a bigger log buffer for better InnoDB write performance

innodb_log_buffer_size = 256M

These are all the changes

[mysqld]
innodb_buffer_pool_instances = 2
innodb_log_buffer_size = 256M
innodb_read_io_threads = 16
innodb_write_io_threads = 16
query_cache_size = 0

MySQL 5.6 – How to Resolve MySQL Instance Stalling on SYNC Index

We were seeing the same issue on two servers on versions 5.6.12 and 5.6.16 running on Windows, with a pair of slaves each. We were stumped, like you, for almost two months.

Solution:

set global binlog_order_commits = 0;

See https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_order_commits for details of the variable.

Explanation:

InnoDB full-text uses a cache (by default 8M in size) containing changes that need to be applied to the actual full-text index on disk.

Once the cache fills up, a couple of transactions are created to perform the work of merging the data that was contained in the cache - this tends to be a large amount of random IO, so unless your entire full-text index can be loaded into the buffer pool, it's a long and slow transaction.

With binlog_order_commits set to true, all transactions containing inserts and updates, started after the long-running fts_sync_index transaction, must wait until it has completed before they can commit.

This is only an issue if binary logging is enabled

Best Answer

Related Solutions

MySQL buffer pool usage + high load

SUGGESTIONS

UPDATE 2014-08-11 16:45 EDT

MySQL 5.6 – How to Resolve MySQL Instance Stalling on SYNC Index

Related Question