我的 MySQL 數據庫會從增加 InnoDB 緩衝池大小中受益嗎？

當然，答案幾乎總是“是”，但我對我的具體情況感興趣。

這類似於了解 INNODB 緩衝池統計資訊，但 IMO 更具體一點。

首先，我正在執行 MySQL 5.5，但很快就會升級到 5.6。據我所知，這兩個版本之間沒有顯著差異，因此建議應該大致相同。

我有 64GiB 伺服器記憶體，並且伺服器幾乎完全用於 MySQL。我目前有：

innodb_buffer_pool_size=38G
innodb_buffer_pool_instances=19

每次伺服器啟動時，我都會手動對其進行預熱，因為我知道有一些表經常被查閱並且受益於緩衝池中的存在。一旦加熱，緩衝池報告的使用率可能只有 10-20%。幾天后，池使用率達到 100%。

據我了解，只要緩衝池不是 100%，記憶體基本上就被浪費了。如果數據庫不完全適合 RAM（而我的不適合），那麼任何小於 100% 的東西都是不好的。但是 100% 本身是壞的嗎？我應該進一步增加緩衝池的大小嗎？當我的硬體達到實際極限時會怎樣……可能在 60GiB/64GiB 左右？如果我達到 100%，這是否意味著我必須擴展我的 RAM？

以下是伺服器的一些統計數據，它已經執行了很長一段時間（目前正常執行時間為 122 天 13 小時 37 分 49 秒）：

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 41758490624; in additional pool allocated 0
Dictionary memory allocated 2031279
Buffer pool size   2490349
Free buffers       240
Database pages     2192757
Old database pages 809045
Modified db pages  1553
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11604834, not young 0
4.16 youngs/s, 0.00 non-youngs/s
Pages read 4213650, created 898538, written 141481897
0.50 reads/s, 0.32 creates/s, 26.45 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2192757, unzip_LRU len: 0
I/O sum[26220]:cur[0], unzip sum[0]:cur[0]

閱讀上面提到的 DBA/SE 問題，目前接受的答案基本上是說如果hit rate很高（就像1000/1000我的情況一樣），那麼一切都很好。

另一方面，MySQLTuner 有這樣的說法：

[!!] InnoDB buffer pool / data size: 38.0G/108.8G
[OK] InnoDB Read buffer efficiency: 100.00% (667137634143 hits/ 667141256884 total)
[!!] InnoDB Write Log efficiency: 56.15% (53957444 hits/ 96087926 total)
[OK] InnoDB log waits: 0.00% (0 waits / 150045370 writes)

所以讀取似乎很好，緩衝池對寫入沒有任何幫助。（寫入日誌的效率很可能是因為我的 5MiB 非常低。）

那麼這裡的一切看起來都不錯嗎？或者我是否會從擴大緩衝池中獲益更多？

2018-09-01 更新

我有一些更多的資訊給任何閱讀這篇文章的人。

首先，我的 CPU 在“負載”下非常滿意，所有 12 個 CPU 核心的使用率都徘徊在 10-15% 左右。單個 CPU 在負載下的使用率似乎低於 50%，而一些 CPU 幾乎什麼也沒做。我似乎有足夠的 CPU 空閒。MySQL 在這個目前執行 SATA SSD 的系統上似乎完全受 IO 限制。如果有必要，我們肯定有成長的空間。

的完整輸出SHOW GLOBAL STATUS：

+------------------------------------------+---------------+
| Variable_name                            | Value         |
+------------------------------------------+---------------+
| Aborted_clients                          | 1693          |
| Aborted_connects                         | 19            |
| Binlog_cache_disk_use                    | 9350          |
| Binlog_cache_use                         | 90526920      |
| Binlog_stmt_cache_disk_use               | 0             |
| Binlog_stmt_cache_use                    | 102760        |
| Bytes_received                           | 741440710519  |
| Bytes_sent                               | 2330763165310 |
| Com_admin_commands                       | 652023569     |
| Com_assign_to_keycache                   | 0             |
| Com_alter_db                             | 0             |
| Com_alter_db_upgrade                     | 1             |
| Com_alter_event                          | 0             |
| Com_alter_function                       | 0             |
| Com_alter_procedure                      | 0             |
| Com_alter_server                         | 0             |
| Com_alter_table                          | 113           |
| Com_alter_tablespace                     | 0             |
| Com_analyze                              | 0             |
| Com_begin                                | 28            |
| Com_binlog                               | 0             |
| Com_call_procedure                       | 1             |
| Com_change_db                            | 10            |
| Com_change_master                        | 0             |
| Com_check                                | 268           |
| Com_checksum                             | 0             |
| Com_commit                               | 10676104      |
| Com_create_db                            | 1             |
| Com_create_event                         | 0             |
| Com_create_function                      | 0             |
| Com_create_index                         | 0             |
| Com_create_procedure                     | 2             |
| Com_create_server                        | 0             |
| Com_create_table                         | 44            |
| Com_create_trigger                       | 0             |
| Com_create_udf                           | 0             |
| Com_create_user                          | 0             |
| Com_create_view                          | 0             |
| Com_dealloc_sql                          | 24            |
| Com_delete                               | 1074437       |
| Com_delete_multi                         | 0             |
| Com_do                                   | 0             |
| Com_drop_db                              | 1             |
| Com_drop_event                           | 0             |
| Com_drop_function                        | 0             |
| Com_drop_index                           | 0             |
| Com_drop_procedure                       | 4             |
| Com_drop_server                          | 0             |
| Com_drop_table                           | 1             |
| Com_drop_trigger                         | 0             |
| Com_drop_user                            | 0             |
| Com_drop_view                            | 0             |
| Com_empty_query                          | 0             |
| Com_execute_sql                          | 24            |
| Com_flush                                | 130           |
| Com_grant                                | 0             |
| Com_ha_close                             | 0             |
| Com_ha_open                              | 0             |
| Com_ha_read                              | 0             |
| Com_help                                 | 3             |
| Com_insert                               | 112556804     |
| Com_insert_select                        | 764124        |
| Com_install_plugin                       | 0             |
| Com_kill                                 | 9             |
| Com_load                                 | 0             |
| Com_lock_tables                          | 0             |
| Com_optimize                             | 5             |
| Com_preload_keys                         | 0             |
| Com_prepare_sql                          | 24            |
| Com_purge                                | 0             |
| Com_purge_before_date                    | 0             |
| Com_release_savepoint                    | 0             |
| Com_rename_table                         | 0             |
| Com_rename_user                          | 0             |
| Com_repair                               | 0             |
| Com_replace                              | 9448          |
| Com_replace_select                       | 0             |
| Com_reset                                | 0             |
| Com_resignal                             | 0             |
| Com_revoke                               | 0             |
| Com_revoke_all                           | 0             |
| Com_rollback                             | 10794957      |
| Com_rollback_to_savepoint                | 0             |
| Com_savepoint                            | 0             |
| Com_select                               | 797113543     |
| Com_set_option                           | 41389732      |
| Com_signal                               | 0             |
| Com_show_authors                         | 0             |
| Com_show_binlog_events                   | 0             |
| Com_show_binlogs                         | 0             |
| Com_show_charsets                        | 1             |
| Com_show_collations                      | 282541        |
| Com_show_contributors                    | 0             |
| Com_show_create_db                       | 0             |
| Com_show_create_event                    | 0             |
| Com_show_create_func                     | 0             |
| Com_show_create_proc                     | 0             |
| Com_show_create_table                    | 5             |
| Com_show_create_trigger                  | 0             |
| Com_show_databases                       | 101           |
| Com_show_engine_logs                     | 0             |
| Com_show_engine_mutex                    | 0             |
| Com_show_engine_status                   | 3             |
| Com_show_events                          | 0             |
| Com_show_errors                          | 0             |
| Com_show_fields                          | 20568         |
| Com_show_function_status                 | 0             |
| Com_show_grants                          | 0             |
| Com_show_keys                            | 38146952      |
| Com_show_master_status                   | 0             |
| Com_show_open_tables                     | 0             |
| Com_show_plugins                         | 3             |
| Com_show_privileges                      | 0             |
| Com_show_procedure_status                | 0             |
| Com_show_processlist                     | 198           |
| Com_show_profile                         | 0             |
| Com_show_profiles                        | 0             |
| Com_show_relaylog_events                 | 0             |
| Com_show_slave_hosts                     | 2             |
| Com_show_slave_status                    | 4             |
| Com_show_status                          | 481905        |
| Com_show_storage_engines                 | 3             |
| Com_show_table_status                    | 10            |
| Com_show_tables                          | 109           |
| Com_show_triggers                        | 0             |
| Com_show_variables                       | 431041        |
| Com_show_warnings                        | 8             |
| Com_slave_start                          | 0             |
| Com_slave_stop                           | 0             |
| Com_stmt_close                           | 24            |
| Com_stmt_execute                         | 24            |
| Com_stmt_fetch                           | 0             |
| Com_stmt_prepare                         | 24            |
| Com_stmt_reprepare                       | 0             |
| Com_stmt_reset                           | 0             |
| Com_stmt_send_long_data                  | 0             |
| Com_truncate                             | 0             |
| Com_uninstall_plugin                     | 0             |
| Com_unlock_tables                        | 0             |
| Com_update                               | 8715420       |
| Com_update_multi                         | 160406        |
| Com_xa_commit                            | 0             |
| Com_xa_end                               | 0             |
| Com_xa_prepare                           | 0             |
| Com_xa_recover                           | 0             |
| Com_xa_rollback                          | 0             |
| Com_xa_start                             | 0             |
| Compression                              | OFF           |
| Connections                              | 5240587       |
| Created_tmp_disk_tables                  | 7746864       |
| Created_tmp_files                        | 40834         |
| Created_tmp_tables                       | 146517652     |
| Delayed_errors                           | 0             |
| Delayed_insert_threads                   | 0             |
| Delayed_writes                           | 0             |
| Flush_commands                           | 2             |
| Handler_commit                           | 2225650354    |
| Handler_delete                           | 2513929       |
| Handler_discover                         | 0             |
| Handler_prepare                          | 258039138     |
| Handler_read_first                       | 5335209       |
| Handler_read_key                         | 106045918332  |
| Handler_read_last                        | 9             |
| Handler_read_next                        | 328378371619  |
| Handler_read_prev                        | 120           |
| Handler_read_rnd                         | 3024880351    |
| Handler_read_rnd_next                    | 41307678157   |
| Handler_rollback                         | 1915470       |
| Handler_savepoint                        | 0             |
| Handler_savepoint_rollback               | 0             |
| Handler_update                           | 76207796      |
| Handler_write                            | 2981602720    |
| Innodb_buffer_pool_pages_data            | 2194942       |
| Innodb_buffer_pool_bytes_data            | 35961929728   |
| Innodb_buffer_pool_pages_dirty           | 131           |
| Innodb_buffer_pool_bytes_dirty           | 2146304       |
| Innodb_buffer_pool_pages_flushed         | 149210414     |
| Innodb_buffer_pool_pages_free            | 17            |
| Innodb_buffer_pool_pages_misc            | 295390        |
| Innodb_buffer_pool_pages_total           | 2490349       |
| Innodb_buffer_pool_read_ahead_rnd        | 0             |
| Innodb_buffer_pool_read_ahead            | 135971        |
| Innodb_buffer_pool_read_ahead_evicted    | 955           |
| Innodb_buffer_pool_read_requests         | 704130093976  |
| Innodb_buffer_pool_reads                 | 3768682       |
| Innodb_buffer_pool_wait_free             | 0             |
| Innodb_buffer_pool_write_requests        | 1010990531    |
| Innodb_data_fsyncs                       | 269329521     |
| Innodb_data_pending_fsyncs               | 0             |
| Innodb_data_pending_reads                | 0             |
| Innodb_data_pending_writes               | 0             |
| Innodb_data_read                         | 71935987712   |
| Innodb_data_reads                        | 4390704       |
| Innodb_data_writes                       | 360055349     |
| Innodb_data_written                      | 5021614685696 |
| Innodb_dblwr_pages_written               | 149210414     |
| Innodb_dblwr_writes                      | 51073994      |
| Innodb_have_atomic_builtins              | ON            |
| Innodb_log_waits                         | 0             |
| Innodb_log_write_requests                | 101591974     |
| Innodb_log_writes                        | 158807817     |
| Innodb_os_log_fsyncs                     | 159790383     |
| Innodb_os_log_pending_fsyncs             | 0             |
| Innodb_os_log_pending_writes             | 0             |
| Innodb_os_log_written                    | 131810433024  |
| Innodb_page_size                         | 16384         |
| Innodb_pages_created                     | 950701        |
| Innodb_pages_read                        | 4390495       |
| Innodb_pages_written                     | 149210414     |
| Innodb_row_lock_current_waits            | 0             |
| Innodb_row_lock_time                     | 6219927       |
| Innodb_row_lock_time_avg                 | 650           |
| Innodb_row_lock_time_max                 | 51855         |
| Innodb_row_lock_waits                    | 9558          |
| Innodb_rows_deleted                      | 2512888       |
| Innodb_rows_inserted                     | 113153043     |
| Innodb_rows_read                         | 425620156257  |
| Innodb_rows_updated                      | 8210549       |
| Innodb_truncated_status_writes           | 0             |
| Key_blocks_not_flushed                   | 0             |
| Key_blocks_unused                        | 12864         |
| Key_blocks_used                          | 13396         |
| Key_read_requests                        | 1238110859    |
| Key_reads                                | 23827152      |
| Key_write_requests                       | 311403712     |
| Key_writes                               | 23962043      |
| Last_query_cost                          | 0.000000      |
| Max_used_connections                     | 45            |
| Not_flushed_delayed_rows                 | 0             |
| Open_files                               | 106           |
| Open_streams                             | 0             |
| Open_table_definitions                   | 286           |
| Open_tables                              | 600           |
| Opened_files                             | 31033981      |
| Opened_table_definitions                 | 1166          |
| Opened_tables                            | 2138          |
| Performance_schema_cond_classes_lost     | 0             |
| Performance_schema_cond_instances_lost   | 0             |
| Performance_schema_file_classes_lost     | 0             |
| Performance_schema_file_handles_lost     | 0             |
| Performance_schema_file_instances_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_table_handles_lost    | 0             |
| Performance_schema_table_instances_lost  | 0             |
| Performance_schema_thread_classes_lost   | 0             |
| Performance_schema_thread_instances_lost | 0             |
| Prepared_stmt_count                      | 0             |
| Qcache_free_blocks                       | 1856          |
| Qcache_free_memory                       | 6474208       |
| Qcache_hits                              | 1953817508    |
| Qcache_inserts                           | 363888531     |
| Qcache_lowmem_prunes                     | 162106761     |
| Qcache_not_cached                        | 432856579     |
| Qcache_queries_in_cache                  | 7147          |
| Qcache_total_blocks                      | 16243         |
| Queries                                  | 3636998091    |
| Questions                                | 2980040458    |
| Rpl_status                               | AUTH_MASTER   |
| Select_full_join                         | 32510         |
| Select_full_range_join                   | 1074987       |
| Select_range                             | 19633284      |
| Select_range_check                       | 0             |
| Select_scan                              | 50014427      |
| Slave_heartbeat_period                   | 0.000         |
| Slave_open_temp_tables                   | 0             |
| Slave_received_heartbeats                | 0             |
| Slave_retried_transactions               | 0             |
| Slave_running                            | OFF           |
| Slow_launch_threads                      | 0             |
| Slow_queries                             | 2316          |
| Sort_merge_passes                        | 23601         |
| Sort_range                               | 33648691      |
| Sort_rows                                | 4809913781    |
| Sort_scan                                | 83532099      |
| Ssl_accept_renegotiates                  | 0             |
| Ssl_accepts                              | 0             |
| Ssl_callback_cache_hits                  | 0             |
| Ssl_cipher                               |               |
| Ssl_cipher_list                          |               |
| Ssl_client_connects                      | 0             |
| Ssl_connect_renegotiates                 | 0             |
| Ssl_ctx_verify_depth                     | 0             |
| Ssl_ctx_verify_mode                      | 0             |
| Ssl_default_timeout                      | 0             |
| Ssl_finished_accepts                     | 0             |
| Ssl_finished_connects                    | 0             |
| Ssl_session_cache_hits                   | 0             |
| Ssl_session_cache_misses                 | 0             |
| Ssl_session_cache_mode                   | Unknown       |
| Ssl_session_cache_overflows              | 0             |
| Ssl_session_cache_size                   | 0             |
| Ssl_session_cache_timeouts               | 0             |
| Ssl_sessions_reused                      | 0             |
| Ssl_used_session_cache_entries           | 0             |
| Ssl_verify_depth                         | 0             |
| Ssl_verify_mode                          | 0             |
| Ssl_version                              |               |
| Table_locks_immediate                    | 2068275041    |
| Table_locks_waited                       | 17            |
| Tc_log_max_pages_used                    | 0             |
| Tc_log_page_size                         | 0             |
| Tc_log_page_waits                        | 137           |
| Threads_cached                           | 6             |
| Threads_connected                        | 22            |
| Threads_created                          | 1073          |
| Threads_running                          | 3             |
| Uptime                                   | 11107125      |
| Uptime_since_flush_status                | 11107125      |
+------------------------------------------+---------------+

MySQLTuner 的輸出（減去缺少的外掛等）：

>>  MySQLTuner 1.7.10 - Major Hayden <major@mhtx.net>
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in MyISAM tables: 352.4M (Tables: 26)
[--] Data in InnoDB tables: 109.5G (Tables: 219)
[OK] Total fragmented tables: 0

-------- Performance Metrics
[--] Up for: 128d 13h 22m 6s (2B q [268.298 qps], 5M conn, TX: 2170G, RX: 690G)
[--] Reads / Writes: 86% / 14%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 63.0G
[--] Max MySQL memory    : 38.5G
[--] Other process memory: 422.3M
[--] Total buffers: 38.1G global + 2.7M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 38.2G (60.57% of installed RAM)
[OK] Maximum possible memory usage: 38.5G (61.01% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (2K/2B)
[OK] Highest usage of available connections: 29% (45/151)
[OK] Aborted connections: 0.00%  (20/5240615)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 71.0% (1B cached / 2B selects)
[!!] Query cache prunes per day: 1261000
[OK] Sorts requiring temporary tables: 0% (23K temp sorts / 117M sorts)
[!!] Joins performed without indexes: 32510
[OK] Temporary tables created on disk: 5% (7M on disk / 146M total)
[OK] Thread cache hit rate: 99% (1K created / 5M connections)
[OK] Table cache hit rate: 28% (600 open / 2K opened)
[OK] Open file limit used: 7% (106/1K)
[OK] Table locks acquired immediately: 99% (2B immediate / 2B locks)
[OK] Binlog cache memory access: 99.99% (90518299 Memory / 90527649 Total)

-------- MyISAM Metrics
[!!] Key buffer used: 21.5% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/55.3M
[OK] Read Key buffer hit rate: 98.1% (1B cached / 23M reads)
[!!] Write Key buffer hit rate: 7.7% (311M cached / 23M writes)

-------- InnoDB Metrics
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 38.0G/109.5G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.0256990131578947 %): 5.0M * 2/38.0G should be equal 25%
[!!] InnoDB buffer pool instances: 19
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (704129278359 hits/ 704133047059 total)
[!!] InnoDB Write Log efficiency: 56.32% (57216580 hits/ 101592706 total)
[OK] InnoDB log waits: 0.00% (0 waits / 158809286 writes)

-------- Recommendations
General recommendations:
   Adjust your join queries to always utilize indexes
Variables to adjust:
   query_cache_size (=0)
   query_cache_type (=0)
   query_cache_size (> 16M)
   join_buffer_size (> 128.0K, or always use indexes with JOINs)
   innodb_buffer_pool_size (>= 109.5G) if possible.
   innodb_log_file_size should be (=4G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
   innodb_buffer_pool_instances(=38)

超限：

Limit                     Soft Limit           Hard Limit
Max cpu time              unlimited            unlimited
Max file size             unlimited            unlimited
Max data size             unlimited            unlimited
Max stack size            8388608              unlimited
Max core file size        0                    unlimited
Max resident set          unlimited            unlimited
Max processes             515758               515758
Max open files            1361                 1361
Max locked memory         65536                65536
Max address space         unlimited            unlimited
Max file locks            unlimited            unlimited
Max pending signals       515758               515758
Max msgqueue size         819200               819200
Max nice priority         0                    0
Max realtime priority     0                    0
Max realtime timeout      unlimited            unlimited

我沒有足夠的空間來發布我的my.cnf或其他一些統計資訊，我希望盡可能多地直接在文章中提供資訊，這樣它就不會被例如 pastebin 自動清除。我可以發布哪些特別有用的內容？我的 InnoDB 配置並不比我已經發布的複雜多少。

不，增加 innodb_buffer_pool_size 會有所幫助。

當 innodb_data_reads / uptime 結果 < 1.00

無需增加 I_b_p_s。您的結果是 0.39，這意味著您對整個系統中的任何表的讀取次數少於每秒一次。

如果你想讓你的伺服器更快，請通過 Skype 與我聯繫。

緩衝池大小

如果太大，則會浪費 RAM，但不會浪費性能。

如果太小並且工作集足夠小，則“記憶體”使其大小無關緊要。

如果太小而工作集太大，那麼 I/O 會影響性能。當涉及 UUID 時，尤其會發生這種情況。

底線：關注 buffer_pool_size 只是性能公式的一部分。

什麼是重要的？

查看“慢”查詢更為重要。最小化磁碟命中次數更為重要。這會導致更小的 buffer_pool。

也就是說，備份到查詢以確定要做什麼。

得到GLOBAL STATUS

不要SELECT用來獲取STATUS值；SELECT已修改結果。請務必使用pastbin（或其他工具）發布數據。

有關獲取變數/狀態的更多資訊，以及查找“最差”查詢：http: //mysql.rjweb.org/doc.php/mysql_analysis。我查看了大約 200 個公式，通常會發現大約 20 個值得擔心的公式。

沒有變數的狀態不是很有用。

查詢記憶體

大多數生產系統應該完全關閉 QC（大小=0和類型=0）。啟用它會在簡單查詢上花費大約 11% 的性能。

生產系統通常涉及對錶的更改——這涉及清除 QC 中的條目。請注意，您每秒大約有 15 個李子。這可能比收益更昂貴。

60/64?

將 buffer_pool 設置得太接近 RAM 大小可能安全，也可能不安全。交換比使用稍小的 buffer_pool 要糟糕得多*。*

中央處理器

CPU 似乎很高，僅為 268 qps。這可能意味著索引不佳或其他查詢問題。慢日誌應該有助於隔離問題。

正常執行時間 = 128 天

活動是否“相同”了那麼長時間？還是事情變得更加活躍？我提出這個是因為任何結論VARIABLES都GLOBAL STATUS可能已經過時了。

引用自：https://dba.stackexchange.com/questions/215974