How to monitor performance metrics in MySQL or MariaDB

Performance metrics turn “it feels slow” into concrete signals, helping isolate whether latency comes from expensive queries, cache misses, lock contention, or storage bottlenecks before user-facing timeouts appear.

MySQL and MariaDB expose most operational counters through global status variables, while InnoDB adds engine-specific visibility for buffer pool behavior, redo/flush pressure, and transaction locking. Many values are cumulative since startup, so meaningful interpretation typically comes from sampling at two points in time and calculating deltas as rates.

Access to global status and engine status output depends on privileges, and some installations disable or restrict sources such as performance_schema to reduce overhead. Sampling too frequently can add load, especially on busy systems, so polling intervals should stay conservative and aligned with the workload pattern under investigation.

1. Connect to the server using an account that can read global status variables.

   $ mysql -u admin -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   mysql>

2. Record server uptime to anchor rate calculations.

   mysql> SHOW GLOBAL STATUS LIKE 'Uptime';
   +---------------+--------+
   | Variable_name | Value  |
   +---------------+--------+
   | Uptime        | 187240 |
   +---------------+--------+
   1 row in set (0.00 sec)

3. Capture baseline throughput and concurrency counters.

   mysql> SHOW GLOBAL STATUS WHERE Variable_name IN ('Questions','Queries','Threads_connected','Threads_running','Slow_queries');
   +-------------------+----------+
   | Variable_name     | Value    |
   +-------------------+----------+
   | Queries           | 12789432 |
   | Questions         | 12789432 |
   | Slow_queries      | 23       |
   | Threads_connected | 42       |
   | Threads_running   | 4        |
   +-------------------+----------+
   5 rows in set (0.00 sec)

   Threads_running spikes correlate with CPU saturation or lock waits, while growing Slow_queries usually indicates query plan or indexing issues.

4. Record configuration guardrails that influence common performance counters.

   mysql> SHOW GLOBAL VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size','tmp_table_size','max_heap_table_size');
   +----------------------+------------+
   | Variable_name        | Value      |
   +----------------------+------------+
   | innodb_buffer_pool_size | 17179869184 |
   | max_heap_table_size  | 268435456  |
   | tmp_table_size       | 268435456  |
   +----------------------+------------+
   3 rows in set (0.00 sec)

   tmp_table_size and max_heap_table_size cap in-memory internal temporary tables, and the smaller value effectively wins.

5. Check InnoDB buffer pool read pressure.

   mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
   +----------------------------------+-----------+
   | Variable_name                    | Value     |
   +----------------------------------+-----------+
   | Innodb_buffer_pool_read_requests | 843291221 |
   | Innodb_buffer_pool_reads         | 9812      |
   +----------------------------------+-----------+
   2 rows in set (0.00 sec)

   Hit rate ≈ 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests), where larger is better.

6. Check InnoDB buffer pool residency and dirty-page pressure.

   mysql> SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_pages_total','Innodb_buffer_pool_pages_free','Innodb_buffer_pool_pages_dirty');
   +------------------------------+--------+
   | Variable_name                | Value  |
   +------------------------------+--------+
   | Innodb_buffer_pool_pages_total | 1048576 |
   | Innodb_buffer_pool_pages_free  | 18432   |
   | Innodb_buffer_pool_pages_dirty | 9216    |
   +------------------------------+--------+
   3 rows in set (0.00 sec)

   Low Innodb_buffer_pool_pages_free with rising Innodb_buffer_pool_reads often indicates cache pressure, and sustained high Innodb_buffer_pool_pages_dirty can point to flush pressure.

7. Watch temporary table creation to spot memory pressure and disk spills.

   mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp%';
   +-------------------------+-------+
   | Variable_name           | Value |
   +-------------------------+-------+
   | Created_tmp_disk_tables | 1423  |
   | Created_tmp_files       | 12    |
   | Created_tmp_tables      | 9812  |
   +-------------------------+-------+
   3 rows in set (0.00 sec)

   High Created_tmp_disk_tables can indicate missing indexes, oversized GROUP BY, or low tmp_table_size and max_heap_table_size.

8. Check InnoDB lock-wait counters for transactional contention.

   mysql> SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
   +-------------------------------+--------+
   | Variable_name                 | Value  |
   +-------------------------------+--------+
   | Innodb_row_lock_current_waits | 0      |
   | Innodb_row_lock_time          | 81234  |
   | Innodb_row_lock_time_avg      | 68     |
   | Innodb_row_lock_time_max      | 2401   |
   | Innodb_row_lock_waits         | 1192   |
   +-------------------------------+--------+
   5 rows in set (0.00 sec)

   Compare deltas for Innodb_row_lock_waits and Innodb_row_lock_time between samples to spot rising contention during load.

9. Review handler read counters to detect full scans and poor index usage.

   mysql> SHOW GLOBAL STATUS LIKE 'Handler_read%';
   +-----------------------+----------+
   | Variable_name         | Value    |
   +-----------------------+----------+
   | Handler_read_first    | 182      |
   | Handler_read_key      | 9812212  |
   | Handler_read_next     | 84121212 |
   | Handler_read_rnd_next | 1298121  |
   +-----------------------+----------+
   4 rows in set (0.00 sec)

   Large Handler_read_rnd_next growth is commonly associated with table scans.

10. Inspect InnoDB engine state for lock waits and checkpoint activity.

    mysql> SHOW ENGINE INNODB STATUS\G
    *************************** 1. row ***************************
      Type: InnoDB
      Name:
    Status:
    ##### snipped #####
    1 row in set (0.01 sec)

    Running SHOW ENGINE INNODB STATUS too frequently can add overhead on busy systems, so keep it for targeted snapshots during investigation.

11. Create a fixed sampling interval without closing the session.

    mysql> SELECT SLEEP(60);
    +-----------+
    | SLEEP(60) |
    +-----------+
    |         0 |
    +-----------+
    1 row in set (1 min 0.00 sec)

12. Resample key counters to calculate per-second rates.

    mysql> SHOW GLOBAL STATUS WHERE Variable_name IN ('Uptime','Queries','Threads_running');
    +----------------+----------+
    | Variable_name  | Value    |
    +----------------+----------+
    | Queries        | 12809432 |
    | Threads_running| 5        |
    | Uptime         | 187300   |
    +----------------+----------+
    3 rows in set (0.00 sec)

    Queries per second ≈ (Queries_delta / Uptime_delta), so (12809432 - 12789432) / (187300 - 187240) ≈ 333.3 QPS.