Re: Connection pool improvements

On 2/3/2010 2:43 PM, Kevin Rosenberg wrote:
> On Feb 3, 2010, at 7:17 AM, Ralf Mattes wrote:
>   
>> But we still have some "issues" with connection pooling. One nagging
>> problem we repeatedly run into: the pool never realeases connections, so
>> if you have a peek of parallel connections the pool keeps them hanging
>> around.
>>     
> Well, that is the point of the pool -- to keep connections open with the
> assumption that they'd all be used at some point in the future. I do see
> your point about if there is a significantly higher peak usage than average
> usage so non-needed connections are left open.
>
> There are several ways to add flexibility to the logic of removing connections
> from the pool that one doesn't think would be needed later on. Some 
> possibilities, in increasing order of implementation complexity, are:
>
> 1) Use the existing CLEAR-CONN-POOL function in your application to clear 
> all connections.
>
>   

Note this function disconnects them without regard to whether they are
actively being used by another thread.

> 2) Add a variable, such as *MAXIMUM-POOLED-CONNECTIONS* so that if a connection
> is released back, but the pool has hit maximum size, then the released connection
> will be closed and discarded. A value of NIL means no maximum, which is the current
> behavior.

I implemented logic similar to the above, but based it on the number of
free connections available when a connection is released to the pool. It
is named clsql-sys:*db-pool-max-free-connections*. It defaults to 4.

This isn't a hard limit, it is possible that there could be more free
connections in the pool--a benign race-condition where both check at the
same time, find there to be fewer free connections than the threshold,
and add to the pool. In general though I think this will be an effective
means of keeping the number of idle connections from ballooning.

The logic around connection pooling has also improved:
 *  It now does a better job of ensuring a connection retrieved from the
pool is valid before handing it off to your code.
 * Finer-grained locking should result in less contention between threads.
 * On databases that provide the functionality(Postgresql and MSSQL) we
reset connection variables before returning it from the pool.

It is up in the master branch at git://git.b9.com/clsql.git.

Nathan