MySQL for Self-Hosted Communities: What I Tune First

Confession: for my first self-hosted community I installed MySQL, accepted every default and moved on with my life. Six months later the feed took two seconds to load and I was reading database docs at 2am next to a cold slice of pizza. Learn from my mistakes.

Here's what I now do on every new box before the first member signs up. The numbers come from a 4 GB VPS running UNA with about 3,000 members, so your mileage will vary.

1. Give InnoDB some memory

The default buffer pool is tiny. Raising innodb_buffer_pool_size to about half the RAM (less if PHP shares the server) took our median feed query from roughly 900 ms to 180 ms. Biggest single win, by a mile.

2. Turn on the slow query log

Log anything slower than one second and actually read the log once a week. Nine times out of ten the culprit is a custom block or a missing index, not the core.

3. Use utf8mb4 everywhere

Your members will use emoji. If the tables aren't utf8mb4, those posts get mangled or rejected. Check this before launch, because converting a live database is a very long evening.

4. Separate users, minimal rights

The app gets its own database user with access to its own database and nothing else. Root stays with you, from localhost only, with a long password that lives in a password manager.

5. Backups you've actually restored

Nightly dumps, copied off the server, kept for 30 days. Once a month I restore one into a scratch database. A backup you've never restored is just a rumour.

It's boring work, but it's the difference between a community that feels snappy and one where people give up waiting for the page. Questions welcome in UNA Builders, where I'm usually lurking long after I should be asleep.