当innodb_buffer_pool_size设置为高时DB会随机锁定

Question

我一直在努力找出这里可能发生的事情，但是很多个月来，自从升级到MySQL5.6之后，我们似乎在页面加载中经历了随机的滞后高峰，而页面加载已经被特别地追溯到mysql和innodb_buffer_pool_size设置。

服务器如下；

• CentOS 6 64位cpanel/WHM
• 双英特尔XeonE5-2620-Hex核心2.0GHz，15 E5缓存，HyperThreaded 12个核心，24个线程
• 24 RAM
• 2x240 SSD
• Apache 2.4
• PHP 5.5 (fcgid处理程序)
• mysql 5.6

我们正在运行一个Magento网站，每天大约有1500名访问者，DB大小约为5GB。没什么大不了的。

使用Newrelic，我们可以看到我们可以轻松地将12 we的ram分配给innodb_buffer_pool_size。然而，大约24小时后，我们开始随机加载高峰，其中页面可能需要10秒到分钟的加载，然而，如果您在这样一个加载高峰的中间点击刷新，页面将按照正常情况加载不到1秒。

当我们尝试使用php作为php处理程序以便正确地使用opcache时，这个问题就特别明显了。当我们尝试这样做时，PHP进程将在mysql锁期间得到备份，进程号会增加，直到站点崩溃。它变得如此不稳定，以至于我们不得不回到使用fcgid，它只是暂停，而不是崩溃。

看看Newrelic，我们可以看到mysql中的加载时间峰值，但是服务器内存和CPU的使用仍然远远不够。

奇怪的是，把innodb_buffer_pool_size留给拖欠债务似乎会让问题至少持续一周左右。一旦mysql的使用量开始达到900 we到1gb的范围，尖峰就会再次出现，我们必须重新启动mysql。那我们就可以再待一周左右了。

使用默认的mysql设置，Newrelic显示大约80%的ram是免费的。

下面是我们当前的my.cnf；(如您所见，我们已经注释掉了innodb缓冲区行和所有日志记录)

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock

# MyISAM #
key_buffer_size                = 64M
myisam_recover_options         = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip-name-resolve
innodb                         = FORCE

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
#log-bin                        = /var/lib/mysql/mysql-bin
#expire_logs_days               = 14
#sync_binlog                    = 1

# CACHES AND LIMITS #
wait_timeout               = 300
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 500

# INNODB #
innodb_log_file_size           = 256M # if changing, stop database, remove old log files, then start!
innodb_file_per_table          = 1
#innodb_buffer_pool_size        = 12G
#innodb_buffer_pool_instances   = 12

# LOGGING #
#log-error                      = /var/lib/mysql/mysql-error.log
#log-queries-not-using-indexes  = 1
#slow-query-log                 = 1
#slow_query_log_file            = /var/lib/mysql/mysql-slow.log

我们已经去了我们的主机供应商，并要求他们的帮助。最初，他们告诉我提高innodb_buffer_pool_instances，因为他们认为他们可能是一些锁的争论。我把它设置为12到24，这个问题一直存在。

然后，他们运行了一个memtest来检查内存中的问题，结果一无所获。最后，他们放弃了，让我们去咨询DB专家。

我就是想不出来。任何帮助都将不胜感激。

*更新1*

因此，我一直在深入研究这个问题，并有了一些新发现。

我注释了innodb_buffer_pool_size = 12G，并开始监视SHOW的输出。我注意到，在延迟时间内，查询，特别是查询，有时会陷入“写到网”状态，有时一次只停留几分钟。这些查询将在毫秒内从cli或mysql工作台执行。

为了尝试更多关于服务器在这些缓慢写入网络状态时发生了什么的数据，我安装了Percona工具，并设置了一个pt-茎守护进程来监视服务器，并且每当一个查询处于写到网络状态的时间超过5秒时就触发一个集合。

看一下pt茎输出文件，看起来非常有趣的是opentables1和opentables2文件中的输出，它们总是说.

2015_12_06_05_01_04太多的打开表: 2135

开放式桌子的数量各不相同，但似乎总是远远超过1000张。最初，我将此作为一条错误消息，并引发未修复问题的服务器的打开文件ulimit。然后，我找到了这个bug报告https://bugs.launchpad.net/percona-toolkit/+bug/1307377，它解释了这不是一个错误，而是一个警告pt-茎触发时，有超过1000个打开的表。

另一件事，藤茎可以告诉我，确切的时间，延迟开始发生。我注意到，对net状态的缓慢写入总是在Mysql达到4.8到5GB的内存使用时就开始发生的。4.8G恰好是tmp磁盘分区cpanel设置的大小。虽然这个分区似乎有很大的空闲空间，在任何时候只使用了380 my，但我的直觉告诉我，我应该尝试增加它，看看它是否有用。

这将是我的下一步，我将在这里报告结果。

如果有其他任何人认为我应该尝试，鉴于上述信息，请做分享。

Answer 1

这个问题是由我们过时的内核(Linux2.6.32-358.18.1.el6.x86_64)引起的，并通过将内核升级到最新版本(kernel.x86_64 2.6.32-573.12.1.el6)解决了这个问题。

Answer 2

我也遇到过类似的问题。实验表明重新启动Apache是解决方法。也就是说，MySQL不是错误的(就我所见过的情况而言)。它是Apache2.4.4，所以可能与您的相同。

请用理论来检验，只重新启动Apache。

证据表明它不是MySQL:在SHOW PROCESSLIST中什么都没有。当使用更多Apache线程时，更有可能发生这种情况。