MySQL Cases-記錄大量waiting for handler commit

哎呀我的天吶 發表於 2021-10-15
人工智慧 MySQL MIT

什麼是waiting for handler commit檢視官方文件, https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html

  • waiting for handler commitThe 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.56Tasks:  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


檢視系統空間

[[email protected] ~]# 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或掃二維碼

MySQL Cases-記錄大量waiting for handler commit

作者:姚崇 Oracle OCM、 OCP、Oceanbase OBCA、PingCAP PCTA認證,擅長基於Oracle、 Performance Turning及多種關係型 NoSQL

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29990276/viewspace-2827118/,如需轉載,請註明出處,否則將追究法律責任。