DB Pool

[bcurtu]

Hi,

I use MySQLdb lib to access my DB, because I want to opttimize 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?

 

[Diez B. Roggisch]

Hi,

I use MySQLdb lib to access my DB, because I want to opttimize 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?

What has the above (getting *cursors* from a given connection) to do with
connection-pooling?

I'm not sure what the actual costs of creating a cursor are (might well be
that these are neglibel) but why don't you stop closing the cursor and just
re-use it?

Connection pooling is of course useful - frameworks such as SQLAlchemy and
SQLObject do that for you, if used proper.


Diez

 

[John Nagle]

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