Mysql线程卡在 'query end'，如何防止疯狂刷新

Question

MySQL 变得无响应，因为许多简单的 UPDATE 和 INSERT 线程陷入“查询结束”状态。

---TRANSACTION F528F961, ACTIVE (PREPARED) 858 sec
 mysql tables in use 1, locked 1
 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
 MySQL thread id 82683520, OS thread handle 0x7f73a6925700, query id 14714499253 192.168.1.22 wms query end
UPDATE  `users`
    SET `id` = '6016', `es_id` = '4817', `department_id` = '4',
        `schedule_id` = '1', `username` = 'john.doe',
        `user_role` = 'Guest,Admin,Picker',
        `status` = '1', `team` = '2', `email` = NULL,
        `wms_user` = '1', `logged_in_time` = '2016-02-01 07:06:45',
        `last_activity` = '2016-02-01 13:07:49',
        `session_id` = 'qbei0rrfiu05l9olcckh6sg976'
    WHERE  (id = 6016) 

CPU 负载上升，磁盘 IO 上升，命中率下降。 CPU Load/Disk IO甚至“use db”和“show master status”线程也出现在慢速日志中。

据我所知，这是“剧烈的潮红”。用户通过应用程序运行了一个大型 SELECT 语句。 select 内连接会连接 12 个 sum(data_length + index_length) = 11.2G 的 InnoDB 表并对结果进行排序。问题是这并不是一个不寻常的查询。它运行频率很高，但工作集要小得多:

# Query_time: 1.737293 Lock_time: 0.000027 Rows_sent: 7051 Rows_examined: 1109050

这次用户想要过去 2 个月的数据，这导致:

# Query_time: 370.063806 Lock_time: 0.000039 Rows_sent: 919 Rows_examined: 27994638

来自引擎 InnoDB 状态:

主线程进程号24701，id 140134828910336，状态:刷新缓冲池页面
0.00 插入/秒、0.00 更新/秒、0.00 删除/秒、894332.73 读取/秒

服务器运行Debian 6.0.4，MySQL 5.5.31社区版，32核CPU 2.60GHz/64GB RAM/SSD

我的.cnf:

innodb_buffer_pool_size =  40G 
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_thead_concurrency = 0 ## modified to 32 after crash
innodb_read_io_threads  = 4
innodb_write_io_threads = 4 
innodb_old_blocks_pct = 37 
innodb_flush_method=NULL ## would change to O_DIRECT but needs restart
innodb_old_blocks_time = 0 ## modified to 1000 after crash

这可能是一个孤立的案例，但我想知道将来如何防止这种情况发生。请提供您的意见。谢谢。

Answer 1

为用户构建汇总表。这将

• 防止SELECT减慢UPDATE。
• 使 SELECT 的运行速度提高 10 倍。
• 减少整体系统使用率。

More discussion .