编辑，MySQL 5.5

Question 1

毫无疑问，我坚信它是 InnoDB 存储引擎。

它几乎就像一个活生生的、会呼吸的有机体。

以下是 Percona 首席技术官 Vadim Tkachenko 的图示

InnoDB架构

请注意 InnoDB 缓冲池。如果它有大量脏页（要写回物理表的更改）和相应的索引更改（缓冲池的插入缓冲区部分），请注意以下写入

将 I/O 线程从缓冲池写入 .ibd 文件（物理表文件）。这可能需要读/写 I/O，可能通过 LRU 算法打开和关闭表
对插入缓冲区写入系统表空间文件 (ibdata1) 的更改
由于您有 innodb_flush_log_at_trx_commit = 2，日志缓冲区每秒刷新一次（在休眠系统中这不是问题，因为 innodb_log_buffer_size 为 16，但在大量写入期间可能会出现问题）
脏页被写入双写缓冲区（帮助 mysqld 在 mysqld 崩溃时存活下来（或者至少是相当数量的 InnoDB 损害控制））

讨论 InnoDB 存储引擎有什么意义？

如果整个工作日中有大量脏页被写入，那么可能会有一些活动像心脏循环血液一样在引擎周围清除更改。即使是轻微的写入也可以隐藏一段时间。由于您有 2G 缓冲池，InnoDB 可能只是在搜索缓冲池（通过其清除线程）。

从 my.cnf 来看，我认为你可能使用的是 MySQL 5.5 或更高版本。InnoDB 在未进行调整时，往往会更积极地处理清除线程。

现在，令人惊讶的是，您是否知道即使至少 75% 的缓冲区仍是脏的，InnoDB 仍可以保持安静？

MySQL5.5和5.6，innodb_max_dirty_pages_pct = 75
MySQL5.0和5.1，innodb_max_dirty_pages_pct = 90

这意味着 InnoDB 不会容忍超过 75%（或 90%）的脏页。当脏页超过阈值时，它会启动积极的清除线程活动innodb_max_dirty_pages_pct。一旦它低于 75% 或 90% 的阈值，InnoDB 将稳定地刷新脏页当一切准备就绪时。即使没有进行 INSERT、UPDATE 和 DELETE，这也表现为写入 I/O、服务器负载和 CPU 增加。尽管如此，InnoDB 仍会决定何时需要清除真正旧的脏页。

您可能需要的只是一些调整。以下是您需要的一些设置的示例。

[mysqld]
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_thread_concurrency = 0

要深入了解如何让 InnoDB 使用更多超线程和 CPU，请参阅我的 DBA StackExchange 帖子：

顺便问一下，你有 24GB 的 RAM，但缓冲池只有 2GB？请参阅我的旧帖子mysql innodb_buffer_pool_size 应该有多大？。为什么？即使对超线程和 CPU 进行了适当的调整，缓冲池过小，仍然可能会出现间歇性写入。

更新时间 2014-12-12 10:30 EST

由于您现在使用的是 MySQL 5.0，因此您可以使用以下设置使所有刷新尽可能彻底：

[mysqld]
innodb_thread_concurrency = 0
innodb_max_dirty_pages_pct = 0

这应该可以最大限度地减少或至少减少负载和 CPU 突然上升的情况

这些都是可选的

[mysqld]
innodb_fast_shutdown = 0
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

为什么这些可选设置？

环境innodb_fast_shutdown= 0
- 确保所有事务都刷新到磁盘
- 加快启动速度
环境innodb_flush_log_at_trx_commit设置为 1 即可正确刷新到表和重做日志。注意：某些硬件可能仍然不响应此设置。
如果你启用了二进制日志记录，sync_binlog将正确刷新二进制日志

从长远来看，您确实需要使用 MySQL 5.6。

Answer

毫无疑问，我坚信它是 InnoDB 存储引擎。

它几乎就像一个活生生的、会呼吸的有机体。

以下是 Percona 首席技术官 Vadim Tkachenko 的图示

InnoDB架构

请注意 InnoDB 缓冲池。如果它有大量脏页（要写回物理表的更改）和相应的索引更改（缓冲池的插入缓冲区部分），请注意以下写入

将 I/O 线程从缓冲池写入 .ibd 文件（物理表文件）。这可能需要读/写 I/O，可能通过 LRU 算法打开和关闭表
对插入缓冲区写入系统表空间文件 (ibdata1) 的更改
由于您有 innodb_flush_log_at_trx_commit = 2，日志缓冲区每秒刷新一次（在休眠系统中这不是问题，因为 innodb_log_buffer_size 为 16，但在大量写入期间可能会出现问题）
脏页被写入双写缓冲区（帮助 mysqld 在 mysqld 崩溃时存活下来（或者至少是相当数量的 InnoDB 损害控制））

讨论 InnoDB 存储引擎有什么意义？

如果整个工作日中有大量脏页被写入，那么可能会有一些活动像心脏循环血液一样在引擎周围清除更改。即使是轻微的写入也可以隐藏一段时间。由于您有 2G 缓冲池，InnoDB 可能只是在搜索缓冲池（通过其清除线程）。

从 my.cnf 来看，我认为你可能使用的是 MySQL 5.5 或更高版本。InnoDB 在未进行调整时，往往会更积极地处理清除线程。

现在，令人惊讶的是，您是否知道即使至少 75% 的缓冲区仍是脏的，InnoDB 仍可以保持安静？

MySQL5.5和5.6，innodb_max_dirty_pages_pct = 75
MySQL5.0和5.1，innodb_max_dirty_pages_pct = 90

这意味着 InnoDB 不会容忍超过 75%（或 90%）的脏页。当脏页超过阈值时，它会启动积极的清除线程活动innodb_max_dirty_pages_pct。一旦它低于 75% 或 90% 的阈值，InnoDB 将稳定地刷新脏页当一切准备就绪时。即使没有进行 INSERT、UPDATE 和 DELETE，这也表现为写入 I/O、服务器负载和 CPU 增加。尽管如此，InnoDB 仍会决定何时需要清除真正旧的脏页。

您可能需要的只是一些调整。以下是您需要的一些设置的示例。

[mysqld]
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_thread_concurrency = 0

要深入了解如何让 InnoDB 使用更多超线程和 CPU，请参阅我的 DBA StackExchange 帖子：

顺便问一下，你有 24GB 的 RAM，但缓冲池只有 2GB？请参阅我的旧帖子mysql innodb_buffer_pool_size 应该有多大？。为什么？即使对超线程和 CPU 进行了适当的调整，缓冲池过小，仍然可能会出现间歇性写入。

更新时间 2014-12-12 10:30 EST

由于您现在使用的是 MySQL 5.0，因此您可以使用以下设置使所有刷新尽可能彻底：

[mysqld]
innodb_thread_concurrency = 0
innodb_max_dirty_pages_pct = 0

这应该可以最大限度地减少或至少减少负载和 CPU 突然上升的情况

这些都是可选的

[mysqld]
innodb_fast_shutdown = 0
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

为什么这些可选设置？

环境innodb_fast_shutdown= 0
- 确保所有事务都刷新到磁盘
- 加快启动速度
环境innodb_flush_log_at_trx_commit设置为 1 即可正确刷新到表和重做日志。注意：某些硬件可能仍然不响应此设置。
如果你启用了二进制日志记录，sync_binlog将正确刷新二进制日志

从长远来看，您确实需要使用 MySQL 5.6。

Question 2

该问题与存储引擎无关，问题可能是因为一些 SQL 查询占用了过多的 CPU 资源来处理该特定站点/应用程序。这可能是因为查询使用了“未索引”列或查询效率不高。更好地理解该问题，请在 mysql 上启用“慢查询”日志。

log_slow_queries=/var/log/mysql/slow-query.log
long_query_time=1

上述设置将导致处理时间超过 1 秒的查询被记录下来/var/log/mysql/slow-query.log。稍后您可以参考日志来识别查询并对其进行微调。

识别问题的另一种方法是使用SHOW FULL PROCESSLIST命令检查当前正在运行的查询列表。通过查看它，您可以缩小导致此问题的查询类型范围。

并粘贴的输出vmstat 5 10。这将有助于查找是否存在硬件瓶颈。

Answer

该问题与存储引擎无关，问题可能是因为一些 SQL 查询占用了过多的 CPU 资源来处理该特定站点/应用程序。这可能是因为查询使用了“未索引”列或查询效率不高。更好地理解该问题，请在 mysql 上启用“慢查询”日志。

log_slow_queries=/var/log/mysql/slow-query.log
long_query_time=1

上述设置将导致处理时间超过 1 秒的查询被记录下来/var/log/mysql/slow-query.log。稍后您可以参考日志来识别查询并对其进行微调。

识别问题的另一种方法是使用SHOW FULL PROCESSLIST命令检查当前正在运行的查询列表。通过查看它，您可以缩小导致此问题的查询类型范围。

并粘贴的输出vmstat 5 10。这将有助于查找是否存在硬件瓶颈。

编辑，MySQL 5.5

编辑，MySQL 5.5

编辑，已解决（自行解决）

编辑2014年12月29日

答案1

更新时间 2014-12-12 10:30 EST

答案2

相关内容