记一次磁盘问题导致的Mysql卡顿

obus
262 阅读4分钟

解决流程

问题现象

应用后台出现大量慢查询打印，且大部分集中在凌晨。一段时间后，表现为应用全天都卡顿。

slow sql 32856 millis. UPDATE QRTZ_SCHEDULER_STATE

问题排查

  1. 该打印为druid框架的慢查询打印。应用使用了quartz框架执行定时任务，quartz框架每分钟会到数据库更新状态，更新时很卡，导致出现大量打印。
  2. 查询mysql数据库(版本为8.0.24)，对应的QRTZ_SCHEDULER_STAT表只有一行数据，排除了表设计问题。
  3. 注意到凌晨，有大数据平台会持续向mysql写入数据，怀疑是并发写入导致的。观察写入任务的日志，这几周写入时间突然变长，由几分钟变为几十分钟。但任务并没有新增和调整，可能与大量写入无关。
  4. 初次遇到mysql性能问题，且mysql数据库所在服务器的内存与cpu利用率很奇怪，都比较低，考虑调整mysql参数，看是否有改善。初次加大参数后，性能有所变化，但仍然很卡。将参数改动还原后，更卡了。
  5. 写入mysql时是采用的批量insert方式，是否是每条insert语句插入的数据行数太少，加大每条的插入行数。有一定提升，但仍旧很慢。
  6. 问题逐渐变得诡异。用iostat查看大量写入mysql时的利用率很高，接近100%。
  7. 根据同事反馈另外一个应用前段时间也出现了卡顿问题，迁移服务器后问题消失。于是，迁移服务器，mysql恢复正常。
  8. 最终确定该问题是 磁盘 硬件问题导致的。

iostat -xm 5显示磁盘利用率达到100%，切换服务器后，利用率下降，任务也恢复正常了。

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           5.62    0.00    0.77   18.06    0.00   75.56

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00    0.00  161.71     0.00    25.90   327.95     2.26   28.51    0.00   28.51   6.18 100.01
  
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.89    0.00    0.26   31.22    0.00   66.64

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00    0.00    9.43     0.00     4.32   938.41     4.02    3.42    0.00    3.42 106.06 100.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.37    0.00    0.03   31.22    0.00   68.38

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00    0.00    1.57     0.00     0.01    15.64     4.00    0.55    0.00    0.55 636.36 100.00

问题反思

  1. 日志中的打印逐渐增多。一开始只在凌晨，后来变为全天都有打印。
  2. 写入任务的配置变化不大，但执行时间逐渐变长。
  3. 调整mysql参数的影响有限，似乎根本不起作用。
  4. iostat一开始的利用率没有这么高，逐渐接近100%。结合cpu与内存利用率低，可以确定瓶颈在磁盘，且磁盘状况逐渐变坏。
  5. 之前就有过磁盘损坏的先例，遇到磁盘问题，可以考虑该原因。

可参考的文档

  1. Mysql8文档
  2. MySQL Performance Tuning Guide
  3. 浅谈性能分析-耀阳居士

排查方式总结

修改系统配置

ulimit限制，ulimit -n显示1024，提高该数值

修改文件/etc/security/limits.conf

查看/proc/[pid]/limits是否生效

systemd方式启动可能还需要修改/etc/systemd/system.conf 或 user.conf

避免使用swap

修改vm.swappiness=0

查看Mysql日志

/var/log/messages 日志

*-slow.log 日志

开启慢查询日志，记录5秒以上的查询

slow_query_log=ON;

long_query_time=5;

*.err 日志

*.shutdown 日志

查看Mysql参数、状态

show global variables

show global status

flush status 清空状态缓存

查看Mysql当前事务

show processlist

select * from information_schema.INNODB_TRX

SHOW OPEN TABLES where In_use >0

修改Mysql锁等级

也会影响性能，但修改可能导致幻读，幻写等问题。默认为 Repeatable read

修改Mysql参数

临时修改。如 set global max_connections = 512

永久修改，修改/etc/my.cnf

部分Mysql参数

读写进程数，默认是4

innodb_read_io_threads=4

innodb_write_io_threads=4

最大连接数

max_connections

超时时间

net_read_timeout

net_write_timeout

connect_timeout

是否跳过dns反向查询

skip_name_resolve=on

缓冲池总大小，实例数量，块大小。总大小=实例数*块大小的倍数

innodb_buffer_pool_size

innodb_buffer_pool_instances

innodb_buffer_pool_chunk_size

redo log (即ib_logfile文件) 的文件数量，每个文件的大小，以及缓冲。

innodb_log_files_in_group

innodb_log_file_size

innodb_log_buffer_size

binlog缓冲大小

binlog_cache_size

刷新硬盘的频率，默认都是1，调整以后会影响可靠性，网上推荐2，0，或者2，100

innodb_flush_log_at_trx_commit=1

sync_binlog=1

每秒的IO，和硬盘种类有关，固态可以设大一些，需谨慎设置

innodb_io_capacity

innodb_io_capacity_max

查看innodb状态及分析

SHOW ENGINE INNODB STATUS

根据浅谈性能分析-耀阳居士

ROW OPERATIONS部分 一直提示state=checking free log space

pstack pid查看对应线程的堆栈

需要调整innodb_page_cleaners，该值=innodb_buffer_pool_instances

ROW OPERATIONS部分 一直提示state=doing insert buffer merge

提高innodb_buffer_pool_size

avatar
文章
阅读
粉丝