我有一个繁忙的数据库,只有InnoDB表,大小约为5GB。该数据库运行在使用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(位于一个单独的网络服务器上)向数据库插入一个大的帖子(例如超过10KB)时,它将永远持续下去,而且页面不能正确返回。
关于这些,我想知道我的innodb_buffer_pool_size应该是多少,才能达到最佳性能。我很感谢你的建议,为这种情况设置这个参数和其他参数的最佳值。
你的innodb_buffer_pool_size是巨大的。你把它设置为20971520000
。那是19.5135GB。如果你只有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实际使用了多少GB的内存 此刻InnoDB缓冲池中的数据。
我以前写过这方面的文章:https://dba.stackexchange.com/questions/19164/what-to-set-innodb-buffer-pool-and-why/19181#19181
你可以现在就运行这个`DataGB'查询,而不是重新配置、重新启动和等待一个星期。
这个值DataGB
更接近于InnoDB缓冲池应该有多大+(innodb_change_buffer_max_size中指定的百分比)。我确信这将远远小于你现在保留的20000M。节省下来的RAM可以用来调整其他东西,比如
这是非常重要的一点:有时,InnoDB可能需要比innodb_buffer_pool_size的值多出10%。下面是MySQL文档对此的说明。
你设置的这个值越大,访问表的数据所需的磁盘I/O就越少 表中的数据。在一个专用的数据库服务器上,你可以把这个值设置为高达 到机器物理内存大小的80%。请准备好在以下情况下缩减 这个值,如果出现这些其他问题。
对物理内存的竞争可能会导致操作系统中的分页。 系统中的分页。
InnoDB为缓冲区和控制结构保留了额外的内存。 这样,分配的总空间大约比 指定的大小。
地址空间必须是连续的,这在Windows 系统中的DLLs在特定地址加载时可能会出现问题。
初始化缓冲池的时间大致上与它的 大小。在大型设备上,这个初始化时间可能是 显著。例如,在一个现代Linux x86_64服务器上。 10GB缓冲池的初始化大约需要6秒。 参见第8.9.1节,"InnoDB缓冲池"。
我在你的 "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日
:https://dba.stackexchange.com/questions/2918/about-single-threaded-versus-multithreaded-databases-performance/2948#29482011年9月12日
: https://dba.stackexchange.com/questions/5666/possible-to-make-mysql-use-more-then-one-core/5670#56702011年9月20日
: https://dba.stackexchange.com/questions/5926/multi-cores-and-mysql-performance/5968#5968我刚刚在ServerFault中用一个更简洁的公式回答了这样的问题。
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;
你的标题问的是innodb_buffer_pool_size,但我怀疑这不是真正的问题。 (Rolando评论说,为什么你把它设置得足够大,甚至太大。)
我已经设置了最大连接数=800,这有时会使服务器达到饱和,并使其停滞。
这一点还不清楚。 800个用户在"睡眠"模式下,对系统的影响几乎为零。 800个活动线程将是一场灾难。 有多少线程在"运行"?
这些线程是否相互阻塞? 请参阅 "SHOW ENGINE INNODB STATUS",了解一些关于死锁的线索,等等。
是否有任何查询显示在慢速日志中? 让我们来优化它们。
你使用的是什么版本? XtraDB(InnoDB的直接替代品)在使用多核方面做得更好。 5.6.7的工作甚至更好。
innodb_buffer_pool_instances -- 改为8(假设有20G的buffer_pool);这将稍微减少Mutex的竞争。
你是I/O绑定还是CPU绑定? 解决方案是完全不同的,这取决于你的答案。
SSD -- 如果所有的日志文件都在非SSD驱动器上,可能会更好。