MySQL InnoDB:漫长的信号量等待：

Question

今天早上我们遇到了一个生产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.)

Answer 1

这可能解决“page_cleaner”的一个问题:降低这个值：

lru_scan_depth = 50