Thursday, April 29, 2010

Npgsql Connection Pool Explained

Hi all!

From time to time, we receive some questions regarding connection pool in Npgsql and I think I should post some info about its current design.

Npgsql connection pool implements the common pattern of having some connections open beforehand so when one is needed, it will be readily available for using.

How it works

When a application opens a connection, Npgsql tries to find a pool of connections based on the connection string. If a pool doesn't exist, it is created with a number of connections specified in the MinPoolSize connectionstring parameter. After that, a connection is retrieved from this pool.

The min and max number of connections created in each pool is controlled by connection string parameters called MinPoolSize and MaxPoolSize respectively. This way, users can fine tune the pool behavior to match their scalability needs.

Npgsql controls the lifetime of unused connections in the pool, trying to get connections number near the minimum value set by user. This is done by closing unused connections which are open far long than NpgsqlConnection.ConnectionLifeTime. This control is helpful in a scenario where application uses a lot of connections in a peak situation and later goes back to normal connection usage. Those "extra" connections will stay open but won't be used anytime soon, so instead of laying there consuming server resources, Npgsql simply closes them when their lifetime is reached.

Applications also can clear a pool or all pools by using NpgsqlConnection.ClearPool() and NpgsqlConnection.ClearAllPools() static methods.

EOF Error Message

There is one error message which appears in server log with applications which use Npgsql with pooled connections. This is the error message:

LOG: unexpected EOF on client connection

This is generally caused when the application is terminated and there are connections in the pool. The Tcp connection is closed by the .Net framework without Npgsql sending the Terminate message. Sending the Terminate message to all open connections would be the best thing to do, but Npgsql, by itself, isn't be able to know when the application is being terminated and so the log is generated. According to docs, this disconnection will make the backend clean up and terminate the connection ok. So, the only drawback of this situation is this message log.

In order to get more information about Npgsql connection pooling, you may check the NpgsqlConnectorPool.cs file.

I hope this information helps developers to understand better how connection pool works with Npgsql.

If you have any other questions, please drop by Npgsql forums.

1 comment:

Sergio said...
This comment has been removed by the author.