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 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
檢視系統空間
[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、 OCP、Oceanbase OBCA、PingCAP PCTA認證，擅長基於Oracle、 Performance Turning及多種關係型 NoSQL 。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-2827118/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL：簡單記錄一下Waiting for commit lockMySqlAIMIT
- MySQL：Innodb 關於Handler_commit每次DML增加2的原因MySqlMIT
- 記錄一次mysql批量修改大量資料MySql
- git如何清空所有的commit記錄GitMIT
- MySQL5.7 Waiting FOR TABLE FLUSHMySqlAI
- MySQL5.7 Waiting for global read lockMySqlAI
- MySQL新增索引偶遇waiting for table metadata lockMySql索引AI
- MySQL DDL Waiting for table metadata lock 解決MySqlAI
- MySQL:Analyze table導致'waiting for table flush'MySqlAI
- MySQL Cases-執行計劃向Oracle看齊（FORMAT=tree）MySqlOracleORM
- MySQL學習記錄MySql
- 【記錄】MySQL 學習筆記MySql筆記
- [記錄] MySQL 學習筆記MySql筆記
- ORA_ROWSCN與按照commit時間對記錄進行排序MIT排序
- mysql handler語句之一MySql
- Mysql Replication學習記錄MySql
- MySQL_踩坑記錄MySql
- Android Handler機制之總目錄Android
- MySQL FQA自我整理和記錄MySql
- mysql 隨機查詢記錄MySql隨機
- MySQL 常用命令記錄MySql
- mysql刪除一條記錄MySql
- MySQL 優化實戰記錄MySql優化
- mysql怎麼插入空記錄MySql
- Android學習筆記·HandlerAndroid筆記
- Reviewbot 開源 | 有些 git commit 記錄真的不敢恭維, 我推薦每位工程師都常用 git rebase 和 git commit --amendViewGitMIT工程師
- 資料庫mysql學習筆記記錄資料庫MySql筆記
- MYSQL Group Replication搭建過程記錄MySql
- mysql之行(記錄)的詳細操作MySql
- mysql中文全文索引的記錄MySql索引
- mysql load 相關實驗記錄MySql
- 【實操記錄】MySQL主從配置MySql
- Linux下MySQL多例項部署記錄LinuxMySql
- MySQL 分組後取最新記錄MySql
- MySQL鞏固學習記錄（一）MySql
- MySQL的一些操作記錄MySql
- 2018.03.06 Android Handler學習筆記Android筆記
- MySQL函式查詢目錄樹問題記錄MySql函式