Re: Threads - Mailing list pgsql-hackers

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!

>
>  
>