How can I scale a server with limited database connections

Question

I've made a TCP server for a turn-based game in C#. The server uses MySql for the database and uses stored procedures for all database interactions. All database interactions are contained within a repository layer which is injected into a service layer. These services are then injected throughout the server. The database interactions currently include Inserts/Selects/Updates for the following

• Logging
• Account System
• Match history
• Player Stats

I need the server to scale to thousands of concurrent clients. I began load testing and quickly hit a wall with the MySql error 'Too Many Connections'. At any given time, the server may need to call X amount of stored procedures, but there is a limit on how many connections I can have open at any given time.

What I've considered:

• The only solution I've thought of so far is to have some kind of event system queue where you can add a database request to a queue, the queue fires off these requests based on a max connection number and an event gets fired when each request is complete containing the return data. However, I've not done this before so I don't have a clear idea on implementation.

What answer I'm hoping to get:

A solution on how I could scale the server to handle X amount of database calls when there is a limited number of available connections. Ideally with a written example in C# or pseudo code.

Answer 1

As AndrewR suggested, I had a look at connection pools and I ended up writing my own. Not sure if it's particiularly efficient but I've tested it with a load of 10,000 users (bots) and it seems to be working. Here it is:

(Side note - It centres around using a Blocking Collection)

public interface IDbConnection : IDisposable
{
    MySqlConnection? Connector { get; }

    void Connect();
    void Disconnect();
    void Release(bool dispose);
}

public sealed class DbConnection : IDbConnection
{
public MySqlConnection? Connector { get; private set; }

private readonly Config config;
private readonly System.Timers.Timer timeout;

public DbConnection(
    Config config)
{
    this.config = config;
    timeout = new System.Timers.Timer();
    timeout.Elapsed += TimeoutElapsed;
    timeout.Interval = config.Database?.ConnectionTimeout ?? 0;
    timeout.AutoReset = false;
}

public void Connect()
{
    timeout?.Stop();

    if (Connector?.State is System.Data.ConnectionState.Open)
        return;
    
    if (Connector != null && Connector?.State != System.Data.ConnectionState.Open)
        Disconnect();

    Connector = new MySqlConnection(config.Database?.DbConnectionString);
    Connector.Open();
}

public void Release(bool dispose)
{
    if (dispose)
        Dispose();
    else
        timeout?.Start();
}

private void TimeoutElapsed(object? sender, ElapsedEventArgs e) => Disconnect();

public void Disconnect()
{
    Connector?.Close();
    Connector?.Dispose();
}

public void Dispose()
{
    timeout?.Stop();
    timeout?.Dispose();
    Connector?.Close();
    Connector?.Dispose();
    Connector = null;
}
}

public interface IDbConnectionPool : IDisposable
{
int OpenDbConnections { get; }

void Start(CancellationToken cancelToken);
IDbConnection? GetConnection();
void ReleaseConnection(IDbConnection connection);
}

public sealed class DbConnectionPool : IDbConnectionPool
{
public int OpenDbConnections => config.Database?.MaxConnections ?? 0 - freeConnections.Count;

private readonly Config config;
private BlockingCollection<IDbConnection> freeConnections = new();
private CancellationToken cancelToken;

public DbConnectionPool(Config config) => this.config = config;

public void Start(CancellationToken cancelToken)
{
    this.cancelToken = cancelToken;
    freeConnections = new BlockingCollection<IDbConnection>(new ConcurrentBag<IDbConnection>(), config.Database?.MaxConnections ?? 0);
    InitConnections();
}       

public IDbConnection? GetConnection()
{
    if (cancelToken.IsCancellationRequested)
        return null;
    
    if (freeConnections.TryTake(out IDbConnection? connection, config.Database?.ConnectionWaitTime ?? 0))
        connection.Connect();

    return connection;
}

public void ReleaseConnection(IDbConnection connection)
{
    connection.Release(cancelToken.IsCancellationRequested);

    if (!cancelToken.IsCancellationRequested)
        freeConnections.Add(connection);

}       

public void Dispose()
{
    while (freeConnections.TryTake(out IDbConnection? connection))
        connection?.Dispose();

    try { freeConnections?.Dispose(); }
    catch (Exception) { }
}

private void InitConnections()
{
    int count = 0;
    while (count < config.Database?.MaxConnections)
    {
        var con = Container.ServiceProvider?.GetService<IDbConnection>();
        if (con != null)
            freeConnections.Add(con);

        count++;
    }
}
}

Answer 2

You are looking for a connection pool - a layer in your application which will open a fixed number of connections and allow higher layers to use those.

Higher layer will request a connection from the pool, execute operations and release the connection back to the pool.