Organizing Proper Data Caching (PlayerInfo) in RAM

One of the most common architectural mistakes when developing SA:MP/CRMP server scripts is dispatching SQL write queries to the database every time a player's state is modified. Handing out cash, buying weapons, gaining experience, changing faction ranks—if your script reacts to each of these events by triggering a heavy UPDATE query, the server will quickly start to lag.

With a population of 150–200 players, the volume of minor transactions per second scales into the hundreds. The storage drive subsystem of your hosting infrastructure (even fast NVMe SSDs) becomes heavily saturated with input/output (I/O) operations. As a result, the server thread hangs, and players experience severe ping spikes. In this article, we will examine how to organize proper data caching using the PlayerInfo structure in the server's RAM to minimize hosting resource consumption.

The Concept of Caching: RAM as the Single Source of Truth

Random Access Memory (RAM) operates thousands of times faster than any physical disk drive. A proper approach to server script architecture is built on a simple rule:

Player records should be read from the database exactly once — upon authorization, and written back to the database only when the player disconnects or via a global autosave timer loop.

The rest of the time a player remains online, any mutations to their level, wallet balances, or inventory should occur exclusively within the server's volatile memory—inside a designated array structure (the cache).

Step 1: Structuring Variables Correctly (The PlayerInfo Array)

To implement a cache in Pawn, an enumerator (enum) is paired with a global multi-dimensional array, the size of which is strictly bound to the server's maximum slot configuration (the MAX_PLAYERS macro definition).

enum E_PLAYER_DATA
{
    pID,
    pName[MAX_PLAYER_NAME],
    pLevel,
    pMoney,
    pAdminLevel,
    pFraction,
    bool:pIsLoggedIn
}

new PlayerInfo[MAX_PLAYERS][E_PLAYER_DATA];

Whenever a user connects or disconnects, this cache segment must be cleared to ensure legacy records are not inherited by a new connection logging in under the same player ID:

public OnPlayerDisconnect(playerid, reason)
{
    SavePlayerAccount(playerid);

    PlayerInfo[playerid][pID] = 0;
    PlayerInfo[playerid][pName][0] = EOS;
    PlayerInfo[playerid][pLevel] = 0;
    PlayerInfo[playerid][pMoney] = 0;
    PlayerInfo[playerid][pAdminLevel] = 0;
    PlayerInfo[playerid][pFraction] = 0;
    PlayerInfo[playerid][pIsLoggedIn] = false;
    return 1;
}

Step 2: Building the Asynchronous Save Routine

The serialization routine must aggregate all runtime modifications held within the PlayerInfo cache segment and dispatch them to the database instance in a single batch using an asynchronous mysql_tquery thread:

stock SavePlayerAccount(playerid)
{
    if(!PlayerInfo[playerid][pIsLoggedIn]) return 0;

    new query[256];
    format(query, sizeof(query),
        "UPDATE `users` SET `level` = %d, `money` = %d, `admin` = %d, `fraction` = %d WHERE `id` = %d",
        PlayerInfo[playerid][pLevel],
        PlayerInfo[playerid][pMoney],
        PlayerInfo[playerid][pAdminLevel],
        PlayerInfo[playerid][pFraction],
        PlayerInfo[playerid][pID]
    );

    mysql_tquery(db_handle, query);
    return 1;
}

Step 3: Setting Up a Global Autosave Loop (Every 15 Minutes)

To safeguard user progress against data corruption in the event of an unexpected application crash or hardware failure, a cyclic backup loop must be deployed.

Warning: Never save every connected client at the exact same second! If 300 active players hit an identical timer constraint, the sudden burst of 300 heavy SQL updates will saturate the background thread queue, bottlenecking all other operational queries. Dispatches must be staggered smoothly over time.

Implementing a Staggered Chunk-Based Autosave Loop:

public OnGameModeInit()
{
    SetTimer("GlobalAutoSave", 60000, true);
    return 1;
}

forward GlobalAutoSave();
public GlobalAutoSave()
{
    static current_chunk = 0;
    
    for(new i = 0; i < MAX_PLAYERS; i++)
    {
        if(!IsPlayerConnected(i) || !PlayerInfo[i][pIsLoggedIn]) continue;
        
        if(i % 15 == current_chunk)
        {
            SavePlayerAccount(i);
        }
    }
    
    current_chunk++;
    if(current_chunk >= 15) current_chunk = 0;
    return 1;
}

Evaluating Storage Subsystem Performance

Let's map out the metric projections for disk load behaviors:

Summary: Keeping data cached within RAM is the only professional strategy for deploying code optimized for heavy player tracking loops. Let the volatile memory manage fast, repeating mutations, and treat your database strictly as a reliable long-term historical ledger.