DB Pool

[John Nagle]

bcurtu wrote:
> Hi,
> 
> I use MySQLdb lib to access my DB, because I want to optimize my SQL
> queries. The application I'm working on has quite a few traffic load,
> so I want to minimize the time of creating/destroying cursors:
> 
> My typical code is sth like:
> 
>         cursor=con.cursor()
>         cursor.execute(sql)
>         all= cursor.fetchall()
>         cursor.close()
> 
> So, the question is... how can I minimize this cost? I was thinking on
> Connection Pools, but I didn't find any good documentation or sample.
> Any idea?

    Creating and destroying MySQLdb cursors is cheap, because it
doesn't really do anything.  MySQL doesn't actually support
cursors; you can only do one thing at a time per connection, and
thus you can only have one cursor per connection.  So cursor creation
is a dummy operation for MySQLdb.

    Creating connections to the database is more expensive, and it
helps to reuse those.  There was once something called the "pool
module" for doing this ("http://dustman.net/andy/python/Pool")
but it's a dead link now.

    This is usually an issue only in multithreaded programs.
Also, it's OK to have multiple connections open to the database
from multiple threads, until you have so many that the database
server starts to run out of resources.

				John Nagle