MySQL5.7/8.0 performance Analysis shell script

Share a useful shell script for performance analysis, which is also applicable to the mysql8.0 version:

# 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\ 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 2116

14:31:41 | 10102 | 841 3869 3869 | 11741 4575 00 | 101467 980

14:31:42 | 10722 | 980 4076 00 | 12869 4748 00 | 110550 1023

14:31:44 | 8935 | 785 3419 10 | 13413 3994 10 | 106559 1112

14:31:45 | 9681 | 828 3710 00 | 9530 4370 00 | 94369 1223

14:31:46 | 10258 | 981 3840 3840 | 288668 4513 00 | 137434 992

14:31:47 | 10414 | 919 4027 10 | 26074 4745 10 | 151924 1032

14:31:48 | 9365 | 846 3558 3558 | 6309 4217 00 | 80498 956

14:31:49 | 10592 | 941 4014 00 | 9557 4692 1010 | 98196 1010

14:31:50 | 9338 | 850 3583 0 0 | 85377 4249 00 | 104768 1013

14:31:51 | 11355 | 1005 4392 10 | 4969 5140 10 | 92914 966

14:31:52 | 10869 | 888 4323 00 | 27514 5035 00 | 90211 1088

14:31:53 | 8975 | 800 3408 00 | 41949 4074 00 | 99829 1006

14:31:54 | 9599 | 829 3655 00 | 43782 4302 1052 | 187823 1052

14:31:55 | 9813 | 870 3737 00 | 14861 4410 00 | 111723 1302

14:31:56 | 9929 | 875 3806 3806 | 13099 4497 00 | 96647 934

14:31:57 | 10534 | 1502 3810 00 | 98051 4468 00 | 174053 971

14:31:58 | 11310 | 2508 3714 00 | 144681 4378 4378 | 180342 951

14:31:58 | 11564 | 2619 3774 00 | 18841 4453 00 | 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 10 | 239705 957

14:32:00 | 9437 | 800 3605 00 | 33533 4268 00 | 169835 1312

14:32:01 | 9891 | 1679 3542 0 0 | 24979 4140 0 0 | 153787 4140

14:32:02 | 12949 | 2733 4390 10 | 51019 5059 10 | 258339 942

14:32:04 | 11818 | 2646 3861 0 0 | 44841 4564 0 0 | 225847 4564

14:32:05 | 9910 | 1178 3646 00 | 43531 4317 00 | 208128 1293

14:32:06 | 11548 | 2701 3654 00 | 101652 4299 00 | 166021 1776

14:32:07 | 12025 | 2714 3852 00 | 240576 4548 00 | 255133 4548

14:32:08 | 10443 | 1094 3877 0 0 | 134656 4605 0 0 | 492296 4605

14:32:09 | 11780 | 2560 3847 00 | 60253 4533 00 | 275701 4533

14:32:10 | 10082 | 2404 3244 00 | 35307 3785 00 | 186048 416

14:32:11 | 10731 | 1417 3839 0 0 | 72411 4445 0 0 | 305717 4445

14:32:12 | 11018 | 2434 3569 0 0 | 281151 4222 0 0 | 323895 4222

14:32:13 | 10911 | 2375 3590 0 0 | 137497 4248 0 0 | 514110 3590

14:32:14 | 10269 | 913 3943 3943 | 218782 4645 00 | 759658 448

14:32:15 | 10752 | 974 4114 0 0 | 219235 4864 4864 | 762128 341

14:32:16 | 9767 | 924 3731 0 0 | 305691 4378 4378 | 851373 285

14:32:17 | 10279 | 862 3907 3907 | 129354 4622 00 | 473603 734

14:32:18 | 9199 | 803 3539 3539 | 312288 4211 00 | 359733 708

14:32:18 | 9625 | 839 3694 3694 | 2112 4357 00 | 68472 970

-|-|-- MySQL Command Status-- |-Innodb row operation-|-- Buffer Pool Read--

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .