Re: Reasoning behind process instead of thread based arch?

On Wed, Oct 27, 2004 at 05:56:16PM +0200, nd02tsk(at)student(dot)hig(dot)se wrote:
>
> I understand PostgreSQL uses processes rather than threads. I found this
> statement in the archives:
>
> "The developers agree that multiple processes provide
> more benefits (mostly in stability and robustness) than costs (more
> connection startup costs). The startup costs are easily overcome by
> using connection pooling."
>
> Please explain why it is more stable and robust?

I can't speak for the developers, but here are my thoughts:

A critical problem in a thread could terminate the entire process
or corrupt its data. If the database server were threaded, such
problems would affect the entire server. With each connection
handled by a separate process, a critical error is more likely to
affect only the connection that had the problem; the rest of the
server survives unscathed.

> "Also, each query can only use one processor; a single query can't be
> executed in parallel across many CPUs. However, several queries running
> concurrently will be spread across the available CPUs."
>
> And it is because of the PostgreSQL process architecture that a query
> can't be executed by many CPU:s right? Although I wonder if this is the
> case in MySQL. It only says in their manual that each connection is a
> thread.

I don't know if MySQL can use multiple threads for a single query;
it might simply be using one thread per connection instead of a one
process per connection. If that's the case, then queries executed
by a particular connection are still single-threaded, the same as
in PostgreSQL.

A database that uses a separate process for each connection could
still employ multiple threads within each process if somebody could
figure out a way to distribute a query amongst the threads. I don't
know what the PostgreSQL developers' thoughts on that are.

A disadvantage of threads is that some systems (e.g., FreeBSD 4)
implement threads in userland and threads don't take advantage of
multiple CPUs. On such systems, using multiple processes better
employs additional CPUs.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/