Common MySQL Performance Bottlenecks and How to Fix Them

When MySQL slows down, the first symptom is rarely "the database is slow." It is usually a checkout page that hangs, a queue that stops draining, a dashboard that times out, or an API that suddenly needs three seconds for a request that used to finish in 80 ms.

The fastest way to waste time is to tune random settings before you know where the wait is. Start by asking a plain question: is MySQL waiting on query work, locks, memory, disk, CPU, network, or too many connections? The fix depends on the answer.

1. Slow Queries

Slow queries are arguably the most common performance bottleneck. They can arise from various factors, including inefficient query design, missing indexes, or large table scans. Identifying these queries is the first step to resolution.

Identifying Slow Queries

The MySQL slow query log is an invaluable tool for identifying queries that take longer than a specified threshold to execute. You can enable and configure this log in your my.cnf (or my.ini) configuration file.

Example my.cnf configuration:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

In this example:

• slow_query_log = 1: Enables the slow query log.
• slow_query_log_file: Specifies the path to the log file.
• long_query_time = 2: Sets the threshold to 2 seconds. Queries taking longer than this will be logged.
• log_queries_not_using_indexes = 1: Logs queries that don't use indexes, which are often prime candidates for optimization.

After enabling the log, you can analyze its contents. Tools like mysqldumpslow can help summarize and sort the log file, making it easier to pinpoint the most problematic queries.

Optimizing Slow Queries

Once slow queries are identified, several strategies can be employed:

• Indexing: Ensure appropriate indexes are created for columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses. Use EXPLAIN to analyze query execution plans and identify missing indexes.

  • Example: If a query frequently filters by user_id on a large orders table, an index on orders(user_id) can drastically improve performance.

  CREATE INDEX idx_user_id ON orders (user_id);
  

• Query Rewriting: Sometimes, a query can be rewritten for better efficiency. This might involve simplifying joins, avoiding SELECT *, or using subqueries more judiciously.

  • Example: Replacing a correlated subquery with a JOIN might offer better performance.

• Database Schema Design: Reviewing the database schema for normalization issues or for opportunities to denormalize (cautiously) can also help.

2. Inefficient Indexing

While indexing is key to query performance, poorly designed or excessive indexes can also become a bottleneck. Indexes consume disk space and add overhead to write operations (INSERT, UPDATE, DELETE).

Identifying Indexing Issues

• EXPLAIN Plan Analysis: Always use EXPLAIN before and after making indexing changes. Look for full table scans (type: ALL) on large tables, or rows examined that are much higher than rows returned.

  EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
  

• Unused Indexes: MySQL 5.6+ has a feature to track index usage. You can check performance_schema.table_io_waits_summary_by_index_usage to identify indexes that are never or rarely used.

• Redundant Indexes: Indexes that cover the same columns or are prefixes of other indexes can be redundant.

Indexing Best Practices

• Index Selectively: Only create indexes where they are truly needed based on query patterns.
• Composite Indexes: For queries filtering on multiple columns, consider composite indexes. The order of columns in a composite index matters.
• Covering Indexes: Aim for covering indexes where all columns needed by a query are part of the index. This allows MySQL to retrieve data directly from the index without accessing the table.
• Regular Review: Periodically review your indexes, especially after schema changes or shifts in application usage.

3. Buffer Pool and Memory Configuration

The InnoDB buffer pool is a critical memory area where InnoDB caches data and index pages. Insufficient buffer pool size can lead to excessive disk I/O, significantly slowing down operations.

Tuning the InnoDB Buffer Pool

The innodb_buffer_pool_size parameter is one of the most important settings for InnoDB performance.

Recommendation: For dedicated database servers, setting innodb_buffer_pool_size to 50-75% of available RAM is a common starting point. Some systems can run higher, but only if the operating system is not swapping and connection memory is under control.

Example my.cnf configuration:

[mysqld]
innodb_buffer_pool_size = 8G

This sets the buffer pool to 8 Gigabytes.

Monitoring: Observe the buffer pool read pattern. A very high hit rate often means most reads are served from memory, but it does not prove every query is healthy. You can monitor this using:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

The hit rate can be calculated as (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests.

Other Memory Settings

• innodb_log_file_size: Affects write performance and recovery time. Larger files can improve write throughput but increase recovery time after a crash.
• innodb_flush_log_at_trx_commit: Controls durability versus performance. Setting it to 1 (default) ensures full ACID compliance but can be slower. Setting it to 0 or 2 can improve performance at the cost of some durability guarantees.

4. Locking Issues and Concurrency

Locking is essential for data consistency but can become a bottleneck if not managed properly. Excessive locking can lead to query contention, timeouts, and deadlocks.

Identifying Locking Problems

• SHOW ENGINE INNODB STATUS: This command provides detailed information about InnoDB's internal state, including active transactions, locks held, and lock waits.
• Performance Schema lock tables: In MySQL 8.0, use Performance Schema tables such as data_locks and data_lock_waits. Older versions exposed lock information through information_schema tables.
• Monitoring Tools: Performance monitoring tools can often highlight high lock wait times or deadlocks.

Resolving Locking Issues

• Optimize Queries Causing Locks: Shorter, more efficient queries reduce the time locks are held.
• Transaction Management: Keep transactions as short as possible. Avoid long-running operations within transactions that require extensive locking.
• Lock Granularity: InnoDB uses row-level locking for most operations, which is generally good for concurrency. However, understanding how your queries might escalate to table locks (e.g., ALTER TABLE without online DDL) is important.
• Deadlock Detection and Resolution: MySQL has a deadlock detector. When a deadlock is detected, InnoDB will typically roll back one of the transactions involved, allowing the other to proceed. Analyze deadlock information from SHOW ENGINE INNODB STATUS to understand the cause and adjust application logic or query order.

5. Resource Contention (CPU, Disk, Network)

Even with optimized queries and proper configuration, insufficient hardware resources or contention for these resources can limit performance.

Identifying Resource Bottlenecks

• CPU Usage: High CPU usage by the mysqld process can indicate inefficient queries, heavy sorting, or insufficient processing power.
• Disk I/O: High disk read/write activity, especially with low buffer pool hit rates, points to disk I/O as a bottleneck. Look for high iowait times on Linux systems.
• Network Throughput: Excessive network traffic can occur with large result sets being transferred or high numbers of client connections.

Addressing Resource Bottlenecks

• Hardware Upgrades: Sometimes, the simplest solution is to add CPU, RAM, or faster storage. Treat this as a fix only after you know the workload is reasonable; hardware can hide a bad query, but it rarely makes it disappear.
• Query Optimization: Reduce the amount of data processed and transferred, which indirectly reduces CPU, disk, and network load.
• Connection Pooling: Implement connection pooling in your application to reduce the overhead of establishing new connections and manage the number of active connections effectively.
• Read Replicas: For read-heavy workloads, consider setting up read replicas to distribute the read load away from the primary server.

A Triage Flow That Works Under Pressure

When an incident is active, do not start with a full tuning project. Get a quick picture first.

Check active queries:

SHOW FULL PROCESSLIST;

If you see many sessions stuck on the same query, capture it. If you see many sessions waiting for locks, do not kill things randomly; identify the blocking transaction first.

Check InnoDB state:

SHOW ENGINE INNODB STATUS\G

Look for deadlocks, lock waits, checkpoint pressure, and long-running transactions. A transaction that has been open for an hour can hold back purge work and make unrelated queries slower.

Check whether the server is saturated:

top
vmstat 1
iostat -xz 1
ss -s

High CPU with low I/O usually points toward expensive query execution, sorting, parsing, or too much concurrency. High iowait points toward storage. Swap activity is a red flag; MySQL under swap pressure often behaves unpredictably.

Then check the slow query log for the last few minutes, not just the all-time worst query. The query that caused today's incident may be new, tied to a deploy, or tied to a traffic pattern that only appears at peak time.

Connection Storms

A common MySQL bottleneck is not one bad query, but too many application connections doing small amounts of work. If every web worker opens its own connection and the app scales out suddenly, MySQL can spend too much time scheduling sessions and allocating per-connection memory.

Symptoms include:

• Threads_connected rising sharply.
• Threads_running staying high.
• Application errors such as Too many connections.
• CPU rising without one obvious slow query.

Useful checks:

SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

The fix is often in the application layer: use connection pooling, set sane pool limits, and make timeouts explicit. Raising max_connections can buy time, but it can also make the server fall over harder if each connection uses memory for joins, sorts, and temporary tables.

Temporary Tables and Sorts

Queries with GROUP BY, ORDER BY, DISTINCT, or large joins can create temporary tables. Some temporary tables stay in memory. Larger ones spill to disk. Disk temporary tables are not automatically a disaster, but a sudden increase often explains latency spikes.

Check:

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

Then inspect the query plans. If EXPLAIN shows Using temporary and Using filesort, ask whether an index can support the filter and order together. For example:

SELECT customer_id, created_at, total
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

An index on (status, created_at) may reduce both filtering and sorting work. Increasing tmp_table_size can help in some cases, but it is a per-session risk. If many sessions allocate large temporary tables at once, memory disappears quickly.

Replication Lag as a Performance Symptom

If reads go to replicas, replication lag can look like a database performance problem even when the primary is fine. Users refresh a page and do not see their own change. Background jobs read stale rows. Reports disagree.

Check replica status with the tooling appropriate for your MySQL version:

SHOW REPLICA STATUS\G

Older versions use:

SHOW SLAVE STATUS\G

Lag can come from slow SQL on the replica, large transactions from the primary, insufficient replica hardware, row-by-row maintenance jobs, or network issues. The fix may be query tuning, breaking large writes into smaller chunks, improving replica resources, or changing where fresh reads are routed.

What to Change First

Prefer fixes that reduce work:

• Add or adjust an index for a proven hot query.
• Rewrite a query to read fewer rows.
• Shorten transactions that hold locks.
• Limit connection pool size so MySQL is not flooded.
• Move heavy reports away from the primary.

Be more cautious with fixes that only increase capacity:

• Raising max_connections.
• Increasing sort and join buffers globally.
• Increasing temporary table limits.
• Adding replicas without fixing the query that hurts them.

Capacity changes have their place, but they should follow evidence. A good MySQL troubleshooting session leaves you with a smaller amount of database work, not just a larger server doing the same wasteful work.