监控mysql的QPS、读、写脚本

爱被打了一巴掌 2024-02-17 20:41 25阅读 0赞

mysqladmin -P3306 -uroot -proot-h127.0.0.1 -r -i 1 extended-status |\\

awk -F"|" \\

"BEGIN\{ count=0; \}"\\

'\{ if($2 ~ /Variable\_name/&& ((++count)%20 == 1))\{\\

print "----------|---------|--- MySQLCommand Status --|----- Innodb Row Operation ----|-- Buffer Pool Read--";\\

print "---Time---|---QPS---|selectinsert update delete| read insertedupdated deleted| logical physical";\\

\}\\

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("|%10d %11d\\n",innodb\_lor,innodb\_phr);\\

\}\}'

或

\#实时监控SQL语句的执行频率  
\#mysqladmin -uroot -p123456 -h192.168.56.81 -r -i 1 -c 10 ext |\\  
mysqladmin -uroot -p123456 -h192.168.56.81 -r -i 1 ext |\\  
awk -F"|" \\  
"BEGIN\{ count=0; \}"\\  
'\{ if($2 ~ /Variable\_name/ && ++count == 1)\{\\  
print "----------|---------|----Transactions----|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --|-----threads-----";\\  
print "---Time---|---QPS---|commit rollback ~TPS|select insert update delete| read inserted updated deleted| logical physical|connected running";\\  
\}\\  
else if ($2 ~ /Queries/)\{queries=$3;\}\\  
else if ($2 ~ /Com\_commit /)\{Com\_commit=$3;\}\\  
else if ($2 ~ /Com\_rollback /)\{Com\_rollback=$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 ~ /Threads\_connected /)\{Threads\_connected=$3;\}\\  
else if ($2 ~ /Threads\_running /)\{Threads\_running=$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",Com\_commit,Com\_rollback,Com\_commit+Com\_rollback);\\  
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("|%10d %11d",innodb\_lor,innodb\_phr);\\  
printf("|%9d %7d\\n",Threads\_connected,Threads\_running);\\  
\}\}'