我有一个繁忙的数据库，只有大约 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。即使这可能太高了。
20971520000
这是你应该做的。首先运行这个查询
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
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
这个值DataGB更接近于 InnoDB 缓冲池应该有多大+（innodb_change_buffer_max_size 中指定的百分比）。我相信这将远远少于您现在保留的20000M。RAM 中的节省可用于调整其他内容
需要注意这一点非常重要：有时，InnoDB 可能需要比innodb_buffer_pool_size的值多出 10% 。以下是MySQL 文档对此的说明：
该值设置得越大，访问表中的数据所需的磁盘 I/O 就越少。在专用数据库服务器上，您最多可以将其设置为机器物理内存大小的 80%。如果出现这些其他问题，请准备缩减此值： 物理内存的竞争可能会导致操作系统中的分页。 InnoDB 为缓冲区和控制结构保留额外的内存，因此总分配的空间大约比指定大小大 10%。 地址空间必须是连续的，这在具有在特定地址加载的 DLL 的 Windows 系统上可能是一个问题。 初始化缓冲池的时间大致与其大小成正比。在大型安装中，此初始化时间可能很长。例如，在现代 Linux x86_64 服务器上，10GB 缓冲池的初始化大约需要 6 秒。请参阅第 8.9.1 节，“InnoDB 缓冲池”。
该值设置得越大，访问表中的数据所需的磁盘 I/O 就越少。在专用数据库服务器上，您最多可以将其设置为机器物理内存大小的 80%。如果出现这些其他问题，请准备缩减此值：
物理内存的竞争可能会导致操作系统中的分页。
InnoDB 为缓冲区和控制结构保留额外的内存，因此总分配的空间大约比指定大小大 10%。
地址空间必须是连续的，这在具有在特定地址加载的 DLL 的 Windows 系统上可能是一个问题。
初始化缓冲池的时间大致与其大小成正比。在大型安装中，此初始化时间可能很长。例如，在现代 Linux x86_64 服务器上，10GB 缓冲池的初始化大约需要 6 秒。请参阅第 8.9.1 节，“InnoDB 缓冲池”。
我在您的my.cnf
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
我刚刚在 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;
