mysql innodb_buffer_pool_size 應該有多大？
我有一個繁忙的數據庫，只有大約 5GB 的 InnoDB 表。該數據庫在使用 SSD 磁碟的 Debian 伺服器上執行，並且我設置了最大連接數 = 800，這有時會使伺服器飽和並停止執行。平均每秒查詢量約為 2.5K。所以我需要優化記憶體使用，為盡可能多的連接騰出空間。
我已經看到建議 innodb_buffer_pool_size 應該達到總記憶體的 %80。另一方面，我從 Tuning-primer 腳本中得到這個警告：
Max Memory Ever Allocated : 91.97 G Configured Max Per-thread Buffers : 72.02 G Configured Max Global Buffers : 19.86 G Configured Max Memory Limit : 91.88 G Physical Memory : 94.58 G這是我目前的 innodb 變數：
| innodb_adaptive_flushing | ON | | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 20971520 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_size | 20971520000 | | innodb_change_buffering | all | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_file_per_table | ON | | innodb_flush_log_at_trx_commit | 2 | | innodb_flush_method | O_DIRECT | | innodb_force_load_corrupted | OFF | | innodb_force_recovery | 0 | | innodb_io_capacity | 200 | | innodb_large_prefix | OFF | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 4194304 | | innodb_log_file_size | 524288000 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 75 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 0 | | innodb_open_files | 300 | | innodb_purge_batch_size | 20 | | innodb_purge_threads | 0 | | innodb_random_read_ahead | OFF | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_rollback_segments | 128 | | innodb_spin_wait_delay | 6 | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | ON | | innodb_stats_sample_pages | 8 | | innodb_strict_mode | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 4 | | innodb_thread_sleep_delay | 10000 | | innodb_use_native_aio | ON | | innodb_use_sys_malloc | ON | | innodb_version | 1.1.8 | | innodb_write_io_threads | 4 |一個可能相關的旁注：我看到當我嘗試從 Drupal（位於單獨的 Web 伺服器上）插入一個大文章（比如超過 10KB）到數據庫時，它會永遠持續並且頁面不會正確返回。
關於這些，我想知道我的 innodb_buffer_pool_size 應該是什麼以獲得最佳性能。感謝您為這種情況設置最佳參數和其他參數的建議。
你的innodb_buffer_pool_size是巨大的。您將其設置為
20971520000. 那是 19.5135 GB。如果你只有 5GB 的 InnoDB 數據和索引，那麼你應該只有大約 8GB。即使這可能太高了。這是你應該做的。首先執行這個查詢
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A;這將為您提供 RIBPS，即建議的 InnoDB 緩衝池大小，基於所有 InnoDB 數據和索引，再加上 60%。
例如
mysql> SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM -> (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes -> FROM information_schema.tables WHERE engine='InnoDB') A; +-------+ | RIBPS | +-------+ | 8 | +-------+ 1 row in set (4.31 sec) mysql>使用此輸出，您將在 /etc/my.cnf 中設置以下內容
[mysqld] innodb_buffer_pool_size=8G下一個，
service mysql restart重啟後，執行 MySQL 一到兩週。然後，執行此查詢：
SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM (SELECT variable_value PagesData FROM information_schema.global_status WHERE variable_name='Innodb_buffer_pool_pages_data') A, (SELECT variable_value PageSize FROM information_schema.global_status WHERE variable_name='Innodb_page_size') B;這將為您提供此時 InnoDB 緩衝池中的 InnoDB 數據正在使用多少實際 GB 記憶體。
我之前寫過這個：什麼設置innodb_buffer_pool以及為什麼..？
您可以
DataGB立即執行此查詢，而不是重新配置、重新啟動並等待一周。這個值
DataGB更接近於 InnoDB 緩衝池應該有多大+（innodb_change_buffer_max_size 中指定的百分比）。我相信這將遠遠少於您現在保留的20000M。RAM 中的節省可用於調整其他內容，例如警告 #1
需要注意這一點非常重要：有時，InnoDB 可能需要比innodb_buffer_pool_size的值多出 10% 。以下是MySQL 文件對此的說明：
該值設置得越大，訪問表中的數據所需的磁碟 I/O 就越少。在專用數據庫伺服器上，您最多可以將其設置為機器物理記憶體大小的 80%。如果出現這些其他問題，請準備縮減此值：
物理記憶體的競爭可能會導致作業系統中的分頁。
InnoDB 為緩衝區和控制結構保留額外的記憶體，因此總分配的空間大約比指定大小大 10%。
地址空間必須是連續的，這在具有在特定地址載入的 DLL 的 Windows 系統上可能是一個問題。
初始化緩衝池的時間大致與其大小成正比。在大型安裝中，此初始化時間可能很長。例如，在現代 Linux x86_64 伺服器上，10GB 緩衝池的初始化大約需要 6 秒。請參閱第 8.9.1 節，“InnoDB 緩衝池”。
注意事項 #2
我在您的
my.cnf| innodb_io_capacity | 200 | | innodb_read_io_threads | 4 | | innodb_thread_concurrency | 4 | | innodb_write_io_threads | 4 |這些數字將阻礙 InnoDB 訪問多個核心
請設置以下內容：
[mysqld] innodb_io_capacity = 2000 innodb_read_io_threads = 64 innodb_thread_concurrency = 0 innodb_write_io_threads = 64我之前在 DBA StackExchange 上寫過這個
- 2011 年 5 月 26 日：關於單執行緒與多執行緒數據庫的性能
- 2011 年 9 月 12 日：可以讓 MySQL 使用多個核心嗎？
- 2011 年 9 月 20 日：多核和 MySQL 性能
我剛剛在 Server Fault 中使用更簡潔的公式回答了這樣一個問題：
SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1)) Recommended_InnoDB_Buffer_Pool_Size FROM ( SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw FROM ( SELECT SUM(data_length+index_length)*1.1*growth RIBPS FROM information_schema.tables AAA, (SELECT 1.25 growth) BBB WHERE ENGINE='InnoDB' ) AA ) A;