MySQL5.7/8.0性能分析shell脚本
分享一个好用的性能分析shell脚本，也适用于mysql8.0版本:
# mysqladmin -P3306 -uroot -p -r -i 1 ext |\
awk -F"|" \
"BEGIN{ count=0; }"\
'{ if($2 ~ /Variable_name/ && ((++count)%20 == 1)){\
print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\
print "---Time---|---QPS---|select insert update delete| read inserted updated deleted| logicalphysical";\
}\
else if ($2 ~ /Queries/){queries=$3;}\
else if ($2 ~ /Com_select /){com_select=$3;}\
else if ($2 ~ /Com_insert /){com_insert=$3;}\
else if ($2 ~ /Com_update /){com_update=$3;}\
else if ($2 ~ /Com_delete /){com_delete=$3;}\
else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\
else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\
else if ($2 ~ /Uptime / && count >= 2){\
printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
printf("|d d\n",innodb_lor,innodb_phr);\
}}'
Enter password:
----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --
---Time---|---QPS---|select insert update delete| read inserted updated deleted| logicalphysical
14:31:40 | 4789| 479 1831 0 0| 4404 2116 0 0| 50198 498
14:31:41 | 10102| 841 3869 0 0| 11741 4575 0 0| 101467 980
14:31:42 | 10722| 980 4076 0 0| 12869 4748 0 0| 110550 1023
14:31:44 | 8935| 785 3419 1 0| 13413 3994 1 0| 106559 1112
14:31:45 | 9681| 828 3710 0 0| 9530 4370 0 0| 94369 1223
14:31:46 | 10258| 981 3840 0 0|288668 4513 0 0| 137434 992
14:31:47 | 10414| 919 4027 1 0| 26074 4745 1 0| 151924 1032
14:31:48 | 9365| 846 3558 0 0| 6309 4217 0 0| 80498 956
14:31:49 | 10592| 941 4014 0 0| 9557 4692 0 0| 98196 1010
14:31:50 | 9338| 850 3583 0 0| 85377 4249 0 0| 104768 1013
14:31:51 | 11355| 1005 4392 1 0| 4969 5140 1 0| 92914 966
14:31:52 | 10869| 888 4323 0 0| 27514 5035 0 0| 90211 1088
14:31:53 | 8975| 800 3408 0 0| 41949 4074 0 0| 99829 1006
14:31:54 | 9599| 829 3655 0 0| 43782 4302 0 0| 187823 1052
14:31:55 | 9813| 870 3737 0 0| 14861 4410 0 0| 111723 1302
14:31:56 | 9929| 875 3806 0 0| 13099 4497 0 0| 96647 934
14:31:57 | 10534| 1502 3810 0 0| 98051 4468 0 0| 174053 971
14:31:58 | 11310| 2508 3714 0 0|144681 4378 0 0| 180342 951
14:31:58 | 11564| 2619 3774 0 0| 18841 4453 0 0| 144142 914
----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --
---Time---|---QPS---|select insert update delete| read inserted updated deleted| logicalphysical
14:31:59 | 6420| 914 2256 1 0|340903 2525 1 0| 239705 957
14:32:00 | 9437| 800 3605 0 0| 33533 4268 0 0| 169835 1312
14:32:01 | 9891| 1679 3542 0 0| 24979 4140 0 0| 153787 908
14:32:02 | 12949| 2733 4390 1 0| 51019 5059 1 0| 258339 942
14:32:04 | 11818| 2646 3861 0 0| 44841 4564 0 0| 225847 898
14:32:05 | 9910| 1178 3646 0 0| 43531 4317 0 0| 208128 1293
14:32:06 | 11548| 2701 3654 0 0|101652 4299 0 0| 166021 1776
14:32:07 | 12025| 2714 3852 0 0|240576 4548 0 0| 255133 889
14:32:08 | 10443| 1094 3877 0 0|134656 4605 0 0| 492296 838
14:32:09 | 11780| 2560 3847 0 0| 60253 4533 0 0| 275701 683
14:32:10 | 10082| 2404 3244 0 0| 35307 3785 0 0| 186048 416
14:32:11 | 10731| 1417 3839 0 0| 72411 4445 0 0| 305717 810
14:32:12 | 11018| 2434 3569 0 0|281151 4222 0 0| 323895 835
14:32:13 | 10911| 2375 3590 0 0|137497 4248 0 0| 514110 571
14:32:14 | 10269| 913 3943 0 0|218782 4645 0 0| 759658 448
14:32:15 | 10752| 974 4114 0 0|219235 4864 0 0| 762128 341
14:32:16 | 9767| 924 3731 0 0|305691 4378 0 0| 851373 285
14:32:17 | 10279| 862 3907 0 0|129354 4622 0 0| 473603 734
14:32:18 | 9199| 803 3539 0 0|312288 4211 0 0| 359733 708
14:32:18 | 9625| 839 3694 0 0| 2112 4357 0 0| 68472 970
----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --
............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............
............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............
............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............
............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............
............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............ ............
