1、 问题现象

　　MySQL 更新一条数据耗时在0.5s左右，而且commit耗时占用99%的时间。

 

2、 问题分析

2.1 查看MySQL中的线程

show processlist;
+------+-----------------+-------------------+----------+---------+--------+----------------------------+------------------+
| Id   | User            | Host              | db       | Command | Time   | State                      | Info             |
+------+-----------------+-------------------+----------+---------+--------+----------------------------+------------------+
|    5 | event_scheduler | 10.10.42.11       | NULL     | Daemon  | 670106 | Waiting on empty queue     | NULL             |
|  698 | products        | 10.10.42.11:48564 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  701 | products        | 10.10.42.11:48570 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  702 | products        | 10.10.42.11:48572 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  703 | products        | 10.10.42.11:48574 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  705 | products        | 10.10.42.11:48578 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  706 | products        | 10.10.42.11:48580 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  709 | products        | 10.10.42.11:48586 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  710 | products        | 10.10.42.11:48588 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1106 | products        | 10.10.42.11       | NULL     | Query   |      0 | init                       | show processlist |
+------+-----------------+-------------------+----------+---------+--------+----------------------------+------------------+

2.2 刷新日志

flush logs;

2.3 查看磁盘的空间使用率

查看磁盘空间已经满了，说明因为磁盘空间不足导致MySQL无法redo commit，数据库最终被hang住。

df -k
Filesystem     1K-blocks     Used Available Use% Mounted on
devtmpfs          929484        0    929484   0% /dev
tmpfs             941004        0    941004   0% /dev/shm
tmpfs             941004      560    940444   1% /run
tmpfs             941004        0    941004   0% /sys/fs/cgroup
/dev/vda1       51473868 23046092  26230532  47% /
/dev/vdb1       10189076  9648456         0 100% /data
tmpfs             188204        0    188204   0% /run/user/0

2.4 查看硬盘的IO性能

　　重点分析硬盘读写吞吐量和硬盘读写延时（iostat -kdxt 3/iotop -c）