我们正在使用 InnoDB 表作为 Web 应用程序的后端，大约两年来一切都很顺利，直到几周前我们不得不重新启动 MySQL。（我们没有禁用反向 DNS 查找，尽管我们实际上并没有使用它们，但我们的托管系统突然停止响应这些请求。它们现在已被禁用。）不幸的是，配置文件已更改，我们没有其原始状态的副本可供比较。
在修复了最重要的问题之后，我们遇到了一个真正的难题：在高负载下，数据库查询开始花费比平时更长的时间。在这种时候，我们有来自七台 Apache 服务器的数百个打开的连接。运行 SHOW PROCESSLIST 发现这些连接中有一半或更多处于“正在发送数据”状态，通常需要几百秒的时间。几乎所有的查询都是 SELECT，类似的查询往往会聚集在一起。事实上，列表中最低的块往往是完全相同的查询（我希望它在查询缓存中），返回 1104 行，每行两个整数。其他常见的问题包括几百个单整数行、几个单整数行，甚至一个 COUNT(*) 结果的列表。
我们尝试在其中一个时间段内关闭 Web 服务器，但问题在重新启动后一分钟内再次出现。但是，完全重新启动 mysqld 可以解决问题，直到第二天。问题可能是什么？我们如何验证和/或修复它？
答案1
好吧，请注意，如果我没记错的话（我做数据库工作已经有一段时间了）在 innodb 表上没有 WHERE 子句的 COUNT(*) 查询比在 MyISAM 和内存表上要慢得多。
另外，这是一个 Xen DomU 吗？
前端语言是什么？如果是 PHP，使用的是 MySQL 还是 MySQLi？它们使用的是持久连接吗？
您没有提到底层操作系统，但在 Linux 的情况下，我会首先查看输出free -m，特别注意最后两行以查看内存是否整体紧张。
[0:504] callisto:cyanotype $ free -m
total used free shared buffers cached
Mem: 3961 3816 144 0 184 1454
-/+ buffers/cache: 2177 1784
Swap: 2898 0 2898
这里我们有一个健康的系统（这是我的工作站）。第二列不包括缓冲区和缓存，所以我实际上使用了 2177mb 内存，并且有 1784 兆字节可用。
最后一行表明到目前为止我根本没有使用交换。
然后给出vmstat(8)，看看你的系统是否正在疯狂地破坏也是有用的。
[0:505] callisto:cyanotype $ vmstat 5 10
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 0 134116 189828 1499948 0 0 11 3 44 49 1 1 98 0
0 0 0 143112 189836 1489688 0 0 0 6 526 2177 1 1 98 0
0 0 0 139268 190504 1491864 0 0 512 4 663 4704 2 1 96 1
2 0 0 136688 191084 1493484 0 0 473 5 641 3039 1 1 97 1
0 0 0 52636 191712 1518620 0 0 5066 4 1321 6600 8 2 86 4
5 0 0 72992 193264 1377324 0 0 10742 31 1602 7441 12 3 80 5
2 1 0 84036 193896 1202012 0 0 10126 43 2621 4305 31 2 57 10
3 0 0 42456 195812 1060904 0 0 3970 75 55327 9806 43 5 41 10
8 1 0 34620 197040 942940 0 0 3554 64 50892 12531 43 6 44 6
^C
[0:506] callisto:cyanotype $
（抱歉，我的台式机在这里确实没法做那么多事情。真是浪费了 8 个完好无损的核心）
如果您看到很多进程在“b”列中花费时间，则意味着它们被阻塞，正在等待某些东西。通常是 IO。这里重要的列是si和so。检查它们是否填充了高值。如果是这样，这可能是您的问题——某些东西正在消耗大量内存，超出您实际承受能力。使用top(4)并按内存百分比对列进行排序（在 top 中按 shift+m）可能会显示罪魁祸首。
您的系统可能正在交换和卸载磁盘，并使磁盘饱和，从而导致线程和进程阻塞。应该试用该工具iostat(8)（通常是软件包的一部分sysstat）来查看是否有进程阻塞、卡在 IO_WAIT 上。磁盘饱和可能对高负载下的整个系统造成坏消息，尤其是在系统大量交换的情况下。
您可以每五秒运行一次带有扩展统计信息的 iostat，例如：
[0:508] callisto:cyanotype $ iostat -x 5
Linux 2.6.35-23-generic (callisto) 2010-11-30 _x86_64_ (8 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
16,55 0,12 2,70 2,60 0,00 78,02
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sdc 0,00 2,00 1,00 0,80 27,20 22,40 27,56 0,01 3,33 3,33 0,60
sdd 0,00 12,60 67,60 4,80 4222,40 139,20 60,24 0,62 8,62 3,29 23,80
sde 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
sdf 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
avg-cpu: %user %nice %system %iowait %steal %idle
32,02 0,10 1,83 0,44 0,00 65,61
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sdc 0,60 3,20 11,00 0,80 265,60 32,00 25,22 0,05 3,90 2,88 3,40
sdd 0,00 8,20 0,00 3,00 0,00 89,60 29,87 0,02 8,00 7,33 2,20
sde 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
sdf 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
avg-cpu: %user %nice %system %iowait %steal %idle
49,26 0,22 3,12 0,12 0,00 47,28
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sdc 6,20 3,00 7,40 3,80 208,00 54,40 23,43 0,09 7,86 2,50 2,80
sdd 0,00 15,20 0,20 4,00 1,60 152,00 36,57 0,03 6,67 6,19 2,60
sde 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
sdf 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
avg-cpu: %user %nice %system %iowait %steal %idle
16,00 0,54 1,05 1,07 0,00 81,35
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sdc 4,20 0,00 31,40 0,00 3204,80 0,00 102,06 0,17 4,90 2,68 8,40
sdd 0,00 28,20 0,20 2,60 1,60 246,40 88,57 0,02 7,14 7,14 2,00
sde 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
sdf 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00
^C
这应该可以让您轻松查看卷是否已饱和。例如，在这里，您可以看到我的磁盘利用率严重不足，系统大部分 CPU 周期处于空闲状态，等等。如果该百分比主要在 % IOWAIT 列中，那么这里就存在 IO 瓶颈。您可能已经知道所有这些，但只是为了确保万无一失，我们只介绍所有基础知识。
这个想法是，您的配置文件发生了变化，而您没有历史记录（出于这个原因，将您的配置文件置于版本控制之下是一个好主意） - 并且缓冲区的大小突然改变并不是不可能的，从而使得昂贵的查询（如没有 SELECT 的 COUNT(*)）突然开始吞噬资源。
根据您从以前使用上述工具中了解到的情况 - 您应该检查配置文件（这是唯一改变的东西，很可能是罪魁祸首）以查看缓冲区值是否适合您的平均负载。
缓冲区有多大，比如值query_cache_size，特别是sort_buffer大小？（如果内存装不下，它将在磁盘上执行，我相信您可以想象这将花费巨大的成本）。
有多大innodb_buffer_pool_size？
这个值有多大？table_cache最重要的是，这个值是否符合文件句柄的系统限制？（[mysqld] 中的 open-files-limit 和操作系统级别）。
另外，我不记得这是否仍然正确，但我相当确定，每当必须提交自动增量字段时，innodb 实际上都会锁定整个表。我在 Google 上搜索，找不到这是否仍然正确。
您还可以使用它innotop(1)来更详细地了解正在发生的事情。
我希望这能有所帮助或者给你一个起点:)
答案2
事实证明，这是innodb_file_per_table、default-storage-engine = innodb以及创建临时表的频繁访问页面。每次关闭连接时，它都会删除该表，从缓冲池 LRU 中丢弃页面。这会导致服务器暂停一段时间，但永远不会停止真正导致问题的查询。
更糟糕的是，在服务器因为完全不相关的原因而必须重新启动之前，该innodb_file_per_table设置已经在我们的文件中搁置了几个月，在此期间我们一直在使用这些临时表，没有任何问题。（NOC 突然关闭了 DNS 服务器，导致每个新连接都挂起，因为我们没有启用，并且几个小时内都不会承认发生了任何变化。）my.cnfskip-name-resolve
幸运的是，我们能够重写有问题的页面，使用一组更快的查询，将大部分工作加载到前端 Web 服务器上，从此再也没有出现问题。
答案3
造成这种情况的原因可能有很多。在我们的特定情况下，这种情况发生在查询数量在短时间内激增时，这导致 CPU 出现抖动，因为线程数量超过了服务器上核心数量的 4 倍。我们的问题是查询数量激增实际上对我们的应用程序来说是正常的，POSIX 实现在“大多数”时间里都能正常工作，但会间歇性地停止运行。经过大量调查，我们偶然发现了一个名为 thread-pool 的 Oracle mySQL 企业插件，它提供了处理线程的替代实现。更好的是 - Percona 服务器已经原生实现了这个（不需要插件），并且更改是在我们的 cnf 文件中测试的一行代码。结果显著提高了重负载性能。虽然这不太可能是许多实现的问题，但我希望这可能是一些实现的问题，并且这个简单的更改值得测试。
这是另一个用例示例：