Master MySQL Performance Metrics: TPS, QPS, InnoDB Stats & Monitoring Tips
This article explains how to retrieve and interpret key MySQL performance metrics—including TPS, QPS, thread status, InnoDB I/O, buffer pool health, MyISAM statistics, temporary tables, response time, and replication lag—using mysqladmin, SHOW GLOBAL STATUS, and Percona tools, with practical calculation formulas and command examples.
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 STATUSlists 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_commitQPS = Questions / smysqladmin extended-status --relative --sleep=1 | grep -w QuestionsMethod 2: Derive TPS and QPS directly from Com_% counters:
TPS = (Com_insert + Com_update + Com_delete) / sQPS = (Com_select + Com_insert + Com_update + Com_delete) / sThread Status
threads_running: Number of threads currently activethreads_connected: Number of currently connected threadsTraffic 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 filesinnodb_data_writes: Avg. writes per second to filesinnodb_data_fsyncs: Avg. fsync() operations per secondInnoDB Buffer Pool
innodb_buffer_pool_reads: Avg. physical page reads per secondinnodb_buffer_pool_read_requests: Avg. logical read requests per secondinnodb_buffer_pool_write_requests: Avg. writes to the buffer pool per secondinnodb_buffer_pool_pages_dirty: Avg. dirty pages in the pool per secondinnodb_buffer_pool_pages_flushed: Avg. flushed pages per secondinnodb_buffer_read_hit_ratio = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100Innodb_buffer_usage = (1 - Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total) * 100InnoDB Log
innodb_os_log_fsyncs: Avg. fsync() writes to the log file per secondinnodb_os_log_written: Avg. bytes written to the log file per secondinnodb_log_writes: Avg. physical log writes per secondinnodb_log_write_requests: Avg. log write requests per secondInnoDB Row Operations
innodb_rows_deleted: Avg. rows deleted per secondinnodb_rows_inserted: Avg. rows inserted per secondinnodb_rows_read: Avg. rows read per secondinnodb_rows_updated: Avg. rows updated per secondinnodb_row_lock_waits: Number of row lock waitsinnodb_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 secondkey_write_requests: Avg. writes to the key buffer per secondkey_reads: Avg. reads from disk per secondkey_writes: Avg. writes to disk per secondMyISAM Buffer Pool
Key_usage_ratio = Key_blocks_used / (Key_blocks_used + Key_blocks_unused) * 100Key_read_hit_ratio = (1 - Key_reads / Key_read_requests) * 100Key_write_hit_ratio = (1 - Key_writes / Key_write_requests) * 100Temporary Tables
Created_tmp_disk_tables: Number of temporary tables created on disk per secondCreated_tmp_tables: Number of temporary tables created in memory per secondCreated_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 secondssort_rows: Number of rows sortedopen_files: Number of open filesopen_tables: Number of open tablesselect_scan: Number of joins that perform a full scan on the first tableResponse 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.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
