How to optimize MySQL performance

1. Measure first
2. Add proper indexes
3. Size the InnoDB buffer pool
4. Connections and "Too many connections"
5. The query cache is gone in MySQL 8.0
6. Temp tables, sort buffers, and logging
7. Server resources
8. Conclusion

MySQL performance tuning is measure-then-change, not copy-pasting a my.cnf you found in a forum. Random config tweaks usually do nothing, and occasionally make things worse. The reliable way to optimize MySQL performance is to find what's actually slow, fix that one thing, and confirm it helped before moving on. This guide walks through the levers that matter, roughly in the order you should reach for them.

#Measure first

Before you touch a single setting, find out what's slow. Turn on the slow query log and let it collect real traffic:

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

long_query_time = 1 logs anything over one second; lower it once the obvious offenders are gone. After a day of traffic, summarise the log with mysqldumpslow or pt-query-digest to see which queries cost the most total time.

The queries at the top of that list are where your tuning effort belongs. Rewriting one bad query usually beats any config change. See MySQL query optimization for how to read EXPLAIN and fix the offenders.

#Add proper indexes

The single most common cause of a slow query is a missing index. A query that filters or joins on an unindexed column forces a full table scan, which scales linearly with table size: fine at a thousand rows, painful at a million.

EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';

If EXPLAIN shows type: ALL and key: NULL, you're scanning. Add an index that covers the filtered columns:

ALTER TABLE orders ADD INDEX idx_orders_user_status (user_id, status);

Indexing is deep enough to deserve its own treatment, including composite indexes, the leftmost-prefix rule, and what not to index. See how database indexing works.

#Size the InnoDB buffer pool

After indexes, the buffer pool is the highest-impact setting. innodb_buffer_pool_size is the in-memory cache for table and index data; when your working set fits in it, reads come from RAM instead of disk.

On a dedicated database server, set it to roughly 50-70% of total RAM, leaving headroom for the OS, connections, and per-query buffers:

[mysqld]
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 4

To pick a number, first check how much RAM the machine has, see how much memory is on Ubuntu. On an 8 GB dedicated box, 6G is reasonable. On a shared box that also runs PHP-FPM and Nginx, be more conservative, those processes need memory too, and pushing MySQL too high causes swapping.

The default is only 128 MB, so this is almost always worth changing. Verify it took effect:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

#Connections and "Too many connections"

max_connections caps how many clients can connect at once. The default of 151 is fine for many apps, but a traffic spike or a connection leak can exhaust it, and new clients get ERROR 1040: Too many connections.

[mysqld]
max_connections = 300

Resist the urge to set this to thousands. Each connection consumes memory, and a high cap can let a runaway app pile up connections until the server runs out of RAM. The real fix is usually shorter-lived connections, a sane pool size in the app, or persistent connections, not a bigger number. See MySQL 1040: too many connections for diagnosis and the proper fix.

#The query cache is gone in MySQL 8.0

If an old tuning guide tells you to set query_cache_size, ignore it. The query cache was removed in MySQL 8.0, it was a global-lock bottleneck that hurt throughput on write-heavy workloads. Don't try to enable it; the variables no longer exist and MySQL won't start if you reference them.

Cache in the application layer instead. Redis or Memcached in front of expensive read queries gives you far more control and none of the contention.

#Temp tables, sort buffers, and logging

A few per-session buffers help specific workloads, but only raise them when the slow log points there:

[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 4M

tmp_table_size and max_heap_table_size together decide when an in-memory temp table spills to disk; raise both (they must match) if you see created_tmp_disk_tables climbing in SHOW GLOBAL STATUS. Keep sort_buffer_size modest, it's allocated per connection, so a large value multiplied by many connections eats memory fast. Leave the slow query log on in production at a sensible long_query_time so regressions surface on their own.

#Server resources

No config can rescue a starved server. MySQL wants RAM, and it must not swap, going to disk for memory turns millisecond queries into multi-second ones. If the box is borderline, add a swap file as a safety valve against the OOM killer, but treat real swapping as a signal to add RAM or shrink the buffer pool. See add swap space on Ubuntu.

Storage matters too: use SSDs, always. And if you're still on MySQL 5.7, upgrading to 8.0 is one of the biggest free wins available, a smarter optimizer, better defaults, and improved indexing limits. See upgrading MySQL 5.7 to 8.0 on Ubuntu.

#Conclusion

Optimizing MySQL performance is a loop, not a one-time config dump: measure with the slow query log, fix the worst query (usually with an index), size the buffer pool to your RAM, keep connections sane, and make sure the server has memory to spare without swapping. Change one thing at a time and confirm it helped. That discipline beats any "ultimate my.cnf" you'll find online.