Performance

Performance & best practices

omp-MySQL is fast by default (async, one worker per connection), but how you use it decides whether your server stays smooth at 100 players. Here's what matters.

Golden rule: never block the main thread

The main thread runs your game (movement, sync, callbacks). If it waits on the database, every player lags.

• ✅ Use async queries (mysql_execute, mysql_execute_for, prepared statements with a callback). They run on a worker thread; your callback fires when ready.
• ⚠️ Use mysql_execute_sync only for one-time startup (creating tables). Never in a player event, command, or timer.

// GOOD - async, server keeps ticking
mysql_execute(g_DB, "UPDATE accounts SET money=100 WHERE id=5");

// BAD in a hot path - blocks every player until the DB answers
mysql_execute_sync(g_DB, "UPDATE accounts SET money=100 WHERE id=5");

Don't query in tight loops or per-tick

Calling the database every OnPlayerUpdate (many times a second, per player) will bury it. Instead:

• Keep state in Pawn variables during play.
• Save periodically (a timer, e.g. every 1–2 minutes) and on disconnect.
• See the autosave recipe in the Cookbook.

Batch where you can

Saving one player? One UPDATE. Saving everyone on a timer? Still one UPDATE per player is fine — but don't issue 22 separate queries to save 22 fields of one player. Use a single prepared statement that sets all columns at once (the mysql-admin demo saves ~20 columns in one statement).

Let the database do the work

Don't pull all rows into Pawn to find/sort/count them — ask MySQL:

// GOOD - the DB sorts + limits; you get 10 rows
mysql_execute(g_DB, "SELECT name, score FROM accounts ORDER BY score DESC LIMIT 10", "OnTop");

// BAD - pulling every account to sort in Pawn
mysql_execute(g_DB, "SELECT name, score FROM accounts", "OnEveryone");

Use WHERE, ORDER BY, LIMIT, COUNT(*), SUM(...) — that's what SQL is for.

Index what you search

Every column you frequently WHERE on (e.g. name for login lookups) should have an index, or MySQL scans the whole table. See Designing your tables.

Prepared statements pay off when reused

A prepared statement is parsed once and can be executed many times with different values — ideal for things you run constantly (login lookups, saves). They're also the safe choice for any player input. See Prepared statements.

Connection guards (abuse / runaway protection)

You can cap load per connection (off by default; opt in) with mysql_set_limit:

mysql_set_limit(g_DB, LIMIT_RATE_PER_SEC, 200);   // max queries/second
mysql_set_limit(g_DB, LIMIT_MAX_PENDING, 1000);   // backpressure: max queued/in-flight
mysql_set_limit(g_DB, LIMIT_MAX_LENGTH, 1048576); // reject queries over 1 MiB

These reject abusive bursts before they reach the database. (0 = no limit.)

Watch the queue

mysql_pending_count(g_DB) tells you how many async jobs are queued/in-flight. If it keeps climbing, you're issuing queries faster than the DB can serve them — back off, batch, or add indexes.

Compression (optional)

For a remote database over a slow link, wire compression can help:

mysql_config_set(cfg, COMPRESSION, "zstd");   // or "zlib"

On a local DB it's usually not worth the CPU.

Quick checklist

• No *_sync calls outside startup.
• State cached in Pawn; saved on a timer + on disconnect, not per-tick.
• Filtering/sorting/counting done in SQL, not Pawn.
• Indexes on columns you search by.
• Prepared statements for hot/repeated queries and all player input.

Next: Backups & maintenance · Security