MariaDB / MySQL Production Tuning

MariaDB and MySQL tuning for production

The database is the most common bottleneck in an online store, though it usually presents as CPU load. Out-of-the-box settings are designed to let the server start, not to run under load.

What delivers the biggest effect

• Buffer pool size. If it is smaller than the working data set, the database reads from disk instead of memory constantly. The classic situation: the value was left at its default while the database grew to several gigabytes.
• Slow queries. I enable the log and find what runs on every page without a suitable index. One added index often achieves more than all the configuration tuning combined.
• Connection limits — aligned with the PHP worker count, otherwise you get either queuing or memory exhaustion.
• Table engine. Older stores sometimes contain tables in a format that locks the whole table on write — on an active site that is noticeable.
• Maintenance: cleaning and optimising the order and session tables, which grow over time.

What I do not do

I do not copy configurations from the internet. Values are calculated for your actual memory and load profile: a "powerful server" configuration applied to a small one causes crashes, because the system runs out of memory.

Order of work

Measurements under real load, slow query analysis, one change at a time with a control measurement after each. The output is a configuration matched to your server, plus a list of queries worth fixing at code level.