是否有任何 MySQL 基准测试工具？[关闭]

Question

alfish

Asked: 2012-10-22 07:03:45 +0800 CST2012-10-22 07:03:45 +0800 CST 2012-10-22 07:03:45 +0800 CST

mysql innodb_buffer_pool_size 应该有多大？

772

我有一个繁忙的数据库，只有大约 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 应该是什么以获得最佳性能。感谢您为这种情况设置最佳参数和其他参数的建议。

4 个回答

Voted

RolandoMySQLDBA · Answer 1 · 2012-10-22T16:30:39+08:00

你的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;

Rick James · Answer 2 · 2015-02-07T17:02:07+08:00

像这样的东西？使用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%
如果超出范围怎么办： 如果有足够的，则增加 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_size

表达式： Innodb_buffer_pool_reads / Uptime
含义： 读取
推荐范围： 0-100/秒。
如果超出范围怎么办： 增加innodb_buffer_pool_size？

表达式： (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) / Uptime
含义： InnoDB I/O
推荐范围： 0-100/sec。
如果超出范围怎么办： 增加innodb_buffer_pool_size？

表达式： Innodb_buffer_pool_pages_flushed / Uptime
含义： 写入（刷新）
推荐范围： 0-100/秒。
如果超出范围怎么办： 增加innodb_buffer_pool_size？

表达式： Innodb_buffer_pool_wait_free / Uptime
含义： buffer_pool 中没有空闲页面时的计数器。也就是说，所有页面都是脏的。
推荐范围： 0-1/秒。
超出范围怎么办： 首先确保innodb_buffer_pool_size设置合理；如果仍然有问题，请减少 innodb_max_dirty_pages_pct

Rick James · Answer 3 · 2012-10-23T15:19:55+08:00

您的标题询问了 innodb_buffer_pool_size，但我怀疑这不是真正的问题。（罗兰多评论了为什么你把它设置得足够大，甚至太大。）

我设置了最大连接数 = 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 驱动器上可能会更好。

user77376 · Answer 4 · 2014-03-15T02:23:47+08:00

user77376

2014-03-15T02:23:47+08:002014-03-15T02:23:47+08:00

更多内存总是更好，但根据我的经验，大多数情况下缓冲池大小不适合您的数据大小。许多表在大多数情况下都处于非活动状态，例如周围的备份表，因此 innodb 缓冲池大小应该适合您的活动数据大小。

您为活动页面指定的时间范围会影响性能，但有一个最佳点，您不会在更大的缓冲区大小下获得更高的性能。您可以通过以下方式估计/计算/测量show engine innodb status

6

mysql innodb_buffer_pool_size 应该有多大？

警告 #1

注意事项 #2

如何查看 Oracle 中的数据库列表？

mysql innodb_buffer_pool_size 应该有多大？

列出指定表的所有列

从 .frm 和 .ibd 文件恢复表？

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

如何选择每组的第一行？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

mysql innodb_buffer_pool_size 应该有多大？

4 个回答

警告 #1

注意事项 #2

相关问题