Hi all,

I am sure, many of you would like to delete this message before reading, hold 
on. :-)

There is much talk about threading on this list and the idea is always 
deferred for want of robust thread models across all supported platforms and 
feasibility of gains v/s efforts required.

I think threads are useful in difference situations namely parallelising 
blocking conditions and using multiple CPUs.

Attached is a framework that I ported to C from a C++ server I have written. 
It has threadpool and threads implementation based on pthreads.

This code expects minimum pthreads implementation and does not assume anything 
on threads part (e.g kernel threads or not etc.)

I request hackers on this list to take a look at it. It should be easily 
pluggable in any source code and is released without any strings for any use.

This framework allows to plug-in the worker function and argument on the fly. 
The threads created are sleeping by default and can be woken up s and when 
required.

I propose to use it incrementally in postgresql. Let's start with I/O. When a 
block of data is being read, rather than blocking for read, we can set up 
creator-consumer link between two threads That we way can utilize that I/O 
time in a overlapped fashion.

Further threads can be useful when the server has more CPUs. It can spread CPU 
intensive work to different threads such as index creation or sorting. This 
way we can utilise idle CPU which we can not as of now.

There are many advantages that I can see.

1)Threads can be optionally turned on/off depending upon the configuration. So 
we can entirely keep existing functionality and convert them one-by-one to 
threaded application.

2)For each functionality we can have two code branches, one that do not use 
threads i.e. current code base and one that can use threads. Agreed the 
binary will be bit bloated but that would give enormous flexibility. If we 
find a thread implementation buggy, we simply switch it off either in 
compilation or inconfiguration.

3) Not much efforts should be required to plug code into this model. The idea 
of using threads is to assign exclusive work to each thread. So that should 
not require much of a locking.

In case of using multiple CPUs, separate functions need be written that can 
handle the things in a thread-safe fashion. Also a merger function would be 
required which would merge results of worker threads. That would be totally 
additional.

I would say two threads per CPU per back-end should be a reasonable default as 
that would cover I/O blocking well. Of course unless threading is turned off 
in build or in configuration.

Please note that I have tested the code in C++ and my C is rusty. Quite likely 
there are bugs in the code. I will stress test the code on monday but I would 
like to seek an opinion on this as soon as possible. ( Hey but it compiles 
clean..)

If required I can post example usage of this code, but I don't think that 
should be necessary.:-)

Bye
 Shridhar
Fri, 3 Jan 2003 20:54:11 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
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.

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.

(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)

(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.

(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.

(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.




Fri, 03 Jan 2003 15:47:22 -0500
mlw <pgsql@mohawksoft.com>
Re: Threads
On Fri, 2003-01-03 at 14:47, mlw wrote:
> Please no threading threads!!!
> 

Ya, I'm very pro threads but I've long since been sold on no threads for
PostgreSQL.  AIO on the other hand... ;)

Your summary so accurately addresses the issue it should be a whole FAQ
entry on threads and PostgreSQL.  :)


> 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.
> 
> (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)


These are things that can't be stressed enough.  IMO, these are some of
the many reasons why applications running on MS platforms tend to have
much lower application and system up times (that and resources leaks
which are inherent to the platform).

BTW, if you do much in the way of threaded coding, there is libHorde
which is a heap library for heavily threaded, memory hungry
applications.  It excels in performance, reduces heap lock contention
(maintains multiple heaps in a very thread smart manner), and goes a
long way toward reducing heap fragmentation which is common for heavily
memory based, threaded applications.


> (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.
> 

Most modern thread implementations use a page guard on the stack to
determine if it needs to grow or not.  Generally speaking, for most
modern platforms which support threading, stack considerations rarely
become an issue.


> (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.
> 

I agree with this, however, using threads does open the door for things
like splitting queries and sorts across multiple CPUs.  Something the
current process model, which was previously agreed on, would not be able
to address because of cost.

Example: "select foo from bar where x > y order by foo ;", could be run
on multiple CPUs if the sort were large enough to justify.

After it's all said and done, I do agree that threading just doesn't
seem like a good fit for PostgreSQL.

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


03 Jan 2003 15:34:10 -0600
Greg Copeland <greg@CopelandConsulting.Net>
Re: Threads
>
>
>I am sure, many of you would like to delete this message before reading, hold 
>on. :-)
>

I'm afraid most posters did not read the message.  Those who replied 
"Why bother?" did not address your challenge:

>I think threads are useful in difference situations namely parallelising 
>blocking conditions and using multiple CPUs.
>  
>
This is indeed one of the few good reasons for threads.  Indeed, 
large/robust systems use a mix.

The consensus of the group is that those who do the work are not ready 
for threads.  Which is fine.  Looking into my crystal ball, I see that 
it will happen, though it appears so far away.

bbaker



Fri, 03 Jan 2003 15:50:48 -0600
"bbaker@priefert.com" <bbaker@priefert.com>
Re: Threads


Greg Copeland wrote:

>On Fri, 2003-01-03 at 14:47, mlw wrote:
>  
>
>>Please no threading threads!!!
>>
>>    
>>
>
>Ya, I'm very pro threads but I've long since been sold on no threads for
>PostgreSQL.  AIO on the other hand... ;)
>
>Your summary so accurately addresses the issue it should be a whole FAQ
>entry on threads and PostgreSQL.  :)
>
Thanks! I do like threads myself. Love them! Loving them, however does 
not mean that one should ignore their weaknesses. I have a PHP session 
handler (msession) which is threaded, but I am very careful with memory 
allocation, locks, and so on. I also do a lot of padding in memory 
allocations. I know it is wasteful in the short term, but it keeps the 
little gnats from hosing up the heap.

>>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.
>>
>>(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)
>>    
>>
>
>
>These are things that can't be stressed enough.  IMO, these are some of
>the many reasons why applications running on MS platforms tend to have
>much lower application and system up times (that and resources leaks
>which are inherent to the platform).
>
>BTW, if you do much in the way of threaded coding, there is libHorde
>which is a heap library for heavily threaded, memory hungry
>applications.  It excels in performance, reduces heap lock contention
>(maintains multiple heaps in a very thread smart manner), and goes a
>long way toward reducing heap fragmentation which is common for heavily
>memory based, threaded applications.
>
Thank's I'll take a look.

>  
>
>>(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.
>>
>>    
>>
>
>Most modern thread implementations use a page guard on the stack to
>determine if it needs to grow or not.  Generally speaking, for most
>modern platforms which support threading, stack considerations rarely
>become an issue.
>
One of my projects, msesson, I wrote a SQL (PG and ODBC) plugin. The 
main system thread didn't crash, the server threads went down quickly. I 
had to bump the thread stack up to 250K to work. That doesn't sound like 
much, but if you have 200 connections to your server, thats a lot of 
memory that has to be fit into the process space.

>>(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.
>>
>>    
>>
>
>I agree with this, however, using threads does open the door for things
>like splitting queries and sorts across multiple CPUs.  Something the
>current process model, which was previously agreed on, would not be able
>to address because of cost.
>
>Example: "select foo from bar where x > y order by foo ;", could be run
>on multiple CPUs if the sort were large enough to justify.
>
>After it's all said and done, I do agree that threading just doesn't
>seem like a good fit for PostgreSQL.
>
Yes, absolutely, if PostgreSQL ever grew threads, I think that should be 
the focus, forget the threaded connection crap, threaded queries!!

How about this:
select T1.foo, X1.bar from (select * from T) as T1, (select * from X) as 
X1 where T1.id = X1.id


The two sub queries could execute in parallel. That would rock!

>
>  
>



Fri, 03 Jan 2003 17:26:00 -0500
mlw <pgsql@mohawksoft.com>
Re: Threads
Also remember that in even well developed OS's like FreeBSD, all a
process's threads will execute only on one CPU.  This might change in
FreeBSD 5.0, but still a threaded app (such as MySQL) cannot use mutliple
CPUs on a FreeBSD system.

Chris

On Fri, 3 Jan 2003, mlw wrote:

> 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.
>
> 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.
>
> (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)
>
> (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.
>
> (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.
>
> (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.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>


Sat, 4 Jan 2003 13:33:20 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Threads
On Saturday 04 January 2003 03:20 am, you wrote:
> >I am sure, many of you would like to delete this message before reading,
> > hold on. :-)
>
> I'm afraid most posters did not read the message.  Those who replied
>
> "Why bother?" did not address your challenge:

Our challenges may be..;-) 

Anyway you are absolutely right. Looks like evrybody thought it as an attempt 
to convert postgresql to a thread per connection model.

> >I think threads are useful in difference situations namely parallelising
> >blocking conditions and using multiple CPUs.
>
> This is indeed one of the few good reasons for threads.  Indeed,
> large/robust systems use a mix.
>
> The consensus of the group is that those who do the work are not ready
> for threads.  Which is fine.  Looking into my crystal ball, I see that
> it will happen, though it appears so far away.

I hope it happens and I will be able to contribute to it if I can.

 Shridhar

Mon, 6 Jan 2003 12:02:43 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: Threads
>>>>> "Shridhar" == Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:

    Shridhar> On Saturday 04 January 2003 03:20 am, you wrote:
    >> >I am sure, many of you would like to delete this message
    >> before reading, > hold on. :-)
    >> 
    >> I'm afraid most posters did not read the message.  Those who
    >> replied
    >> 
    >> "Why bother?" did not address your challenge:

    Shridhar> Our challenges may be..;-)

Not having threading does reduce some of the freedom we've been having
in our work. But then we have ripped the process model a fair bit and
we have the freedom of an entirely new process to deal with data
streams entering the system and we're experimenting with threading for
asynchronous I/O there.

However, in general I agree with the spirit of the previous messages
in this thread that threading isn't the main issue for PG.

One thing that I missed so far in the threading thread. Context
switches are (IMHO) far cheaper between threads, because you save TLB
flushes. Whether this makes a real difference in a data intensive
application, I don't know. I wonder how easy it is to measure the x86
counters to see TLB flushes/misses.

In a database system, even if one process dies, I'd be very chary of
trusting it. So I am not too swayed by the fact that a
process-per-connection gets you better isolation. 

BTW, many commercial database systems also use per-process models on
Unix. However they are very aggressive with connection sharing and
reuse - even to the point of reusing the same process for multiple
active connections .. maybe at transaction boundaries. Good when a
connection is maintained for a long duaration with short-lived
transactions separated by fair amouns of time. 

Moreover, in db2 for instance, the same code base is used for both
per-thread and per-process models - in other words, the entire code is
MT-safe, and the scheduling mechanism is treated as a policy (Win32 is
MT, and some Unices MP). AFAICT though, postgres code, such as perhaps
the memory contexts is not MT-safe (of course the bufferpool/shmem
accesses are safe). 

-- 
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh

05 Jan 2003 23:36:38 -0800
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Re: Threads
On Sat, 4 Jan 2003, Christopher Kings-Lynne wrote:

> Also remember that in even well developed OS's like FreeBSD, all a
> process's threads will execute only on one CPU.

I would say that that's not terribly well developed. Solaris will split
a single processes' threads over multiple CPUs, and I expect most other
major vendors Unixes will as well. In the world of free software, the
next release of NetBSD will do the same. (The scheduler activations
system, which support m userland to n kernel threads mapping, was
recently merged from its branch into NetBSD-current.)

 From my experience, threaded sorts would be a big win. I managed to
shave index generation time for a large table from about 12 hours to
about 8 hours by generating two indices in parallel after I'd added a
primary key to the table. It would have been much more of a win to be
able to generate the primary key followed by other indexes with parallel
sorts rather than having to generate the primary key on one CPU (while
the other remains idle), wait while that completes, generate two more
indices, and then generate the last one .

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Fri, 24 Jan 2003 00:02:11 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Threads

On Sat, 4 Jan 2003, Christopher Kings-Lynne wrote:
> 
> Also remember that in even well developed OS's like FreeBSD, all a
> process's threads will execute only on one CPU.

I doubt that - it certainly isn't the case on Linux and Solaris.
A thread may *start* execution on the same CPU as it's parent, but
native threads are not likely to be constrained to a specific CPU
with an SMP OS.

-- 
Steve Wampler <swampler@noao.edu>
National Solar Observatory

23 Jan 2003 08:12:10 -0700
Steve Wampler <swampler@noao.edu>
Re: Threads
On Thursday 23 January 2003 08:42 pm, you wrote:
> On Sat, 4 Jan 2003, Christopher Kings-Lynne wrote:
> > Also remember that in even well developed OS's like FreeBSD, all a
> > process's threads will execute only on one CPU.
>
> I doubt that - it certainly isn't the case on Linux and Solaris.
> A thread may *start* execution on the same CPU as it's parent, but
> native threads are not likely to be constrained to a specific CPU
> with an SMP OS.

I am told that linuxthreads port available on freeBSD uses rfork and is 
capable of using multiple CPUs within a single process.

Native freeBSD threads can not do that. Need to check that with freeBSD5.0.

 Shridhar



Thu, 23 Jan 2003 21:00:01 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: Threads
On Thu, 2003-01-23 at 09:12, Steve Wampler wrote:
> On Sat, 4 Jan 2003, Christopher Kings-Lynne wrote:
> > 
> > Also remember that in even well developed OS's like FreeBSD, all a
> > process's threads will execute only on one CPU.
> 
> I doubt that - it certainly isn't the case on Linux and Solaris.
> A thread may *start* execution on the same CPU as it's parent, but
> native threads are not likely to be constrained to a specific CPU
> with an SMP OS.

You are correct.  When spawning additional threads, should an idle CPU
be available, it's very doubtful that the new thread will show any bias
toward the original thread's CPU.  Most modern OS's do run each thread
within a process spread across n-CPUs.  Those that don't are probably
attempting to modernize as we speak.

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


23 Jan 2003 12:02:21 -0600
Greg Copeland <greg@CopelandConsulting.Net>
Re: Threads
Greg Copeland wrote:

>On Thu, 2003-01-23 at 09:12, Steve Wampler wrote:
>  
>
>>On Sat, 4 Jan 2003, Christopher Kings-Lynne wrote:
>>    
>>
>>>Also remember that in even well developed OS's like FreeBSD, all a
>>>process's threads will execute only on one CPU.
>>>      
>>>
>>I doubt that - it certainly isn't the case on Linux and Solaris.
>>A thread may *start* execution on the same CPU as it's parent, but
>>native threads are not likely to be constrained to a specific CPU
>>with an SMP OS.
>>    
>>
>
>You are correct.  When spawning additional threads, should an idle CPU
>be available, it's very doubtful that the new thread will show any bias
>toward the original thread's CPU.  Most modern OS's do run each thread
>within a process spread across n-CPUs.  Those that don't are probably
>attempting to modernize as we speak
>
AFAIK, FreeBSD is one of the OSes that are trying to modernize. Last I 
looked it did not have kernel threads.

>  
>






Greg Copeland wrote:

On Thu, 2003-01-23 at 09:12, Steve Wampler wrote:
  

On Sat, 4 Jan 2003, Christopher Kings-Lynne wrote:
    

Also remember that in even well developed OS's like FreeBSD, all a
process's threads will execute only on one CPU.
      

I doubt that - it certainly isn't the case on Linux and Solaris.
A thread may *start* execution on the same CPU as it's parent, but
native threads are not likely to be constrained to a specific CPU
with an SMP OS.
    


You are correct.  When spawning additional threads, should an idle CPU
be available, it's very doubtful that the new thread will show any bias
toward the original thread's CPU.  Most modern OS's do run each thread
within a process spread across n-CPUs.  Those that don't are probably
attempting to modernize as we speak

AFAIK, FreeBSD is one of the OSes that are trying to modernize. Last I looked
it did not have kernel threads.
Thu, 23 Jan 2003 14:50:03 -0500
mlw <pgsql@mohawksoft.com>
Re: Threads