Quantum Ink

Writing about the future as it unfolds.

Diagnosing MySQL performance bottlenecks in clusters

How to Diagnose Performance Bottlenecks Inside the Cluster

When a MySQL InnoDB cluster slows down, it is rarely a single problem. You need a repeatable way to decide whether the bottleneck is CPU, I/O, locks, queries, or replication/cluster coordination. This article walks through a practical, DBA-style approach for diagnosing performance bottlenecks inside a MySQL-based cluster.

1. Establish the Scope of the Bottleneck

First, confirm whether the slowdown is local to one node or affects the whole cluster.

Cluster view (logical)

┌─────────────┐      ┌─────────────┐
│  Primary    │◀────▶│  Replica 1  │
└─────────────┘      └─────────────┘
        ▲                  ▲
        │                  │
        ▼                  ▼
┌─────────────┐      ┌─────────────┐
│  Replica 2  │      │  Replica 3  │
└─────────────┘      └─────────────┘

Check:

  • Is the issue on all nodes or only specific ones?
  • Is it read-only traffic, write-heavy, or mixed?
  • Has anything changed: schema, queries, deployment, data volume?

Quick checks per node (RHEL/Rocky Linux):

top -H -p $(pidof mysqld)        # CPU, run queue, threads
vmstat 1 10                      # run queue, context switches, I/O
iostat -x 1 10                   # per-device I/O utilisation

If only one node is saturated while others are idle, the bottleneck may be:

  • Uneven load balancing
  • Replication lag on other nodes
  • Configuration drift (buffer sizes, logging, etc.)

2. Check Core Server Health on Each Node

Inside MySQL, gather basic health indicators.

2.1 Connections and Thread Activity

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL VARIABLES LIKE 'max_connections';
  • Threads_running consistently close to or above CPU core count suggests CPU saturation or slow queries.
  • Connections near max_connections indicate connection storms or leaks.

2.2 InnoDB Buffer Pool and I/O

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Innodb_data_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_data_writes';
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';

Look at buffer pool hit ratio (simplified snapshot):

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Innodb_buffer_pool_reads',
  'Innodb_buffer_pool_read_requests'
);

If Innodb_buffer_pool_reads is large relative to Innodb_buffer_pool_read_requests, the buffer pool may be too small, or queries are scanning too much data.

Innodb_log_waits > 0 means writes are waiting on log flushes, often due to slow storage or undersized log file / log buffer.

2.3 Temporary Tables and Sorts

SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';
SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size';

A high rate of on-disk temporary tables (Created_tmp_disk_tables) suggests poor indexing or small tmp table limits, which can cause I/O bottlenecks.

3. Identify Query-Level Bottlenecks

3.1 Use Performance Schema / sys Schema

Ensure Performance Schema is enabled (performance_schema=ON in my.cnf). Then query the top statements:

USE sys;

SELECT
  DIGEST_TEXT AS query_sample,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT/1e12, 3) AS total_sec,
  ROUND(AVG_TIMER_WAIT/1e9, 3) AS avg_ms
FROM statement_analysis
ORDER BY total_sec DESC
LIMIT 10;

This quickly surfaces the heaviest queries by total time.

3.2 Live Session and Lock Inspection

Find currently running expensive queries:

SELECT
  id,
  user,
  host,
  db,
  command,
  time,
  state,
  info
FROM information_schema.PROCESSLIST
WHERE command <> 'Sleep'
ORDER BY time DESC
LIMIT 20;

Check lock waits in InnoDB:

SELECT *
FROM information_schema.INNODB_LOCK_WAITS
LIMIT 20;

If many sessions are waiting on locks, the bottleneck is probably contention on a hot row or index. Use the blocking transaction IDs to inspect the offending queries.

3.3 EXPLAIN Problem Queries

Once you have candidate slow queries, run EXPLAIN or EXPLAIN ANALYZE (if available) to check:

  • Full table scans on large tables
  • Missing or non-selective indexes
  • Filesort and temp table usage
EXPLAIN SELECT ...;

Optimising the worst 5–10 queries often yields more benefit than any configuration change.

4. Detect InnoDB Contention and Hotspots

4.1 Row Lock Contention

Look for row-level contention:

SHOW ENGINE INNODB STATUS\G

In the TRANSACTIONS section, check for many transactions in LOCK WAIT state and long-running transactions holding locks.

Common causes:

  • Hot counters or status rows updated very frequently
  • Large batch updates without commits
  • Non-indexed foreign key relationships causing table scans with locks

4.2 Buffer Pool and Flush Pressure

From SHOW ENGINE INNODB STATUS, review:

  • Buffer pool hit rate
  • Checkpoint age and flushing activity
  • Pending reads/writes

If you see many pages being flushed and a lot of pending I/O, the storage system may be the bottleneck, or dirty pages are accumulating faster than the system can flush them.

5. Check Replication and Cluster-Specific Bottlenecks

In a replicated or native MySQL InnoDB Cluster, replication and group coordination can be limiting factors.

5.1 Asynchronous Replication Lag

On replicas:

SHOW SLAVE STATUS\G   -- or SHOW REPLICA STATUS\G depending on version

Key fields:

  • Seconds_Behind_Master (or equivalent): high values indicate lag.
  • SQL_Delay, SQL_Running, Last_SQL_Errno: check for errors or stopped threads.

If replicas are consistently behind, the primary may be saturating I/O or CPU, or replicas are slower hardware/configuration.

5.2 Group Replication / InnoDB Cluster Metrics

For Group Replication-based clusters, check group status:

SELECT * FROM performance_schema.replication_group_members;
SELECT * FROM performance_schema.replication_group_member_stats;

Look for:

  • Nodes in ERROR or RECOVERING state
  • High Transactions_remote_in_applier_queue
  • High Transactions_remote_applier_queue_time

This indicates replication applier lag or network/coordination bottlenecks.

5.3 Network Latency Between Nodes

On each node, verify network RTT to peers:

ping -c 5 other-node

For more detail, use ss or tcptraceroute (if installed) to diagnose packet loss or high latency. Group replication is sensitive to network quality; poor latency directly impacts commit performance.

6. Node-Level Resource Bottlenecks

6.1 CPU Saturation

Use top or pidstat to check CPU usage of MySQL threads:

pidstat -t -p $(pidof mysqld) 1 10

Symptoms:

  • Load average > number of vCPUs for extended periods
  • High user CPU with many active queries → query optimisation needed
  • High system CPU → possible kernel, I/O, or context-switch overhead

6.2 Disk I/O Bottlenecks

iostat -x 1 10

Watch for:

  • %util near 100% on MySQL data devices
  • High await or svctm times

If the storage volume is saturated, consider:

  • Reducing I/O via better indexing and query tuning
  • Moving logs and data to faster storage
  • Reviewing InnoDB flushing and log configuration

6.3 Memory Pressure

Check for swapping and memory usage:

free -m
vmstat 1 10

If si/so (swap in/out) in vmstat are non-zero during load, the OS is swapping, which is catastrophic for MySQL latency. Reduce buffer sizes or increase RAM.

7. Configuration and Schema Consistency Across the Cluster

Configuration drift can cause one node to become a bottleneck or behave unpredictably.

7.1 Compare Key MySQL Variables

On each node:

SHOW VARIABLES WHERE Variable_name IN (
  'innodb_buffer_pool_size',
  'innodb_log_file_size',
  'innodb_flush_log_at_trx_commit',
  'sync_binlog',
  'max_connections',
  'innodb_flush_method'
);

Ensure values are aligned with intended roles (e.g. replicas may use different durability settings, but all replicas should be consistent with each other).

7.2 Schema and Index Alignment

All nodes should share identical schemas and indexes. If using online schema changes or external tools, verify consistency:

SHOW CREATE TABLE db.table\G

Differences in indexes can cause replicas to execute the same queries with very different performance characteristics.

8. Best Practices for Systematic Diagnosis

  • Always start from symptoms: latency, throughput, errors, and which services are affected.
  • Check cluster-wide health first, then drill down to individual nodes.
  • Use Performance Schema/sys views for data-driven query tuning.
  • Regularly capture baselines (status counters, OS metrics) to compare against.
  • Automate metric collection (e.g. Prometheus, Percona Monitoring and Management, or similar) to avoid guessing.
  • Change one thing at a time and re-measure.

Conclusion

Diagnosing performance bottlenecks inside a MySQL InnoDB cluster means moving methodically from the cluster view down to individual queries. By combining OS metrics, MySQL status counters, Performance Schema analysis, and replication/cluster health checks, you can quickly narrow down whether the problem is CPU, I/O, locks, or coordination. Apply fixes incrementally, verify their impact, and keep configurations and schemas consistent across all nodes.

This article offers general technical guidance. Validate all configurations in a safe environment before applying them to production.

Smart reads for curious minds

We don’t spam! Read more in our privacy policy