Understanding and Calculating QPS and TPS in MySQL

QPS Definition and Calculation Methods

QPS (Queries‑per‑second) measures how many queries a MySQL server processes each second. Although the MySQL documentation does not define it explicitly, three common calculation methods exist:

Method 1: Count only SELECT statements (DQL). This yields the read‑only pressure and is rarely used.

Method 2: Use the Queries status variable. Retrieve the value with show global status like 'Queries' , wait t seconds, retrieve it again, and compute the difference divided by t . This is the widely accepted "Queries‑per‑second" method.

Method 3: Use the Questions status variable similarly. However, Questions excludes statements executed inside stored programs, so it usually yields a lower count than Queries .

The official MySQL \s status display shows a "Queries per second avg" value that actually derives from the Questions counter, which appears to be a documentation inconsistency.

TPS Definition and Calculation Methods

TPS (Transactions‑per‑second) measures how many transactions a MySQL server commits each second. Only InnoDB tables support transactions, so TPS is meaningful only for InnoDB workloads.

Three calculation approaches are discussed:

Method 1: Sum the counters for Com_insert , Com_delete , Com_update , Com_select , Com_delete_multi , and Com_update_multi from show global status , compute the difference over a time interval t , and divide by t . This method is inaccurate because a single transaction may contain multiple statements.

Method 2: Add Com_commit and Com_rollback . This fails in MySQL because autocommit is enabled by default, so many transactions are not reflected in these counters.

Method 3: Use GTID (Global Transaction Identifier). The GTID set (e.g., Executed_Gtid_Set from show master status or show slave status ) increments by one for each committed transaction, providing the most precise TPS measurement. The TPS can be calculated as the difference in GTID values over a time interval.

For GTID‑based TPS, the command show master status returns Executed_Gtid_Set , whose numeric part can be compared across two snapshots to obtain the transaction count per second.

Summary

The article reviews multiple ways to compute QPS and TPS in MySQL, highlights the ambiguities in official documentation, and recommends using the Queries status variable for QPS and GTID differences for TPS as the most reliable approaches.