> -----Original Message-----
> From: mlw [mailto:pgsql@mohawksoft.com] 
> Sent: Friday, January 03, 2003 12:47 PM
> To: Shridhar Daithankar
> Cc: PGHackers
> Subject: Re: [HACKERS] Threads
> 
> 
> Please no threading threads!!!
> 
> Has anyone calculated the interval and period of "PostgreSQL needs 
> threads" posts?
> 
> The *ONLY* advantage threading has over multiple processes is 
> the time 
> and resources used in creating new processes.

Threading is absurdly easier to do portably than fork().

Will you fork() successfully on MVS, VMS, OS/2, Win32?

On some operating systems, thread creation is absurdly faster than
process creation (many orders of magnitude).
 
> That being said, I admit that creating a threaded program is 
> easier than 
> one with multiple processes, but PostgreSQL is already there 
> and working.
> 
> Drawbacks to a threaded model:
> 
> (1) One thread screws up, the whole process dies. In a 
> multiple process 
> application this is not too much of an issue.

If you use C++ you can try/catch and nothing bad happens to anything but
the naughty thread.
 
> (2) Heap fragmentation. In a long uptime application, such as a 
> database, heap fragmentation is an important consideration. With 
> multiple processes, each process manages its own heap and what ever 
> fragmentation that exists goes away when the connection is closed.  A 
> threaded server is far more vulnerable because the heap has to manage 
> many threads and the heap has to stay active and unfragmented in 
> perpetuity. This is why Windows applications usually end up 
> using 2G of 
> memory after 3 months of use. (Well, this AND memory leaks)

Poorly written applications leak memory.  Fragmentation is a legitimate
concern.
 
> (3) Stack space. In a threaded application they are more 
> limits to stack 
> usage. I'm not sure, but I bet PostgreSQL would have a problem with a 
> fixed size stack, I know the old ODBC driver did.

A single server with 20 threads will consume less total free store
memory and automatic memory than 20 servers.  You have to decide how
much stack to give a thread, that's true.
 
> (4) Lock Contention. The various single points of access in a process 
> have to be serialized for multiple threads. heap allocation, 
> deallocation, etc all have to be managed. In a multple process model, 
> these resources would be separated by process contexts.

Semaphores are more complicated than critical sections.  If anything, a
shared memory approach is more problematic and fragile, especially when
porting to multiple operating systems.
 
> (5) Lastly, why bother? Seriously? Process creation time is an issue 
> true, but its an issue with threads as well, just not as bad. 
> Anyone who 
> is looking for performance should be using a connection pooling 
> mechanism as is done in things like PHP.
> 
> I have done both threaded and process servers. The threaded 
> servers are 
> easier to write. The process based severs are more robust. From an 
> operational point of view, a "select foo from bar where x > 
> y" will take 
> he same amount of time.

Probably true.  I think a better solution is a server that can start
threads or processes or both.  But that's neither here nor there and I'm
certainly not volunteering to write it.

Here is a solution to the dilemma.  Make the one who suggests the
feature be the first volunteer on the team that writes it.

Is it a FAQ?  If not, it ought to be.

Fri, 3 Jan 2003 12:52:48 -0800
"Dann Corbit" <DCorbit@connx.com>
Re: Threads
On Fri, 2003-01-03 at 14:52, Dann Corbit wrote:
> > -----Original Message-----
> > (1) One thread screws up, the whole process dies. In a 
> > multiple process 
> > application this is not too much of an issue.
> 
> If you use C++ you can try/catch and nothing bad happens to anything but
> the naughty thread.

That doesn't protect against the type of issues he's talking about. 
Invalid pointer reference is a very common snafu which really hoses
threaded applications.  Not to mention resource leaks AND LOCKED
resources which are inherently an issue on Win32.

Besides, it's doubtful that PostgreSQL is going to be rewritten in C++
so bringing up try/catch is pretty much an invalid argument.

>  
> > (2) Heap fragmentation. In a long uptime application, such as a 
> > database, heap fragmentation is an important consideration. With 
> > multiple processes, each process manages its own heap and what ever 
> > fragmentation that exists goes away when the connection is closed.  A 
> > threaded server is far more vulnerable because the heap has to manage 
> > many threads and the heap has to stay active and unfragmented in 
> > perpetuity. This is why Windows applications usually end up 
> > using 2G of 
> > memory after 3 months of use. (Well, this AND memory leaks)
> 
> Poorly written applications leak memory.  Fragmentation is a legitimate
> concern.

And well written applications which attempt to safely handle segfaults,
etc., often leak memory and lock resources like crazy.  On Win32,
depending on the nature of the resources, once this happens, even
process termination will not free/unlock the resources.

> > (4) Lock Contention. The various single points of access in a process 
> > have to be serialized for multiple threads. heap allocation, 
> > deallocation, etc all have to be managed. In a multple process model, 
> > these resources would be separated by process contexts.
> 
> Semaphores are more complicated than critical sections.  If anything, a
> shared memory approach is more problematic and fragile, especially when
> porting to multiple operating systems.

And critical sections lead to low performance on SMP systems for Win32
platforms.  No task can switch on ANY CPU for the duration of the
critical section.  It's highly recommend by MS as the majority of Win32
applications expect uniprocessor systems and they are VERY fast.  As
soon as multiple processors come into the mix, critical sections become
a HORRIBLE idea if any soft of scalability is desired.


> Is it a FAQ?  If not, it ought to be.

I agree.  I think mlw's list of reasons should be added to a faq.  It
terse yet says it all!


-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting


03 Jan 2003 15:45:20 -0600
Greg Copeland <greg@CopelandConsulting.Net>
Re: Threads
----- Original Message ----- 
From: "Greg Copeland" <greg@CopelandConsulting.Net>
Sent: January 03, 2003 4:45 PM

> > > (1) One thread screws up, the whole process dies. In a 
> > > multiple process 
> > > application this is not too much of an issue.
> > 
> > If you use C++ you can try/catch and nothing bad happens to anything but
> > the naughty thread.
> 
> That doesn't protect against the type of issues he's talking about. 
> Invalid pointer reference is a very common snafu which really hoses
> threaded applications.  Not to mention resource leaks AND LOCKED
> resources which are inherently an issue on Win32.

(1) is an issue only for user-level threads. And besides...

----- Original Message ----- 
From: "Dann Corbit" <DCorbit@connx.com>
Sent: January 03, 2003 3:52 PM

> Here is a solution to the dilemma.  Make the one who suggests the
> feature be the first volunteer on the team that writes it.

.. and that's exactly what Shridhar did - he's sent in the code
_already_ in his post, allowing framework to plug in the model
into PG, as he says allowing turning on and off threads where
appropriate and keeping the current model as well. But noone
bothered to go over it if it makes sense.


----- Original Message ----- 
From: "Greg Copeland" <greg@CopelandConsulting.Net>
Sent: January 03, 2003 4:45 PM

> > Is it a FAQ?  If not, it ought to be.
> 
> I agree.  I think mlw's list of reasons should be added to a faq.  It
> terse yet says it all!

<http://developer.postgresql.org/readtext.php?src/FAQ/FAQ_DEV.html+Developers-FAQ#1.9>

But it's not as complete as this threaded thread.

-s

Fri, 3 Jan 2003 17:54:20 -0500
"Serguei Mokhov" <mokhov@cs.concordia.ca>
Re: Threads
"Serguei Mokhov" <mokhov@cs.concordia.ca> writes:
>>> (1) One thread screws up, the whole process dies. In a 
>>> multiple process application this is not too much of an issue.

> (1) is an issue only for user-level threads.

Uh, what other kind of thread have you got in mind here?

I suppose the lack-of-cross-thread-protection issue would go away if
our objective was only to use threads for internal parallelism in each
backend instance (ie, you still have one process per connection, but
internally it would use multiple threads to process subqueries in
parallel).

Of course that gives up the hope of faster connection startup that has
always been touted as a major reason to want Postgres to be threaded...

			regards, tom lane

Fri, 03 Jan 2003 20:34:02 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Threads 
On Fri, 2003-01-03 at 19:34, Tom Lane wrote:
> "Serguei Mokhov" <mokhov@cs.concordia.ca> writes:
> >>> (1) One thread screws up, the whole process dies. In a 
> >>> multiple process application this is not too much of an issue.
> 
> > (1) is an issue only for user-level threads.
> 


Umm.  No.  User or system level threads, the statement is true.  If a
thread kills over, the process goes with it.  Furthermore, on Win32
platforms, it opens a whole can of worms no matter how you care to
address it.

> Uh, what other kind of thread have you got in mind here?
> 
> I suppose the lack-of-cross-thread-protection issue would go away if
> our objective was only to use threads for internal parallelism in each
> backend instance (ie, you still have one process per connection, but
> internally it would use multiple threads to process subqueries in
> parallel).
> 

Several have previously spoken about a hybrid approach (ala Apache). 
IIRC, it was never ruled out but it was simply stated that no one had
the energy to put into such a concept.

> Of course that gives up the hope of faster connection startup that has
> always been touted as a major reason to want Postgres to be threaded...
> 
> 			regards, tom lane

Faster startup, should never be the primary reason as there are many
ways to address that issue already.  Connection pooling and caching are
by far, the most common way to address this issue.  Not only that, but
by definition, it's almost an oxymoron.  If you really need high
performance, you shouldn't be using transient connections, no matter how
fast they are.  This, in turn, brings you back to persistent connections
or connection pools/caches.


-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting


03 Jan 2003 20:11:17 -0600
Greg Copeland <greg@CopelandConsulting.Net>
Re: Threads
On Fri, 2003-01-03 at 21:39, mlw wrote:
> Connection time should *never* be in the critical path. There, I've
> said it!! People who complain about connection time are barking up the
> wrong tree. Regardless of the methodology, EVERY OS has issues with
> thread creation, process creation, the memory allocation, and system
> manipulation  required to manage it. Under load this is ALWAYS slower.
> 
> I think that if there is ever a choice, "do I make startup time
> faster?" or "Do I make PostgreSQL not need a dump/restore for upgrade"
> the upgrade problem has a much higher impact to real PostgreSQL sites.


Exactly.  Trying to speed up something that shouldn't be in the critical
path is exactly what I'm talking about.

I completely agree with you!


-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting


03 Jan 2003 21:38:46 -0600
Greg Copeland <greg@CopelandConsulting.Net>
Re: Threads
Greg Copeland wrote:

>  
>
>>Of course that gives up the hope of faster connection startup that has
>>always been touted as a major reason to want Postgres to be threaded...
>>
>>			regards, tom lane
>>    
>>
>
>Faster startup, should never be the primary reason as there are many
>ways to address that issue already.  Connection pooling and caching are
>by far, the most common way to address this issue.  Not only that, but
>by definition, it's almost an oxymoron.  If you really need high
>performance, you shouldn't be using transient connections, no matter how
>fast they are.  This, in turn, brings you back to persistent connections
>or connection pools/caches.
>
Connection time should *never* be in the critical path. There, I've said 
it!! People who complain about connection time are barking up the wrong 
tree. Regardless of the methodology, EVERY OS has issues with thread 
creation, process creation, the memory allocation, and system 
manipulation  required to manage it. Under load this is ALWAYS slower.

I think that if there is ever a choice, "do I make startup time faster?" 
or "Do I make PostgreSQL not need a dump/restore for upgrade" the 
upgrade problem has a much higher impact to real PostgreSQL sites.








Greg Copeland wrote:


  

Of course that gives up the hope of faster connection startup that has
always been touted as a major reason to want Postgres to be threaded...

			regards, tom lane
    


Faster startup, should never be the primary reason as there are many
ways to address that issue already.  Connection pooling and caching are
by far, the most common way to address this issue.  Not only that, but
by definition, it's almost an oxymoron.  If you really need high
performance, you shouldn't be using transient connections, no matter how
fast they are.  This, in turn, brings you back to persistent connections
or connection pools/caches.

Connection time should *never* be in the critical path. There, I've said
it!! People who complain about connection time are barking up the wrong tree.
Regardless of the methodology, EVERY OS has issues with thread creation,
process creation, the memory allocation, and system manipulation  required
to manage it. Under load this is ALWAYS slower. 

I think that if there is ever a choice, "do I make startup time faster?"
or "Do I make PostgreSQL not need a dump/restore for upgrade" the upgrade
problem has a much higher impact to real PostgreSQL sites.
Fri, 03 Jan 2003 22:39:08 -0500
mlw <pgsql@mohawksoft.com>
Re: Threads
> Umm.  No.  User or system level threads, the statement is true.  If a
> thread kills over, the process goes with it.  Furthermore, on Win32

Hm. This is a database system. If one of the backend processes dies 
unexpectedly, I'm not sure I would trust the consistency and state of the 
others.

Or maybe I'm just being chicken.

-- 
Kaare Rasmussen            --Linux, spil,--        Tlf:        3816 2582
Kaki Data                tshirts, merchandize      Fax:        3816 2501
Howitzvej 75               ï¿½ben 12.00-18.00        Email: kar@kakidata.dk
2000 Frederiksberg        Lï¿½rdag 12.00-16.00       Web:      www.suse.dk

Sat, 4 Jan 2003 13:59:35 +0100
Kaare Rasmussen <kar@kakidata.dk>
Re: Threads
On Sat, 2003-01-04 at 06:59, Kaare Rasmussen wrote:
> > Umm.  No.  User or system level threads, the statement is true.  If a
> > thread kills over, the process goes with it.  Furthermore, on Win32
> 
> Hm. This is a database system. If one of the backend processes dies 
> unexpectedly, I'm not sure I would trust the consistency and state of the 
> others.
> 
> Or maybe I'm just being chicken.

I'd call that being wise.  That's the problem with using threads. 
Should a thread do something naughty, the state of the entire process is
in question.  This is true regardless if it is a user mode, kernel mode,
or hybrid thread implementation.  That's the power of using the process
model that is currently in use.  Should it do something naughty, we
bitch and complain politely, throw our hands in the air and exit.  We no
longer have to worry about the state and validity of that backend.  This
creates a huge systemic reliability surplus.

This is also why the concept of a hybrid thread/process implementation
keeps coming to the surface on the list.  If you maintain the process
model and only use threads for things that ONLY relate to the single
process (single session/connection), should a thread cause a problem,
you can still throw you hands in the air and exit just as is done now
without causing problems for, or questioning the validity of, other
backends.

The cool thing about such a concept is that it still opens the door for
things like parallel sorts and queries as it relates to a single
backend.


-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting


04 Jan 2003 12:21:20 -0600
Greg Copeland <greg@CopelandConsulting.Net>
Re: Threads

Greg Copeland <greg@CopelandConsulting.Net> writes:

> That's the power of using the process model that is currently in use. Should
> it do something naughty, we bitch and complain politely, throw our hands in
> the air and exit. We no longer have to worry about the state and validity of
> that backend.

You missed the point of his post. If one process in your database does
something nasty you damn well should worry about the state of and validity of
the entire database, not just that one backend.

Are you really sure you caught the problem before it screwed up the data in
shared memory? On disk?


This whole topic is in need of some serious FUD-dispelling and careful
analysis. Here's a more calm explanation of the situation on this particular
point. Perhaps I'll follow up with something on IO concurrency later.

The point in consideration here is really memory isolation. Threads by default
have zero isolation between threads. They can all access each other's memory
even including their stack. Most of that memory is in fact only needed by a
single thread. 

Processes by default have complete memory isolation. However postgres actually
weakens that by doing a lot of work in a shared memory pool. That memory gets
exactly the same protection as it would get in a threaded model, which is to
say none.

So the reality is that if you have a bug most likely you've only corrupted the
local data which can be easily cleaned up either way. In the thread model
there's also the unlikely but scary risk that you've damaged other threads'
memory. And in either case there's the possibility that you've damaged the
shared pool which is unrecoverable.

In theory minimising the one case of corrupting other threads' local data
shouldn't make a big difference to the risk in the case of an assertion
failure. I'm not sure in practice if that's true though. Processes probably
reduce the temptation to do work in the shared area too.

--
greg


07 Jan 2003 13:21:47 -0500
Greg Stark <gsstark@mit.edu>
Re: Threads
On Tue, 2003-01-07 at 12:21, Greg Stark wrote:
> Greg Copeland <greg@CopelandConsulting.Net> writes:
> 
> > That's the power of using the process model that is currently in use. Should
> > it do something naughty, we bitch and complain politely, throw our hands in
> > the air and exit. We no longer have to worry about the state and validity of
> > that backend.
> 
> You missed the point of his post. If one process in your database does
> something nasty you damn well should worry about the state of and validity of
> the entire database, not just that one backend.
> 

I can assure you I did not miss the point.  No idea why you're
continuing to spell it out.  In this case, it appears the quotation is
being taken out of context or it was originally stated in an improper
context.

> Are you really sure you caught the problem before it screwed up the data in
> shared memory? On disk?
> 
> 
> This whole topic is in need of some serious FUD-dispelling and careful
> analysis. Here's a more calm explanation of the situation on this particular
> point. Perhaps I'll follow up with something on IO concurrency later.
> 


Hmmm.  Not sure what needs to be dispelled since I've not seen any FUD.


> The point in consideration here is really memory isolation. Threads by default
> have zero isolation between threads. They can all access each other's memory
> even including their stack. Most of that memory is in fact only needed by a
> single thread. 
> 

Again, this has been covered already.


> Processes by default have complete memory isolation. However postgres actually
> weakens that by doing a lot of work in a shared memory pool. That memory gets
> exactly the same protection as it would get in a threaded model, which is to
> say none.
> 

Again, this has all been covered, more or less.  You're comments seem to
imply that you did not fully read what has been said on the topic thus
far or that you misunderstood something that was said.  Of course, it's
also possible that I may of said something out of it's proper context
which may be confusing you.

I think it's safe to say I don't have any further comment unless
something new is being brought to the table.  Should there be something
new to cover, I'm happy to talk about it.  At this point, however, it
appears that it's been beat to death already.


-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting


07 Jan 2003 12:42:33 -0600
Greg Copeland <greg@CopelandConsulting.Net>
Re: Threads
Greg Stark <gsstark@mit.edu> writes:
> You missed the point of his post. If one process in your database does
> something nasty you damn well should worry about the state of and validity of
> the entire database, not just that one backend.

Right.  And in fact we do blow away all the processes when any one of
them crashes or panics.  Nonetheless, memory isolation between processes
is a Good Thing, because it reduces the chances that a process gone
wrong will cause damage via other processes before they can be shut
down.

Here is a simple example of a scenario where that isolation buys us
something: suppose that we have a bug that tromps on memory starting at
some point X until it falls off the sbrk boundary and dumps core.
(There are plenty of ways to make that happen, such as miscalculating
the length of a memcpy or memset operation as -1.)  Such a bug causes
no serious damage in isolation, because the process suffering the
failure will be in a tight data-copying or data-zeroing loop until it
gets the SIGSEGV exception.  It won't do anything bad based on all the
data structures it has clobbered during its march to the end of memory.

However, put that same bug in a multithreading context, and it becomes
entirely possible that some other thread will be dispatched and will
try to make use of already-clobbered data structures before the ultimate
SIGSEGV exception happens.  Now you have the potential for unlimited
trouble.

In general, isolation buys you some safety anytime there is a delay
between the occurrence of a failure and its detection.

> Processes by default have complete memory isolation. However postgres
> actually weakens that by doing a lot of work in a shared memory
> pool. That memory gets exactly the same protection as it would get in
> a threaded model, which is to say none.

Yes.  We try to minimize the risk by keeping the shared memory pool
relatively small and not doing more than we have to in it.  (For
example, this was one of the arguments against creating a shared plan
cache.)  It's also very helpful that in most platforms, shared memory
is not address-wise contiguous to normal memory; thus for example a
process caught in a memset death march will hit a SIGSEGV before it
gets to the shared memory block.

It's interesting to note that this can be made into an argument for
not making shared_buffers very large: the larger the fraction of your
address space that the shared buffers occupy, the larger the chance
that a wild store will overwrite something you'd wish it didn't.
I can't recall anyone having made that point during our many discussions
of appropriate shared_buffer sizing.

> So the reality is that if you have a bug most likely you've only corrupted the
> local data which can be easily cleaned up either way. In the thread model
> there's also the unlikely but scary risk that you've damaged other threads'
> memory. And in either case there's the possibility that you've damaged the
> shared pool which is unrecoverable.

In a thread model, *most* of the accessible memory space would be shared
with other threads, at least potentially.  So I think you're wrong to
categorize the second case as unlikely.

			regards, tom lane

Tue, 07 Jan 2003 14:01:53 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Threads 