How to Monitor MySQL QPS and TPS with a Simple Bash/Awk Script

QPS (Queries Per Second) and TPS (Transactions Per Second) are two key MySQL performance indicators that should be monitored regularly and compared with benchmark results.

Calculation Method

QPS is calculated as the difference in the Queries counter over a time interval, e.g., sampling for 10 seconds: QPS = (Q2 - Q1) / 10. TPS is derived from the sum of Com_commit and Com_rollback counters: TPS = (Com_commit + Com_rollback) / Seconds. Both counters are obtained via SHOW STATUS or mysqladmin extended-status.

Monitoring Script

The script runs mysqladmin -uroot -p'password' extended-status each second, extracts the values Queries , Com_commit , Com_rollback , Threads_connected and Threads_running , and computes QPS, TPS, and thread counts.

Key goals:

Collect QPS, TPS, Threads_connected, Threads_running every second.

Below is the core awk script used for parsing:

BEGIN{flag=0;
print "";
print "QPS   TPS    Threads_con Threads_run";
print "-------------------------------------"}
$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(flag==0){ flag=1; }
else { printf "%-6d %-8d %-10d %d 
", q, c+r, tc, tr; }

The script prints a header on the first run, then for each subsequent second it outputs the calculated QPS, TPS, and thread counts.

You can download the full script from http://devdd.oss-cn-beijing.aliyuncs.com/mysql_QPS_TPS.txt and try it in your environment.

Images illustrating the metrics and script output: