Database basics on bare metal
A database on a dedicated server has access to the whole machine. The default configuration assumes it does not, which is why the first hour of tuning gives the biggest return.
Give it the memory
The single most important setting in MySQL and MariaDB is
innodb_buffer_pool_size. It is the cache that holds your data and
indexes in memory. On a machine dedicated to the database, 60–70% of total RAM
is a sound starting point; the default is often 128 MB, which is a rounding
error.
PostgreSQL splits this: shared_buffers at roughly 25% of RAM, and
effective_cache_size at about 60% — the second is not an
allocation, it tells the planner how much the operating system is probably
caching.
You are aiming for a state where your working set is read from memory and the disk only sees writes. Check whether you are there:
SHOW ENGINE INNODB STATUS\G — look at the buffer pool hit
rate
Durability, deliberately
innodb_flush_log_at_trx_commit decides what happens on a power cut.
At 1 (the default) every commit is flushed to disk: safe, slower. At
2 a crash of the machine can lose about a second of transactions, and
throughput rises substantially.
Choose consciously. For orders and payments, stay at 1. For analytics you can rebuild, 2 is defensible. Never choose it by accident because a tuning guide said so.
Set innodb_flush_method=O_DIRECT so data is not cached twice, once by
InnoDB and once by the kernel.
Find the slow queries
This is where the real gains are. Hardware makes a bad query twice as fast; an index makes it a thousand times faster.
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1
Leave it on for a day, then read it with mysqldumpslow or
pt-query-digest. The result is almost always a short list, with one
query at the top responsible for most of the load. EXPLAIN that query
and add the index it is asking for.
For PostgreSQL, enable pg_stat_statements and query it — same
idea, better data.
Storage matters more than cores
Databases are usually bound by IO latency rather than processor. NVMe with power-loss protection is the right hardware; see SAS, SATA and NVMe. Keep the data directory on fast storage and put backups somewhere else — ideally another machine, as backups explains.
Three habits
- Test your restore, not just your dump. See restoring from backup.
- Never expose the port. Bind to localhost or a private address; see connecting your own servers.
- Change one thing at a time and measure. Tuning guides that change twenty settings at once teach you nothing about your own workload.
Still stuck? Mail support@novogara.com — an engineer answers, at any hour. Back to the knowledge base