Re: connection pooling

On Thu, Mar 19, 2009 at 02:01:48PM -0700, Tim Roberts wrote:
> David wrote:
> > What specific benefit does an within-process connection pool provide
> > to a system?
> >
> > Psycopg2 is threadsafe to the cursor level, (threadsafety=2) so a
> > process can create a global connection that is shared by all threads;
> > each thread can get a new cursor for its own purposes, and all threads
> > in the process can benefit from one persistant connection.  That would
> > seem to provide all the benefits of connection pooling, without the
> > overhead of checking connections into and out-of  a pool.
> >   
> 
> Also, remember that Python-based web applications on Linux typically use
> multiple processes, not multiple threads.  At the risk of igniting a
> flame war, threading on Linux is somewhat unpleasant, so applications
> that would be multithreaded on Windows are typically multiprocess on Linux.

And this my contribution to the flame war:

What makes you say that? Where is the significant difference between the
pleasantness of threading on Windows and Unix (pthreads)? Especially on Python?

I think you're mixing up cause and effect here. Programming with threads is much more
difficult and error prone than working with multi-process systems (or using
something like Twisted for that matter), regardless of OS. 
Threads are also a relatively recent arrival. So why convert easily understood
and stable designs to something with lots of potential problems? Performance
is almost a none issue given copy-on-write pages and the fork system call.

Windows 2000 is relatively young and doesn't have good support and performance
for multiprocess archictetures (and no fork), also threading was built in from day one, 
so there wasn't and isn't really any alternative to threading. Incidentally
that's why many multiprocess Unix apps perform so badly when ported to Windows, whereas
in the reverse direction you don't lose as much (Threaded on Windows => threaded
on Unix).

On top of it all, Python has the GIL, so if you want to make all your cores do some
work, you need multi process architectures, also on Windows.

So there ;)