Database Tuning for FiveM Servers

Why Your Database Matters More Than You Think

Most FiveM server owners focus on scripts and configs. The database gets ignored until something breaks — or until the server starts freezing for 200ms every time someone opens their inventory.

The reality: your MySQL database is involved in almost every player action. Logging in, saving positions, managing inventories, banking, vehicle spawning — all of it hits the database. And if your queries are slow, everything feels laggy.

Finding Slow Queries

Enable the Slow Query Log

Add this to your MySQL config (/etc/mysql/mysql.conf.d/mysqld.cnf):

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5

Restart MySQL, let your server run for a few hours, then check the log. You’ll likely find a few queries that run hundreds of times and take too long.

Use oxmysql Debug Mode

If you’re using oxmysql, enable query profiling:

set mysql_debug true

This logs every query with execution time. Look for anything above 50ms.

The Three Most Common Problems

1. Missing Indexes

This is the single most impactful fix. Without proper indexes, MySQL scans every row in a table to find what it needs.

Check your indexes:

SHOW INDEX FROM users;
SHOW INDEX FROM owned_vehicles;
SHOW INDEX FROM items;

Common indexes that should exist but often don’t:

CREATE INDEX idx_users_identifier ON users(identifier);
CREATE INDEX idx_vehicles_owner ON owned_vehicles(owner);
CREATE INDEX idx_items_owner ON items(owner);
CREATE INDEX idx_banking_identifier ON banking(identifier);

A single missing index on a table with 50,000 rows can turn a 0.5ms query into a 500ms query.

2. Bloated Tables

Tables grow over time. Player logs, chat history, phone messages — they accumulate thousands of rows that are never cleaned up.

Check table sizes:

SELECT table_name, table_rows, 
       ROUND(data_length / 1024 / 1024, 2) AS data_mb
FROM information_schema.tables
WHERE table_schema = 'es_extended'
ORDER BY data_length DESC;

Fix: Archive or delete old data. Phone messages older than 30 days? Chat logs from 6 months ago? Delete them or move them to an archive table.

3. N+1 Query Patterns

This happens when a script loops through players and makes a database query for each one — instead of fetching all the data in a single query.

-- Bad: one query per player (N+1)
for _, playerId in ipairs(GetPlayers()) do
  local result = MySQL.Sync.fetchAll(
    'SELECT * FROM users WHERE identifier = ?', 
    {GetPlayerIdentifier(playerId)}
  )
end

-- Better: single query for all
local identifiers = {}
for _, playerId in ipairs(GetPlayers()) do
  table.insert(identifiers, GetPlayerIdentifier(playerId))
end
local result = MySQL.Sync.fetchAll(
  'SELECT * FROM users WHERE identifier IN (?)', 
  {identifiers}
)

MySQL Configuration Tweaks

Default MySQL settings are conservative. For a FiveM server database, these adjustments help:

innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit = 2
max_connections = 100

innodb_buffer_pool_size should be roughly 50-70% of available RAM on a dedicated database server. For a shared VPS, 256M-512M is usually appropriate.

Maintenance Routine

Run these periodically (weekly or after major data changes):

OPTIMIZE TABLE users;
OPTIMIZE TABLE owned_vehicles;
OPTIMIZE TABLE items;
ANALYZE TABLE users;

This reclaims space from deleted rows and updates index statistics so MySQL can make better query plans.

When Queries Aren’t the Problem

Sometimes the database itself is fine, but the connection handling is the bottleneck. If you’re using mysql-async instead of oxmysql, the connection pool might be exhausted under load.

Switching to oxmysql with a properly sized connection pool (10-20 connections for most servers) can reduce query latency significantly.

If you want a detailed analysis of your database performance, that’s part of every Full Audit.