mysql - InnoDB : page_cleaner: 1000ms intended loop took xxx ms. 设置可能不是最佳的

Question

我在 Ubuntu 16.04.1 LTS 上使用 5.7.16-10-log Percona Server。

我最近将我的 AWS ec2 实例从 m4.2xlarge 实例类型升级到 m4.4xlarge。升级后我的 mysql 错误日志定期填充以下行:

2017-09-15T17:10:30.678258+05:30 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4460ms. The settings might not be optimal. (flushed=140, during the time.)
2017-09-15T17:14:31.048443+05:30 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6047ms. The settings might not be optimal. (flushed=107, during the time.)
2017-09-15T17:15:06.312119+05:30 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 8262ms. The settings might not be optimal. (flushed=64, during the time.)

当 Percona Innobackupex 按照完全备份和增量备份的计划运行时，这些情况很常见。在这种时候，这里是根据 iostat 的磁盘使用情况:

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
xvda              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
xvdf              0.00    10.00    0.00    2.00     0.00    48.00    48.00     0.00    0.00    0.00    0.00   0.00   0.00
xvdb             21.00     0.00  672.00  816.00 63328.00 104448.00   225.51    30.65   20.74   19.28   21.94   0.67 100.00

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
xvda              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
xvdf              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
xvdb              0.00     4.95  630.69  802.97 62875.25 102677.23   230.95    30.27   21.11   19.80   22.14   0.69  99.01

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
xvda              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
xvdf              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
xvdb             23.00   153.00  988.00  668.00 94888.00 72104.00   201.68    26.84   16.21   14.57   18.62   0.59  98.40

即使在将 lru 扫描深度从默认值 1024 降低后，我仍然在 mysql 错误日志中收到这些消息，我发现网络上的一些类似帖子推荐了这种深度。

SET GLOBAL innodb_lru_scan_depth=256;

根据某人的建议，我还尝试通过以下方法找出问题是出在 LRU 列表还是刷新列表:

SHOW ENGINE INNODB STATUS\G

在下面的输出中，在某些情况下，我能够在待处理写入中看到大于零的刷新列表:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 50300190720
Dictionary memory allocated 18819582
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 3805993024  (764908736 + 3041084288)
    Page hash           5976584 (buffer pool 0 only)
    Dictionary cache    210046766   (191227184 + 18819582)
    File system         1570552     (812272 + 758280)
    Lock system         119847848   (119530904 + 316944)
    Recovery system     0   (0 + 0)
Buffer pool size   2948760
Buffer pool size, bytes 0
Free buffers       417019
Database pages     2346130
Old database pages 866201
Modified db pages  60127
Pending reads      0
Pending writes: LRU 0, flush list 10, single page 0
Pages made young 5082, not young 0
0.17 youngs/s, 0.00 non-youngs/s
Pages read 2139505, created 206625, written 1680831
1323.32 reads/s, 0.70 creates/s, 21.87 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 1314.62/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2346130, unzip_LRU len: 0
I/O sum[0]:cur[128], unzip sum[0]:cur[0]

这是我使用的 my.cnf 的一个片段:

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/run/mysqld/mysqld.sock
pid-file                       = /var/run/mysqld/mysqld.pid
basedir    = /usr
tmpdir   = /var/tmp
lc-messages-dir  = /usr/share/mysql
explicit_defaults_for_timestamp
log_timestamps                 = SYSTEM
skip-name-resolve
net-write-timeout              = 600
net-read-timeout               = 600
innodb-page-cleaners           = 8
performance-schema             = OFF

# MyISAM #
key-buffer-size                = 32M

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
sysdate-is-now                 = 1

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

# BINARY LOGGING #
server-id                      = 2
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1
max_binlog_size                = 1G
max_binlog_files               = 20

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 4510
table-definition-cache         = 4096
table-open-cache               = 4096

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 45G
innodb-print-all-deadlocks     = ON

# LOGGING #
log-error                      = /var/log/mysql/mysql-error.log
slow-query-log-file            = /var/log/mysql/mysql-slow.log
lower-case-table-names         = 1
character-set-client-handshake = FALSE
character-set-server           = utf8mb4
collation-server               = utf8mb4_general_ci

#Slow query queries
slow-query-log                 = 1
long-query-time                = 10
slow-query-log-always-write-time = 15 #Slow query queries
log-slow-verbosity             = full
log-slow-rate-type             = query
log-slow-rate-limit            = 100 #queries logged/sec
log-queries-not-using-indexes  = 1

任何解决和解决此问题的建议/帮助/指标都会有很大帮助。

这是我的产品中的限制 -a :

$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 257583
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 257583
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

Answer 1

您打开的文件数限制为 1024，请将其提高到 90000。每个文件的操作系统成本在 RAM 中几乎为零。在实现时验证您的更改。然后在你的配置部分要做的事情只有 1 改变，监控如果没有负面影响，请在下一个工作日再更改一次。许多可以动态更改，请参阅用户指南。

thread_cache_size=100 # from 50 to minimize 
threads_created
open_files_limit=30000 # from 4510
table_definition_cache=10000 # from 4096
table_open_cache=10000 # from 4096

这几天的修改/监控将改进您的实例。

在另一天(或一个小时)针对不使用索引的单个查询

slow_query_log=1  # for ON
min_examined_row_limit=1  # to eliminate useless chatter
log_queries_not_using_indexes=1 
long_query_time=5000 # for 5000 seconds which will be RARE 

避免慢速查询和不使用索引信息的查询混合在一起

当你完成这个单一的焦点时，

min_examined_row_limit=0 for useless chatter in the slow query log
log_queries_not_using_indexes=0
long_query_time=10    or less depending on your need to monitor.

为了进一步分析，您有多少 RAM，您使用的是 SSD 还是旋转硬盘？请发布 A) SHOW GLOBAL STATUS 的 TEXT 结果； B) 显示全局变量；