Custom Connection pool for Exasol Ado.Net provider

Question

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 1

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.