我已经看到一些建议,即innodb_buffer_pool_size最多应占总内存的%80。另一方面,我从调谐底漆脚本收到此警告:
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应该如何获得最佳性能。感谢您提出的建议,以针对此方案最佳地设置此参数和其他参数。
#1 楼
您的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;
,这将为您提供基于所有InnoDB数据和索引的RIBPS(建议的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缓冲池中的数据
我之前已经写过有关此内容:设置innodb_buffer_pool的原因以及原因..?
您可以立即运行此
DataGB
查询,而不必重新配置,重新启动和等待此值
DataGB
更类似于InnoDB缓冲池应为+的大小(在innodb_change_buffer_max_size中指定的百分比)。我敢肯定,这将远远小于您现在保留的20000M。节省的RAM可用于调整其他内容,例如join_buffer_size
read_buffer_size
read_rnd_buffer_size
max_connection
<注释1,这很重要:有时,InnoDB可能需要比innodb_buffer_pool_size的值额外增加10%。这是MySQL文档对此的说明:
设置的值越大,访问表中的数据所需的磁盘I / O越少。在专用数据库服务器上,您可以将其设置为计算机物理内存大小的80%。如果发生以下其他问题,请准备缩减此值:
物理内存的竞争可能会导致操作系统中的分页。
InnoDB为缓冲区和控制结构保留了额外的内存,因此总分配空间大约比内存大10%。
指定的大小。
地址空间必须是连续的,这在带有DLL且在特定地址加载的Windows系统上可能是一个问题。
初始化缓冲池的时间大致与其
大小成正比。在大型安装中,此初始化时间可能很重要。例如,在现代Linux x86_64服务器上,
初始化10GB缓冲池大约需要6秒钟。
请参见第8.9.1节“ InnoDB缓冲池”。
CAVEAT#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;
#2 楼
像这样吗使用SHOW VARIABLES
和SHOW GLOBAL STATUS
:表达式:
innodb_buffer_pool_size / _ram
含义:用于InnoDB buffer_pool的RAM的百分比建议范围:60〜80%表达:Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
含义:读取必须命中磁盘的请求建议范围:0-2%超出范围时要执行的操作:如果您有足够的RAM则增加innodb_buffer_pool_size表达式:Innodb_pages_read / Innodb_buffer_pool_read_requests
含义:读取必须打到磁盘的请求建议范围:0-2%如果超出范围怎么办:如果有足够的RAM则增加innodb_buffer_pool_size :Innodb_pages_written / Innodb_buffer_pool_write_requests
含义:写入必须命中磁盘的请求建议范围:0-15%如果超出范围怎么办:检查innodb_buffer_pool_sizeExpression:Innodb_buffer_pool_reads / Uptime
含义:读取建议范围:0-100 / sec。如果超出范围怎么办:增加innodb_buffer_pool_size表达式:(Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) / Uptime
含义:InnoDB I / OR建议范围:0-100 / sec如果超出范围怎么办:增加innodb_buffer_pool_size表达式:Innodb_buffer_pool_pages_flushed / Uptime
含义:写入(刷新)推荐范围:0-100 / sec。如果超出范围,执行以下操作:增加innodb_buffer_pool_size?表达式:Innodb_buffer_pool_wait_free / Uptime
含义:buffer_pool中没有可用页时的计数器。也就是说,所有页面都是脏的。建议范围:0-1 /秒。如果超出范围,该怎么办:首先确保innodb_buffer_pool_size设置合理;如果仍然有问题,请减少innodb_max_dirty_pages_pct 评论
感谢@Rick的好评。 innodb_buffer_pool_size值指定什么?实际大小还是配置一个?
–小丑
17年12月21日在12:01
@joker-innodb_buffer_pool_size表示最大大小。在典型的服务器中,“缓冲池”开始时很小,但很快就增长到最大大小并停留在那里。注意:如果它大于RAM(或什至接近RAM),则将导致交换,这对于性能而言非常糟糕。
–里克·詹姆斯(Rick James)
19年4月20日在18:47
所以你是说innodb_buffer_pool_size很重要吗? :)不错的有用答案
–mikato
3月12日17:07
@mikato-调整性能是主要的事情。太小->更多的I / O。比RAM更大->交换或OOM崩溃。
–里克·詹姆斯(Rick James)
3月13日下午0:21
#3 楼
您的标题询问innodb_buffer_pool_size,但我怀疑这不是真正的问题。 (罗兰多评论了为什么将其设置得足够大,甚至太大。) >还不清楚。 “睡眠”模式下的800个用户对系统几乎没有任何影响。 800个活动线程将是一场灾难。 “正在运行”多少个线程?
线程是否互相阻塞?有关死锁等的一些线索,请参阅SHOW ENGINE INNODB STATUS。
慢速日志中是否显示任何查询?让我们对其进行优化。
您正在使用哪个版本? XtraDB(InnoDB的替代品)在使用多个内核方面做得更好。 5.6.7做得更好。
innodb_buffer_pool_instances-将其更改为8(假设20G buffer_pool);
您是I / O绑定还是CPU绑定?解决方案完全不同,具体取决于您的答案。
SSD-如果所有日志文件都位于非SSD驱动器上,则可能会更好。
#4 楼
内存越大总越好,但是以我的经验,大多数时候缓冲池的大小都不适合您的数据大小。许多表在大多数情况下都是不活动的,例如周围有备用表,因此innodb缓冲池的大小应该更适合您的活动数据大小。您为活动页面指定的时间范围会影响性能,但是最佳点,对于更大的缓冲区大小,您将不会获得更多的性能。您可以通过
show engine innodb status
进行估算/计算/测量
评论
感谢您的精彩文章!您的以SELECT(PagesData * PageSize)/ POWER(1024,3)DataGB FROM ...开头的公式在MySQL 5.7上产生以下错误:“'INFORMATION_SCHEMA.GLOBAL_STATUS'功能已禁用;请参阅'show_compatibility_56'文档。”您会不会有更新的版本?
–本杰明
16 Jun 27'在11:31
我得到307 RIBPS和264G。就是说我需要307GB的RAM?
– E_Blue
2016年9月6日20:50
更像264G。但是您应该有足够的RAM用于此,否则将上面提到的RAM的80%分配给mysql,这取决于系统上还运行了什么。
– sjas
16-09-28在8:23
@Benjamin:从MySQL 5.7.6开始,information_schema合并到performance_schema中。因此,只需在查询中将“ information_schema”更改为“ performance_schema”即可使其正常工作。资料来源:dev.mysql.com/doc/refman/5.7/en/status-table.html
–拉尔夫·博尔顿(Ralph Bolton)
17年3月13日在10:59
注意:“巨大”是相对的。这个词是在多年前的2012年写的。
–里克·詹姆斯(Rick James)
1月26日20:26