这简直要了我的命。请帮我。我的生产服务器每周卡住一次。有些查询会锁定行并阻止其他查询，一直占用 100% 的 CPU。我需要在服务器恢复工作之前手动终止那些卡住的查询。

我有一个系统可以根据任何给定的时段在网站上显示最高的点击率横幅。这是我的表结构。

CREATE TABLE IF NOT EXISTS `banners` (
  `banner_id` int(5) NOT NULL AUTO_INCREMENT,
  `banner_slot` varchar(15) NOT NULL,
  `banner_img_path` varchar(200) NOT NULL,
  `banner_link` varchar(200) NOT NULL,
  `banner_views` int(8) NOT NULL DEFAULT '1',
  `banner_clicks` int(8) NOT NULL DEFAULT '0',
  `banner_ctr` double(5,3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`banner_id`),
  KEY `banner_slot` (`banner_slot`,`banner_views`,`banner_ctr`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4231;

这在生产服务器上运行(CentOS6.3/MySQL 5.5.28/Apache2/四核 CPU/8GB 内存)。很老了，我知道。

服务器还运行了几个 wordpress 站点，我已经使用了 WP super 缓存插件。有一些来自 wordpress 的查询出现在慢查询日志文件中，但它们都在 5 秒内。

最近，我每天的页面浏览量约为 2 万次。在高峰时段有大约 2-3K UIP(所有域加起来)。

这是我的 my.cnf 设置:

innodb_file_per_table=1
open_files_limit=10192
skip-external-locking
key_buffer_size=800M
max_allowed_packet=100M
table_open_cache=512
table_cache=500
sort_buffer_size=2M
read_buffer_size=4M
read_rnd_buffer_size=8M
thread_cache_size=4
query_cache_type=0
query_cache_size=4M
join_buffer_size=8M
tmp_table_size=512M
max_heap_table_size=256M
max_connections=200

这是我的慢查询日志文件。您会在这里看到这样一个基本查询的锁定时间很长。

# Query_time: 84.554967  Lock_time: 37.070954 Rows_sent: 0  Rows_examined: 1
use db_name;
SET timestamp=1537010708;
UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=6;
# Query_time: 84.614748  Lock_time: 37.130587 Rows_sent: 0  Rows_examined: 1
use db_name;
SET timestamp=1537010708;
UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=60;
# Query_time: 54.288041  Lock_time: 0.000018 Rows_sent: 0  Rows_examined: 1
SET timestamp=1537010708;
UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=884;
# Query_time: 104.154232  Lock_time: 34.661097 Rows_sent: 0  Rows_examined: 1
use db_name;
SET timestamp=1537010744;
UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=60;
# Query_time: 107.847145  Lock_time: 38.354068 Rows_sent: 0  Rows_examined: 1
SET timestamp=1537010744;
UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=6;
# Query_time: 81.974780  Lock_time: 26.446288 Rows_sent: 0 Rows_examined: 1
SET timestamp=1537010771;
UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=6;
# Query_time: 102.331612  Lock_time: 46.507686 Rows_sent: 0  Rows_examined: 1
use db_name;
SET timestamp=1537010772;
UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=60;
# Query_time: 81.808158  Lock_time: 36.196089 Rows_sent: 0  Rows_examined: 1
SET timestamp=1537010772;
UPDATE banners SET banner_views=banner_views+1 WHERE banner_id=7;

我已经检查了 mysql-tuner.pl，根据建议，几乎所有设置都是绿色的。

我也在解释所有的 SELECT 查询，它们都使用索引。因为我在 MySQL 5.5 上运行，所以我无法解释任何其他查询。

对我的问题有什么建议吗？提前致谢!感谢您的帮助!

最佳答案

每秒速率= RPS

关于 my.cnf [mysqld] 部分的建议

# 20180916 12:30  by  mysqlservertuning.com

query_cache_size=0  # from 4M conserve RAM & CPU QC type=0 = OFF to start with
read_buffer_size=128K  # from 4M to reduce CPU busy & handler_read_next RPS
read_rnd_buffer_size=256K  # from 8M to reduce CPU busy & handler_read_rnd_next RPS
join_buffer_size=128K  # from 8M to conserve RAM
thread_cache_size=32  # from 4 to reduce threads_created & CPU overhead
max_heap_table_size=512M  # from 256M should be same as tmp_table_size always

复制现有的 my.cnf 备份到某处，(以防万一)

要复制到 my.cnf 的 [mysqld] 部分的 END 的日期行和所有 6 个变量 覆盖以前请求的值。

停止/启动您的实例或重新启动您的实例。如果时间允许，请通过 SKYPE 联系我。

关于更新查询上的 MySQL 高 lock_time 导致服务器卡住，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/52348822/