mysql 画图生成_gnuplot生成MySQL QPS图形

ゞ浴缸里的玫瑰 2024-03-30 15:30 87阅读 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;  
\}  
\}  
\}'

mysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 extended-status |\
    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|   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);\
    }}'

2、执行脚本

nohup sh mysql\_qps.sh > qps\_per\_5

![36548f44ba3b4a3d8d5d2fe2f33a5206.png][]

3、安装gnuplot

yum install gunplot

No package gunplot available.  
  
yum -y install epel-release  
安装epel-release。epel安装完成之后，再重新安装之前的安装失败的软件即可。  
// 下载安装epel-release  
yum install epel-release  
  
然后清理本地缓存  
yum clean all  
更新  
yum update  
生成缓存  
yum makecache

`yum install gnuplot`就不会再报错了。

4、生成gnuplot图形

\[root@test1 ~\]\# gnuplot

G N U P L O T

Version4.2 patchlevel 6

last modified Sep 2009System: Linux2.6.32-431.el6.x86\_64

Type \`help\` to access the on-line reference manual.

The gnuplot FAQ is available from http://www.gnuplot.info/faq/

Send bug reports and suggestions to

Terminal type set to'x11'gnuplot> plot "qps\_per\_5" using 1 w lines title "QPS"

![b7d5858aab524931ab0a7996180e1ef8.png][]

生成QPS和load的截图

gnuplot> plot "qps\_per\_5" using 1 w lines title "QPS","qps\_per\_5" using 2 w lines title "load"

![06da28d1fb9d44b3887921f7d3a17152.png][]

gnuplot的参数如下

【转】gnuplot分析多组数据绘图 - 剑帝的日志 - 网易博客

http://blog.163.com/heloyure@126/blog/static/64377576201181992822/

Tpcc-MySQL测试并使用gnuplot生成图表-鸟哥のlinux-ChinaUnix博客

http://blog.chinaunix.net/uid-25266990-id-4080103.html

使用gnuplot对tpcc-mysql压测结果生成图表 - lzfj - 博客园

http://www.cnblogs.com/lizhi221/p/6814025.html

gnuplot工具的安装与使用-陈舒婷-ChinaUnix博客

http://blog.chinaunix.net/uid-28412198-id-3763133.html

[36548f44ba3b4a3d8d5d2fe2f33a5206.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/013bcf95655244e0bfde09bbfbf88c08.png
[b7d5858aab524931ab0a7996180e1ef8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/48c024f2be2646988322378b495881c5.png
[06da28d1fb9d44b3887921f7d3a17152.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/2552bc10d35f46c796fb8b55a7626a90.png