|
《MySQL中强大的mysqladmin》要点：
本文介绍了MySQL中强大的mysqladmin，希望对您有用。如果有疑问，可以联系我们。
如果对MySQL的性能测试工具,比如sysbench做压力测试就可以看到我们关注的性能指标QPS,TPS,压测过程中的性能变化一目了然. 而在平时的工作中,如果也想看这个指标的值,使用sysbench就不合适了.所以我们得先了解下TPS,QPS是怎么计算的,没有sysbench这些工具,我们能不能算出来. 首先性能指标值我们是可以算出来的,我们通过show status能够得到一个实时变化的状态,里面的数值基本上都是累计值,我们可以通过设定频度来换算,由此得到一个近乎实时的状态数据. 这个时候mysqladmin就上场了,不光能够很方便的查看参数情况,比如mysqladmin var,很方便的查看参数等.mysqladmin shutdown来停库,mysqladmin flush-hosts,mysqladmin flush-privileges来刷新权限,或者使用mysqladmin pro来查看线程情况. QPS
> show status like 'queries';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Queries | 9978587 |
+---------------+---------+
1 row in set (0.00 sec)
> show status like 'queries';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Queries | 9978588 |
+---------------+---------+
1 row in set (0.00 sec) TPS = (Com_commit + Com_rollback) / Seconds mysqladmin extended-status mysqladmin -r -i 1 extended-status|grep Innodb_pages_read # mysqladmin -r -i 1 extended-status|grep Handler_read_rnd_next
| Handler_read_rnd_next | 6814
| Handler_read_rnd_next | 399
| Handler_read_rnd_next | 399
| Handler_read_rnd_next | 399
| Handler_read_rnd_next | 399
| Handler_read_rnd_next | 399
| Handler_read_rnd_next | 399
mysqladmin -r -i 1 extended-status
|grep "Questions|Queries|Innodb_rows|Com_select |Com_insert |Com_update |Com_delete "
mysqladmin -r -i 1 ext |
awk -F"|" '{
if($2 ~ /Variable_name/){
print " <------------- " strftime("%H:%M:%S") " ------------->";
}
if($2 ~ /Questions|Queries|Innodb_rows|Com_select |Com_insert |Com_update |Com_delete |Innodb_buffer_pool_read_requests/)
print $2 $3;
}'
# sh aa.sh
<------------- 23:07:24 ------------->
Com_delete 6
Com_insert 4953706
Com_select 2672
Com_update 4
Innodb_buffer_pool_read_requests 67500881
Innodb_rows_deleted 10
Innodb_rows_inserted 8464857
Innodb_rows_read 46945213
Innodb_rows_updated 3
Queries 9978553
Questions 2454058
<------------- 23:07:25 ------------->
Com_delete 0
Com_insert 0
Com_select 0
Com_update 0
Innodb_buffer_pool_read_requests 0
Innodb_rows_deleted 0
Innodb_rows_inserted 0
Innodb_rows_read 0
Innodb_rows_updated 0
Queries 1
Questions 1
mysqladmin -r -i 1 ext |
awk -F"|"
"BEGIN{ count=0; }"
'{ if($2 ~ /Variable_name/ && ++count == 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 %11dn",innodb_lor,innodb_phr);
}}' # sh aa.sh
----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --
---Time---|---QPS---|select insert update delete| read inserted updated deleted| logical physical
23:10:47 | 193| 64 1 2 0| 12296 1 2 0| 1755 0
23:10:48 | 129| 44 0 2 0| 11184 0 2 0| 1582 0
23:10:49 | 89| 33 0 1 0| 11898 0 1 0| 1667 0
23:10:50 | 138| 47 0 2 0| 7634 0 2 0| 1099 0
23:10:51 | 137| 38 0 3 0| 16537 0 3 0| 2232 0 简化版 mysqladmin 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;
}
}
}' # sh aa.sh
QPS Commit Rollback TPS Threads_con Threads_run
-------------------------------------------------------
108 0 3 3 46 2
173 0 1 1 47 2
69 0 0 0 47 2
89 0 1 1 47 2
144 0 0 0 48 2
235 0 2 2 47 2
166 0 1 1 48 2
http://www.orczhou.com/index.php/2014/03/some-tricky-about-mysqladmin-extended-status/
https://segmentfault.com/a/1190000009506375
《MySQL中强大的mysqladmin》是否对您有启发，欢迎查看更多与《MySQL中强大的mysqladmin》相关教程，学精学透。编程之家PHP学院为您提供精彩教程。 （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
