MariaDB / MySQL Configuration for Production

The database is often a site’s main bottleneck. The default MariaDB/MySQL settings know nothing about your data volume or your RAM. Tuning speeds up queries and makes the database stable under load.

The parameters that matter

• innodb_buffer_pool_size — the key parameter, sized to your RAM;
• innodb_log_file_size and the flush policy;
• connections, timeouts, temporary tables;
• character set and storage engine (InnoDB).

Slow queries

The slow query log is enabled, the heaviest queries analysed and indexes added on frequently filtered fields. A missing index is very often the reason a catalogue or a report drags.

Stability and backups

Regular backups (logical or physical) are set up along with restore verification — because a backup you have never restored is worth nothing.

The stages

1. load and data volume analysed;
2. configuration tuned to available RAM;
3. slow queries found and fixed;
4. backups and monitoring.

Common questions

What buffer pool should I set?

Roughly 50–70% of RAM on a dedicated database server — I will size it exactly for your case.

Do I need the query cache?

On current versions usually not — there are better approaches.

Backups without downtime?

Yes, hot backups are configured.

Database slow or unstable? I will configure MariaDB/MySQL for production.