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!

>
>
>