Mrinal Kamboj Mrinal Kamboj - 3 months ago 16
C# Question

Custom Connection pool for Exasol Ado.Net provider

We are using a In memory database

Exasol
, which does provide a Ado.Net provider, but it seems to be missing some important functionality like
ConnectionPooling
, thus every connection is created and destroyed per request, which is impacting our performance, as we are connecting to a hosted database on
AWS
. I have created a simple
ConnectionPool
, with an ability to
Resize
, please suggest if this would serve the purpose or I need to do something more.

Please note I am not looking for code review, but critical analysis of what I might be missing in current implementation, also if there's a implementation available (Nuget, Git), which I can reuse. Currently I am resizing based on size, how shall achieve the same based on time, where certain duration of idleness, shall purge few resources from the queue, thus decreasing the size.

Important details:


  1. Use
    ConcurrentQueue
    internally for thread safe access to resources, from multiple clients

  2. Use
    AutoResetEvent
    for wait and signal if the pool is empty

  3. Use
    TPL
    for resizing operations, without halting the calling code, my understanding is this work even when the client call returns, as its on a
    Threadpool thread


    class ExasolConnectionPool
    {
    /// <summary>
    /// Thread safe queue for storing the connection objects
    /// </summary>
    private ConcurrentQueue<EXAConnection> ExasolConnectionQueue { get; set; }

    /// <summary>
    /// Number of connections on the Connection pool
    /// </summary>
    private int _connectionCount;

    /// <summary>
    /// Max Pool Size
    /// </summary>
    private int MaxPoolSize { get; set; }

    /// <summary>
    /// Min Pool Size
    /// </summary>
    private int MinPoolSize { get; set; }

    /// <summary>
    /// Increase in Pool Size
    /// </summary>
    private int IncreasePoolSize { get; set; }

    /// <summary>
    /// Decrease in Pool Size
    /// </summary>
    private int DecreasePoolSize { get; set; }

    /// <summary>
    /// Connection string for the Connection pool connections
    /// </summary>
    private string ConnectionString { get; set; }

    /// <summary>
    /// Auto Reset event for the connection pool
    /// </summary>
    private AutoResetEvent ExasolConnectionPoolAre { get; set; }

    /// <summary>
    /// Connection pool specific Lock object
    /// </summary>
    private readonly object lockObject;

    /// <summary>
    /// Connection pool constructor
    /// </summary>
    /// <param name="connectionString"></param>
    /// <param name="poolSize"></param>
    public ExasolConnectionPool(string connectionString, int poolSize = 10)
    {
    // Set the Connection String
    ConnectionString = connectionString;

    // Intialize the Connection Queue
    ExasolConnectionQueue = new ConcurrentQueue<EXAConnection>();

    // Enqueue initial set of connections
    for (int counter = 0; counter < poolSize; counter++)
    {
    var exaConnection = new EXAConnection {ConnectionString = ConnectionString};

    ExasolConnectionQueue.Enqueue(exaConnection);
    }

    // Initialize Lock object
    lockObject = new object();

    // Set the Connection queue count
    _connectionCount = poolSize;

    // Max pool size
    MaxPoolSize = poolSize;

    // Min Pool Size
    MinPoolSize = 2;

    IncreasePoolSize = 5;

    DecreasePoolSize = 3;

    ExasolConnectionPoolAre = new AutoResetEvent(false);
    }

    /// <summary>
    ///
    /// </summary>
    /// <returns></returns>
    public EXAConnection GetConnection()
    {
    // Return ExaConnection object
    EXAConnection returnConnection;

    // Try Dequeue the connection object from the Concurrent Queue
    var validExasolConnection = ExasolConnectionQueue.TryDequeue(out returnConnection);

    // If No Valid connection is available, then wait using AutoReset signaling mechanism
    while (!validExasolConnection)
    {
    ExasolConnectionPoolAre.WaitOne();

    validExasolConnection = ExasolConnectionQueue.TryDequeue(out returnConnection);
    }

    // Thread safe connection count update
    Interlocked.Decrement(ref _connectionCount);

    Task.Factory.StartNew(() =>
    {
    lock (lockObject)
    {
    if (_connectionCount > MinPoolSize) return;

    for (var counter = 0; counter < IncreasePoolSize; counter++)
    {
    var exaConnection = new EXAConnection {ConnectionString = ConnectionString};

    ExasolConnectionQueue.Enqueue(exaConnection);

    Interlocked.Increment(ref _connectionCount);
    }
    }
    });

    return (returnConnection);
    }

    /// <summary>
    ///
    /// </summary>
    /// <param name="returnedConnection"></param>
    public void ReturnConnection(EXAConnection returnedConnection)
    {
    ExasolConnectionQueue.Enqueue(returnedConnection);

    Interlocked.Increment(ref _connectionCount);

    ExasolConnectionPoolAre.Set();

    Task.Factory.StartNew(() =>
    {
    lock (lockObject)
    {
    if (_connectionCount < MaxPoolSize * 1.5) return;

    for (var counter = 0; counter < DecreasePoolSize; counter++)
    {
    EXAConnection exaConnection;

    if (ExasolConnectionQueue.TryDequeue(out exaConnection))
    {
    exaConnection.Dispose();

    exaConnection = null;

    Interlocked.Decrement(ref _connectionCount);
    }
    }
    }
    });
    }
    }


Answer

Implementation for your pool is fine. I'm not aware of any NuGet implementations which are that small and aren't overcomplicated for your case. I just want to add a small amount of suggestions you can investigate at your own.

  1. StartNew is Dangerous article by Stephen Cleary is great post about the method you are using for resizing logic. Most important part is this:

    Thread "A" will run on whatever TaskScheduler is currently executing!

    So your code sometimes could use the UI thread context and degrade the performance of your application. If it's ok for you (e.g. for an ASP.NET application), fine, but if not, I suggest you to use the Task.Run method instead. You can also examine Stephen's blog regarding TPL best practices.

  2. In general, resizing logic is done with a simple manner, with doubling the size, so if you've reached the limit, size became twice as it, and vice versa for a decreasing. I think that providing the users the ability to manage this constants could lead to some strange errors, like negative pool size and similar.

    So you should do your property setters as private and, as for me, remove the properties regarding the resize. Maybe in future you can gather the statistics for the pool size in average for your application, and use that parameter as default.