Mysql – How can I scale a server with limited database connections

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:

NOTES

• This solution is coupled with MySqlConnection. You can replace with this with any data access class of your choosing
• The max connections the pool will handle is determined by the parameter passed into InstantiateConnectionsAsync.

Interfaces:

public interface IDbConnection : IDisposable
{
    int Id { get; set; }
    MySqlConnection Connector { get; }

    void Connect(int id);
    void Disconnect();
    void Release();
}

public interface IDbConnectionPool : IDisposable
{
    Task<IDbConnection> GetConnectionAsync();

    void ReleaseConnection(IDbConnection connection);
}

DbConnection.cs

using MySqlConnector;
using System.Timers;
public class DbConnection : IDbConnection
{
    public int Id { get; set; }
    public MySqlConnection Connector { get; private set; }

    private readonly Config config;
    private System.Timers.Timer timeout { get; set; }
    private readonly object connectionLock = new();

    public DbConnection(
        Config config)
    {
        this.config = config;
        Id = -1;
        timeout = new System.Timers.Timer();
        timeout.Elapsed += TimeoutElapsed;
        timeout.Interval = config.Database.OpenConnectionTimeout;
        timeout.AutoReset = false;
    }

    public void Connect(int id)
    {       
        timeout?.Stop();
        lock (connectionLock)
        {
            Connector ??= new MySqlConnection();
            if (Connector.State != System.Data.ConnectionState.Open)
            {
                Disconnect();
                Connector = new MySqlConnection(config.Database.DbConnectionString);
                Connector.Open();
            }
            Id = id;
        }                                           
    }

    public void Release()
    {
        timeout?.Start();
        Id = -1;
    }

    private void TimeoutElapsed(object sender, ElapsedEventArgs e)
    {
        Disconnect();
    }

    public void Disconnect()
    {
        lock (connectionLock)
        {
            if (Id < 0)
            {
                Connector?.Close();
                Connector?.Dispose();
            }               
        }               
    }

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

DbConnectionPool.cs

using MySqlConnector;
public class DbConnectionPool : IDbConnectionPool
{
    private readonly Config config;
    private Queue<int> waiting;
    private readonly object idLock = new();
    private readonly object waitingLock = new();
    private readonly object connectionsLock = new();
    private IList<IDbConnection> connections;
    private bool active;
    private int ticketId = 0;

    public DbConnectionPool(
        Config config)
    {
        this.config = config;
        waiting = new Queue<int>();
        connections = new List<IDbConnection>();
        InstantiateConnectionsAsync(config.Database.MaxConnections);
        active = true;
        _ = Task.Run(ConnectionHandler);
    }

    public async Task<IDbConnection> GetConnectionAsync()
    {
        int id;
        id = GenerateTicketId();
        Enqueue(id);

        while (!IsConnectionReady(id))
            await Task.Delay(config.Database.ConnectionWaitTime);

        return connections.FirstOrDefault(f => f.Id == id);
    }

    public void ReleaseConnection(IDbConnection connection)
    {
        lock (connectionsLock)
        {
            connection?.Release();
        }
    }

    private int GenerateTicketId()
    {
        lock (idLock)
        {
            if (ticketId >= int.MaxValue - 1)
                return ticketId = 0;

            return ++ticketId;
        }           
    }

    private void InstantiateConnectionsAsync(int maxConnections)
    {
        int count = 0;
        while (count < maxConnections)
        {
            connections.Add(DIContainer.ServiceProvider.GetService<IDbConnection>());
            count++;
        }
    }

    private void Enqueue(int id)
    {
        try
        {
            lock (waitingLock)
                waiting.Enqueue(id);
        }
        catch (Exception e)
        {
            // getting a weird/inconsistent error around here
        }
    }

    private void ConnectionHandler()
    {
        while (active)
        {
            if (waiting.Count < 1)
            {
                Task.Delay(config.Database.ConnectionHandlerDelay);
                continue;
            }

            IDbConnection connection = null;
            lock (connectionsLock)
            {
                while (waiting.Count > 0 && GetAvailableConnection(ref connection) != null)
                {
                    var id = -1;
                    lock (waitingLock)
                        id = waiting.Dequeue();

                    connection.Connect(id);
                    connection = null;
                }
            }
        }
    }

    private IDbConnection GetAvailableConnection(ref IDbConnection connection) => connection = connections.FirstOrDefault(f => f.Id < 0);

    private bool IsConnectionReady(int id) => connections.Any(a => a.Id == id);     

    public void Dispose()
    {
        active = false;
        lock (connectionsLock)
        {
            Parallel.ForEach(connections ?? new List<IDbConnection>(), connection =>
            {
                connection?.Dispose();
            });
            connections = null;
        }
    }
}

Create a singleton of DbConnectionPool. You can then get a connection by calling GetConnectionAsync(). Here is how calling GetConnectionAsync might look:

public async Task<bool> SomeMethodAsync()
    {
        try
        {
            var con = await pool.GetConnectionAsync();
            using var cmd = new MySqlCommand("UpdateSomething", con.Connector)
            {
                CommandType = CommandType.StoredProcedure
            };

            var result = await cmd.ExecuteNonQueryAsync() > 0;

            await cmd.DisposeAsync();
            pool.ReleaseConnection(con);
            return result;
        }
        catch (Exception ex)
        {
            // Do something with the exception
            return false;
        }
    }