Master MySQL Performance Metrics: TPS, QPS, InnoDB Stats & Monitoring Tips

Most MySQL performance metrics can be obtained via two methods: mysqladmin and SHOW GLOBAL STATUS .

Using mysqladmin extended-status returns cumulative values; adding --relative (or -r) and --sleep (or -i) shows per‑interval differences.

SHOW GLOBAL STATUS

lists cumulative server status values.

TPS/QPS

TPS (Transactions Per Second) and QPS (Queries Per Second) are essential performance indicators.

Method 1: Calculate TPS from Com_commit and Com_rollback, and QPS from Questions: TPS = (Com_commit + Com_rollback) / s Example command:

mysqladmin extended-status --relative --sleep=1 | grep -w Com_commit

QPS = Questions / s

mysqladmin extended-status --relative --sleep=1 | grep -w Questions

Method 2: Derive TPS and QPS directly from Com_% counters:

TPS = (Com_insert + Com_update + Com_delete) / s

QPS = (Com_select + Com_insert + Com_update + Com_delete) / s

Thread Status

threads_running: Number of threads currently active

threads_connected: Number of currently connected threads

Traffic Statistics

Bytes_received/s: Average bytes received per second (KB)

Bytes_sent/s: Average bytes sent per second (KB)

InnoDB File I/O

innodb_data_reads: Avg. reads per second from files

innodb_data_writes: Avg. writes per second to files

innodb_data_fsyncs: Avg. fsync() operations per second

InnoDB Buffer Pool

innodb_buffer_pool_reads: Avg. physical page reads per second

innodb_buffer_pool_read_requests: Avg. logical read requests per second

innodb_buffer_pool_write_requests: Avg. writes to the buffer pool per second

innodb_buffer_pool_pages_dirty: Avg. dirty pages in the pool per second

innodb_buffer_pool_pages_flushed: Avg. flushed pages per second

innodb_buffer_read_hit_ratio = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100

Innodb_buffer_usage = (1 - Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total) * 100

InnoDB Log

innodb_os_log_fsyncs: Avg. fsync() writes to the log file per second

innodb_os_log_written: Avg. bytes written to the log file per second

innodb_log_writes: Avg. physical log writes per second

innodb_log_write_requests: Avg. log write requests per second

InnoDB Row Operations

innodb_rows_deleted: Avg. rows deleted per second

innodb_rows_inserted: Avg. rows inserted per second

innodb_rows_read: Avg. rows read per second

innodb_rows_updated: Avg. rows updated per second

innodb_row_lock_waits: Number of row lock waits

innodb_row_lock_time: Total lock wait time (ms)

innodb_row_lock_time_avg: Average lock wait time (ms)

MyISAM Read/Write

key_read_requests: Avg. reads from the key buffer per second

key_write_requests: Avg. writes to the key buffer per second

key_reads: Avg. reads from disk per second

key_writes: Avg. writes to disk per second

MyISAM Buffer Pool

Key_usage_ratio = Key_blocks_used / (Key_blocks_used + Key_blocks_unused) * 100

Key_read_hit_ratio = (1 - Key_reads / Key_read_requests) * 100

Key_write_hit_ratio = (1 - Key_writes / Key_write_requests) * 100

Temporary Tables

Created_tmp_disk_tables: Number of temporary tables created on disk per second

Created_tmp_tables: Number of temporary tables created in memory per second

Created_tmp_disk_tables / Created_tmp_tables should stay below 10% to avoid excessive disk usage.

Other Important Metrics

slow_queries: Queries exceeding long_query_time seconds

sort_rows: Number of rows sorted

open_files: Number of open files

open_tables: Number of open tables

select_scan: Number of joins that perform a full scan on the first table

Response Time

Percona’s tcprstat tool can collect response time statistics; enable it with query_response_time_stats=1.

Two ways to view response time:

Run SHOW QUERY_RESPONSE_TIME to see statistics.

Query the INFORMATION_SCHEMA.QUERY_RESPONSE_TIME table.

Slave Delay

On a replica, execute SHOW SLAVE STATUS\G; the Seconds_Behind_Master value indicates replication lag in seconds.