Fast MySQL: 80% of the gain in five settings
Misconfigured MySQL/MariaDB is the leading cause of slow web applications we see. The good news: most of the gain fits in a few my.cnf lines.
1. InnoDB buffer pool: the king of settings
This is the data and index cache. Rule: 60-70% of RAM dedicated to MySQL. On a Titan (16 GB) with MySQL mostly alone: innodb_buffer_pool_size = 10G. All data fits in RAM, disk reads disappear.
2. innodb_flush_log_at_trx_commit = 2
Writes the transaction log once per second instead of every commit. Potential loss: 1 second of transactions on a brutal crash (not an OS outage). Gain: up to 5× on write-intensive workloads. Acceptable for 99% of applications.
3. innodb_io_capacity adapted to NVMe
Default values (200) date from mechanical disks. On our NVMe Gen4: innodb_io_capacity = 4000 and innodb_io_capacity_max = 8000. MySQL stops throttling its background writes.
4. Disable the query cache (MySQL 5.7/MariaDB)
Counter-intuitive but true: the query cache locks and slows things under concurrency. Redis as an application cache does better, without contention.
5. slow_query_log = 1
Enable the slow query log (long_query_time = 0.5) and review it weekly. A missing index on a 10M-row table does more damage than all settings combined.
Measure before and after
sysbench oltp_read_write or a simple ab/k6 on your heaviest page. Our customers applying these five settings typically measure TTFB halved on WooCommerce.