[Tcl-tdbc] Connection pooling for TDBC

[Cyan O. <cya...@gm...>]

I've put together a thread-aware connection pooling mechanism for TDBC and
would like to know if there is interest in merging the changes into TDBC
itself, and if so, what the process for that would be.

The design looks like this:

To retrieve a handle from the pool:

    tdbc::${driver}::connection create db -host localhost -db foo

becomes:

    tdbc::pool $driver create db -host localhost -db foo

When the thread is finished with the connection, it instead of

    db destroy   (or db close)

it would do:

    db release

which destroys db and any statements and resultsets associated with it,
rolls back the transaction if one is open, and parks the database handle
for the connection in a pool of detached connections.  The next call to
tdbc::pool that matches the driver and connection arguments will retrieve
the most recently released connection instead of creating a new connection
(making it more of a stack than a pool).  This is done to keep the busy set
of handles to a minimum and allow the idle ones to time out (2 minutes) and
be closed.  This way the pool will grow as needed with a load spike and
then shrink back down once the spike has passed and idle detached
connections time out.

When tdbc::pool issues a connection from the pool (creating a fresh one if
needed), and he pool is empty, it initiates a new connection in a
background thread and adds it to the pool, priming it for the next
request.  The intent of the pooling mechanism is primarily to hide the
connection setup latency from short-lived users of the db handles, as is
typical of web server usage patterns.

Time to get a handle from the pool on my test machine is between 80 and 300
microseconds, vs 7000 to 18000 microseconds without the pool (postgres or
mysql database on localhost).

The detached connections in the pool are periodically checked (every 20
seconds), and those that are no longer connected to the server or which are
older than 2 minutes are closed and removed from the pool.  If the pool
size goes to 0 as a result, a new connection is created to prime the pool.

In order to support connection pooling, a TDBC driver needs 3 things:

- Support construction with a single parameter "-attach $handle", which
should create a new connection instance and attach it to the previously
detached database handle indicated by $handle:

    tdbc::${driver}::connection new -attach $handle

- A new method "detach" that destroys the connection and returns a handle
that can be used in the future to reattach to this database connection
(possibly from another thread):

    $connection detach

- A new method "connected" which verifies that the underlying database
connection is still valid, returning a boolean:

    $connection connected

I have implemented these changes for the postgres and mysql drivers, using
a global Tcl_HashTable protected with a mutex to store the ConnectionData
struct against the issued handle (with the pidata removed).

If a driver lacks these additions, the behaviour falls back to detach ->
destroy, and tdbc::pool will just always construct a fresh connection
object, so it is backwards and forwards compatible with drivers that don't
know about the connection pooling.

I have a basic set of test cases for the changes in the postgres and mysql
drivers, and the connection pool machinery itself (currently implemented in
the TDBC core package, in Tcl code).  If this feature is something that
could be merged, then I'll write the docs and try to write test cases for
the more pathological race conditions.

It currently supports vanilla Tcl using the Thread package, and NaviServer
using it's own threading (because thread::create interacts badly NaviServer
during certain parts of the server startup).  That means there is some
polyfill code to provide thread::send when using NaviServer's threads,
which lack that capability (using [chan pipe]).  Since my motivation for
this connection pooling mechanism is to replace ns_db in NaviServer for
rubylane.com, it's important to me that it works in NaviServer, but the
polyfill may be too grubby and specific for TDBC generally.  It could be
merged with just Thread support and I'll just maintain the polyfill for
NaviServer separately for our use.

Cyan