The database behind a roleplay server

A roleplay server is a database with a game attached. Every character, vehicle, property, inventory item and bank balance is a row, and the server's performance under load is mostly the database's performance under load.

That is not how anyone sets one up. The database is installed in the first hour, never configured, and then blamed two years later when everything is slow.

The failure that actually happens

Not the database falling over. A query that takes 40 milliseconds instead of 2.

Game servers query on the main thread far more than they should (a player connecting, an inventory opening, a vehicle spawning) and a synchronous query is a tick the server is not doing anything else. Forty milliseconds is most of a tick budget spent waiting on a disk.

So database performance shows up as lag, not as database errors, and people go looking in the wrong place. The tell is a stutter correlated with player actions rather than with player count.

The three things that fix ninety per cent of it

Indexes on what you actually query. Every framework ships a schema and most ship inadequate indexes, because the author did not know which queries your resources would run. A lookup by Steam identifier, by character ID, by owner — each needs an index, and without one the database reads the whole table.

This is the single largest win available and it takes an afternoon: log the slow queries, look at what they filter on, add the index.

Stop querying on every tick. Some resources poll the database in a loop because it was the simplest thing to write. Cache in memory and write on change. This is a code fix rather than a database one, and it is the most common cause of a server that degrades as the player count rises.

Give it memory. A database with a buffer pool large enough to hold the working set reads from RAM. One without reads from disk, every time, forever. The default configuration assumes a small shared machine, and it is almost always too small for a dedicated one.

Backups have to be transactional

A database copied while it is being written is a database that may not restore. The dump has to be consistent, and it has to be taken in the same window as the world save.

A world from 03:00 and a database from 03:40 will disagree about who owns what — a player who sold a car in between has the money and the car, or neither. That inconsistency is worse than a clean rollback because it is silent and it propagates.

Dump transactionally, in the same window, and keep them together. It is the same discipline as any other backup plus one constraint that is easy to miss.

The growth nobody watches

Logs and transaction history grow without limit unless something prunes them. A roleplay server running for two years accumulates millions of rows nobody will ever read, and every query that scans the table gets slower.

Set a retention policy on anything that is a log. Ninety days of transaction history is generous for dispute resolution and a fraction of the size of forever, and an unbounded table is a disk that fills on a schedule nobody has calculated, which is the failure that arrives at the worst moment.

Indexes, or the query that gets slower every month

The other half of the growth problem is that nothing gets slower until it does, and then it gets slower very quickly.

A framework ships with indexes on the columns it queries itself. It has no indexes on the columns your scripts query, because it has never seen them. So a lookup by character name, or by plate, or by discord id runs as a full table scan, which is instant on a thousand rows and takes half a second on a hundred thousand.

That half second is not spent in the background. Most framework database calls run on the server's main thread unless the resource author deliberately made them asynchronous, so the query time is server time, and every player experiences it as the tick that did not happen.

The diagnostic is the slow query log. Turn it on, set the threshold to 100ms, and leave it for an evening. It will name three queries, and adding an index to each is usually a one-line change with an effect you can feel the same night.

What a migration actually costs you

Framework updates change the schema, and the update instructions almost always include a SQL file to run.

Two things about that file are worth knowing before you run it. It is not reversible: there is no down migration, so the only way back is the dump you took first. And on a large table an ALTER locks it for the duration, which on a live server means every script touching that table stalls until it finishes.

The safe order is dump, stop the server, migrate, start, verify. The order people actually use is run the SQL while the server is up and hope, which usually works and produces the incident that has no explanation when it does not, and which is the same class of change as any other you did not stage.

Do not put it on the same disk as the world

If you have one machine, you have one disk, and the world save and the database contend for it. A backup job on one stalls the other, and both show up as lag.

Where you can separate them, do. Where you cannot, at least stagger anything scheduled so they are not writing at the same time: the same reasoning as running several servers on one box, where the contention is invisible from inside any one of them.

What to do this week

Turn on the slow query log. Leave it for a busy evening. Read the top five.

Almost every roleplay server has two or three queries responsible for most of the pain, and they are nearly always missing an index or being run far more often than necessary. Finding them is an hour, and it is the difference between a server that is slow at forty players and one that is not, which is a much cheaper answer than buying a bigger machine for a problem the hardware was never causing.