Boost MySQL Performance: 80/20 Tips to Gain Up to 80% Speed

I don't expect to become an expert DBA, but I follow the 80/20 rule: simple configuration tweaks can yield up to 80% performance improvement, especially as server resources become cheaper.

Warning

No two databases or applications are identical; the advice assumes a typical web site that prioritizes fast queries, good user experience, and high traffic.

Back up your database before making any server optimizations.

1. Use the InnoDB storage engine

If you are still using MyISAM, switch to InnoDB. InnoDB stores both indexes and data in memory, whereas MyISAM stores only indexes, making memory‑resident data faster to access.

Convert a table to InnoDB with: ALTER TABLE table_name ENGINE=InnoDB; Ensure appropriate indexes are created, as indexing remains the top priority for performance.

2. Let InnoDB use all available memory

Edit my.cnf and set innodb_buffer_pool_size to about 80% of the server's physical RAM. For a 32 GB server, set it to roughly 25 GB: innodb_buffer_pool_size = 25600M If the server has less than 1 GB RAM, upgrade the hardware; if it has a very large amount (e.g., 200 GB), you don't need to reserve 40 GB for the OS.

3. Enable InnoDB multi‑tasking

When innodb_buffer_pool_size exceeds 1 GB, you can split the buffer pool into multiple instances using innodb_buffer_pool_instances. This reduces contention in multi‑threaded workloads.

Official guidance recommends each buffer‑pool instance to have at least 1 GB of memory.

For a 32 GB server with a 25 GB buffer pool, a suitable setting is:

innodb_buffer_pool_instances = 24

Note!

After modifying my.cnf, restart MySQL for changes to take effect: sudo service mysql restart These guidelines provide a practical baseline for improving MySQL server performance.

Original source: Rich Barrett Translation source: Linux China/qhwdw