[mod_python] Best practise for database connection pooling?

Simon Willison wrote:

> We are looking to build a large content management system using 
> mod_python and PostgreSQL. I'm interested in what is considered "best 
> practise" for database connection pooling. So far, I've been playing 
> with the idea of setting up a single database connection as a global 
> variable at the start of each mod_python script - since scripts stay 
> in memory between requests, this can be reused for requests without 
> needing to reopen the connection. It also means that every httpd child 
> process spun off by the server ends up with its own database connection.

We have a system where we use multiple different database connections 
for different applications running on the same server.
Each application object exists once per child, and has its own database 
connection.

> The postgres server is running on the same machine as the web server, 
> but we may move to separate servers for the two in the future. Am I on 
> the right track, or is there a better way of managing database 
> connnections?

You may want to look at http://sqlrelay.sourceforge.net/ - I haven't 
used it, but it seems useful particularly if you have separate servers.
For those not going and reading the page:
SQL Relay is a persistent database connection pooling, proxying and load 
balancing system for Unix and Linux supporting ODBC, Oracle, MySQL, 
mSQL, PostgreSQL, Sybase, MS SQL Server, IBM DB2, Interbase, Lago and 
SQLite with APIs for C, C++, Perl, Perl-DBD, Python, Python-DB, Zope, 
PHP, Ruby, Ruby-DBD, TCL and Java, command line clients, a GUI 
configuration tool and extensive documentation. The APIs support 
advanced database operations such as bind variables, multi-row fetches, 
client side result set caching and suspended transactions. It is ideal 
for speeding up database-driven web-based applications, accessing 
databases from unsupported platforms, migrating between databases, 
distributing access to replicated databases and throttling database access.

>
> Incidentally, the database connection module we are using is 
> http://pypgsql.sourceforge.net/

I've found PyGreSQL to be better...
http://www.druid.net/pygresql/
But you may find otherwise...

David