Most MySQL performance issues don’t come from the database engine itself but from misconfigured defaults. MySQL ships with conservative settings that are safe for small deployments but often disastrous for production workloads. At JusDB, we regularly encounter teams struggling with slow databases simply because a handful of parameters were left at defaults. Fixing them can deliver 2x–10x performance improvements.
In this post, we cover the Top 10 MySQL configuration mistakes we see most often and how to fix them, including ready-to-use configuration snippets.
1) InnoDB Buffer Pool Sized Incorrectly
The InnoDB buffer pool is MySQL’s primary cache. If it’s too small, queries thrash the disk. Too large, and the OS starves. Aim to dedicate 60–70% of RAM for OLTP workloads.
Configuration Example 1
[mysqld]
innodb_buffer_pool_size = 64G
innodb_buffer_pool_instances = 82) Relying on the Legacy Query Cache
The query cache is removed in MySQL 8.0. On older versions, it often hurts scalability. Disable it and use Redis/Valkey for caching.
Configuration Example 2
[mysqld]
query_cache_type = 0
query_cache_size = 03) Redo Log Capacity Too Small
Small redo logs force frequent checkpoints, hurting throughput. Modern workloads need larger redo logs.
Configuration Example 3
[mysqld]
innodb_redo_log_capacity = 4096M4) Temporary Tables Falling to Disk
When internal temp tables exceed limits, they spill to disk. Align tmp_table_size and max_heap_table_size to reduce this risk.
Configuration Example 4
[mysqld]
tmp_table_size = 256M
max_heap_table_size = 256M5) Connections Mismanagement
Unbounded connections consume memory. Instead of setting max_connections extremely high, use application-level pooling.
Configuration Example 5
[mysqld]
max_connections = 300
thread_cache_size = 100
sort_buffer_size = 256K
join_buffer_size = 256K6) Durability vs Throughput Knobs Set Blindly
innodb_flush_log_at_trx_commit and sync_binlog control durability. Choose strict settings for primaries, relaxed ones for replicas or analytics workloads.
Configuration Example 6
[mysqld]
innodb_flush_log_at_trx_commit = 1
sync_binlog = 17) Table Cache and File Descriptors Out of Balance
If table_open_cache is too low, MySQL constantly reopens tables. Too high, and you exceed open_files_limit. Balance both.
Configuration Example 7
[mysqld]
table_open_cache = 4000
open_files_limit = 655358) Ignoring I/O Capacity
SSDs handle much higher throughput than MySQL’s defaults assume. Tune I/O parameters to match hardware.
Configuration Example 8
[mysqld]
innodb_io_capacity = 10000
innodb_io_capacity_max = 200009) Reverse DNS Lookups on Connect
MySQL may attempt reverse DNS lookups for incoming connections, slowing handshakes. Disable with skip_name_resolve.
Configuration Example 9
[mysqld]
skip_name_resolve = ON10) Flush Method Not Tuned for Storage
Linux deployments often benefit from O_DIRECT to avoid double-buffering. Always benchmark to validate.
Configuration Example 10
[mysqld]
innodb_flush_method = O_DIRECTCopy-Paste Starter Snippet
[mysqld]
innodb_buffer_pool_size = 64G
innodb_redo_log_capacity = 4096M
tmp_table_size = 256M
max_heap_table_size = 256M
max_connections = 300
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_io_capacity = 10000
skip_name_resolve = ON
innodb_flush_method = O_DIRECTHow to Verify Improvements
- Check
SHOW GLOBAL STATUScounters for temp tables, I/O, and buffers. - Use the slow query log or query digest analysis.
- Leverage Performance Schema & monitoring dashboards.
Conclusion
Every workload is different, but avoiding these 10 common mistakes will prevent 80% of performance headaches. Always start with safe baselines, benchmark carefully, and iterate. If you need help implementing MySQL configuration best practices, planning migrations, or conducting performance tuning, reach out to JusDB.
Author: JusDB Database Reliability Engineering Team
Working with JusDB on MySQL Configuration
Configuration mistakes are the most common source of MySQL performance problems we see — and the fastest to fix. A 30-minute configuration review can eliminate issues that teams have been chasing for months. We review MySQL configuration as part of our MySQL consulting engagements. Reach out.
Related reading: MySQL Performance Tuning | InnoDB Architecture | MySQL Timeout Variables