专栏首页MySQL故障优化案例MySQL Cases-记录大量waiting for handler commit
原创

MySQL Cases-记录大量waiting for handler commit

什么是waiting for handler commit查看官方文档,https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html

  • waiting for handler commit The thread is waiting for a transaction to commit versus other parts of query processing.

从字面意思看,当前正在等待事务提交,应该就是最后的commit redo

本案例

一套测试库环境,开发说数据库hang住了,给了show processlist信息,让帮分析下原因,看到waiting for handler commit第一反应是不是磁盘空间满了呢?下面我们带着这样的猜测来验证下。

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           |
|  711 | products        | 10.10.42.11:48590 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  712 | products        | 10.10.42.11:48592 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  713 | products        | 10.10.42.11:48594 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  714 | products        | 10.10.42.11:48596 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  718 | products        | 10.10.42.11:48604 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  720 | products        | 10.10.42.11:48608 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  721 | products        | 10.10.42.11:48610 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  722 | products        | 10.10.42.11:48612 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  724 | products        | 10.10.42.11:48616 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  725 | products        | 10.10.42.11:48618 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  726 | products        | 10.10.42.11:48620 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  727 | products        | 10.10.42.11:48622 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  729 | products        | 10.10.42.11:48626 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  730 | products        | 10.10.42.11:48628 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  732 | products        | 10.10.42.11:48632 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  733 | products        | 10.10.42.11:48634 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  734 | products        | 10.10.42.11:48636 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  735 | products        | 10.10.42.11:48638 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  738 | products        | 10.10.42.11:48644 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  739 | products        | 10.10.42.11:48646 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  740 | products        | 10.10.42.11:48648 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  741 | products        | 10.10.42.11:48650 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  742 | products        | 10.10.42.11:48652 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  743 | products        | 10.10.42.11:48654 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  744 | products        | 10.10.42.11:48656 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  745 | products        | 10.10.42.11:48658 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  746 | products        | 10.10.42.11:48660 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  747 | products        | 10.10.42.11:48662 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  749 | products        | 10.10.42.11:48666 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  750 | products        | 10.10.42.11:48668 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  751 | products        | 10.10.42.11:48670 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  752 | products        | 10.10.42.11:48672 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  753 | products        | 10.10.42.11:48674 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  754 | products        | 10.10.42.11:48676 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  755 | products        | 10.10.42.11:48678 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  756 | products        | 10.10.42.11:48680 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  757 | products        | 10.10.42.11:48682 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  758 | products        | 10.10.42.11:48684 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  759 | products        | 10.10.42.11:48686 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  760 | products        | 10.10.42.11:48688 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  761 | products        | 10.10.42.11:48690 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  763 | products        | 10.10.42.11:48694 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  764 | products        | 10.10.42.11:48696 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  765 | products        | 10.10.42.11:48698 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  767 | products        | 10.10.42.11:48702 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  768 | products        | 10.10.42.11:48704 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  769 | products        | 10.10.42.11:48706 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  770 | products        | 10.10.42.11:48708 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  771 | products        | 10.10.42.11:48710 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  772 | products        | 10.10.42.11:48712 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  775 | products        | 10.10.42.11:48718 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  776 | products        | 10.10.42.11:48720 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  778 | products        | 10.10.42.11:48724 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  780 | products        | 10.10.42.11:48728 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  781 | products        | 10.10.42.11:48730 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  789 | products        | 10.10.42.11:48748 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  791 | products        | 10.10.42.11:48752 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  792 | products        | 10.10.42.11:48754 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  793 | products        | 10.10.42.11:48756 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  794 | products        | 10.10.42.11:48758 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  795 | products        | 10.10.42.11:48760 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  796 | products        | 10.10.42.11:48762 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  797 | products        | 10.10.42.11:48764 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  798 | products        | 10.10.42.11:48766 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  799 | products        | 10.10.42.11:48768 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  800 | products        | 10.10.42.11:48770 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  803 | products        | 10.10.42.11:48776 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  804 | products        | 10.10.42.11:48778 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  806 | products        | 10.10.42.11:48782 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  808 | products        | 10.10.42.11:48786 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  809 | products        | 10.10.42.11:48788 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  810 | products        | 10.10.42.11:48790 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  818 | products        | 10.10.42.11:48806 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  819 | products        | 10.10.42.11:48808 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  820 | products        | 10.10.42.11:48834 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  821 | products        | 10.10.42.11:48836 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  822 | products        | 10.10.42.11:48838 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  823 | products        | 10.10.42.11:48840 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  824 | products        | 10.10.42.11:48842 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  825 | products        | 10.10.42.11:48844 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  826 | products        | 10.10.42.11:48846 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  827 | products        | 10.10.42.11:48848 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  828 | products        | 10.10.42.11:48850 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  829 | products        | 10.10.42.11:48852 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  830 | products        | 10.10.42.11:48854 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  831 | products        | 10.10.42.11:48856 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  832 | products        | 10.10.42.11:48858 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  834 | products        | 10.10.42.11:48862 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  835 | products        | 10.10.42.11:48864 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  836 | products        | 10.10.42.11:48866 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  837 | products        | 10.10.42.11:48868 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  838 | products        | 10.10.42.11:48870 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  839 | products        | 10.10.42.11:48872 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  840 | products        | 10.10.42.11:48874 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  841 | products        | 10.10.42.11:48876 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  842 | products        | 10.10.42.11:48878 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  843 | products        | 10.10.42.11:48880 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  844 | products        | 10.10.42.11:48882 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  846 | products        | 10.10.42.11:48888 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  849 | products        | 10.10.42.11:48892 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  850 | products        | 10.10.42.11:48894 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  851 | products        | 10.10.42.11:48896 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  852 | products        | 10.10.42.11:48898 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  856 | products        | 10.10.42.11:48910 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  857 | products        | 10.10.42.11:48912 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  858 | products        | 10.10.42.11:48906 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  859 | products        | 10.10.42.11:48908 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  860 | products        | 10.10.42.11:48914 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  861 | products        | 10.10.42.11:48916 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  862 | products        | 10.10.42.11:48918 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  863 | products        | 10.10.42.11:48920 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  864 | products        | 10.10.42.11:48922 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  865 | products        | 10.10.42.11:49236 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  866 | products        | 10.10.42.11:49238 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  867 | products        | 10.10.42.11:49240 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  868 | products        | 10.10.42.11:49242 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  869 | products        | 10.10.42.11:49244 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  870 | products        | 10.10.42.11:49246 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  871 | products        | 10.10.42.11:49248 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  872 | products        | 10.10.42.11:49250 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  873 | products        | 10.10.42.11:49252 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  874 | products        | 10.10.42.11:49254 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  875 | products        | 10.10.42.11:49256 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  876 | products        | 10.10.42.11:49258 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  877 | products        | 10.10.42.11:49260 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  878 | products        | 10.10.42.11:49262 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  879 | products        | 10.10.42.11:49264 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  880 | products        | 10.10.42.11:49268 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  881 | products        | 10.10.42.11:49270 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  882 | products        | 10.10.42.11:49272 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  885 | products        | 10.10.42.11:49278 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  886 | products        | 10.10.42.11:49280 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  887 | products        | 10.10.42.11:49266 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  888 | products        | 10.10.42.11:49282 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  889 | products        | 10.10.42.11:49920 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  890 | products        | 10.10.42.11:49922 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  892 | products        | 10.10.42.11:49926 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  893 | products        | 10.10.42.11:49928 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  894 | products        | 10.10.42.11:49936 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  895 | products        | 10.10.42.11:49938 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  896 | products        | 10.10.42.11:49940 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  897 | products        | 10.10.42.11:49942 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  898 | products        | 10.10.42.11:49944 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  899 | products        | 10.10.42.11:49946 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  900 | products        | 10.10.42.11:49948 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  901 | products        | 10.10.42.11:49950 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  902 | products        | 10.10.42.11:49952 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  903 | products        | 10.10.42.11:49954 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  904 | products        | 10.10.42.11:49956 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  905 | products        | 10.10.42.11:49958 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  906 | products        | 10.10.42.11:49960 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  907 | products        | 10.10.42.11:49962 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  908 | products        | 10.10.42.11:49964 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  909 | products        | 10.10.42.11:49966 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  910 | products        | 10.10.42.11:49970 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  911 | products        | 10.10.42.11:49972 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  912 | products        | 10.10.42.11:49968 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  913 | products        | 10.10.42.11:51042 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  914 | products        | 10.10.42.11:51044 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  915 | products        | 10.10.42.11:51046 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  916 | products        | 10.10.42.11:51048 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  917 | products        | 10.10.42.11:51050 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  918 | products        | 10.10.42.11:51052 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  919 | products        | 10.10.42.11:51054 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  920 | products        | 10.10.42.11:51056 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  921 | products        | 10.10.42.11:51058 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  922 | products        | 10.10.42.11:51060 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  923 | products        | 10.10.42.11:51062 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  924 | products        | 10.10.42.11:51064 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  925 | products        | 10.10.42.11:51066 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  926 | products        | 10.10.42.11:51068 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  927 | products        | 10.10.42.11:51070 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  928 | products        | 10.10.42.11:51072 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  929 | products        | 10.10.42.11:51074 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  930 | products        | 10.10.42.11:51076 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  931 | products        | 10.10.42.11:51078 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  932 | products        | 10.10.42.11:51080 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  933 | products        | 10.10.42.11:51082 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  934 | products        | 10.10.42.11:51084 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  935 | products        | 10.10.42.11:51086 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  936 | products        | 10.10.42.11:51088 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  937 | products        | 10.10.42.11:51090 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  938 | products        | 10.10.42.11:51092 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  939 | products        | 10.10.42.11:51094 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  940 | products        | 10.10.42.11:51096 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  941 | products        | 10.10.42.11:51098 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  942 | products        | 10.10.42.11:51100 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  943 | products        | 10.10.42.11:51102 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  944 | products        | 10.10.42.11:51104 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  945 | products        | 10.10.42.11:51106 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  946 | products        | 10.10.42.11:51108 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  947 | products        | 10.10.42.11:51110 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  948 | products        | 10.10.42.11:51112 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  949 | products        | 10.10.42.11:51114 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  950 | products        | 10.10.42.11:51116 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  951 | products        | 10.10.42.11:51118 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  952 | products        | 10.10.42.11:51120 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  953 | products        | 10.10.42.11:51122 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  954 | products        | 10.10.42.11:51124 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  955 | products        | 10.10.42.11:51126 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  957 | products        | 10.10.42.11:51130 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  958 | products        | 10.10.42.11:51132 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  959 | products        | 10.10.42.11:51134 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  960 | products        | 10.10.42.11:51136 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  961 | products        | 10.10.42.11:51138 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  965 | products        | 10.10.42.11:51146 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  966 | products        | 10.10.42.11:51148 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  967 | products        | 10.10.42.11:51150 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  969 | products        | 10.10.42.11:51154 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  970 | products        | 10.10.42.11:51156 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  972 | products        | 10.10.42.11:51160 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  973 | products        | 10.10.42.11:51162 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  974 | products        | 10.10.42.11:51164 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  977 | products        | 10.10.42.11:51170 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  979 | products        | 10.10.42.11:51174 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  980 | products        | 10.10.42.11:51176 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  981 | products        | 10.10.42.11:51178 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  982 | products        | 10.10.42.11:51180 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  983 | products        | 10.10.42.11:51182 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  984 | products        | 10.10.42.11:51184 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  985 | products        | 10.10.42.11:51186 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  986 | products        | 10.10.42.11:51188 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  987 | products        | 10.10.42.11:51190 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  988 | products        | 10.10.42.11:51192 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  989 | products        | 10.10.42.11:51194 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  990 | products        | 10.10.42.11:51196 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  993 | products        | 10.10.42.11:51202 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  995 | products        | 10.10.42.11:51206 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  996 | products        | 10.10.42.11:51208 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
|  997 | products        | 10.10.42.11:51210 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  998 | products        | 10.10.42.11:51212 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
|  999 | products        | 10.10.42.11:51214 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1000 | products        | 10.10.42.11:51216 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1001 | products        | 10.10.42.11:51218 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1002 | products        | 10.10.42.11:51220 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
| 1003 | products        | 10.10.42.11:51222 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1004 | products        | 10.10.42.11:51224 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1006 | products        | 10.10.42.11:51228 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1007 | products        | 10.10.42.11:51230 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1008 | products        | 10.10.42.11:51232 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1009 | products        | 10.10.42.11:51234 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1010 | products        | 10.10.42.11:51236 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1011 | products        | 10.10.42.11:51238 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1012 | products        | 10.10.42.11:51240 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
| 1013 | products        | 10.10.42.11:51242 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
| 1014 | products        | 10.10.42.11:51244 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1016 | products        | 10.10.42.11:51248 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1017 | products        | 10.10.42.11:51250 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1019 | products        | 10.10.42.11:51254 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1023 | products        | 10.10.42.11:51262 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1024 | products        | 10.10.42.11:51264 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1025 | products        | 10.10.42.11:51266 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1026 | products        | 10.10.42.11:51268 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1027 | products        | 10.10.42.11:51270 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
| 1028 | products        | 10.10.42.11:51272 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1029 | products        | 10.10.42.11:51274 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1030 | products        | 10.10.42.11:51276 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1031 | products        | 10.10.42.11:51280 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1032 | products        | 10.10.42.11:51284 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1033 | products        | 10.10.42.11:51286 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1034 | products        | 10.10.42.11:51288 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1035 | products        | 10.10.42.11:51290 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1036 | products        | 10.10.42.11:51292 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1037 | products        | 10.10.42.11:51294 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1038 | products        | 10.10.42.11:51296 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1039 | products        | 10.10.42.11:51298 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1040 | products        | 10.10.42.11:51300 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1041 | products        | 10.10.42.11:51302 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1042 | products        | 10.10.42.11:51304 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
| 1043 | products        | 10.10.42.11:51306 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
| 1044 | products        | 10.10.42.11:51308 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1045 | products        | 10.10.42.11:51310 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1046 | products        | 10.10.42.11:51312 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1047 | products        | 10.10.42.11:51314 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1048 | products        | 10.10.42.11:51316 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1049 | products        | 10.10.42.11:51318 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1050 | products        | 10.10.42.11:51322 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1051 | products        | 10.10.42.11:51324 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1052 | products        | 10.10.42.11:51326 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1053 | products        | 10.10.42.11:51328 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
| 1054 | products        | 10.10.42.11:51330 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1055 | products        | 10.10.42.11:51332 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1056 | products        | 10.10.42.11:51334 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1057 | products        | 10.10.42.11:51336 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1058 | products        | 10.10.42.11:51338 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1059 | products        | 10.10.42.11:51340 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1060 | products        | 10.10.42.11:51342 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1061 | products        | 10.10.42.11:51344 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1062 | products        | 10.10.42.11:51346 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1063 | products        | 10.10.42.11:51348 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1065 | products        | 10.10.42.11:51352 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1066 | products        | 10.10.42.11:51354 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1067 | products        | 10.10.42.11:51356 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
| 1068 | products        | 10.10.42.11:51358 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1069 | products        | 10.10.42.11:51360 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1070 | products        | 10.10.42.11:51375 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
| 1071 | products        | 10.10.42.11:51387 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1072 | products        | 10.10.42.11:51389 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1073 | products        | 10.10.42.11:51391 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
| 1074 | products        | 10.10.42.11:51393 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1075 | products        | 10.10.42.11:51395 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
| 1076 | products        | 10.10.42.11:51397 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1077 | products        | 10.10.42.11:51399 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1078 | products        | 10.10.42.11:51401 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1085 | products        | 10.10.42.11:51415 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1091 | products        | 10.10.42.11:51427 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1092 | products        | 10.10.42.11:51431 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1093 | products        | 10.10.42.11:51433 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1095 | products        | 10.10.42.11:51437 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1096 | products        | 10.10.42.11:51439 | products | Query   |  64670 | waiting for handler commit | COMMIT           |
| 1097 | products        | 10.10.42.11:51282 | products | Query   |  64671 | waiting for handler commit | COMMIT           |
| 1106 | products        | 10.10.42.11       | NULL     | Query   |      0 | init                       | show processlist |
+------+-----------------+-------------------+----------+---------+--------+----------------------------+------------------+

执行flush logs也卡住了。

分析过程

查看系统负载,cpu使用率低,内存充足

top.original - 12:37:41 up 16 days, 23:44,  4 users,  load average: 2.18, 4.22, 5.56
Tasks:  99 total,   4 running,  95 sleeping,   0 stopped,   0 zombie
%Cpu(s):  1.7 us,  7.1 sy, 90.8 ni,  0.0 id,  0.0 wa,  0.0 hi,  0.3 si,  0.0 st
KiB Mem :  1882008 total,   323816 free,  1155344 used,   402848 buff/cache
KiB Swap:  4194300 total,  3186684 free,  1007616 used.   567628 avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND                                                                                                   
    9 root      20   0       0      0      0 R  0.3  0.0  17:04.26 rcu_sched                                                                                                 
 6988 mysql     20   0 2214444   7096    556 S  0.3  0.4   5:14.72 mysqld                                                                                                    
    1 root      20   0   76344   3140   2136 S  0.0  0.2   2:49.74 systemd                                                                                                   
    2 root      20   0       0      0      0 S  0.0  0.0   0:00.07 kthreadd                                                                                                  
    4 root       0 -20       0      0      0 S  0.0  0.0   0:00.00 kworker/0:0H                                                                                              
    6 root      20   0       0      0      0 R  0.0  0.0  10:27.10 ksoftirqd/0                                                                                               
    7 root      rt   0       0      0      0 S  0.0  0.0   0:00.00 migration/0                                                                                               
    8 root      20   0       0      0      0 S  0.0  0.0   0:00.00 rcu_bh                                                                                                    
   10 root       0 -20       0      0      0 S  0.0  0.0   0:00.00 lru-add-drain                                                                                             
   13 root      20   0       0      0      0 S  0.0  0.0   0:00.00 kdevtmpfs                                                                                                 
   14 root       0 -20       0      0      0 S  0.0  0.0   0:00.00 netns                                                                                                     
   15 root      20   0       0      0      0 S  0.0  0.0   0:01.84 khungtaskd                                                                                                
   16 root       0 -20       0      0      0 S  0.0  0.0   0:00.00 writeback                                                                                                 
   17 root       0 -20       0      0      0 S  0.0  0.0   0:00.00 kintegrityd                                                                                               
   18 root       0 -20       0      0      0 S  0.0  0.0   0:00.00 bioset                                                                                                    
   19 root       0 -20       0      0      0 S  0.0  0.0   0:00.00 bioset                                                                                                    
   20 root       0 -20       0      0      0 S  0.0  0.0   0:00.00 bioset                                                                                                    
   21 root       0 -20       0      0      0 S  0.0  0.0   0:00.00 kblockd  

查看系统空间

[root@VM-0-3-centos ~]# 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

查看系统空间,看到空间满了,说明空间不足无法写redo commit,所以数据库hang住了。

总结

show processlist状态信息非常重要,对于排查数据库问题,给出了排查方向,如果你对这些状态熟悉会立马找到问题所在。

类似文章可以看本人写的Sending to client状态详解: https://cloud.tencent.com/developer/article/1856395?from=article.detail.1508877

欢迎关注哦,会有更多案例分享给大家,关于本案例你学废了吗~

更多文章欢迎关注本人公众号,搜dbachongzi或扫二维码

作者:姚崇 Oracle OCM、MySQL OCP、Oceanbase OBCA、PingCAP PCTA认证,擅长基于Oracle、MySQL Performance Turning及多种关系型 NoSQL数据库

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

关注作者,阅读全部精彩内容

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 从库mysqldump会导致复制中断

    ERROR NO 是1756,而且只是 Slave_SQL_Running 停了。

    老叶茶馆
  • 技术分享 | 为什么MGR一致性模式不推荐AFTER

    某次测试过程中,发现在 AFTER 级别下,节点故障会导致集群无法进行事务提交,同时,当事务进入提交阶段后,其它节点无法开启只读事务。整个集群无法正常提供服务,...

    老叶茶馆
  • 优化SQL语句的一般步骤

    在MySQL中,SQL优化是很常见的一种需求,我自己这方面的经验也不是特别充足,在我自己的认知中,通常情况下,会通过下面的步骤去优化一个慢日志较多MyS...

    AsiaYe
  • 一个线上GTID搭建主从复制的问题

    今天上午,我给一台单实例节点成功挂载了一个NFS备份机,挂载完成之后,尝试给这个单实例节点利用xtrabackup的方式搭建一套主从环境,在搭建的过程中出...

    AsiaYe
  • 故障分析 | 记一次 MTS 并行复制导致的死锁排查

    爱可生交付服务团队北京 DBA,对数据库及周边技术有浓厚的学习兴趣,喜欢看书,追求技术。

    爱可生开源社区
  • MySQL 案例:为什么 kill 不掉线程

    在日常的使用过程中,时不时会遇到个别,或者大量的连接堆积在 MySQL 中的现象,这时一般会考虑使用 kill 命令强制杀死这些长时间堆积起来的连接,尽快释放连...

    王文安@DBA
  • MySQL复制问题的三个参数介绍

    今天星期二,早上居然起晚了,上班迟到了,简直是。。。废话不多说,在昨天的文章中,我们提到了三个参数,分别是:

    AsiaYe
  • 由MySQL复制延迟说起

    相信 slave 延迟是MySQL dba 遇到的一个老生长谈的问题了。我们先来分析一下slave延迟带来的风险

    用户1278550
  • MetaData Lock 之三

    一 简介 通过前面两篇文章的介绍,相信读到这里的各位对MDL 锁已经有了比较深入的了解了,本文将结合理论知识介绍几组MDL 锁的案例。 二 常见MDL 锁的...

    用户1278550
  • MySQL GTID 错误处理汇总

    1、GTID是全局事务ID,简化了主从架构的部署使得从库不再需要关心log_file和log_pos 2、由于事务ID的唯一性,使得将其他从库的GTID应用...

    Leshami
  • pt-heartbeat 监测RDS延迟

    官方手册:https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

    二狗不要跑
  • MySQL SHOW PROCESSLIST 状态详解

    官方手册:https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

    二狗不要跑
  • MySQL主从同步-原理&实践篇

    MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式,这样从节点不用一直访问主服务器来更新...

    chengcheng222e
  • 3分钟解决MySQL 1062 主从错误

    1062错误----主键冲突,出现这种情况就是从库出现插入操作,主库又重新来了一遍,iothread没问题,sqlthread出错

    py3study
  • 【云+社区年度征文】测试MySQL主从复制中主库表缺失主键会导致主从延迟的情况

    2、主库会有binlog dump线程实时监测binlog的变更并将这些新的events事件推给从库(Master has sent all binlog to...

    小麦苗DBA宝典
  • 故障分析 | 从库并行回放死锁问题分析

    爱可生服务团队成员,负责处理客户在 MySQL 日常运维中遇到的问题;擅长处理备份相关的问题,对数据库相关技术有浓厚的兴趣,喜欢钻研各种问题。

    爱可生开源社区
  • 解决方案 | MySQL DBA主从复制出错怎么办?

    1. 如果主从复制时发生了主键冲突,从而阻止了主从复制,可以使用sql_slave_skip_counter这个变量来忽略错误将其排除

    数据和云
  • 【MySQL】metadata lock问题

    MySQL使用DML来管理对数据库对象的并发访问,并确保数据一致性。DML不仅适用于表,还适用于模式和存储程序(过程、函数、触发器和计划的事件)

    用户5522200
  • 囧...执行analyze table意外导致waiting for table flush

    以前记录的文章中的案例2,但是其实并不一样,这里是由于analyze table语句造成的。

    老叶茶馆

扫码关注云+社区

领取腾讯云代金券