我有一个仅包含InnoDB表的繁忙数据库,该表的大小约为5GB。该数据库在使用SSD磁盘的Debian服务器上运行,并且我已将max connections设置为800,这有时会饱和并使服务器停止运行。每秒平均查询量约为2.5K。因此,我需要优化内存使用量,以便为最大可能的连接空间。

我已经看到一些建议,即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;


评论


感谢您的精彩文章!您的以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

#2 楼

像这样吗使用SHOW VARIABLESSHOW 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
进行估算/计算/测量