What is the best solution for database connection pooling in python?

Question

I have developed some custom DAO-like classes to meet some very specialized requirements for my project that is a server-side process that does not run inside any kind of framework.

The solution works great except that every time a new request is made, I open a new connection via MySQLdb.connect.

What is the best "drop in" solution to switch this over to using connection pooling in python? I am imagining something like the commons DBCP solution for Java.

The process is long running and has many threads that need to make requests, but not all at the same time... specifically they do quite a lot of work before brief bursts of writing out a chunk of their results.

Edited to add: After some more searching I found anitpool.py which looks decent, but as I'm relatively new to python I guess I just want to make sure I'm not missing a more obvious/more idiomatic/better solution.

Answer 1

Wrap your connection class.

Set a limit on how many connections you make. Return an unused connection. Intercept close to free the connection.

Update: http://www.modpython.org/FAQ/faqw.py?file=faq03.003.htp&req=edit I put something like this in dbpool.py (snippet from http://www.sqlalchemy.org/docs/pooling.myt):

import sqlalchemy.pool as pool

import MySQLdb as mysql

mysql = pool.manage(mysql)

Answer 2

IMO, the "more obvious/more idiomatic/better solution" is to use an existing ORM rather than invent DAO-like classes.

It appears to me that ORM's are more popular than "raw" SQL connections. Why? Because Python is OO, and the mapping from SQL row to to object is absolutely essential. There aren't many cases where you deal with SQL rows that don't map to Python objects.

I think that SQLAlchemy or SQLObject (and the associated connection pooling) the more idiomatic Pythonic solution.

Pooling as a separate feature isn't very common because pure SQL (without object mapping) isn't very popular for the kind of complex, long-running processes that benefit from connection pooling. Yes, pure SQL is used, but it's always used in simpler or more controlled applications where pooling isn't helpful.

I think you might have two alternatives:

1. Revise your classes to use SQLAlchemy or SQLObject. While this appears painful at first [all that work wasted], you should be able to leverage all the design and thought and it's merely an exercise in adopting a widely-used ORM and pooling solution.
2. Roll your own simple connection pool using the algorithm you outlined -- a simple Set or List of connections that you cycle through.

Answer 3

In MySQL?

I'd say don't bother with the connection pooling. They're often a source of trouble and with MySQL they're not going to bring you the performance advantage you're hoping for. This road may be a lot of effort to follow--politically--because there's so much best practices hand waving and textbook verbiage in this space about the advantages of connection pooling.

Connection pools are simply a bridge between the post-web era of stateless applications (e.g. HTTP protocol) and the pre-web era of stateful long-lived batch processing applications. Since connections were very expensive in pre-web databases (since no one used to care too much about how long a connection took to establish), post-web applications devised this connection pool scheme so that every hit didn't incur this huge processing overhead on the RDBMS.

Since MySQL is more of a web-era RDBMS, connections are extremely lightweight and fast. I have written many high volume web applications that don't use a connection pool at all for MySQL.

This is a complication you may benefit from doing without, so long as there isn't a political obstacle to overcome.

Answer 4

I've just been looking for the same sort of thing.

I've found pysqlpool and the sqlalchemy pool module

Answer 5

As per me horizontal scalability is good approach...

A. Have mysql-master (may be multi-master for write scalaing) replicating to some number of mysql-slaves.

B. And on each mysql-slave's also run Lighttpd/NginX web server server with php (mean to say a event based websever with FastCGI-PHP). Create index.php in your document-root directory with this minimal content:

php-start-tag

$u = “mysql-read-only-user”; $p = “mysql-read-only-user-password”; $db = “database-name”; $S = “path-of-mysql-socket-file”

$query = stripslashes($_GET["q"]); if (!$query) { exit(”NO SQL – Please provide ?q=the-sql-statement in the url”); }

$cmd = “mysql -u$u -p$p -S$S $db –xml -e ‘$query’”; $output = passthru(”$cmd”, $stat);

if ($stat != 0) { header(”HTTP/1.1 500 Internal Server Error”); exit(”ERROR 500 – Can not process ‘$query’”); }

php-end-tag

This script is just a small sample using which we can easily query mysql over HTTP and the output as xml. Further to this we can extend this sample script to handle json output and even RESTful operations

C. Use HAproxy to load balance these web apps... And access the mysql data over HTTP like: http://url/?q=select * from table-name limit 2

This is for read scaling...

D. For write scaling, use mysql-proxy/mmm and distribute write data to all masters evenly. For example, in bi-master, write all odd records to mysql-server-1 and all even records to mysql-server-2, and replication to each other.

The above is for write scaling...

E. The main points here are: a) Separate your read and write process and treat them separately. b) You can increase security by implementing web access acl's and database credentials from App layer c) Add new instances/server to scale horizontally...

F. Believe me, this is very similar to what Yahoo/AOL/CNET guys do to scale read/write for mysql.

--From: Sidh.