时间戳

首页 > 解决方案 > MySQL InnoDB：长时间的信号等待：

mysql - MySQL InnoDB：长时间的信号等待：

问题描述

今天早上我们遇到了一个生产 MySQL 问题，该应用程序没有从数据库中得到响应并开始出现问题。

以下是我在日志中可以找到的内容，谁能向我解释 MySQL 上发生了什么以及如何避免此事件再次发生？，我们之前或之后没有其他日志条目，我看不到正在运行什么当时。

Nov 27 07:44:34  mysqld[19527]: Aborted connection 1271090 to db: 'information_schema' user: 'cmon' host: '' (Got an error reading communication packets)
Nov 27 07:46:55  mysqld[19527]: InnoDB: page_cleaner: 1000ms intended loop took 15077ms. The settings might not be optimal. (flushed=923, during the time.)
Nov 27 07:47:16  mysqld[19527]: InnoDB: page_cleaner: 1000ms intended loop took 19703ms. The settings might not be optimal. (flushed=868, during the time.)
Nov 27 07:48:35  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:49:06  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:49:06  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:49:37  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:49:37  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:50:08  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:50:08  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:50:39  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:50:39  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:51:10  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:51:10  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:51:41  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:51:41  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:51:41  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:52:12  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:52:12  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:52:12  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:52:43  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:52:43  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:52:43  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:53:14  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:53:14  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:53:14  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:53:14  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:53:45  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:53:45  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:53:45  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:53:45  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:54:16  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:54:16  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:54:16  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:54:16  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:54:47  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:54:47  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:54:47  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:54:47  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 07:55:04  mysqld[19527]: Aborted connection 1449537 to db: 'information_schema' user: 'cmon' host: '' (Got an error reading communication packets)
Nov 27 07:55:04  mysqld[19527]: Aborted connection 1450163 to db: 'information_schema' user: 'cmon' host: '' (Got an error reading communication packets)
Nov 27 07:55:15  mysqld[19527]: InnoDB: page_cleaner: 1000ms intended loop took 4665ms. The settings might not be optimal. (flushed=1009, during the time.)
Nov 27 07:58:00  mysqld[19527]: InnoDB: page_cleaner: 1000ms intended loop took 160462ms. The settings might not be optimal. (flushed=708, during the time.)
Nov 27 08:02:01  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 08:02:32  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 08:02:32  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 08:02:32  mysqld[19527]: InnoDB: A long semaphore wait:
Nov 27 08:02:54  mysqld[19527]: Aborted connection 1450788 to db: 'information_schema' user: 'cmon' host: '' (Got an error reading communication packets)
Nov 27 08:02:54  mysqld[19527]: InnoDB: page_cleaner: 1000ms intended loop took 290741ms. The settings might not be optimal. (flushed=650, during the time.)

标签： mysqlinnodbsemaphore

解决方案

这可能会解决“page_cleaner”的问题：降低这个：

lru_scan_depth = 50

推荐阅读