监控mysql qps

水深无声 2024-02-17 20:48 28阅读 0赞

mysqladmin -uroot -h192.168.1.111 -p'root' extended-status -i1|awk 'BEGIN\{local\_switch=0;print "QPS Commit Rollback TPS Threads\_con Threads\_run \\n------------------------------------------------------- "\}  
$2 ~ /Queries$/ \{q=$4-lq;lq=$4;\}  
$2 ~ /Com\_commit$/ \{c=$4-lc;lc=$4;\}  
$2 ~ /Com\_rollback$/ \{r=$4-lr;lr=$4;\}  
$2 ~ /Threads\_connected$/ \{tc=$4;\}  
$2 ~ /Threads\_running$/ \{tr=$4;  
if(local\_switch==0)  
\{local\_switch=1; count=0\}  
else \{  
if(count>10)  
\{count=0;print "------------------------------------------------------- \\nQPS Commit Rollback TPS Threads\_con Threads\_run \\n------------------------------------------------------- ";\}  
else\{  
count+=1;  
printf "%-6d %-8d %-7d %-8d %-10d %d \\n", q,c,r,c+r,tc,tr;  
\}  
\}  
\}'

mysqladmin -h 127.0.0.1 -uroot -p'acdee ' -P8066 extended-status -i1|awk 'BEGIN\{local\_switch=0\}  
$2 ~ /Queries$/ \{q=$4-lq;lq=$4;\}  
$2 ~ /com\_commit$/ \{c=$4-lc;lc=$4;\}  
$2 ~ /Com\_rollback$/ \{r=$4-lr;lr=$4;\}  
$2 ~ /Com\_select$/ \{s=$4-ls;ls=$4;\}  
$2 ~ /Com\_update$/ \{u=$4-lu;lu=$4;\}  
$2 ~ /Com\_insert$/ \{i=$4-li;li=$4;\}  
$2 ~ /Com\_delete$/ \{d=$4-ld;ld=$4;\}  
$2 ~ /Innodb\_rows\_read$/ \{irr=$4-lirr;lirr=$4;\}  
$2 ~ /Innodb\_rows\_deleted$/ \{ird=$4-lird;lird=$4;\}  
$2 ~ /Innodb\_rows\_inserted$/ \{iri=$4-liri;liri=$4;\}  
$2 ~ /Innodb\_rows\_updated$/ \{iru=$4-liru;liru=$4;\}  
$2 ~ /Innodb\_buffer\_pool\_read\_requests$/ \{ibprr=$4-libprr;libprr=$4;\}  
$2 ~ /Innodb\_buffer\_pool\_reads$/ \{ibpr=$4-libpr;libpr=$4;\}  
$2 ~ /Threads\_connected$/ \{tc=$4;\}  
$2 ~ /Threads\_running$/ \{tr=$4;  
if(local\_switch==0)  
\{local\_switch=1; count=16\}  
else \{  
if(count>15) \{  
count=0;  
print "------------------------------------------------------------------------------------------------------------------------------------ ";  
print "Time-----| QPS | Commit Rollback TPS | select insert update delete | read inserted updated deleted | logical physical | Tcon Trun";  
print "------------------------------------------------------------------------------------------------------------------------------------ ";  
\}else\{  
count+=1;  
printf "%s | %-5d| %-6d %-7d %-5d| %-7d %-7d %-5d %-6d| %-7d %-7d %-7d %-7d| %-6d %-9d| %-4d %-2d \\n", strftime("%H:%M:%S"),q,c,r,c+r,s,u,i,d,irr,ird,iri,iru,ibprr,ibpr,tc,tr;  
\}  
\}  
\}'