Is PostgreSQL multi-threaded?

* Rich Teer <richard.teer@rite-group.com> [000529 17:03] wrote:

Hi all,

I have a (hopefully) easy & quick question: is PostgreSQL 7.0 (or earlier
releases) multithreaded, i.e., will it make good use of multiple processors?
The target platform is Sun SPARC (SuperSPARC & UltraSPARC), running Solaris 7
or Solaris 8.

Postgresql works under a multi-process model, it will take advantage
of multiple processors. However it not multi-threaded.

Consider trimming your .sig.

thanks,
--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

Alfred Perlstein wrote:

* Rich Teer <richard.teer@rite-group.com> [000529 17:03] wrote:

Hi all,

I have a (hopefully) easy & quick question: is PostgreSQL 7.0 (or earlier
releases) multithreaded, i.e., will it make good use of multiple processors?
The target platform is Sun SPARC (SuperSPARC & UltraSPARC), running Solaris 7
or Solaris 8.

Postgresql works under a multi-process model, it will take advantage
of multiple processors. However it not multi-threaded.

So in other words, it *is* multithreaded. It just uses heavyweight
threads.

On Mon, 29 May 2000, Joseph Shraibman wrote:

Postgresql works under a multi-process model, it will take advantage
of multiple processors. However it not multi-threaded.

So in other words, it *is* multithreaded. It just uses heavyweight
threads.

Thanks - but I did mean (light weight) threads, as opposed to processes.
I think the former can be more efficient than the latter - although perhaps
a bit more tricky to design well!

--
Rich Teer

NT tries to do almost everything UNIX does, but fails - miserably.

The use of Windoze cripples the mind; its use should, therefore, be
regarded as a criminal offence. (With apologies to Edsger W. Dijkstra)

If it ain't analogue, it ain't music.

Voice: +1 (250) 763-6205
WWW: www.rite-group.com

Alfred Perlstein wrote:

* Rich Teer <richard.teer@rite-group.com> [000529 17:03] wrote:

Hi all,

I have a (hopefully) easy & quick question: is PostgreSQL 7.0 (or earlier
releases) multithreaded, i.e., will it make good use of multiple processors?
The target platform is Sun SPARC (SuperSPARC & UltraSPARC), running Solaris 7
or Solaris 8.

Postgresql works under a multi-process model, it will take advantage
of multiple processors. However it not multi-threaded.

So in other words, it *is* multithreaded. It just uses heavyweight
threads.

Exactly.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

At 10:28 PM 29-05-2000 -0400, Joseph Shraibman wrote:

So in other words, it *is* multithreaded. It just uses heavyweight
threads.

I call those ropes ;). A lot more robust, but give your system enough of
them it'll hang itself.

Cheerio,
Link.

Lincoln Yeoh wrote:

At 10:28 PM 29-05-2000 -0400, Joseph Shraibman wrote:

So in other words, it *is* multithreaded. It just uses heavyweight
threads.

I call those ropes ;). A lot more robust, but give your system enough of
them it'll hang itself.

If performance goes down because of missing computing power,
more threads won't make it better.

You're better off by designing the application to use pooled
DB connections, like AOL-Server for example.

Multithreading is kinda Buzzword, and many people today
believe it is the solution for all performance problems.
Well, starting a thread is faster, consumes less resources,
yada, yada. But they forget about the problems.

All threads live in the same address space. In PostgreSQL,
someone can write his own C functions, and run them in his
test database. If such a function is buggy, should it be able
to corrupt the memory of another thread serving the
production DB?

Threads have OS specific limits. Linux for example doesn't
support the POSIX call to set the per thread stack limit. It
manages them dynamically up to 2MB. In other OSs you have to
decide what's the estimated required stack size.

What counts for a DB server is speed and reliability. But I
think it's a bad decision to gain speed from mucking with
reliability.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Rich Teer wrote:

On Mon, 29 May 2000, Joseph Shraibman wrote:

Postgresql works under a multi-process model, it will take advantage
of multiple processors. However it not multi-threaded.

So in other words, it *is* multithreaded. It just uses heavyweight
threads.

Thanks - but I did mean (light weight) threads, as opposed to processes.
I think the former can be more efficient than the latter - although perhaps
a bit more tricky to design well!

Whenever someone talks about multithreaded, I think they are
missing something important. Namely, that the performance
difference between threads and processes is really not that
great and separate processes are far and away more robust.
It's almost impossible for one backend to accedently affect
another.
--
Martijn van Oosterhout <kleptog@cupid.suninternet.com>
http://cupid.suninternet.com/~kleptog/

My $0.02:

Jan Wrote...

Lincoln Yeoh wrote:

At 10:28 PM 29-05-2000 -0400, Joseph Shraibman wrote:

So in other words, it *is* multithreaded. It just uses heavyweight
threads.

I call those ropes ;). A lot more robust, but give your system enough of
them it'll hang itself.

If performance goes down because of missing computing power,
more threads won't make it better.

Right. Except in pathological cases the work done by a thread/process
ought to be a lot more than the work done by the Kernel to switch
the thread or process.

You're better off by designing the application to use pooled
DB connections, like AOL-Server for example.

An NxM multiplexer for PostgreSQL is something that I've been thinking about
writing for a long time. Kind of like being able to tune the number
of server processes that Apache runs.

Multithreading is kinda Buzzword, and many people today
believe it is the solution for all performance problems.
Well, starting a thread is faster, consumes less resources,
yada, yada. But they forget about the problems.

All threads live in the same address space. In PostgreSQL,
someone can write his own C functions, and run them in his
test database. If such a function is buggy, should it be able
to corrupt the memory of another thread serving the
production DB?

Not only are you faced with sharing the memory space, but threaded
programs force the programmer to constantly worry about controlling
access to the critical sections. Myself, I vastly prefer the Tcl
event-driven model. When your handler runs, it's got the process:
do what you gotta do and get out of the way.

Threads have OS specific limits. Linux for example doesn't
support the POSIX call to set the per thread stack limit. It
manages them dynamically up to 2MB. In other OSs you have to
decide what's the estimated required stack size.

Arrg. Stack size. A giant step backwards. I spent way too much
of my youth fighting with overflowing stacks and wild pointers in
real-time systems using RT Kernels like PSOS. Never again. Not
that I didn't like PSOS, but only for Hard with a Capital H real
time.

What counts for a DB server is speed and reliability. But I
think it's a bad decision to gain speed from mucking with
reliability.

Which is what Oracle says. In 95% of the cases you run your process
communicating with an Oracle "Shadow Process" that accesses the
database for you. For things like sql loader[1] they link the app directly
with the oracle access libraries to squeeze the last few percent of
performance out of the system. Otherwise forget it. Not worth the
trouble.

-- cary