1

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                                          |
+-----------------------------------------------+--------------------------------------------------+
6
  • include show global status like 'innodb_%'.
    – danblack
    Dec 17, 2019 at 8:33
  • Added the status. @danblack
    – Jialun Liu
    Dec 17, 2019 at 8:56
  • Most of the values you presented are counters; they are useless without dividing by Uptime. For more analysis, please provide all of SHOW GLOBAL STATUS; and SHOW VARIABLES. There are dozens of settings that may need tuning.
    – Rick James
    Dec 17, 2019 at 15:56
  • 1
    A high Max_used_connections may mean that you have poor indexes. Turn on the slowlog.
    – Rick James
    Dec 17, 2019 at 17:00
  • I put in most of the results from SHOW GLOBAL STATUS;, not being able add more since it is breaching the text size limit. Really appreciated for your help! @RickJames
    – Jialun Liu
    Dec 17, 2019 at 19:23

2 Answers 2

1

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.

3
  • 1
    Is this documentation from dev.mysql.com/doc/refman/8.0/en/… INCORRECT with the 'The current number of pending reads.'? Want to be sure what the SHOW GLOBAL STATUS data represents, i.e. NOW or since startup of instance. Thanks Dec 17, 2019 at 22:46
  • Seems to be from a current value looking at variable code and incremented location and shortly after a pread, decremented (and where its called from). Its it the number that is actively pending on a read. 6 hence is your io read threads + 2 more.
    – danblack
    Dec 18, 2019 at 1:30
  • I am actually planning to increase the io read/write threads to half total number of cores, which means for our strongest host, that is 24 threads for each, is there any bad side effects for setting the io threads number too high? We have other JVM services running on the host as well, I am not sure if that would impact other services ability to process requests.
    – Jialun Liu
    Dec 18, 2019 at 18:30
0

You have good use of innodb buffer pool.

Innodb_data_pending_reads is an outlier a bit, monitor its level. If its frequently non-zero, a small increase in innodb_read_io_threads to 8 or so should reduce contention.

This is still only as good as your storage hardware.

7
  • 131219 Threads_created per hour is very high. But 131219/year is insignificant.
    – Rick James
    Dec 17, 2019 at 16:01
  • I am not sure how to read the "thread_created" count, is that threads created ever since the mysql started running? @RickJames
    – Jialun Liu
    Dec 17, 2019 at 19:02
  • Looking at the buffer pool hit rate, I got 96% hit rate using the formular (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%. Is 96% hit rate considered bad already? @danblack
    – Jialun Liu
    Dec 17, 2019 at 19:07
  • Quite right on all counts. I misread the read rate. Uptime certainly puts Threads_created into perspective. mea culpa
    – danblack
    Dec 17, 2019 at 21:09
  • @JialunLiu - @96% is somewhat low. How much RAM? How big is innodb_buffer_pool_size? Need the VARIABLES, too. See mysql.rjweb.org/doc.php/mysql_analysis#tuning
    – Rick James
    Dec 17, 2019 at 22:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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