Mysql
MySQL 5.6 – 如何判斷是否需要增加 innodb io 執行緒設置？
我正在嘗試為高端系統調整我的 mysql 伺服器。我們使用 16–32 核心主機，記憶體 > 100GB。
我研究了一堆現有的設置，但不確定是否需要調整 innodb_read/write_io_threads。目前僅設置為 4 個。
| 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/smysql> show status like 'max_used_connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 174 | +----------------------+-------+看起來 innodb io 執行緒處於空閒狀態，但有點反直覺，有大量執行緒連接並等待執行。
我只是讀錯了指標嗎？我應該提高 io 執行緒設置嗎？
編輯：
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 | +-----------------------------------------------+--------------------------------------------------+
根據“配置後台 InnoDB I/O 執行緒數”的第 3 段：
如果您有一個高端 I/O 子系統，並且在 SHOW ENGINE INNODB STATUS 輸出中看到超過 64 個 innodb_read_io_threads 掛起的讀取請求，則可以通過增加 innodb_read_io_threads 的值來提高性能。
鑑於上述情況
SHOW ENGINE INNODB STATUS\G，您有Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,每個 I/O 執行緒的所有內容都為 0，因此此時沒有執行緒處於掛起狀態。如果您開始看到 2 或 3 位數字而不是 0，那麼您將在 InnoDB 中等待讀取和寫入。
這也可以在您的全域狀態中看到
| Innodb_data_pending_reads | 0 | Innodb_data_pending_writes | 0如圖所示，自 mysqld 上次啟動以來，您在 InnoDB 儲存引擎中沒有遇到任何掛起的讀取或寫入。
您可以定期監控
Innodb_data_pending_reads或Innodb_data_pending_writes您還可以輪詢
SHOW ENGINE INNODB STATUS並在Pending normal aio.就個人而言，我將innodb_read_io_threads和innodb_write_io_threads都提高到 8 以匹配 Percona Server 的預設值，並稱之為一天。
你很好地利用了 innodb 緩衝池。
Innodb_data_pending_reads有點離群，監控它的水平。如果它經常非零，則小幅增加到innodb_read_io_threads8 左右應該可以減少爭用。這仍然與您的儲存硬體一樣好。