[mod_python] connection pool (newbie question)

> I've looked at connection pool code for a persistent database
> connection.   I don't know where to create the connection pool.
> If in the handler code (I'm using publisher), wouldn't a new
> pool be created each time the handler is called?

You'll get a new pool for each process (not for each thread)

> In doing a test, I think the web server is prefork MPM.

That means you get as many pools as child processes, which means lots! 
(hundreds...)

If your database is MySQL, forget about pooling, it won't help, 
particularly when using prefork. MySQL is so fast in setting up 
connections, that I could not measure any performance difference in 
using a pooling system on a threading Apache.

Step 1 is to determine whether you really need pooling - it's not a 
problem until it's a problem (Oracle for one is notoriously slow in 
setting up connections, but a few seconds per page might not even really 
hurt your application)

Step 2 is to see what you value more: Your DB engine or the simplicity 
and performance of your program. If you value the latter more, I suggest 
considering switching DBMS in favour of one that sets up connections faster.

Step 3: If you then still "need" pooling, get Apache to use threads 
(worker is also very good on most unix systems) so that you won't need 
to cross process boundaries in order to access and manage your pool 
efficiently.

Step 4: Ouch... you have to do some magic now...
One way is to set up a process that just fetches/updates data using a 
private pool, and use some RPC mechanism (may be as simple as a raw 
socket, or a HTTP based thing) to access it.
Another is to have a "pool" of size 1 for each process. Close that 
connection if the client closes the network connection.