path: root/doc/TODO.detail/thread

From mscott@sacadia.com Wed Nov 15 14:50:19 2000
Received: from goldengate.kojoworldwide.com. ([216.133.4.130])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id OAA11583
	for <pgman@candle.pha.pa.us>; Wed, 15 Nov 2000 14:50:13 -0500 (EST)
Received: from localhost (localhost [127.0.0.1])
	by goldengate.kojoworldwide.com. (8.9.1b+Sun/8.9.2) with ESMTP id LAA09998;
	Wed, 15 Nov 2000 11:35:33 -0800 (PST)
Date: Wed, 15 Nov 2000 11:35:33 -0800 (PST)
From: Myron Scott <mscott@sacadia.com>
X-Sender: mscott@goldengate.kojoworldwide.com.
To: "Mikheev, Vadim" <vmikheev@SECTORBASE.COM>,
        Bruce Momjian <pgman@candle.pha.pa.us>, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Please help with some advice
Message-ID: <Pine.GSO.4.10.10011151053260.9940-100000@goldengate.kojoworldwide.com.>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Status: ORr

Dear Sirs,

I have been lurking on the PostgreSQL hackers list for about 3 months now
and your names comes up more than any with helpful info about the project
so I was hoping you could help me.

Let me cut to the chase.  I have been experimenting with 7.0.2 source to
see if I could create a mutlti-threaded version of the backend so
I could link directly from java ( I have a fe<->be protocol that I use for
my apps). Needless to say I got into much more than I bargained for.  I
now have a version that works and it has some nice benefits that are very
helpful to a project that I am working on.  What I gained was

prepared statements outside of spi
batched commits (fsync)
one connection per thread
	multiple threads per process
		multiple processes per installation

I never really intended for anyone else to see the work so I drifted
pretty far from the original code.  I also ended up using Solaris threads
rather than pthreads,  I did my own implementation of the bufmgr.c and
gram.y, and used Solaris implementation of mutex in place of S_LOCK and
TAS. I grabbed all global variables and put them in an environment
variable that is thread local.  I also did some really stupid
things like making TransactionId uint64 and making all my inserts use the
same oid.

My question is this.  I would like to get some critical feedback and
suggestions about the work from others.  What is the best way to go about
this?  I thought about trying to create a project on greatbridge.org
but I am rather new to open source and the code needs commented properly
and cleaned up before too many try and look at it.

Any suggestions would be greatly appreciated.


Thanks in advance,

Myron Scott



From mscott@sacadia.com Thu Nov 16 17:19:45 2000
Received: from goldengate.kojoworldwide.com. ([216.133.4.130])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id RAA04315
	for <pgman@candle.pha.pa.us>; Thu, 16 Nov 2000 17:19:43 -0500 (EST)
Received: from localhost (localhost [127.0.0.1])
	by goldengate.kojoworldwide.com. (8.9.1b+Sun/8.9.2) with ESMTP id OAA11449;
	Thu, 16 Nov 2000 14:05:15 -0800 (PST)
Date: Thu, 16 Nov 2000 14:05:15 -0800 (PST)
From: Myron Scott <mscott@sacadia.com>
X-Sender: mscott@goldengate.kojoworldwide.com.
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: "Mikheev, Vadim" <vmikheev@SECTORBASE.COM>, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Please help with some advice
In-Reply-To: <200011160533.AAA27886@candle.pha.pa.us>
Message-ID: <Pine.GSO.4.10.10011161401570.11441-100000@goldengate.kojoworldwide.com.>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Status: OR

Bruce Momjian wrote:

>I am curious how you isolated each thread.   It seems we pretty much
>assume all our memory is controlled by a single query in the process.
                                                                              

I moved all global variables to a thread global variable which is accessed
by the method GetEnv().  Which looks like this

Env* GetEnv(void) {
	Env* env;
	thr_getspecific(*envkey,(void*)&env);
	return env;
}

The Env struct includes the CurrentMemoryContext, TopMemoryContext,
PortalHeapMemory for each instance of a connection (one thread per
connection). So, for example,
EndPortalAllocMode uses GetEnv()->CurrentMemoryContext

void
EndPortalAllocMode()
{
	PortalHeapMemory context;

	AssertState(PortalManagerEnabled);
	AssertState(IsA(GetEnv()->CurrentMemoryContext,
PortalHeapMemory));

	context = (PortalHeapMemory) GetEnv()->CurrentMemoryContext;
	AssertState(PointerIsValid(context->block));		/* XXX
Trap(...) */

	/* free current mode */
	AllocSetReset(&HEAPMEMBLOCK(context)->setData);
	MemoryContextFree((MemoryContext)
PortalHeapMemoryGetVariableMemory(context),
					  context->block);

	/* restore previous mode */
	context->block = FixedStackPop(&context->stackData);
}




From vmikheev@SECTORBASE.COM Thu Nov 16 17:23:22 2000
Received: from sectorbase2.sectorbase.com ([208.48.122.131])
	by candle.pha.pa.us (8.9.0/8.9.0) with SMTP id RAA04562
	for <pgman@candle.pha.pa.us>; Thu, 16 Nov 2000 17:23:21 -0500 (EST)
Received: by sectorbase2.sectorbase.com with Internet Mail Service (5.5.2650.21)
	id <V8XQB5RW>; Thu, 16 Nov 2000 14:05:24 -0800
Message-ID: <8F4C99C66D04D4118F580090272A7A234D318D@sectorbase1.sectorbase.com>
From: "Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
To: "'Myron Scott'" <mscott@sacadia.com>,
        Bruce Momjian
  <pgman@candle.pha.pa.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: RE: Please help with some advice
Date: Thu, 16 Nov 2000 14:09:30 -0800
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2650.21)
Content-Type: text/plain;
	charset="iso-8859-1"
Status: ORr

I think the question do we want to make backend multy-threaded
should be discussed in hackers.

Vadim

> -----Original Message-----
> From: Myron Scott [mailto:mscott@sacadia.com]
> Sent: Thursday, November 16, 2000 2:05 PM
> To: Bruce Momjian
> Cc: Mikheev, Vadim; Tom Lane
> Subject: Re: Please help with some advice
> 
> 
> Bruce Momjian wrote:
> 
> >I am curious how you isolated each thread.   It seems we pretty much
> >assume all our memory is controlled by a single query in the process.
>                                                               
>                 
> 
> I moved all global variables to a thread global variable 
> which is accessed
> by the method GetEnv().  Which looks like this
> 
> Env* GetEnv(void) {
> 	Env* env;
> 	thr_getspecific(*envkey,(void*)&env);
> 	return env;
> }
> 
> The Env struct includes the CurrentMemoryContext, TopMemoryContext,
> PortalHeapMemory for each instance of a connection (one thread per
> connection). So, for example,
> EndPortalAllocMode uses GetEnv()->CurrentMemoryContext
> 
> void
> EndPortalAllocMode()
> {
> 	PortalHeapMemory context;
> 
> 	AssertState(PortalManagerEnabled);
> 	AssertState(IsA(GetEnv()->CurrentMemoryContext,
> PortalHeapMemory));
> 
> 	context = (PortalHeapMemory) GetEnv()->CurrentMemoryContext;
> 	AssertState(PointerIsValid(context->block));		/* XXX
> Trap(...) */
> 
> 	/* free current mode */
> 	AllocSetReset(&HEAPMEMBLOCK(context)->setData);
> 	MemoryContextFree((MemoryContext)
> PortalHeapMemoryGetVariableMemory(context),
> 					  context->block);
> 
> 	/* restore previous mode */
> 	context->block = FixedStackPop(&context->stackData);
> }
> 
> 
> 

From mscott@sacadia.com Thu Nov 16 22:16:38 2000
Received: from goldengate.kojoworldwide.com. ([216.133.4.130])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id WAA14638
	for <pgman@candle.pha.pa.us>; Thu, 16 Nov 2000 22:16:36 -0500 (EST)
Received: from localhost (localhost [127.0.0.1])
	by goldengate.kojoworldwide.com. (8.9.1b+Sun/8.9.2) with ESMTP id TAA11874;
	Thu, 16 Nov 2000 19:04:48 -0800 (PST)
Date: Thu, 16 Nov 2000 19:04:48 -0800 (PST)
From: Myron Scott <mscott@sacadia.com>
X-Sender: mscott@goldengate.kojoworldwide.com.
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: "Mikheev, Vadim" <vmikheev@SECTORBASE.COM>, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Please help with some advice
In-Reply-To: <200011170156.UAA11438@candle.pha.pa.us>
Message-ID: <Pine.GSO.4.10.10011161904140.11870-100000@goldengate.kojoworldwide.com.>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Status: ORr

Thanks very much, I will post to hackers.

Myron



From pgsql-hackers-owner+M2691@postgresql.org Tue Jan  2 00:30:20 2001
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id AAA08195
	for <pgman@candle.pha.pa.us>; Tue, 2 Jan 2001 00:30:19 -0500 (EST)
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f025UjL33335;
	Tue, 2 Jan 2001 00:30:45 -0500 (EST)
	(envelope-from pgsql-hackers-owner+M2691@postgresql.org)
Received: from mailsys01.intnet.net (tmail.wwc.com [198.252.32.143] (may be forged))
	by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f025UTL33232
	for <pgsql-hackers@postgresql.org>; Tue, 2 Jan 2001 00:30:32 -0500 (EST)
	(envelope-from mscott@sacadia.com)
Received: from [206.112.108.0] (HELO sacadia.com)
  by mailsys01.intnet.net (CommuniGate Pro SMTP 3.3.2)
  with ESMTP id 2214231; Tue, 02 Jan 2001 00:29:47 -0500
Message-ID: <3A5167DB.3050807@sacadia.com>
Date: Mon, 01 Jan 2001 21:32:11 -0800
From: Myron Scott <mscott@sacadia.com>
Reply-To: mscott@sacadia.com
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US; m18) Gecko/20001108 Netscape6/6.0
X-Accept-Language: en
MIME-Version: 1.0
To: "Ross J. Reedstrom" <reedstrm@rice.edu>
CC: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Using Threads?
References: <004401c058fd$fd498d40$f2356880@tracy> <Pine.GSO.4.10.10012032351040.28161-100000@goldengate.kojoworldwide.com.> <20001204113307.B5871@rice.edu>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

For anyone interested,

I have posted my multi-threaded version of PostgreSQL here.

http://www.sacadia.com/mtpg.html

It is based on 7.0.2 and the TAO CORBA ORB which is here.

http://www.cs.wustl.edu/~schmidt/TAO.html

Myron Scott
mkscott@sacadia.com



From bright@fw.wintelcom.net Tue Jan  2 03:02:28 2001
Received: from fw.wintelcom.net (bright@ns1.wintelcom.net [209.1.153.20])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id DAA16169
	for <pgman@candle.pha.pa.us>; Tue, 2 Jan 2001 03:02:27 -0500 (EST)
Received: (from bright@localhost)
	by fw.wintelcom.net (8.10.0/8.10.0) id f0282Vm10623;
	Tue, 2 Jan 2001 00:02:31 -0800 (PST)
Date: Tue, 2 Jan 2001 00:02:31 -0800
From: Alfred Perlstein <bright@wintelcom.net>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Assuming that TAS() will succeed the first time is verboten
Message-ID: <20010102000230.C19572@fw.wintelcom.net>
References: <9850.978067943@sss.pgh.pa.us> <200101020759.CAA15836@candle.pha.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.2.5i
In-Reply-To: <200101020759.CAA15836@candle.pha.pa.us>; from pgman@candle.pha.pa.us on Tue, Jan 02, 2001 at 02:59:20AM -0500
Status: OR

* Bruce Momjian <pgman@candle.pha.pa.us> [010101 23:59] wrote:
> > Alfred Perlstein <bright@wintelcom.net> writes:
> > > One trick that may help is calling sched_yield(2) on a lock miss,
> > > it's a POSIX call and quite new so you'd need a 'configure' test
> > > for it.
> > 
> > The author of the current s_lock code seems to have thought that
> > select() with a zero delay would do the equivalent of sched_yield().
> > I'm not sure if that's true on very many kernels, if indeed any...
> > 
> > I doubt we could buy much by depending on sched_yield(); if you want
> > to assume POSIX facilities, ISTM you might as well go for user-space
> > semaphores and forget the whole TAS mechanism.
> 
> 
> Another issue is that sched_yield brings in the pthreads library/hooks
> on some OS's, which we certainly want to avoid.

I know it's a major undertaking, but since the work is sort of done,
have you guys considered the port to solaris threads and seeing about
making a pthreads port of that?

I know it would probably get you considerable gains under Windows
at the expense of dropping some really really legacy system.

Or you could do what apache (is rumored) does and have it do either
threads or processes or both...

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

From pgsql-hackers-owner+M4275@postgresql.org Mon Feb  5 21:45:00 2001
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id VAA09262
	for <pgman@candle.pha.pa.us>; Mon, 5 Feb 2001 21:44:59 -0500 (EST)
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f162ixx00920;
	Mon, 5 Feb 2001 21:44:59 -0500 (EST)
	(envelope-from pgsql-hackers-owner+M4275@postgresql.org)
Received: from goldengate.kojoworldwide.com. ([216.133.4.130])
	by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f162fSx00595
	for <pgsql-hackers@postgresql.org>; Mon, 5 Feb 2001 21:41:29 -0500 (EST)
	(envelope-from mscott@sacadia.com)
Received: from localhost (localhost [127.0.0.1])
	by goldengate.kojoworldwide.com. (8.9.1b+Sun/8.9.2) with ESMTP id SAA03298
	for <pgsql-hackers@postgresql.org>; Mon, 5 Feb 2001 18:25:05 -0800 (PST)
Date: Mon, 5 Feb 2001 18:25:05 -0800 (PST)
From: Myron Scott <mscott@sacadia.com>
X-Sender: mscott@goldengate.kojoworldwide.com.
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Using Threads?
Message-ID: <Pine.GSO.4.10.10102051823210.3289-100000@goldengate.kojoworldwide.com.>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

I have put a new version of my multi-threaded
postgresql experiment at

http://www.sacadia.com/mtpg.html

This one actually works.  I have added a server
based on omniORB, a CORBA 2.3 ORB from ATT.  It
   is much smaller than TAO and uses the thread per
connection model.  I haven't added the java side
of the JNI interface yet but the C++ side is there.

It's still not stable but it is much better than
the last.

Myron Scott
mkscott@sacadia.com





From pgsql-hackers-owner+M4304@postgresql.org Tue Feb  6 10:24:21 2001
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id KAA22027
	for <pgman@candle.pha.pa.us>; Tue, 6 Feb 2001 10:24:20 -0500 (EST)
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f16FOBx97182;
	Tue, 6 Feb 2001 10:24:11 -0500 (EST)
	(envelope-from pgsql-hackers-owner+M4304@postgresql.org)
Received: from goldengate.kojoworldwide.com. ([216.133.4.130])
	by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f16FLWx96814
	for <pgsql-hackers@postgresql.org>; Tue, 6 Feb 2001 10:21:33 -0500 (EST)
	(envelope-from mscott@sacadia.com)
Received: from localhost (localhost [127.0.0.1])
	by goldengate.kojoworldwide.com. (8.9.1b+Sun/8.9.2) with ESMTP id HAA04170;
	Tue, 6 Feb 2001 07:05:04 -0800 (PST)
Date: Tue, 6 Feb 2001 07:05:04 -0800 (PST)
From: Myron Scott <mscott@sacadia.com>
X-Sender: mscott@goldengate.kojoworldwide.com.
To: Karel Zak <zakkr@zf.jcu.cz>
cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Using Threads
In-Reply-To: <Pine.LNX.3.96.1010206101030.20355B-100000@ara.zf.jcu.cz>
Message-ID: <Pine.GSO.4.10.10102060650250.4153-100000@goldengate.kojoworldwide.com.>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR


> 
>  Sorry I haven't time to see and test your experiment,
> but I have a question. How you solve memory management?
> The current mmgr is based on global variable 
> CurrentMemoryContext that is very often changed and used.
>  Use you for this locks? If yes it is probably problematic
> point for perfomance.
> 
> 			Karel
> 

There are many many globals I had to work around including all the memory
management stuff.  I basically threw everything into and "environment"
variable which I stored in a thread specific using thr_setspecific.

Performance is acually very good for what I am doing.  I was able to batch
commit transactions which cuts down on fsync calls, use prepared
statements from my client using CORBA, and the various locking calls for
the threads (cond_wait,mutex_lock, and sema_wait) seem pretty fast.  I did
some performance tests for inserts 

20 clients, 900 inserts per client, 1 insert per transaction, 4 different
tables.

7.0.2    About    10:52 average completion
multi-threaded    2:42 average completion
7.1beta3          1:13 average completion

If I increased the number of inserts per transaction, multi-threaded got
closer to 7.1 for inserts.  I haven't tested other other types of
commands
yet.


Myron Scott
mkscott@sacadia.com


From pgsql-hackers-owner+M4313@postgresql.org Tue Feb  6 12:32:00 2001
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id MAA29163
	for <pgman@candle.pha.pa.us>; Tue, 6 Feb 2001 12:31:59 -0500 (EST)
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f16HVox17454;
	Tue, 6 Feb 2001 12:31:51 -0500 (EST)
	(envelope-from pgsql-hackers-owner+M4313@postgresql.org)
Received: from ara.zf.jcu.cz (ara.zf.jcu.cz [160.217.161.4])
	by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f16HV6x17323
	for <pgsql-hackers@postgresql.org>; Tue, 6 Feb 2001 12:31:06 -0500 (EST)
	(envelope-from zakkr@zf.jcu.cz)
Received: from localhost (zakkr@localhost)
	by ara.zf.jcu.cz (8.9.3/8.9.3/Debian 8.9.3-21) with SMTP id SAA03980;
	Tue, 6 Feb 2001 18:31:02 +0100
Date: Tue, 6 Feb 2001 18:31:02 +0100 (CET)
From: Karel Zak <zakkr@zf.jcu.cz>
To: Myron Scott <mscott@sacadia.com>
cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Using Threads
In-Reply-To: <Pine.GSO.4.10.10102060650250.4153-100000@goldengate.kojoworldwide.com.>
Message-ID: <Pine.LNX.3.96.1010206182112.3799B-100000@ara.zf.jcu.cz>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR


On Tue, 6 Feb 2001, Myron Scott wrote:

> There are many many globals I had to work around including all the memory
> management stuff.  I basically threw everything into and "environment"
> variable which I stored in a thread specific using thr_setspecific.

 Yes, it's good. I working on multi-thread application server
(http://mape.jcu.cz) and I use for this project some things from PG (like
mmgr), I planning use same solution.

> Performance is acually very good for what I am doing.  I was able to batch
> commit transactions which cuts down on fsync calls, use prepared
> statements from my client using CORBA, and the various locking calls for
> the threads (cond_wait,mutex_lock, and sema_wait) seem pretty fast.  I did
> some performance tests for inserts 
> 
> 20 clients, 900 inserts per client, 1 insert per transaction, 4 different
> tables.
> 
> 7.0.2    About    10:52 average completion
> multi-threaded    2:42 average completion
> 7.1beta3          1:13 average completion

It is very very good for time for 7.1, already look forward to 7.2! :-)  

 BTW, I not sure if you anytime in future will see threads in 
official PostgreSQL and if you spending time on relevant things (IMHO).

		Karel






From pgsql-hackers-owner+M4304@postgresql.org Tue Feb  6 10:24:21 2001
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id KAA22027
	for <pgman@candle.pha.pa.us>; Tue, 6 Feb 2001 10:24:20 -0500 (EST)
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f16FOBx97182;
	Tue, 6 Feb 2001 10:24:11 -0500 (EST)
	(envelope-from pgsql-hackers-owner+M4304@postgresql.org)
Received: from goldengate.kojoworldwide.com. ([216.133.4.130])
	by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f16FLWx96814
	for <pgsql-hackers@postgresql.org>; Tue, 6 Feb 2001 10:21:33 -0500 (EST)
	(envelope-from mscott@sacadia.com)
Received: from localhost (localhost [127.0.0.1])
	by goldengate.kojoworldwide.com. (8.9.1b+Sun/8.9.2) with ESMTP id HAA04170;
	Tue, 6 Feb 2001 07:05:04 -0800 (PST)
Date: Tue, 6 Feb 2001 07:05:04 -0800 (PST)
From: Myron Scott <mscott@sacadia.com>
X-Sender: mscott@goldengate.kojoworldwide.com.
To: Karel Zak <zakkr@zf.jcu.cz>
cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Using Threads
In-Reply-To: <Pine.LNX.3.96.1010206101030.20355B-100000@ara.zf.jcu.cz>
Message-ID: <Pine.GSO.4.10.10102060650250.4153-100000@goldengate.kojoworldwide.com.>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR


> 
>  Sorry I haven't time to see and test your experiment,
> but I have a question. How you solve memory management?
> The current mmgr is based on global variable 
> CurrentMemoryContext that is very often changed and used.
>  Use you for this locks? If yes it is probably problematic
> point for perfomance.
> 
> 			Karel
> 

There are many many globals I had to work around including all the memory
management stuff.  I basically threw everything into and "environment"
variable which I stored in a thread specific using thr_setspecific.

Performance is acually very good for what I am doing.  I was able to batch
commit transactions which cuts down on fsync calls, use prepared
statements from my client using CORBA, and the various locking calls for
the threads (cond_wait,mutex_lock, and sema_wait) seem pretty fast.  I did
some performance tests for inserts 

20 clients, 900 inserts per client, 1 insert per transaction, 4 different
tables.

7.0.2    About    10:52 average completion
multi-threaded    2:42 average completion
7.1beta3          1:13 average completion

If I increased the number of inserts per transaction, multi-threaded got
closer to 7.1 for inserts.  I haven't tested other other types of
commands
yet.


Myron Scott
mkscott@sacadia.com


From lamar.owen@wgcr.org Thu Jun 28 11:14:10 2001
Return-path: <lamar.owen@wgcr.org>
Received: from www.wgcr.org (IDENT:root@www.wgcr.org [206.74.232.194])
	by candle.pha.pa.us (8.10.1/8.10.1) with ESMTP id f5SFE9U18758
	for <pgman@candle.pha.pa.us>; Thu, 28 Jun 2001 11:14:09 -0400 (EDT)
Received: from lowen.wgcr.org (IDENT:lowen@[10.1.2.3])
	by www.wgcr.org (8.9.3/8.9.3/WGCR) with SMTP id LAA11879;
	Thu, 28 Jun 2001 11:14:14 -0400
Content-Type: text/plain;
  charset="iso-8859-1"
From: Lamar Owen <lamar.owen@wgcr.org>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Subject: Process weight (was:Re: [GENERAL] Re: Red Hat to support PostgreSQL)
Date: Thu, 28 Jun 2001 11:14:09 -0400
X-Mailer: KMail [version 1.2]
References: <200106272258.f5RMwIb26959@candle.pha.pa.us>
In-Reply-To: <200106272258.f5RMwIb26959@candle.pha.pa.us>
MIME-Version: 1.0
Message-ID: <01062811140902.01118@lowen.wgcr.org>
Content-Transfer-Encoding: 8bit
Status: ORr

On Wednesday 27 June 2001 18:58, Bruce Momjian wrote:
> > I had almost given up on using Postgres for this system because under
> > Solaris, it just couldn't cut it (MySQL could do the work with one CPU
> > while Postgres took up even more CPU and required *both* CPUs to be
> > enabled), but when we moved the system to a Linux box, things worked
> > much better.

> Ah, back to a PostgreSQL topic.  :-)

> My guess on this one is that Solaris is slower for PostgreSQL because
> process switching is _much_ heavier on Solaris than other OS's.  This is
> because of the way they implemented processes in SVr4.  They got quite
> heavy, almost requiring kernel threads so you weren't switching
> processes all the time.

Now, the question of the week:
Is supporting a thread model for an inefficient OS a desirable thing to do, 
when more efficient OS kernels are available such as FreeBSD 4.x and Linux 
2.4?  My opinion is that our existing model, when used with a 
connection-pooling frontend, is rather efficient.  (Yes, I use a 
connection-pooling frontend.  Performance is rather nice, and I don't have to 
have a full backend spawned for every page hit.)

In fact, on a Linux box threads show as processes.  While I know that the 
kernel actually supports themin a slightly different manner than processes, 
they have more similarities than differences.

However, even on OS's where threads are supported, the mechanism to support 
those threads must be an efficient one -- not all pthreads libraries are 
created equal.  Many are frontends (expensive ones, at that) for plain old 
processes.

Does anyone know of a resource that details the 'weight' of processes for our 
supported platforms?  [reply off-list -- I'll be glad to summarize responses 
to HACKERS, ADMIN, or PORTS, as appropriate, if desired.]
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

From pgsql-hackers-owner+M13599=candle.pha.pa.us=pgman@postgresql.org Wed Sep 26 17:25:32 2001
Return-path: <pgsql-hackers-owner+M13599=candle.pha.pa.us=pgman@postgresql.org>
Received: from server1.pgsql.org (server1.pgsql.org [64.39.15.238] (may be forged))
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id f8QLPWo07589
	for <pgman@candle.pha.pa.us>; Wed, 26 Sep 2001 17:25:32 -0400 (EDT)
Received: from postgresql.org (webmail.postgresql.org [216.126.85.28])
	by server1.pgsql.org (8.11.6/8.11.6) with ESMTP id f8QLPf405606
	for <pgman@candle.pha.pa.us>; Wed, 26 Sep 2001 16:25:41 -0500 (CDT)
	(envelope-from pgsql-hackers-owner+M13599=candle.pha.pa.us=pgman@postgresql.org)
Received: from gromit.dotclick.com (ipn9-f8366.net-resource.net [216.204.83.66])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id f8QKj3h82020
	for <pgsql-hackers@postgresql.org>; Wed, 26 Sep 2001 16:45:03 -0400 (EDT)
	(envelope-from markw@mohawksoft.com)
Received: from mohawksoft.com (IDENT:markw@localhost.localdomain [127.0.0.1])
	by gromit.dotclick.com (8.9.3/8.9.3) with ESMTP id QAA23693;
	Wed, 26 Sep 2001 16:43:02 -0400
Message-ID: <3BB23DD6.E86AF327@mohawksoft.com>
Date: Wed, 26 Sep 2001 16:43:02 -0400
From: mlw <markw@mohawksoft.com>
X-Mailer: Mozilla 4.75 [en] (X11; U; Linux 2.4.2 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: "D. Hageman" <dhageman@dracken.com>,
   "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Spinlock performance improvement proposal
References: <Pine.LNX.4.33.0109261330030.1906-100000@typhon.dracken.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

"D. Hageman" wrote:

> The plan for the new spinlocks does look like it has some potential.  My
> only comment in regards to permformance when we start looking at SMP
> machines is ... it is my belief that getting a true threaded backend may
> be the only way to get the full potential out of SMP machines.  I see that
> is one of the things to experiment with on the TODO list and I have seen
> some people have messed around already with this using Solaris threads.
> It should probably be attempted with pthreads if PostgreSQL is going to
> keep some resemblance of cross-platform compatibility.  At that time, it
> would probably be easier to go in and clean up some stuff for the
> implementation of other TODO items (put in the base framework for more
> complex future items) as threading the backend would take a little bit of
> ideology shift.

I can only think of two objectives for threading. (1) running the various
connections in their own thread instead of their own process. (2) running
complex queries across multiple threads.

For  item (1) I see no value to this. It is a lot of work with no tangible
benefit. If you have an old fashion pthreads implementation, it will hurt
performance because are scheduled within the single process's time slice.. If
you have a newer kernel scheduled implementation, then you will have the same
scheduling as separate processes. The only thing you will need to do is
switch your brain from figuring out how to share data, to trying to figure
out how to isolate data. A multithreaded implementation lacks many of the
benefits and robustness of a multiprocess implementation.

For item (2) I can see how that could speed up queries in a low utilization
system, and that would be cool, but in a server that is under load, threading
the queries probably be less efficient.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

From pgsql-hackers-owner+M13604=candle.pha.pa.us=pgman@postgresql.org Wed Sep 26 18:40:26 2001
Return-path: <pgsql-hackers-owner+M13604=candle.pha.pa.us=pgman@postgresql.org>
Received: from server1.pgsql.org (server1.pgsql.org [64.39.15.238] (may be forged))
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id f8QMePo13437
	for <pgman@candle.pha.pa.us>; Wed, 26 Sep 2001 18:40:25 -0400 (EDT)
Received: from postgresql.org (webmail.postgresql.org [216.126.85.28])
	by server1.pgsql.org (8.11.6/8.11.6) with ESMTP id f8QMeZ417944
	for <pgman@candle.pha.pa.us>; Wed, 26 Sep 2001 17:40:35 -0500 (CDT)
	(envelope-from pgsql-hackers-owner+M13604=candle.pha.pa.us=pgman@postgresql.org)
Received: from foghorn.airs.com (foghorn.airs.com [63.201.54.26])
	by postgresql.org (8.11.3/8.11.4) with SMTP id f8QM59h01247
	for <pgsql-hackers@postgresql.org>; Wed, 26 Sep 2001 18:05:09 -0400 (EDT)
	(envelope-from ian@airs.com)
Received: (qmail 10089 invoked by uid 10); 26 Sep 2001 22:04:49 -0000
Received: (qmail 6837 invoked by uid 269); 26 Sep 2001 22:04:41 -0000
Mail-Followup-To: markw@mohawksoft.com,
  pgsql-hackers@postgresql.org,
  dhageman@dracken.com
To: "D. Hageman" <dhageman@dracken.com>
cc: mlw <markw@mohawksoft.com>,
   "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Spinlock performance improvement proposal
References: <Pine.LNX.4.33.0109261600100.1784-100000@typhon.dracken.com>
From: Ian Lance Taylor <ian@airs.com>
Date: 26 Sep 2001 15:04:41 -0700
In-Reply-To: <Pine.LNX.4.33.0109261600100.1784-100000@typhon.dracken.com>
Message-ID: <si8zf1vcau.fsf@daffy.airs.com>
Lines: 45
User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/20.7
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

"D. Hageman" <dhageman@dracken.com> writes:

> > you have a newer kernel scheduled implementation, then you will have the same
> > scheduling as separate processes. The only thing you will need to do is
> > switch your brain from figuring out how to share data, to trying to figure
> > out how to isolate data. A multithreaded implementation lacks many of the
> > benefits and robustness of a multiprocess implementation.
> 
> Save for the fact that the kernel can switch between threads faster then 
> it can switch processes considering threads share the same address space, 
> stack, code, etc.  If need be sharing the data between threads is much 
> easier then sharing between processes. 

When using a kernel threading model, it's not obvious to me that the
kernel will switch between threads much faster than it will switch
between processes.  As far as I can see, the only potential savings is
not reloading the pointers to the page tables.  That is not nothing,
but it is also not a lot.

> I can't comment on the "isolate data" line.  I am still trying to figure 
> that one out.

Sometimes you need data which is specific to a particular thread.
Basically, you have to look at every global variable in the Postgres
backend, and determine whether to share it among all threads or to
make it thread-specific.  In other words, you have to take extra steps
to isolate the data within the thread.  This is the reverse of the
current situation, in which you have to take extra steps to share data
among all backend processes.

> That last line is a troll if I every saw it ;-)  I will agree that threads 
> isn't for everything and that it has costs just like everything else.  Let 
> me stress that last part - like everything else.  Certain costs exist in 
> the present model, nothing is - how should we say ... perfect.

When writing in C, threading inevitably loses robustness.  Erratic
behaviour by one thread, perhaps in a user defined function, can
subtly corrupt the entire system, rather than just that thread.  Part
of defensive programming is building barriers between different parts
of a system.  Process boundaries are a powerful barrier.

(Actually, though, Postgres is already vulnerable to erratic behaviour
because any backend process can corrupt the shared buffer pool.)

Ian

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

From pgsql-hackers-owner+M13605=candle.pha.pa.us=pgman@postgresql.org Wed Sep 26 18:54:58 2001
Return-path: <pgsql-hackers-owner+M13605=candle.pha.pa.us=pgman@postgresql.org>
Received: from server1.pgsql.org (server1.pgsql.org [64.39.15.238] (may be forged))
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id f8QMsvo14061
	for <pgman@candle.pha.pa.us>; Wed, 26 Sep 2001 18:54:57 -0400 (EDT)
Received: from postgresql.org (webmail.postgresql.org [216.126.85.28])
	by server1.pgsql.org (8.11.6/8.11.6) with ESMTP id f8QMt7420740
	for <pgman@candle.pha.pa.us>; Wed, 26 Sep 2001 17:55:07 -0500 (CDT)
	(envelope-from pgsql-hackers-owner+M13605=candle.pha.pa.us=pgman@postgresql.org)
Received: from goldengate.kojoworldwide.com. ([216.133.4.130])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id f8QMOPh04333
	for <pgsql-hackers@postgresql.org>; Wed, 26 Sep 2001 18:24:26 -0400 (EDT)
	(envelope-from mscott@sacadia.com)
Received: from localhost (localhost [127.0.0.1])
	by goldengate.kojoworldwide.com. (8.9.1b+Sun/8.9.2) with ESMTP id PAA00633
	for <pgsql-hackers@postgresql.org>; Wed, 26 Sep 2001 15:03:00 -0700 (PDT)
Date: Wed, 26 Sep 2001 15:03:00 -0700 (PDT)
From: Myron Scott <mscott@sacadia.com>
X-Sender: mscott@goldengate.kojoworldwide.com.
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Spinlock performance improvement proposal
In-Reply-To: <3BB23DD6.E86AF327@mohawksoft.com>
Message-ID: <Pine.GSO.4.10.10109261428340.563-100000@goldengate.kojoworldwide.com.>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR



On Wed, 26 Sep 2001, mlw wrote:

> I can only think of two objectives for threading. (1) running the various
> connections in their own thread instead of their own process. (2) running
> complex queries across multiple threads.
> 

I did a multi-threaded version of 7.0.2 using Solaris threads about a year
ago in order to try
and get multiple backend connections working under one java process using
jni.  I used the thread per connection model.

I eventually got it working, but it was/is very messy ( there were global
variables everywhere! ).  Anyway, I was able to get a pretty good speed up
on inserts by scheduling buffer writes from multiple connections on one
common writing thread.  

I also got some other features that were important to me at the time.

1.  True prepared statements under java with bound input and output
variables
2.  Better system utilization 
	a.  fewer Solaris lightweight processes mapped to threads.
	b.  Fewer open files per postgres installation 
3.  Automatic vacuums when system activity is low by a daemon thread.

but there were some drawbacks...  One rogue thread or bad user 
function could take down all connections for that process.  This
was and seems to still be the major drawback to using threads.


Myron Scott
mscott@sacadia.com


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

From pgsql-hackers-owner+M13602=candle.pha.pa.us=pgman@postgresql.org Wed Sep 26 17:45:26 2001
Return-path: <pgsql-hackers-owner+M13602=candle.pha.pa.us=pgman@postgresql.org>
Received: from server1.pgsql.org (server1.pgsql.org [64.39.15.238] (may be forged))
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id f8QLjQo08483
	for <pgman@candle.pha.pa.us>; Wed, 26 Sep 2001 17:45:26 -0400 (EDT)
Received: from postgresql.org (webmail.postgresql.org [216.126.85.28])
	by server1.pgsql.org (8.11.6/8.11.6) with ESMTP id f8QLjY409914
	for <pgman@candle.pha.pa.us>; Wed, 26 Sep 2001 16:45:35 -0500 (CDT)
	(envelope-from pgsql-hackers-owner+M13602=candle.pha.pa.us=pgman@postgresql.org)
Received: from typhon.dracken.com (dv07m61.lawrence.ks.us [24.124.61.35])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id f8QLGDh91021
	for <pgsql-hackers@postgresql.org>; Wed, 26 Sep 2001 17:16:13 -0400 (EDT)
	(envelope-from dhageman@dracken.com)
Received: from localhost (dhageman@localhost)
	by typhon.dracken.com (8.11.4/8.11.4) with ESMTP id f8QLEMY01973;
	Wed, 26 Sep 2001 16:14:22 -0500
X-Authentication-Warning: typhon.dracken.com: dhageman owned process doing -bs
Date: Wed, 26 Sep 2001 16:14:22 -0500 (CDT)
From: "D. Hageman" <dhageman@dracken.com>
To: mlw <markw@mohawksoft.com>
cc: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Spinlock performance improvement proposal
In-Reply-To: <3BB23DD6.E86AF327@mohawksoft.com>
Message-ID: <Pine.LNX.4.33.0109261600100.1784-100000@typhon.dracken.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: ORr

On Wed, 26 Sep 2001, mlw wrote:
> 
> I can only think of two objectives for threading. (1) running the various
> connections in their own thread instead of their own process. (2) running
> complex queries across multiple threads.
> 
> For  item (1) I see no value to this. It is a lot of work with no tangible
> benefit. If you have an old fashion pthreads implementation, it will hurt
> performance because are scheduled within the single process's time slice..

Old fashion ... as in a userland library that implements POSIX threads?  
Well, I would agree.  However, most *modern* implementations are done in 
the kernel or kernel and userland coop model and don't have this 
limitation (as you mention later in your e-mail).  You have kinda hit on 
one of my gripes about computers in general.  At what point in time does 
one say something is obsolete or too old to support anymore - that it 
hinders progress instead of adding a "feature"?

> you have a newer kernel scheduled implementation, then you will have the same
> scheduling as separate processes. The only thing you will need to do is
> switch your brain from figuring out how to share data, to trying to figure
> out how to isolate data. A multithreaded implementation lacks many of the
> benefits and robustness of a multiprocess implementation.

Save for the fact that the kernel can switch between threads faster then 
it can switch processes considering threads share the same address space, 
stack, code, etc.  If need be sharing the data between threads is much 
easier then sharing between processes. 

I can't comment on the "isolate data" line.  I am still trying to figure 
that one out.

That last line is a troll if I every saw it ;-)  I will agree that threads 
isn't for everything and that it has costs just like everything else.  Let 
me stress that last part - like everything else.  Certain costs exist in 
the present model, nothing is - how should we say ... perfect.

> For item (2) I can see how that could speed up queries in a low utilization
> system, and that would be cool, but in a server that is under load, threading
> the queries probably be less efficient.

Well, I don't follow your logic and you didn't give any substance to back 
up your claim.  I am willing to listen.

Another thought ... Oracle uses threads doesn't it or at least it has a 
single processor and multi-processor version last time I knew ... which do 
they claim is better?  (Not saying that Oracle's proclimation of what is 
good and what is not matters, but it is good for another view point).

-- 
//========================================================\\
||  D. Hageman                    <dhageman@dracken.com>  ||
\\========================================================//


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

From pgsql-hackers-owner+M13607=candle.pha.pa.us=pgman@postgresql.org Wed Sep 26 19:14:59 2001
Return-path: <pgsql-hackers-owner+M13607=candle.pha.pa.us=pgman@postgresql.org>
Received: from server1.pgsql.org (server1.pgsql.org [64.39.15.238] (may be forged))
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id f8QNExo15536
	for <pgman@candle.pha.pa.us>; Wed, 26 Sep 2001 19:14:59 -0400 (EDT)
Received: from postgresql.org (webmail.postgresql.org [216.126.85.28])
	by server1.pgsql.org (8.11.6/8.11.6) with ESMTP id f8QNF8423944
	for <pgman@candle.pha.pa.us>; Wed, 26 Sep 2001 18:15:09 -0500 (CDT)
	(envelope-from pgsql-hackers-owner+M13607=candle.pha.pa.us=pgman@postgresql.org)
Received: from belphigor.mcnaught.org ([216.151.155.121])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id f8QMe3h07256
	for <pgsql-hackers@postgresql.org>; Wed, 26 Sep 2001 18:40:04 -0400 (EDT)
	(envelope-from doug@wireboard.com)
Received: (from doug@localhost)
	by belphigor.mcnaught.org (8.11.6/8.9.3) id f8QMdkB05502;
	Wed, 26 Sep 2001 18:39:46 -0400
X-Authentication-Warning: belphigor.mcnaught.org: doug set sender to doug@wireboard.com using -f
To: "D. Hageman" <dhageman@dracken.com>
cc: mlw <markw@mohawksoft.com>,
   "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Spinlock performance improvement proposal
References: <Pine.LNX.4.33.0109261600100.1784-100000@typhon.dracken.com>
From: Doug McNaught <doug@wireboard.com>
Date: 26 Sep 2001 18:39:44 -0400
In-Reply-To: "D. Hageman"'s message of "Wed, 26 Sep 2001 16:14:22 -0500 (CDT)"
Message-ID: <m3y9n11sr3.fsf@belphigor.mcnaught.org>
Lines: 26
User-Agent: Gnus/5.0806 (Gnus v5.8.6) XEmacs/21.1 (20 Minutes to Nikko)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

"D. Hageman" <dhageman@dracken.com> writes:

> Save for the fact that the kernel can switch between threads faster then 
> it can switch processes considering threads share the same address space, 
> stack, code, etc.  If need be sharing the data between threads is much 
> easier then sharing between processes. 

This depends on your system.  Solaris has a huge difference between
thread and process context switch times, whereas Linux has very little 
difference (and in fact a Linux process context switch is about as
fast as a Solaris thread switch on the same hardware--Solaris is just
a pig when it comes to process context switching). 

> I can't comment on the "isolate data" line.  I am still trying to figure 
> that one out.

I think his point is one of clarity and maintainability.  When a
task's data is explicitly shared (via shared memory of some sort) it's
fairly clear when you're accessing shared data and need to worry about
locking.  Whereas when all data is shared by default (as with threads)
it's very easy to miss places where threads can step on each other.

-Doug
-- 
In a world of steel-eyed death, and men who are fighting to be warm,
Come in, she said, I'll give you shelter from the storm.    -Dylan

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From pgsql-hackers-owner+M13611=candle.pha.pa.us=pgman@postgresql.org Wed Sep 26 21:05:02 2001
Return-path: <pgsql-hackers-owner+M13611=candle.pha.pa.us=pgman@postgresql.org>
Received: from server1.pgsql.org (server1.pgsql.org [64.39.15.238] (may be forged))
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id f8R152o22010
	for <pgman@candle.pha.pa.us>; Wed, 26 Sep 2001 21:05:02 -0400 (EDT)
Received: from postgresql.org (webmail.postgresql.org [216.126.85.28])
	by server1.pgsql.org (8.11.6/8.11.6) with ESMTP id f8R158430261
	for <pgman@candle.pha.pa.us>; Wed, 26 Sep 2001 20:05:08 -0500 (CDT)
	(envelope-from pgsql-hackers-owner+M13611=candle.pha.pa.us=pgman@postgresql.org)
Received: from sss.pgh.pa.us ([192.204.191.242])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id f8R0lgh29430
	for <pgsql-hackers@postgresql.org>; Wed, 26 Sep 2001 20:47:42 -0400 (EDT)
	(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f8R0kpK14707;
	Wed, 26 Sep 2001 20:46:51 -0400 (EDT)
To: Ian Lance Taylor <ian@airs.com>
cc: "D. Hageman" <dhageman@dracken.com>, mlw <markw@mohawksoft.com>,
   "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Spinlock performance improvement proposal 
In-Reply-To: <si8zf1vcau.fsf@daffy.airs.com> 
References: <Pine.LNX.4.33.0109261600100.1784-100000@typhon.dracken.com> <si8zf1vcau.fsf@daffy.airs.com>
Comments: In-reply-to Ian Lance Taylor <ian@airs.com>
	message dated "26 Sep 2001 15:04:41 -0700"
Date: Wed, 26 Sep 2001 20:46:51 -0400
Message-ID: <14704.1001551611@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

Ian Lance Taylor <ian@airs.com> writes:
> (Actually, though, Postgres is already vulnerable to erratic behaviour
> because any backend process can corrupt the shared buffer pool.)

Not to mention the other parts of shared memory.

Nonetheless, our experience has been that cross-backend failures due to
memory clobbers in shared memory are very infrequent --- certainly far
less often than we see localized-to-a-backend crashes.  Probably this is
because the shared memory is (a) small compared to the rest of the
address space and (b) only accessed by certain specific modules within
Postgres.

I'm convinced that switching to a thread model would result in a
significant degradation in our ability to recover from coredump-type
failures, even given the (implausible) assumption that we introduce no
new bugs during the conversion.  I'm also *un*convinced that such a
conversion will yield significant performance benefits, unless we
introduce additional cross-thread dependencies (and more fragility
and lock contention) by tactics such as sharing catalog caches across
threads.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

From pgsql-hackers-owner+M13616=candle.pha.pa.us=pgman@postgresql.org Wed Sep 26 23:10:52 2001
Return-path: <pgsql-hackers-owner+M13616=candle.pha.pa.us=pgman@postgresql.org>
Received: from server1.pgsql.org (server1.pgsql.org [64.39.15.238] (may be forged))
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id f8R3Aqo03180
	for <pgman@candle.pha.pa.us>; Wed, 26 Sep 2001 23:10:52 -0400 (EDT)
Received: from postgresql.org (webmail.postgresql.org [216.126.85.28])
	by server1.pgsql.org (8.11.6/8.11.6) with ESMTP id f8R3B3438816
	for <pgman@candle.pha.pa.us>; Wed, 26 Sep 2001 22:11:03 -0500 (CDT)
	(envelope-from pgsql-hackers-owner+M13616=candle.pha.pa.us=pgman@postgresql.org)
Received: from spider.pilosoft.com (p55-222.acedsl.com [160.79.55.222])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id f8R2vCh48923
	for <pgsql-hackers@postgresql.org>; Wed, 26 Sep 2001 22:57:12 -0400 (EDT)
	(envelope-from alex@pilosoft.com)
Received: from localhost (alexmail@localhost)
	by spider.pilosoft.com (8.9.3/8.9.3) with ESMTP id WAA27630;
	Wed, 26 Sep 2001 22:58:41 -0400 (EDT)
Date: Wed, 26 Sep 2001 22:58:41 -0400 (EDT)
From: Alex Pilosov <alex@pilosoft.com>
To: "D. Hageman" <dhageman@dracken.com>
cc: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Spinlock performance improvement proposal
In-Reply-To: <Pine.LNX.4.33.0109261733050.2225-100000@typhon.dracken.com>
Message-ID: <Pine.BSO.4.10.10109262249480.14740-100000@spider.pilosoft.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

On Wed, 26 Sep 2001, D. Hageman wrote:

> > > Save for the fact that the kernel can switch between threads faster then 
> > > it can switch processes considering threads share the same address space, 
> > > stack, code, etc.  If need be sharing the data between threads is much 
> > > easier then sharing between processes. 
> > 
> > When using a kernel threading model, it's not obvious to me that the
> > kernel will switch between threads much faster than it will switch
> > between processes.  As far as I can see, the only potential savings is
> > not reloading the pointers to the page tables.  That is not nothing,
> > but it is also
<major snippage>
> > > I can't comment on the "isolate data" line.  I am still trying to figure 
> > > that one out.
> > 
> > Sometimes you need data which is specific to a particular thread.
> 
> When you need data that is specific to a thread you use a TSD (Thread 
> Specific Data).  
Which Linux does not support with a vengeance, to my knowledge.

As a matter of fact, quote from Linus on the matter was something like
"Solution to slow process switching is fast process switching, not another
kernel abstraction [referring to threads and TSD]". TSDs make
implementation of thread switching complex, and fork() complex.

The question about threads boils down to: Is there far more data that is
shared than unshared? If yes, threads are better, if not, you'll be
abusing TSD and slowing things down. 

I believe right now, postgresql' model of sharing only things that need to
be shared is pretty damn good. The only slight problem is overhead of
forking another backend, but its still _fast_.

IMHO, threads would not bring large improvement to postgresql.

 Actually, if I remember, there was someone who ported postgresql (I think
it was 6.5) to be multithreaded with major pain, because the requirement
was to integrate with CORBA. I believe that person posted some benchmarks
which were essentially identical to non-threaded postgres...

-alex


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

From pgsql-hackers-owner+M13619=candle.pha.pa.us=pgman@postgresql.org Thu Sep 27 00:32:55 2001
Return-path: <pgsql-hackers-owner+M13619=candle.pha.pa.us=pgman@postgresql.org>
Received: from server1.pgsql.org (server1.pgsql.org [64.39.15.238] (may be forged))
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id f8R4Wto07075
	for <pgman@candle.pha.pa.us>; Thu, 27 Sep 2001 00:32:55 -0400 (EDT)
Received: from postgresql.org (webmail.postgresql.org [216.126.85.28])
	by server1.pgsql.org (8.11.6/8.11.6) with ESMTP id f8R4X7444942
	for <pgman@candle.pha.pa.us>; Wed, 26 Sep 2001 23:33:07 -0500 (CDT)
	(envelope-from pgsql-hackers-owner+M13619=candle.pha.pa.us=pgman@postgresql.org)
Received: from sss.pgh.pa.us ([192.204.191.242])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id f8R4Jsh61257
	for <pgsql-hackers@postgresql.org>; Thu, 27 Sep 2001 00:19:54 -0400 (EDT)
	(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f8R4JLK15406;
	Thu, 27 Sep 2001 00:19:21 -0400 (EDT)
To: "D. Hageman" <dhageman@dracken.com>
cc: Alex Pilosov <alex@pilosoft.com>,
   "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Spinlock performance improvement proposal 
In-Reply-To: <Pine.LNX.4.33.0109262224040.1173-100000@typhon.dracken.com> 
References: <Pine.LNX.4.33.0109262224040.1173-100000@typhon.dracken.com>
Comments: In-reply-to "D. Hageman" <dhageman@dracken.com>
	message dated "Wed, 26 Sep 2001 22:41:39 -0500"
Date: Thu, 27 Sep 2001 00:19:20 -0400
Message-ID: <15403.1001564360@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

"D. Hageman" <dhageman@dracken.com> writes:
> If you look at Myron Scott's post today you will see that it had other 
> advantages going for it (like auto-vacuum!) and disadvantages ... rogue 
> thread corruption (already debated today).

But note that Myron did a number of things that are (IMHO) orthogonal
to process-to-thread conversion, such as adding prepared statements,
a separate thread/process/whateveryoucallit for buffer writing, ditto
for vacuuming, etc.  I think his results cannot be taken as indicative
of the benefits of threads per se --- these other things could be
implemented in a pure process model too, and we have no data with which
to estimate which change bought how much.

Threading certainly should reduce the context switch time, but this
comes at the price of increased overhead within each context (since
access to thread-local variables is not free).  It's by no means
obvious that there's a net win there.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

From pgsql-hackers-owner+M13621=candle.pha.pa.us=pgman@postgresql.org Thu Sep 27 01:59:44 2001
Return-path: <pgsql-hackers-owner+M13621=candle.pha.pa.us=pgman@postgresql.org>
Received: from server1.pgsql.org (server1.pgsql.org [64.39.15.238] (may be forged))
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id f8R5xio11898
	for <pgman@candle.pha.pa.us>; Thu, 27 Sep 2001 01:59:44 -0400 (EDT)
Received: from postgresql.org (webmail.postgresql.org [216.126.85.28])
	by server1.pgsql.org (8.11.6/8.11.6) with ESMTP id f8R5xi449748
	for <pgman@candle.pha.pa.us>; Thu, 27 Sep 2001 00:59:45 -0500 (CDT)
	(envelope-from pgsql-hackers-owner+M13621=candle.pha.pa.us=pgman@postgresql.org)
Received: from goldengate.kojoworldwide.com. ([216.133.4.130])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id f8R5joh75612
	for <pgsql-hackers@postgresql.org>; Thu, 27 Sep 2001 01:45:50 -0400 (EDT)
	(envelope-from mscott@sacadia.com)
Received: from localhost (localhost [127.0.0.1])
	by goldengate.kojoworldwide.com. (8.9.1b+Sun/8.9.2) with ESMTP id WAA01144
	for <pgsql-hackers@postgresql.org>; Wed, 26 Sep 2001 22:24:29 -0700 (PDT)
Date: Wed, 26 Sep 2001 22:24:29 -0700 (PDT)
From: Myron Scott <mscott@sacadia.com>
X-Sender: mscott@goldengate.kojoworldwide.com.
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Spinlock performance improvement proposal 
In-Reply-To: <15403.1001564360@sss.pgh.pa.us>
Message-ID: <Pine.GSO.4.10.10109262146500.1111-100000@goldengate.kojoworldwide.com.>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR


 
> But note that Myron did a number of things that are (IMHO) orthogonal

yes, I did :)

> to process-to-thread conversion, such as adding prepared statements,
> a separate thread/process/whateveryoucallit for buffer writing, ditto
> for vacuuming, etc.  I think his results cannot be taken as indicative
> of the benefits of threads per se --- these other things could be
> implemented in a pure process model too, and we have no data with which
> to estimate which change bought how much.
> 

If you are comparing just process vs. thread, I really don't think I
gained much for performance and ended up with some pretty unmanageable
code.

The one thing that led to most of the gains was scheduling all the writes
to one thread which, as noted by Tom,  you could do on the process model.
Besides, Most of the advantage in doing this was taken away with the
addition of WAL in 7.1.

The other real gain that I saw with threading was limiting the number of
open files but
that led me to alter much of the file manager in order to synchronize
access to the files which probably slowed things a bit.

To be honest, I don't think I, personally,
would try this again. I went pretty far off
the beaten path with this thing.  It works well for what I am doing 
( a limited number of SQL statements run many times over ) but there
probably was a better way.  I'm thinking now that I should have tried to 
add a CORBA interface for connections. I would have been able to 
accomplish my original goals without creating a deadend for myself.


Thanks all for a great project,

Myron
mscott@sacadia.com


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From pgsql-hackers-owner+M13632=candle.pha.pa.us=pgman@postgresql.org Thu Sep 27 10:21:22 2001
Return-path: <pgsql-hackers-owner+M13632=candle.pha.pa.us=pgman@postgresql.org>
Received: from server1.pgsql.org (server1.pgsql.org [64.39.15.238] (may be forged))
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id f8RELLo08607
	for <pgman@candle.pha.pa.us>; Thu, 27 Sep 2001 10:21:21 -0400 (EDT)
Received: from postgresql.org (webmail.postgresql.org [216.126.85.28])
	by server1.pgsql.org (8.11.6/8.11.6) with ESMTP id f8RELP487000
	for <pgman@candle.pha.pa.us>; Thu, 27 Sep 2001 09:21:26 -0500 (CDT)
	(envelope-from pgsql-hackers-owner+M13632=candle.pha.pa.us=pgman@postgresql.org)
Received: from gromit.dotclick.com (ipn9-f8366.net-resource.net [216.204.83.66])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id f8RE49h21870
	for <pgsql-hackers@postgresql.org>; Thu, 27 Sep 2001 10:04:09 -0400 (EDT)
	(envelope-from markw@mohawksoft.com)
Received: from mohawksoft.com (IDENT:markw@localhost.localdomain [127.0.0.1])
	by gromit.dotclick.com (8.9.3/8.9.3) with ESMTP id KAA24417;
	Thu, 27 Sep 2001 10:02:06 -0400
Message-ID: <3BB3315D.EC99FF65@mohawksoft.com>
Date: Thu, 27 Sep 2001 10:02:05 -0400
From: mlw <markw@mohawksoft.com>
X-Mailer: Mozilla 4.75 [en] (X11; U; Linux 2.4.2 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: "D. Hageman" <dhageman@dracken.com>
cc: Ian Lance Taylor <ian@airs.com>,
   "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Spinlock performance improvement proposal
References: <Pine.LNX.4.33.0109261733050.2225-100000@typhon.dracken.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

"D. Hageman" wrote:

> On 26 Sep 2001, Ian Lance Taylor wrote:
> >
> > > Save for the fact that the kernel can switch between threads faster then
> > > it can switch processes considering threads share the same address space,
> > > stack, code, etc.  If need be sharing the data between threads is much
> > > easier then sharing between processes.
> >
> > When using a kernel threading model, it's not obvious to me that the
> > kernel will switch between threads much faster than it will switch
> > between processes.  As far as I can see, the only potential savings is
> > not reloading the pointers to the page tables.  That is not nothing,
> > but it is also not a lot.
>
> It is my understanding that avoiding a full context switch of the
> processor can be of a significant advantage.  This is especially important
> on processor architectures that can be kinda slow at doing it (x86). I
> will admit that most modern kernels have features that assist software
> packages utilizing the forking model (copy on write for instance).  It is
> also my impression that these do a good job.  I am the kind of guy that
> looks towards the future (as in a year, year and half or so) and say that
> processors will hopefully get faster at context switching and more and
> more kernels will implement these algorithms to speed up the forking
> model.  At the same time, I see more and more processors being shoved into
> a single box and it appears that the threads model works better on these
> type of systems.

"context" switching happens all the time on a multitasking system. On the x86
processor, a context switch happens when you call into the kernel. You have to go
through a call-gate to get to a lower privilege ring. "context" switching is very
fast. The operating system dictates how heavy or light a process switch is. Under
Linux (and I believe FreeBSD with Linux threads, or version 4.x ) threads and
processes are virtually identical. The only difference is that the virtual memory
pages are not "copy on write." Process vs thread scheduling is also virtually
identical.

If you look to the future, then you should accept that process switching should
become more efficient as the operating systems improve.

>
> > > I can't comment on the "isolate data" line.  I am still trying to figure
> > > that one out.
> >
> > Sometimes you need data which is specific to a particular thread.
>
> When you need data that is specific to a thread you use a TSD (Thread
> Specific Data).

Yes, but Postgres has many global variables. The assumption has always been that
it is a stand-alone process with an explicitly shared paradigm, not implicitly.

>
> > Basically, you have to look at every global variable in the Postgres
> > backend, and determine whether to share it among all threads or to
> > make it thread-specific.
>
> Yes, if one was to implement threads into PostgreSQL I would think that
> some re-writing would be in order of several areas.  Like I said before,
> give a person a chance to restructure things so future TODO items wouldn't
> be so hard to implement.  Personally, I like to stay away from global
> variables as much as possible.  They just get you into trouble.

In real live software, software which lives from year to year with active
development, things do get messy. There are always global variables involved in a
program. Efforts, of course, should be made to keep them to a minimum, but the
reality is that they always happen.

Also, the very structure of function calls may need to change when going from a
process model to a threaded model. Functions never before reentrant are now be
reentrant, think about that. That is a huge undertaking. Every single function
may need to be examined for thread safety, with little benefit.

>
> > > That last line is a troll if I every saw it ;-)  I will agree that threads
> > > isn't for everything and that it has costs just like everything else.  Let
> > > me stress that last part - like everything else.  Certain costs exist in
> > > the present model, nothing is - how should we say ... perfect.
> >
> > When writing in C, threading inevitably loses robustness.  Erratic
> > behaviour by one thread, perhaps in a user defined function, can
> > subtly corrupt the entire system, rather than just that thread.  Part
> > of defensive programming is building barriers between different parts
> > of a system.  Process boundaries are a powerful barrier.
>
> I agree with everything you wrote above except for the first line.  My
> only comment is that process boundaries are only *truely* a powerful
> barrier if the processes are different pieces of code and are not
> dependent on each other in crippling ways.  Forking the same code with the
> bug in it - and only 1 in 5 die - is still 4 copies of buggy code running
> on your system ;-)

This is simply not true. All software has bugs, it is an undeniable fact. Some
bugs are more likely to be hit than others. 5 processes , when one process hits a
bug, that does not mean the other 4 will hit the same bug. Obscure bugs kill
software all the time, the trick is to minimize the impact. Software is not
perfect, assuming it can be is a mistake.





---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

From pgsql-hackers-owner+M16320=candle.pha.pa.us=pgman@postgresql.org Thu Dec  6 10:16:20 2001
Return-path: <pgsql-hackers-owner+M16320=candle.pha.pa.us=pgman@postgresql.org>
Received: from west.navpoint.com (west.navpoint.com [207.106.42.13])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id fB6FGJZ29347
	for <pgman@candle.pha.pa.us>; Thu, 6 Dec 2001 10:16:19 -0500 (EST)
Received: from rs.postgresql.org (server1.pgsql.org [64.39.15.238] (may be forged))
	by west.navpoint.com (8.11.6/8.10.1) with ESMTP id fB6FGIE25797
	for <pgman@candle.pha.pa.us>; Thu, 6 Dec 2001 10:16:18 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by rs.postgresql.org (8.11.6/8.11.6) with ESMTP id fB6F8MR55154
	for <pgman@candle.pha.pa.us>; Thu, 6 Dec 2001 09:12:12 -0600 (CST)
	(envelope-from pgsql-hackers-owner+M16320=candle.pha.pa.us=pgman@postgresql.org)
Received: from dendrite.sacadia.com (adsl-64-168-22-137.dsl.snfc21.pacbell.net [64.168.22.137])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id fB3NNfm32380
	for <pgsql-hackers@postgresql.org>; Mon, 3 Dec 2001 18:23:42 -0500 (EST)
	(envelope-from mkscott@sacadia.com)
Received: from sacadia.com (localhost [127.0.0.1])
	by dendrite.sacadia.com (8.10.2+Sun/8.10.2) with ESMTP id fB3NKiK16816
	for <pgsql-hackers@postgresql.org>; Mon, 3 Dec 2001 15:20:44 -0800 (PST)
Message-ID: <3C0C08CC.917CC04B@sacadia.com>
Date: Mon, 03 Dec 2001 15:20:44 -0800
From: mkscott@sacadia.com
X-Mailer: Mozilla 4.78 [en] (X11; U; SunOS 5.8 sun4u)
X-Accept-Language: en
MIME-Version: 1.0
To: PostgreSQL Hackers Mailing List <pgsql-hackers@postgresql.org>
Subject: [HACKERS] Using Threads (again)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR


Hi All,


Since I last posted to this list I have done some work
on a multi-threaded port of Postgres 7.0.2 that I have been kicking
around for a while.  There has been some mild interest
in this in the past so I thought I might try and start a sourceforge
project with what I have so far.

>From past discussions, it is clear to me that a direct port
of postgres which uses threads instead of processes is not a
good idea, how about an embedded version that uses threads.
A multi-threaded postgres might be good for that.
The version I am working on is slower in terms of transaction
throughput than the current postgres but it uses less system
resources and does not require shared memory.

I know it is possible to embed the current postgres but I
believe that is a single user system.

Comments?


Myron Scott
mkscott@sacadia.com


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

From pgsql-hackers-owner+M33671@postgresql.org Fri Jan  3 10:27:00 2003
Return-path: <pgsql-hackers-owner+M33671@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h03FQwl07124
	for <pgman@candle.pha.pa.us>; Fri, 3 Jan 2003 10:26:58 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id EDEBC4764DE; Fri,  3 Jan 2003 10:26:53 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 31554476422; Fri,  3 Jan 2003 10:25:46 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 69252476286
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 10:25:29 -0500 (EST)
Received: from www.pspl.co.in (www.pspl.co.in [202.54.11.65])
	by postgresql.org (Postfix) with ESMTP id 98F754764C3
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 10:23:52 -0500 (EST)
Received: (from root@localhost)
	by www.pspl.co.in (8.11.6/8.11.6) id h03FNtK17518
	for <pgsql-hackers@postgresql.org>; Fri, 3 Jan 2003 20:53:55 +0530
Received: from daithan.itnranet.pspl.co.in (daithan.intranet.pspl.co.in [192.168.7.161])
	by www.pspl.co.in (8.11.6/8.11.0) with ESMTP id h03FNsf17512
	for <pgsql-hackers@postgresql.org>; Fri, 3 Jan 2003 20:53:54 +0530
From: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
To: PGHackers <pgsql-hackers@postgresql.org>
Subject: [HACKERS] Threads
Date: Fri, 3 Jan 2003 20:54:11 +0530
User-Agent: KMail/1.4.3
MIME-Version: 1.0
Content-Type: Multipart/Mixed;
  boundary="------------Boundary-00=_BG9530ZI94UNRKSGBVL5"
Message-ID: <200301032054.11125.shridhar_daithankar@persistent.co.in>
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

--------------Boundary-00=_BG9530ZI94UNRKSGBVL5
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi all,

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

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

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

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

This code expects minimum pthreads implementation and does not assume anyth=
ing=20
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=20
pluggable in any source code and is released without any strings for any us=
e.

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

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

Further threads can be useful when the server has more CPUs. It can spread =
CPU=20
intensive work to different threads such as index creation or sorting. This=
=20
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=20
we can entirely keep existing functionality and convert them one-by-one to=
=20
threaded application.

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

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

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

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

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

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

Bye
 Shridhar

--------------Boundary-00=_BG9530ZI94UNRKSGBVL5
Content-Type: text/x-chdr;
  charset="us-ascii";
  name="thread.h"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="thread.h"

#define _REENTRANT

#include <stdio.h>
#include <stdlib.h>
#include <pthread.h>
#include <unistd.h>


//typedefs
typedef void* (*function)(void *);
typedef void* argtype;

typedef struct
{
 pthread_mutex_t lock;
 pthread_cond_t cond;

 unsigned short freeCount,n,count;
 void *pool;

} threadPool;

typedef struct
{
 pthread_t t;
 pthread_attr_t tattr;
 pthread_mutex_t lock;
 pthread_cond_t cond;

 argtype arg;
 function f;

 unsigned short quit;
 threadPool *p;

} thread;

/*Thread functions*/
void initThread(thread **t,threadPool *pool);
void deleteThread(thread **t);
void stop(thread *thr);

void wakeForWork(thread *thr,function func,argtype a);

argtype runner(void *ptr);

/*thread pool functions*/
void initPool(threadPool **pool,unsigned short numthreads);
void deletePool(threadPool **p);

void putThread(threadPool *p,thread *t);
thread	*getThread(threadPool *p);






--------------Boundary-00=_BG9530ZI94UNRKSGBVL5
Content-Type: text/x-csrc;
  charset="us-ascii";
  name="thread.c"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="thread.c"

#include "thread.h"

void initThread(thread **t,threadPool *pool)
{
 thread *thr=(thread *)malloc(sizeof(thread));

 if(!thr)
 {
  fprintf(stderr,"\nCan not allocate memory for thread. Quitting...\n");
  exit(1);
 }

 *t=thr;

 pthread_attr_init(&(thr->tattr));
 pthread_mutex_init(&(thr->lock), NULL);
 pthread_cond_init(&(thr->cond), NULL);

 pthread_attr_setdetachstate(&(thr->tattr),PTHREAD_CREATE_DETACHED);

 thr->quit=0;
 thr->p=pool;

 //Create the thread
 int ret=pthread_create(&(thr->t),&(thr->tattr),runner,(void *)thr);

 if(ret!=0)
 {
  fprintf(stderr,"\nCan not create thread. Quitting...\n");
  exit(1);
 }
}

void deleteThread(thread **t)
{
 thread *thr=*t;

 if(!t) return;

 stop(thr);

 pthread_attr_destroy(&(thr->tattr));
 pthread_cond_destroy(&(thr->cond));
 pthread_mutex_destroy(&(thr->lock));

 free(thr);
}

void stop(thread *thr)
{
 unsigned short i;
 thr->quit=1;

 pthread_cond_signal(&(thr->cond));

 for(i=0;thr->quit && i<10;i++)
 {
   if(i>=10)
   {
	pthread_kill(thr->t,9);
	break;
   }
   usleep(400);
  }
}

void wakeForWork(thread *thr,function func,argtype a)
{
 thr->f=func;
 thr->arg=a;

 pthread_cond_signal(&(thr->cond));
}

argtype runner(void* arg)
{
 thread *ptr=(thread *)arg;

 while(1)
 {
  pthread_mutex_lock(&(ptr->lock));

  if(ptr->p)
   putThread(ptr->p,ptr);

  pthread_cond_wait(&(ptr->cond),&(ptr->lock));

  if(ptr->quit) break;

  ptr->f((void *)ptr->arg);

  pthread_mutex_unlock(&(ptr->lock));
 }

 ptr->quit=0;

 return NULL;
}


void initPool(threadPool **pool,unsigned short numthreads)
{
 thread **thr;
 threadPool *p=(threadPool *)malloc(sizeof(threadPool));

 if(!p)
 {
  fprintf(stderr,"Can not get memory to create threadpool. Quitting\n");
  exit(1);
 }
 
 if(!pool)
 {
  free(p);
  return;
 }

 *pool=p;

 pthread_mutex_init(&(p->lock), NULL);
 pthread_cond_init(&(p->cond), NULL);

 p->n=numthreads;
 p->freeCount=0;
 p->n=numthreads;

 thr=(thread **)malloc(numthreads*sizeof(thread *));

 if(!thr)
 {
  fprintf(stderr,"Can not get memory to create pool of threads. Quitting\n");
  exit(1);
 }

 p->pool=(void *)thr;

}

void deletePool(threadPool **pool)
{
 threadPool *p=(threadPool *)pool;

 if(!pool) return;

 thread **thr=(thread **)p->pool;
 unsigned short i;

 for(i=0;i<p->n;i++) stop(thr[i]);

 free(p->pool);

 pthread_cond_destroy(&(p->cond));
 pthread_mutex_destroy(&(p->lock));

 free(p);

}

void putThread(threadPool *p,thread *t)
{
 unsigned short i;
 thread **pool;

 if(!p || !t) return;

 pool=(thread **)p->pool;

 pthread_mutex_lock(&(p->lock));

 i=p->freeCount;
 pool[(p->freeCount)++]=t;

 if(i<=0)pthread_cond_signal(&(p->cond));

 pthread_mutex_unlock(&(p->lock));

}

thread *getThread(threadPool *p)
{
 thread *t,**t1;

 if(!p) return NULL;

 t1=(thread **)p->pool;

 pthread_mutex_lock(&(p->lock));

 if((p->freeCount)<=0)pthread_cond_wait(&(p->cond),&(p->lock));

 t=t1[--(p->freeCount)];

 pthread_mutex_unlock(&(p->lock));

 return t;

}

--------------Boundary-00=_BG9530ZI94UNRKSGBVL5
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--------------Boundary-00=_BG9530ZI94UNRKSGBVL5--


From pgsql-hackers-owner+M33682@postgresql.org Fri Jan  3 15:43:54 2003
Return-path: <pgsql-hackers-owner+M33682@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h03Khhl06938
	for <pgman@candle.pha.pa.us>; Fri, 3 Jan 2003 15:43:45 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id DF70F476EA6; Fri,  3 Jan 2003 15:43:34 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 95BA8476514; Fri,  3 Jan 2003 15:43:26 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 71F4E475DBC
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 15:43:14 -0500 (EST)
Received: from snoopy.mohawksoft.com (h0030f1382639.ne.client2.attbi.com [24.60.194.163])
	by postgresql.org (Postfix) with ESMTP id ACE5B475DAD
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 15:43:13 -0500 (EST)
Received: from mohawksoft.com (snoopy.mohawksoft.com [127.0.0.1])
	by snoopy.mohawksoft.com (8.11.6/8.11.6) with ESMTP id h03KlMs24421;
	Fri, 3 Jan 2003 15:47:27 -0500
Message-ID: <3E15F6DA.8000209@mohawksoft.com>
Date: Fri, 03 Jan 2003 15:47:22 -0500
From: mlw <pgsql@mohawksoft.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20020823 Netscape/7.0
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
cc: PGHackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Threads
References: <200301032054.11125.shridhar_daithankar@persistent.co.in>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

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

From pgsql-hackers-owner+M33684@postgresql.org Fri Jan  3 15:56:48 2003
Return-path: <pgsql-hackers-owner+M33684@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h03Kufl08003
	for <pgman@candle.pha.pa.us>; Fri, 3 Jan 2003 15:56:43 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id D0392477118; Fri,  3 Jan 2003 15:56:31 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 31FDC475461; Fri,  3 Jan 2003 15:55:26 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id BD892477147
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 15:55:09 -0500 (EST)
Received: from voyager.corporate.connx.com (unknown [209.20.248.131])
	by postgresql.org (Postfix) with ESMTP id 7EE644771A0
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 15:52:47 -0500 (EST)
content-class: urn:content-classes:message
Subject: Re: [HACKERS] Threads
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Date: Fri, 3 Jan 2003 12:52:48 -0800
X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0
Message-ID: <D90A5A6C612A39408103E6ECDD77B829408A20@voyager.corporate.connx.com>
Thread-Topic: [HACKERS] Threads
Thread-Index: AcKzaMsucwBFaOikSjKML8BqvR/gCAAACDPA
From: "Dann Corbit" <DCorbit@connx.com>
To: "PGHackers" <pgsql-hackers@postgresql.org>
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from quoted-printable to 8bit by candle.pha.pa.us id h03Kufl08003
Status: OR

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

From pgsql-hackers-owner+M33685@postgresql.org Fri Jan  3 16:35:02 2003
Return-path: <pgsql-hackers-owner+M33685@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h03LYsl11402
	for <pgman@candle.pha.pa.us>; Fri, 3 Jan 2003 16:34:56 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 0F09B477168; Fri,  3 Jan 2003 16:34:48 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id C1A9C477132; Fri,  3 Jan 2003 16:34:39 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id D830847630B
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 16:34:25 -0500 (EST)
Received: from CopelandConsulting.Net (dsl-24293-ld.customer.centurytel.net [209.142.135.135])
	by postgresql.org (Postfix) with ESMTP id 025DD476417
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 16:34:24 -0500 (EST)
Received: from [192.168.1.2] (mouse.copelandconsulting.net [192.168.1.2])
	by CopelandConsulting.Net (8.10.1/8.10.1) with ESMTP id h03LY2700731;
	Fri, 3 Jan 2003 15:34:03 -0600 (CST)
X-Trade-Id: <CCC.Fri, 3 Jan 2003 15:34:03 -0600 (CST).Fri, 3 Jan 2003 15:34:03 -0600 (CST).200301032134.h03LY2700731.h03LY2700731@CopelandConsulting.Net.
Subject: Re: [HACKERS] Threads
From: Greg Copeland <greg@CopelandConsulting.Net>
To: mlw <pgsql@mohawksoft.com>
cc: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>,
   PGHackers <pgsql-hackers@postgresql.org>
In-Reply-To: <3E15F6DA.8000209@mohawksoft.com>
References: <200301032054.11125.shridhar_daithankar@persistent.co.in>
  <3E15F6DA.8000209@mohawksoft.com>
Content-Type: text/plain
Organization: Copeland Computer Consulting
Message-ID: <1041629649.15933.135.camel@mouse.copelandconsulting.net>
MIME-Version: 1.0
X-Mailer: Ximian Evolution 1.2.0 
Date: 03 Jan 2003 15:34:10 -0600
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

From pgsql-hackers-owner+M33686@postgresql.org Fri Jan  3 16:47:20 2003
Return-path: <pgsql-hackers-owner+M33686@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h03LlBl12502
	for <pgman@candle.pha.pa.us>; Fri, 3 Jan 2003 16:47:12 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 6873147621D; Fri,  3 Jan 2003 16:47:06 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 97466477133; Fri,  3 Jan 2003 16:46:41 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id E25BB477152
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 16:46:24 -0500 (EST)
Received: from CopelandConsulting.Net (dsl-24293-ld.customer.centurytel.net [209.142.135.135])
	by postgresql.org (Postfix) with ESMTP id 84A87477157
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 16:45:21 -0500 (EST)
Received: from [192.168.1.2] (mouse.copelandconsulting.net [192.168.1.2])
	by CopelandConsulting.Net (8.10.1/8.10.1) with ESMTP id h03LjC712426;
	Fri, 3 Jan 2003 15:45:13 -0600 (CST)
X-Trade-Id: <CCC.Fri, 3 Jan 2003 15:45:13 -0600 (CST).Fri, 3 Jan 2003 15:45:13 -0600 (CST).200301032145.h03LjC712426.h03LjC712426@CopelandConsulting.Net.
Subject: Re: [HACKERS] Threads
From: Greg Copeland <greg@CopelandConsulting.Net>
To: Dann Corbit <DCorbit@connx.com>
cc: PGHackers <pgsql-hackers@postgresql.org>
In-Reply-To: <D90A5A6C612A39408103E6ECDD77B829408A20@voyager.corporate.connx.com>
References:  <D90A5A6C612A39408103E6ECDD77B829408A20@voyager.corporate.connx.com>
Content-Type: text/plain
Organization: Copeland Computer Consulting
Message-ID: <1041630319.15927.146.camel@mouse.copelandconsulting.net>
MIME-Version: 1.0
X-Mailer: Ximian Evolution 1.2.0 
Date: 03 Jan 2003 15:45:20 -0600
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From pgsql-hackers-owner+M33703@postgresql.org Fri Jan  3 20:41:10 2003
Return-path: <pgsql-hackers-owner+M33703@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h041f9l05824
	for <pgman@candle.pha.pa.us>; Fri, 3 Jan 2003 20:41:09 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 7F5764764C8; Fri,  3 Jan 2003 20:41:04 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id BE24547606D; Fri,  3 Jan 2003 20:38:53 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 4D50D476165
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 20:38:39 -0500 (EST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP id 20C8547659F
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 20:34:10 -0500 (EST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h041Y20U023764;
	Fri, 3 Jan 2003 20:34:03 -0500 (EST)
To: "Serguei Mokhov" <mokhov@cs.concordia.ca>
cc: "Greg Copeland" <greg@CopelandConsulting.Net>,
   "Dann Corbit" <DCorbit@connx.com>,
   "PGHackers" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Threads 
In-Reply-To: <004101c2b37b$0f261ae0$0301a8c0@gunnymede.lan> 
References: <D90A5A6C612A39408103E6ECDD77B829408A20@voyager.corporate.connx.com> <1041630319.15927.146.camel@mouse.copelandconsulting.net> <004101c2b37b$0f261ae0$0301a8c0@gunnymede.lan>
Comments: In-reply-to "Serguei Mokhov" <mokhov@cs.concordia.ca>
	message dated "Fri, 03 Jan 2003 17:54:20 -0500"
Date: Fri, 03 Jan 2003 20:34:02 -0500
Message-ID: <23763.1041644042@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

"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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

From pgsql-hackers-owner+M33706@postgresql.org Fri Jan  3 21:16:55 2003
Return-path: <pgsql-hackers-owner+M33706@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h042Gsl08584
	for <pgman@candle.pha.pa.us>; Fri, 3 Jan 2003 21:16:54 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 8F2EB475E22; Fri,  3 Jan 2003 21:16:49 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 72017475FDA; Fri,  3 Jan 2003 21:15:21 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id EA790476242
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 21:15:00 -0500 (EST)
Received: from CopelandConsulting.Net (dsl-24293-ld.customer.centurytel.net [209.142.135.135])
	by postgresql.org (Postfix) with ESMTP id BB7A0475D0D
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 21:11:20 -0500 (EST)
Received: from [192.168.1.2] (mouse.copelandconsulting.net [192.168.1.2])
	by CopelandConsulting.Net (8.10.1/8.10.1) with ESMTP id h042B8729407;
	Fri, 3 Jan 2003 20:11:08 -0600 (CST)
X-Trade-Id: <CCC.Fri, 3 Jan 2003 20:11:08 -0600 (CST).Fri, 3 Jan 2003 20:11:08 -0600 (CST).200301040211.h042B8729407.h042B8729407@CopelandConsulting.Net.
Subject: Re: [HACKERS] Threads
From: Greg Copeland <greg@CopelandConsulting.Net>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: Serguei Mokhov <mokhov@cs.concordia.ca>, Dann Corbit <DCorbit@connx.com>,
   PGHackers <pgsql-hackers@postgresql.org>
In-Reply-To: <23763.1041644042@sss.pgh.pa.us>
References:  <D90A5A6C612A39408103E6ECDD77B829408A20@voyager.corporate.connx.com>
  <1041630319.15927.146.camel@mouse.copelandconsulting.net>
  <004101c2b37b$0f261ae0$0301a8c0@gunnymede.lan>
  <23763.1041644042@sss.pgh.pa.us>
Content-Type: text/plain
Organization: Copeland Computer Consulting
Message-ID: <1041646276.15927.202.camel@mouse.copelandconsulting.net>
MIME-Version: 1.0
X-Mailer: Ximian Evolution 1.2.0 
Date: 03 Jan 2003 20:11:17 -0600
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

From pgsql-hackers-owner+M33709@postgresql.org Fri Jan  3 22:39:26 2003
Return-path: <pgsql-hackers-owner+M33709@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h043dOl13614
	for <pgman@candle.pha.pa.us>; Fri, 3 Jan 2003 22:39:25 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id CA13B47621C; Fri,  3 Jan 2003 22:39:20 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 8DE1D475DFF; Fri,  3 Jan 2003 22:39:04 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 15AA1475AFF
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 22:39:00 -0500 (EST)
Received: from CopelandConsulting.Net (dsl-24293-ld.customer.centurytel.net [209.142.135.135])
	by postgresql.org (Postfix) with ESMTP id 19D8F475ADD
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 22:38:59 -0500 (EST)
Received: from [192.168.1.2] (mouse.copelandconsulting.net [192.168.1.2])
	by CopelandConsulting.Net (8.10.1/8.10.1) with ESMTP id h043ca714568;
	Fri, 3 Jan 2003 21:38:36 -0600 (CST)
X-Trade-Id: <CCC.Fri, 3 Jan 2003 21:38:36 -0600 (CST).Fri, 3 Jan 2003 21:38:36 -0600 (CST).200301040338.h043ca714568.h043ca714568@CopelandConsulting.Net.
Subject: Re: [HACKERS] Threads
From: Greg Copeland <greg@CopelandConsulting.Net>
To: mlw <pgsql@mohawksoft.com>
cc: Tom Lane <tgl@sss.pgh.pa.us>, Serguei Mokhov <mokhov@cs.concordia.ca>,
   Dann Corbit <DCorbit@connx.com>, PGHackers <pgsql-hackers@postgresql.org>
In-Reply-To: <3E16575C.1030805@mohawksoft.com>
References:  <D90A5A6C612A39408103E6ECDD77B829408A20@voyager.corporate.connx.com>
  <1041630319.15927.146.camel@mouse.copelandconsulting.net>
  <004101c2b37b$0f261ae0$0301a8c0@gunnymede.lan>
  <23763.1041644042@sss.pgh.pa.us>
  <1041646276.15927.202.camel@mouse.copelandconsulting.net>
  <3E16575C.1030805@mohawksoft.com>
Content-Type: text/plain
Organization: Copeland Computer Consulting
Message-ID: <1041651525.15927.207.camel@mouse.copelandconsulting.net>
MIME-Version: 1.0
X-Mailer: Ximian Evolution 1.2.0 
Date: 03 Jan 2003 21:38:46 -0600
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

From pgsql-hackers-owner+M33708@postgresql.org Fri Jan  3 22:35:26 2003
Return-path: <pgsql-hackers-owner+M33708@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h043ZOl13418
	for <pgman@candle.pha.pa.us>; Fri, 3 Jan 2003 22:35:25 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 2277B475FDA; Fri,  3 Jan 2003 22:35:21 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id DA681475E18; Fri,  3 Jan 2003 22:35:12 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 8254047595A
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 22:34:58 -0500 (EST)
Received: from snoopy.mohawksoft.com (h0030f1382639.ne.client2.attbi.com [24.60.194.163])
	by postgresql.org (Postfix) with ESMTP id A4D60475921
	for <pgsql-hackers@postgresql.org>; Fri,  3 Jan 2003 22:34:57 -0500 (EST)
Received: from mohawksoft.com (snoopy.mohawksoft.com [127.0.0.1])
	by snoopy.mohawksoft.com (8.11.6/8.11.6) with ESMTP id h043d8s26180;
	Fri, 3 Jan 2003 22:39:09 -0500
Message-ID: <3E16575C.1030805@mohawksoft.com>
Date: Fri, 03 Jan 2003 22:39:08 -0500
From: mlw <pgsql@mohawksoft.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20020823 Netscape/7.0
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Greg Copeland <greg@CopelandConsulting.Net>
cc: Tom Lane <tgl@sss.pgh.pa.us>, Serguei Mokhov <mokhov@cs.concordia.ca>,
   Dann Corbit <DCorbit@connx.com>, PGHackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Threads
References: <D90A5A6C612A39408103E6ECDD77B829408A20@voyager.corporate.connx.com>	 <1041630319.15927.146.camel@mouse.copelandconsulting.net>	 <004101c2b37b$0f261ae0$0301a8c0@gunnymede.lan>	 <23763.1041644042@sss.pgh.pa.us> <1041646276.15927.202.camel@mouse.copelandconsulting.net>
Content-Type: multipart/alternative;
	boundary="------------030005060103020905060907"
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

--------------030005060103020905060907
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit



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.

--------------030005060103020905060907
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <title></title>
</head>
<body>
<br>
<br>
Greg Copeland wrote:<br>
<blockquote type="cite"
 cite="mid1041646276.15927.202.camel@mouse.copelandconsulting.net">
  <pre wrap="">
  </pre>
  <blockquote type="cite">
    <pre wrap="">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
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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.</pre>
</blockquote>
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 &nbsp;required
to manage it. Under load this is ALWAYS slower. <br>
<br>
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.<br>
</body>
</html>

--------------030005060103020905060907--


From pgsql-hackers-owner+M33713@postgresql.org Sat Jan  4 00:34:04 2003
Return-path: <pgsql-hackers-owner+M33713@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h045Y2l23520
	for <pgman@candle.pha.pa.us>; Sat, 4 Jan 2003 00:34:02 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id BCA39476226; Sat,  4 Jan 2003 00:33:56 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 1B030475F09; Sat,  4 Jan 2003 00:33:47 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id A42D847595A
	for <pgsql-hackers@postgresql.org>; Sat,  4 Jan 2003 00:33:37 -0500 (EST)
Received: from houston.familyhealth.com.au (unknown [203.59.48.253])
	by postgresql.org (Postfix) with ESMTP id C14B4475921
	for <pgsql-hackers@postgresql.org>; Sat,  4 Jan 2003 00:33:35 -0500 (EST)
Received: from localhost (chriskl@localhost)
	by houston.familyhealth.com.au (8.11.6/8.11.6) with ESMTP id h045XKt36362;
	Sat, 4 Jan 2003 13:33:23 +0800 (WST)
	(envelope-from chriskl@familyhealth.com.au)
Date: Sat, 4 Jan 2003 13:33:20 +0800 (WST)
From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
To: mlw <pgsql@mohawksoft.com>
cc: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>,
   PGHackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Threads
In-Reply-To: <3E15F6DA.8000209@mohawksoft.com>
Message-ID: <20030104133226.N36192-100000@houston.familyhealth.com.au>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

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
>


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

From pgsql-hackers-owner+M33723@postgresql.org Sat Jan  4 13:21:52 2003
Return-path: <pgsql-hackers-owner+M33723@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h04ILpl25640
	for <pgman@candle.pha.pa.us>; Sat, 4 Jan 2003 13:21:51 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id A5E5D4764F0; Sat,  4 Jan 2003 13:21:50 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id B8D94476021; Sat,  4 Jan 2003 13:21:37 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 7FDFE475CE7
	for <pgsql-hackers@postgresql.org>; Sat,  4 Jan 2003 13:21:28 -0500 (EST)
Received: from CopelandConsulting.Net (dsl-24293-ld.customer.centurytel.net [209.142.135.135])
	by postgresql.org (Postfix) with ESMTP id 71C47474E42
	for <pgsql-hackers@postgresql.org>; Sat,  4 Jan 2003 13:21:27 -0500 (EST)
Received: from [192.168.1.2] (mouse.copelandconsulting.net [192.168.1.2])
	by CopelandConsulting.Net (8.10.1/8.10.1) with ESMTP id h04ILF721061;
	Sat, 4 Jan 2003 12:21:15 -0600 (CST)
X-Trade-Id: <CCC.Sat, 4 Jan 2003 12:21:15 -0600 (CST).Sat, 4 Jan 2003 12:21:15 -0600 (CST).200301041821.h04ILF721061.h04ILF721061@CopelandConsulting.Net.
Subject: Re: [HACKERS] Threads
From: Greg Copeland <greg@CopelandConsulting.Net>
To: kar@kakidata.dk
cc: PGHackers <pgsql-hackers@postgresql.org>
In-Reply-To: <200301041359.35715.kar@kakidata.dk>
References:  <D90A5A6C612A39408103E6ECDD77B829408A20@voyager.corporate.connx.com>
  <23763.1041644042@sss.pgh.pa.us>
  <1041646276.15927.202.camel@mouse.copelandconsulting.net>
  <200301041359.35715.kar@kakidata.dk>
Content-Type: text/plain
Organization: Copeland Computer Consulting
Message-ID: <1041704480.15927.224.camel@mouse.copelandconsulting.net>
MIME-Version: 1.0
X-Mailer: Ximian Evolution 1.2.0 
Date: 04 Jan 2003 12:21:20 -0600
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

From pgsql-hackers-owner+M33819@postgresql.org Mon Jan  6 02:41:01 2003
Return-path: <pgsql-hackers-owner+M33819@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h067exi23864
	for <pgman@candle.pha.pa.us>; Mon, 6 Jan 2003 02:40:59 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id CCD564763B7; Mon,  6 Jan 2003 02:40:56 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 4A6574762E0; Mon,  6 Jan 2003 02:40:54 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 2C31947606A
	for <pgsql-hackers@postgresql.org>; Mon,  6 Jan 2003 02:40:50 -0500 (EST)
Received: from datafix.CS.Berkeley.EDU (datafix.CS.Berkeley.EDU [128.32.37.185])
	by postgresql.org (Postfix) with ESMTP id 8D7AF47603D
	for <pgsql-hackers@postgresql.org>; Mon,  6 Jan 2003 02:40:49 -0500 (EST)
Received: (from sailesh@localhost)
	by datafix.CS.Berkeley.EDU (8.11.6/8.11.6) id h067ac532006;
	Sun, 5 Jan 2003 23:36:38 -0800
X-Authentication-Warning: datafix.CS.Berkeley.EDU: sailesh set sender to sailesh@cs.berkeley.edu using -f
Reply-To: sailesh@cs.berkeley.edu
X-URL: http://www.cs.berkeley.edu/~sailesh
X-Attribution: Sailesh
To: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
cc: PGHackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Threads
References: <200301032054.11125.shridhar_daithankar@persistent.co.in>
	<3E1605B8.5060403@priefert.com>
	<200301061202.43247.shridhar_daithankar@persistent.co.in>
From: Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Date: 05 Jan 2003 23:36:38 -0800
In-Reply-To: <200301061202.43247.shridhar_daithankar@persistent.co.in>
Message-ID: <m3znqeivax.fsf@datafix.CS.Berkeley.EDU>
Lines: 50
User-Agent: Gnus/5.0808 (Gnus v5.8.8) XEmacs/21.1 (Cuyahoga Valley)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

>>>>> "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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

From pgsql-hackers-owner+M33822@postgresql.org Mon Jan  6 06:23:29 2003
Return-path: <pgsql-hackers-owner+M33822@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h06BNSi17987
	for <pgman@candle.pha.pa.us>; Mon, 6 Jan 2003 06:23:28 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id A1204476260; Mon,  6 Jan 2003 06:23:21 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 0B78D476060; Mon,  6 Jan 2003 06:23:19 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 50277475BA0
	for <pgsql-hackers@postgresql.org>; Mon,  6 Jan 2003 06:23:14 -0500 (EST)
Received: from mail.gne.de (mail.gne.de [213.83.0.2])
	by postgresql.org (Postfix) with ESMTP id 27B244758E6
	for <pgsql-hackers@postgresql.org>; Mon,  6 Jan 2003 06:23:13 -0500 (EST)
Received: from DO5GNE-MTA by mail.gne.de
	with Novell_GroupWise; Mon, 06 Jan 2003 12:23:02 +0100
Message-ID: <se197526.086@mail.gne.de>
X-Mailer: Novell GroupWise Internet Agent 6.0.2
Date: Mon, 06 Jan 2003 12:22:57 +0100
From: "Ulrich Neumann" <U_Neumann@gne.de>
To: "<PGHackers" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Threads
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Guinevere: 1.1.14 ; GNE Grebe Neumann Gl
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

Hello all,

it's very interesting to see the discussion of "threads" again.

I've portet PostgreSQL to a "thread-per-connection" model based on
pthreads
and it is functional. Most of the work was finding all the static
globals in the sourcefiles
and swapping them between threads and freeing memory if a thread
terminates.
(PostgreSQL isn't written very clean in the aspects of memory
handling).

My version of the thread-based PostgreSQL is not very efficient at the
moment because
I haven't done any optimisation of the code to better support threads
and I'm using just a
simple semaphore to control switching of data but this could be a
starting point for
others who want to see this code. If this direction will be taken
seriously I'm very willing
to help.

If someone is interested in the code I can send a zip file to everyone
who wants.

Ulrich
----------------------------------
  This e-mail is virus scanned
  Diese e-mail ist virusgeprueft


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

From pgsql-hackers-owner+M33824@postgresql.org Mon Jan  6 07:49:46 2003
Return-path: <pgsql-hackers-owner+M33824@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h06Cnii03541
	for <pgman@candle.pha.pa.us>; Mon, 6 Jan 2003 07:49:44 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id C409E476778; Mon,  6 Jan 2003 07:49:36 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 617C04768C8; Mon,  6 Jan 2003 07:49:01 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id EA9284768AA
	for <pgsql-hackers@postgresql.org>; Mon,  6 Jan 2003 07:48:56 -0500 (EST)
Received: from CopelandConsulting.Net (dsl-24293-ld.customer.centurytel.net [209.142.135.135])
	by postgresql.org (Postfix) with ESMTP id 2DB74476191
	for <pgsql-hackers@postgresql.org>; Mon,  6 Jan 2003 07:48:41 -0500 (EST)
Received: from [192.168.1.2] (mouse.copelandconsulting.net [192.168.1.2])
	by CopelandConsulting.Net (8.10.1/8.10.1) with ESMTP id h06CmL702059;
	Mon, 6 Jan 2003 06:48:21 -0600 (CST)
X-Trade-Id: <CCC.Mon, 6 Jan 2003 06:48:21 -0600 (CST).Mon, 6 Jan 2003 06:48:21 -0600 (CST).200301061248.h06CmL702059.h06CmL702059@CopelandConsulting.Net.
Subject: Re: [HACKERS] Threads
From: Greg Copeland <greg@CopelandConsulting.Net>
To: shridhar_daithankar@persistent.co.in
cc: "<PGHackers" <pgsql-hackers@postgresql.org>
In-Reply-To: <3E19B78B.25689.15BFFE@localhost>
References: <3E19B78B.25689.15BFFE@localhost>
Content-Type: text/plain
Organization: Copeland Computer Consulting
Message-ID: <1041857302.17321.49.camel@mouse.copelandconsulting.net>
MIME-Version: 1.0
X-Mailer: Ximian Evolution 1.2.0 
Date: 06 Jan 2003 06:48:23 -0600
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

On Mon, 2003-01-06 at 05:36, Shridhar Daithankar wrote:
> On 6 Jan 2003 at 12:22, Ulrich Neumann wrote:
> 
> > Hello all,
> > If someone is interested in the code I can send a zip file to everyone
> > who wants.
> 
> I suggest you preserver your work. The reason I suggested thread are mainly two 
> folds.
> 
> 1) Get I/O time used fuitfully


AIO may address this without the need for integrated threading. 
Arguably, from the long thread that last appeared on the topic of AIO,
some hold that AIO doesn't even offer anything beyond the current
implementation.  As such, it's highly doubtful that integrated threading
is going to offer anything beyond what a sound AIO implementation can
achieve.


> 2) Use multiple CPU better.
> 


Multiple processes tend to universally support multiple CPUs better than
does threading.  On some platforms, the level of threading support is
currently only user mode implementations which means no additional CPU
use.  Furthermore, some platforms where user-mode threads are defacto,
they don't even allow for scheduling bias resulting is less work being
accomplished within the same time interval (work slice must be divided
between n-threads within the process, all of which run on a single CPU).


> It will not require as much code cleaning as your efforts might had. However 
> your work will be very useful if somebody decides to use thread in any fashion 
> in core postgresql.
> 
> I was hoping for bit more optimistic response given that what I suggested was 
> totally optional at any point of time but very important from performance 
> point. Besides the change would have been gradual as required..
> 


Speaking for my self, I probably would of been more excited if the
offered framework had addressed several issues.  The short list is:

o Code needs to be more robust.  It shouldn't be calling exit directly
as, I believe, it should be allowing for PostgreSQL to clean up some. 
Correct me as needed.  I would of also expected the code of adopted
PostgreSQL's semantics and mechanisms as needed (error reporting, etc). 
I do understand it was an initial attempt to simply get something in
front of some eyes and have something to talk about.  Just the same, I
was expecting something that we could actually pull the trigger with.

o Code isn't very portable.  Looked fairly okay for pthread platforms,
however, there is new emphasis on the Win32 platform.  I think it would
be a mistake to introduce something as significant as threading without
addressing Win32 from the get-go.

o I would desire a more highly abstracted/portable interface which
allows for different threading and synchronization primitives to be
used.  Current implementation is tightly coupled to pthreads. 
Furthermore, on platforms such as Solaris, I would hope it would easily
allow for plugging in its native threading primitives which are touted
to be much more efficient than pthreads on said platform.

o Code is not commented.  I would hope that adding new code for
something as important as threading would be commented.

o Code is fairly trivial and does not address other primitives
(semaphores, mutexs, conditions, TSS, etc) portably which would be
required for anything but the most trivial of threaded work.  This is
especially true in such an application where data IS the application. 
As such, you must reasonably assume that threads need some form of
portable serialization primitives, not to mention mechanisms for
non-trivial communication.

o Does not address issues such as thread signaling or status reporting.

o Pool interface is rather simplistic.  Does not currently support
concepts such as wake pool, stop pool, pool status, assigning a pool to
work, etc.  In fact, it's not altogether obvious what the capabilities
intent is of the current pool implementation.

o Doesn't seem to address any form of thread communication facilities
(mailboxes, queues, etc).


There are probably other things that I can find if I spend more than
just a couple of minutes looking at the code.  Honestly, I love threads
but I can see that the current code offering is not much more than a
token in its current form.  No offense meant.

After it's all said and done, I'd have to see a lot more meat before I'd
be convinced that threading is ready for PostgreSQL; from both a social
and technological perspective.


Regards,

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From pgsql-hackers-owner+M33899@postgresql.org Tue Jan  7 03:00:25 2003
Return-path: <pgsql-hackers-owner+M33899@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h0780Mi00624
	for <pgman@candle.pha.pa.us>; Tue, 7 Jan 2003 03:00:23 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 46FA747687C; Tue,  7 Jan 2003 03:00:21 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 69717475F25; Tue,  7 Jan 2003 03:00:13 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 5323E475F39
	for <pgsql-hackers@postgresql.org>; Tue,  7 Jan 2003 03:00:01 -0500 (EST)
Received: from www.pspl.co.in (www.pspl.co.in [202.54.11.65])
	by postgresql.org (Postfix) with ESMTP id 351DD475EE1
	for <pgsql-hackers@postgresql.org>; Tue,  7 Jan 2003 02:59:58 -0500 (EST)
Received: (from root@localhost)
	by www.pspl.co.in (8.11.6/8.11.6) id h077xvs03265
	for <pgsql-hackers@postgresql.org>; Tue, 7 Jan 2003 13:29:57 +0530
Received: from daithan (daithan.intranet.pspl.co.in [192.168.7.161])
	by www.pspl.co.in (8.11.6/8.11.0) with ESMTP id h077xvr03260
	for <pgsql-hackers@postgresql.org>; Tue, 7 Jan 2003 13:29:57 +0530
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: "<PGHackers" <pgsql-hackers@postgresql.org>
Date: Tue, 07 Jan 2003 13:30:05 +0530
MIME-Version: 1.0
Subject: Re: [HACKERS] Threads
Reply-To: shridhar_daithankar@persistent.co.in
Message-ID: <3E1AD65D.10112.192793@localhost>
References: <3E19B78B.25689.15BFFE@localhost>
In-Reply-To: <1041857302.17321.49.camel@mouse.copelandconsulting.net>
X-Mailer: Pegasus Mail for Windows (v4.02)
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7BIT
Content-Description: Mail message body
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

On 6 Jan 2003 at 6:48, Greg Copeland wrote:
> > 1) Get I/O time used fuitfully
> AIO may address this without the need for integrated threading. 
> Arguably, from the long thread that last appeared on the topic of AIO,
> some hold that AIO doesn't even offer anything beyond the current
> implementation.  As such, it's highly doubtful that integrated threading
> is going to offer anything beyond what a sound AIO implementation can
> achieve.

Either way, a complete aio or threading implementation is not available on 
major platforms that postgresql runs. Linux definitely does not have one, last 
I checked.

If postgresql is not using aio or threading, we should start using one of them, 
is what I feel. What do you say?

> > 2) Use multiple CPU better.
> Multiple processes tend to universally support multiple CPUs better than
> does threading.  On some platforms, the level of threading support is
> currently only user mode implementations which means no additional CPU
> use.  Furthermore, some platforms where user-mode threads are defacto,
> they don't even allow for scheduling bias resulting is less work being
> accomplished within the same time interval (work slice must be divided
> between n-threads within the process, all of which run on a single CPU).

The frame-work I have posted, threading is optional at build and should be a 
configuration option if it gets integrated. So for the platforms that can not 
spread threads across multiple CPUs, it can simply be turned off..

> Speaking for my self, I probably would of been more excited if the
> offered framework had addressed several issues.  The short list is:
> 
> o Code needs to be more robust.  It shouldn't be calling exit directly
> as, I believe, it should be allowing for PostgreSQL to clean up some. 
> Correct me as needed.  I would of also expected the code of adopted
> PostgreSQL's semantics and mechanisms as needed (error reporting, etc). 
> I do understand it was an initial attempt to simply get something in
> front of some eyes and have something to talk about.  Just the same, I
> was expecting something that we could actually pull the trigger with.

That could be done.

> 
> o Code isn't very portable.  Looked fairly okay for pthread platforms,
> however, there is new emphasis on the Win32 platform.  I think it would
> be a mistake to introduce something as significant as threading without
> addressing Win32 from the get-go.

If you search for "pthread" in thread.c, there are not many instances. Same 
goes for thread.h. From what I understand windows threading, it would be less 
than 10 minutes job to #ifdef the pthread related part on either file.

It is just that I have not played with windows threading and nor I am inclined 
to...;-)

> 
> o I would desire a more highly abstracted/portable interface which
> allows for different threading and synchronization primitives to be
> used.  Current implementation is tightly coupled to pthreads. 
> Furthermore, on platforms such as Solaris, I would hope it would easily
> allow for plugging in its native threading primitives which are touted
> to be much more efficient than pthreads on said platform.

Same as above. If there can be two cases separated with #ifdef, there can be 
more.. But what is important is to have a thread that can be woken up as and 
when required with any function desired. That is the basic idea.

> o Code is not commented.  I would hope that adding new code for
> something as important as threading would be commented.

Agreed. 
 
> o Code is fairly trivial and does not address other primitives
> (semaphores, mutexs, conditions, TSS, etc) portably which would be
> required for anything but the most trivial of threaded work.  This is
> especially true in such an application where data IS the application. 
> As such, you must reasonably assume that threads need some form of
> portable serialization primitives, not to mention mechanisms for
> non-trivial communication.

I don't get this. Probably I should post a working example. It is not threads 
responsibility to make a function thread safe which is changed on the fly. The 
function has to make sure that it is thread safe. That is altogether different 
effort..
 
> o Does not address issues such as thread signaling or status reporting.

>From what I learnt from pthreads on linux, I would not mix threads and signals. 
One can easily add code in runner function that disables any signals for thread 
while the thread starts running. This would leave original signal handling 
mechanism in place.

As far as status reporting is concerned, the thread sould be initiated while 
back-end starts and terminated with backend termination. What is about status 
reporting?
 
> o Pool interface is rather simplistic.  Does not currently support
> concepts such as wake pool, stop pool, pool status, assigning a pool to
> work, etc.  In fact, it's not altogether obvious what the capabilities
> intent is of the current pool implementation.

Could you please elaborate? I am using same interface in c++ for a server 
application and never faced a problem like that..;-)

 
> o Doesn't seem to address any form of thread communication facilities
> (mailboxes, queues, etc).

Not part of this abstraction of threading mechanism. Intentionally left out to 
keep things clean.

> There are probably other things that I can find if I spend more than
> just a couple of minutes looking at the code.  Honestly, I love threads
> but I can see that the current code offering is not much more than a
> token in its current form.  No offense meant.

None taken. Point is it is useful and that is enough for me. If you could 
elaborate examples for any problems you see, I can probably modify it. (Code 
documentation is what I will do now)

> After it's all said and done, I'd have to see a lot more meat before I'd
> be convinced that threading is ready for PostgreSQL; from both a social
> and technological perspective.

Tell me about it..

 
Bye
 Shridhar

--
What's this script do?    unzip ; touch ; finger ; mount ; gasp ; yes ; umount 
; sleepHint for the answer: not everything is computer-oriented. Sometimes 
you'rein a sleeping bag, camping out.(Contributed by Frans van der Zande.)


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

From pgsql-hackers-owner+M33921@postgresql.org Tue Jan  7 11:10:53 2003
Return-path: <pgsql-hackers-owner+M33921@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h07GApX13277
	for <pgman@candle.pha.pa.us>; Tue, 7 Jan 2003 11:10:51 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 5BDC0477200; Tue,  7 Jan 2003 11:06:58 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 9EE41477268; Tue,  7 Jan 2003 11:06:40 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id ACEA5477260
	for <pgsql-hackers@postgresql.org>; Tue,  7 Jan 2003 11:06:35 -0500 (EST)
Received: from CopelandConsulting.Net (dsl-24293-ld.customer.centurytel.net [209.142.135.135])
	by postgresql.org (Postfix) with ESMTP id 78B51477165
	for <pgsql-hackers@postgresql.org>; Tue,  7 Jan 2003 11:06:28 -0500 (EST)
Received: from [192.168.1.2] (mouse.copelandconsulting.net [192.168.1.2])
	by CopelandConsulting.Net (8.10.1/8.10.1) with ESMTP id h07G68711510;
	Tue, 7 Jan 2003 10:06:09 -0600 (CST)
X-Trade-Id: <CCC.Tue, 7 Jan 2003 10:06:09 -0600 (CST).Tue, 7 Jan 2003 10:06:09 -0600 (CST).200301071606.h07G68711510.h07G68711510@CopelandConsulting.Net.
Subject: Re: [HACKERS] Threads
From: Greg Copeland <greg@CopelandConsulting.Net>
To: shridhar_daithankar@persistent.co.in
cc: "<PGHackers" <pgsql-hackers@postgresql.org>
In-Reply-To: <3E1AD65D.10112.192793@localhost>
References: <3E19B78B.25689.15BFFE@localhost>
  <3E1AD65D.10112.192793@localhost>
Content-Type: text/plain
Organization: Copeland Computer Consulting
Message-ID: <1041955572.17639.148.camel@mouse.copelandconsulting.net>
MIME-Version: 1.0
X-Mailer: Ximian Evolution 1.2.0 
Date: 07 Jan 2003 10:06:12 -0600
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

On Tue, 2003-01-07 at 02:00, Shridhar Daithankar wrote:
> On 6 Jan 2003 at 6:48, Greg Copeland wrote:
> > > 1) Get I/O time used fuitfully
> > AIO may address this without the need for integrated threading. 
> > Arguably, from the long thread that last appeared on the topic of AIO,
> > some hold that AIO doesn't even offer anything beyond the current
> > implementation.  As such, it's highly doubtful that integrated threading
> > is going to offer anything beyond what a sound AIO implementation can
> > achieve.
> 
> Either way, a complete aio or threading implementation is not available on 
> major platforms that postgresql runs. Linux definitely does not have one, last 
> I checked.
> 

There are two or three significant AIO implementation efforts currently
underway for Linux.  One such implementation is available from the Red
Hat Server Edition (IIRC) and has been available for some time now.  I
believe Oracle is using it.  SGI also has an effort and I forget where
the other one comes from.  Nonetheless, I believe it's going to be a
hard fought battle to get AIO implemented simply because I don't think
anyone, yet, can truly argue a case on the gain vs effort.

> If postgresql is not using aio or threading, we should start using one of them, 
> is what I feel. What do you say?
> 

I did originally say that I'd like to see an AIO implementation.  Then
again, I don't current have a position to stand other than simply saying
it *might* perform better.  ;)  Not exactly a position that's going to
win the masses over.  

> > was expecting something that we could actually pull the trigger with.
> 
> That could be done.
> 

I'm sure it can, but that's probably the easiest item to address.

> > 
> > o Code isn't very portable.  Looked fairly okay for pthread platforms,
> > however, there is new emphasis on the Win32 platform.  I think it would
> > be a mistake to introduce something as significant as threading without
> > addressing Win32 from the get-go.
> 
> If you search for "pthread" in thread.c, there are not many instances. Same 
> goes for thread.h. From what I understand windows threading, it would be less 
> than 10 minutes job to #ifdef the pthread related part on either file.
> 
> It is just that I have not played with windows threading and nor I am inclined 
> to...;-)
> 

Well, the method above is going to create a semi-ugly mess.  I've
written thread abstraction layers which cover OS/2, NT, and pthreads. 
Each have subtle distinction.  What really needs to be done is the
creation of another abstraction layer which your current code would sit
on top of.  That way, everything contained within is clear and easy to
read.  The big bonus is that as additional threading implementations
need to be added, only the "low-level" abstraction stuff needs to
modified.  Done properly, each thread implementation would be it's own
module requiring little #if clutter.

As you can see, that's a fair amount of work and far from where the code
currently is.

> > 
> > o I would desire a more highly abstracted/portable interface which
> > allows for different threading and synchronization primitives to be
> > used.  Current implementation is tightly coupled to pthreads. 
> > Furthermore, on platforms such as Solaris, I would hope it would easily
> > allow for plugging in its native threading primitives which are touted
> > to be much more efficient than pthreads on said platform.
> 
> Same as above. If there can be two cases separated with #ifdef, there can be 
> more.. But what is important is to have a thread that can be woken up as and 
> when required with any function desired. That is the basic idea.
> 

Again, there's a lot of work in creating a well formed abstraction layer
for all of the mechanics that are required.  Furthermore, different
thread implementations have slightly different semantics which further
complicates things.  Worse, some types of primitives are simply not
available with some thread implementations.  That means those platforms
require it to be written from the primitives that are available on the
platform.  Yet more work.


> > o Code is fairly trivial and does not address other primitives
> > (semaphores, mutexs, conditions, TSS, etc) portably which would be
> > required for anything but the most trivial of threaded work.  This is
> > especially true in such an application where data IS the application. 
> > As such, you must reasonably assume that threads need some form of
> > portable serialization primitives, not to mention mechanisms for
> > non-trivial communication.
> 
> I don't get this. Probably I should post a working example. It is not threads 
> responsibility to make a function thread safe which is changed on the fly. The 
> function has to make sure that it is thread safe. That is altogether different 
> effort..


You're right, it's not the thread's responsibility, however, it is the
threading toolkit's.  In this case, you're offering to be the toolkit
which functions across two platforms, just for starters.  Reasonably,
you should expect a third to quickly follow.

>  
> > o Does not address issues such as thread signaling or status reporting.
> 
> >From what I learnt from pthreads on linux, I would not mix threads and signals. 
> One can easily add code in runner function that disables any signals for thread 
> while the thread starts running. This would leave original signal handling 
> mechanism in place.
> 
> As far as status reporting is concerned, the thread sould be initiated while 
> back-end starts and terminated with backend termination. What is about status 
> reporting?
>  
> > o Pool interface is rather simplistic.  Does not currently support
> > concepts such as wake pool, stop pool, pool status, assigning a pool to
> > work, etc.  In fact, it's not altogether obvious what the capabilities
> > intent is of the current pool implementation.
> 
> Could you please elaborate? I am using same interface in c++ for a server 
> application and never faced a problem like that..;-)
> 
>  
> > o Doesn't seem to address any form of thread communication facilities
> > (mailboxes, queues, etc).
> 
> Not part of this abstraction of threading mechanism. Intentionally left out to 
> keep things clean.
> 
> > There are probably other things that I can find if I spend more than
> > just a couple of minutes looking at the code.  Honestly, I love threads
> > but I can see that the current code offering is not much more than a
> > token in its current form.  No offense meant.
> 
> None taken. Point is it is useful and that is enough for me. If you could 
> elaborate examples for any problems you see, I can probably modify it. (Code 
> documentation is what I will do now)
> 
> > After it's all said and done, I'd have to see a lot more meat before I'd
> > be convinced that threading is ready for PostgreSQL; from both a social
> > and technological perspective.
> 
> Tell me about it..
> 

Long story short, if PostgreSQL is to use threads, it shouldn't be
handicapped by having a very limited subset of functionality.  With the
code that has been currently submitted, I don't believe you could even 
effectively implement a parallel sort.

To get an idea of the types of things that would be needed, check out
the ACE Toolkit.  There are a couple of other fairly popular toolkits as
well.  Nonetheless, it's a significant effort and the current code is a
long ways off from being usable.


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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

From pgsql-hackers-owner+M33944@postgresql.org Tue Jan  7 13:22:04 2003
Return-path: <pgsql-hackers-owner+M33944@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h07IM2X05350
	for <pgman@candle.pha.pa.us>; Tue, 7 Jan 2003 13:22:02 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 544EF476AC1; Tue,  7 Jan 2003 13:22:05 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 341134761E8; Tue,  7 Jan 2003 13:21:55 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 48974475ADE
	for <pgsql-hackers@postgresql.org>; Tue,  7 Jan 2003 13:21:50 -0500 (EST)
Received: from sabre.velocet.net (sabre.velocet.net [216.138.209.205])
	by postgresql.org (Postfix) with ESMTP id B8D40475AD7
	for <pgsql-hackers@postgresql.org>; Tue,  7 Jan 2003 13:21:49 -0500 (EST)
Received: from stark.dyndns.tv (H162.C233.tor.velocet.net [216.138.233.162])
	by sabre.velocet.net (Postfix) with ESMTP
	id 887681382B9; Tue,  7 Jan 2003 13:21:48 -0500 (EST)
Received: from localhost
	([127.0.0.1] helo=stark.dyndns.tv ident=foobar)
	by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian))
	id 18VyMF-0002zN-00; Tue, 07 Jan 2003 13:21:47 -0500
To: Greg Copeland <greg@CopelandConsulting.Net>
cc: kar@kakidata.dk, PGHackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Threads
References: <D90A5A6C612A39408103E6ECDD77B829408A20@voyager.corporate.connx.com>
	<23763.1041644042@sss.pgh.pa.us>
	<1041646276.15927.202.camel@mouse.copelandconsulting.net>
	<200301041359.35715.kar@kakidata.dk>
	<1041704480.15927.224.camel@mouse.copelandconsulting.net>
In-Reply-To: <1041704480.15927.224.camel@mouse.copelandconsulting.net>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 07 Jan 2003 13:21:47 -0500
Message-ID: <87isx0izwk.fsf@stark.dyndns.tv>
Lines: 43
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR


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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From pgsql-hackers-owner+M33945@postgresql.org Tue Jan  7 13:48:12 2003
Return-path: <pgsql-hackers-owner+M33945@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h07Im8X15155
	for <pgman@candle.pha.pa.us>; Tue, 7 Jan 2003 13:48:08 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id BF0454773D3; Tue,  7 Jan 2003 13:43:10 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 781634773A6; Tue,  7 Jan 2003 13:43:03 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 28074477390
	for <pgsql-hackers@postgresql.org>; Tue,  7 Jan 2003 13:42:59 -0500 (EST)
Received: from CopelandConsulting.Net (dsl-24293-ld.customer.centurytel.net [209.142.135.135])
	by postgresql.org (Postfix) with ESMTP id 1392B476682
	for <pgsql-hackers@postgresql.org>; Tue,  7 Jan 2003 13:42:42 -0500 (EST)
Received: from [192.168.1.2] (mouse.copelandconsulting.net [192.168.1.2])
	by CopelandConsulting.Net (8.10.1/8.10.1) with ESMTP id h07IgS715128;
	Tue, 7 Jan 2003 12:42:28 -0600 (CST)
X-Trade-Id: <CCC.Tue, 7 Jan 2003 12:42:28 -0600 (CST).Tue, 7 Jan 2003 12:42:28 -0600 (CST).200301071842.h07IgS715128.h07IgS715128@CopelandConsulting.Net.
Subject: Re: [HACKERS] Threads
From: Greg Copeland <greg@CopelandConsulting.Net>
To: Greg Stark <gsstark@mit.edu>
cc: kar@kakidata.dk, PGHackers <pgsql-hackers@postgresql.org>
In-Reply-To: <87isx0izwk.fsf@stark.dyndns.tv>
References:  <D90A5A6C612A39408103E6ECDD77B829408A20@voyager.corporate.connx.com>
  <23763.1041644042@sss.pgh.pa.us>
  <1041646276.15927.202.camel@mouse.copelandconsulting.net>
  <200301041359.35715.kar@kakidata.dk>
  <1041704480.15927.224.camel@mouse.copelandconsulting.net>
  <87isx0izwk.fsf@stark.dyndns.tv>
Content-Type: text/plain
Organization: Copeland Computer Consulting
Message-ID: <1041964952.29180.10.camel@mouse.copelandconsulting.net>
MIME-Version: 1.0
X-Mailer: Ximian Evolution 1.2.0 
Date: 07 Jan 2003 12:42:33 -0600
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

From pgsql-hackers-owner+M33946@postgresql.org Tue Jan  7 14:02:33 2003
Return-path: <pgsql-hackers-owner+M33946@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h07J2TX22478
	for <pgman@candle.pha.pa.us>; Tue, 7 Jan 2003 14:02:30 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 6A905477204; Tue,  7 Jan 2003 14:02:32 -0500 (EST)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 3546E476688; Tue,  7 Jan 2003 14:02:21 -0500 (EST)
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id E3CC44760BD
	for <pgsql-hackers@postgresql.org>; Tue,  7 Jan 2003 14:02:14 -0500 (EST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP id 3D8FA475AD7
	for <pgsql-hackers@postgresql.org>; Tue,  7 Jan 2003 14:02:14 -0500 (EST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h07J1s0U019750;
	Tue, 7 Jan 2003 14:01:54 -0500 (EST)
To: Greg Stark <gsstark@mit.edu>
cc: Greg Copeland <greg@CopelandConsulting.Net>, kar@kakidata.dk,
   PGHackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Threads 
In-Reply-To: <87isx0izwk.fsf@stark.dyndns.tv> 
References: <D90A5A6C612A39408103E6ECDD77B829408A20@voyager.corporate.connx.com> <23763.1041644042@sss.pgh.pa.us> <1041646276.15927.202.camel@mouse.copelandconsulting.net> <200301041359.35715.kar@kakidata.dk> <1041704480.15927.224.camel@mouse.copelandconsulting.net> <87isx0izwk.fsf@stark.dyndns.tv>
Comments: In-reply-to Greg Stark <gsstark@mit.edu>
	message dated "07 Jan 2003 13:21:47 -0500"
Date: Tue, 07 Jan 2003 14:01:53 -0500
Message-ID: <19749.1041966113@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
Status: OR

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

From pgsql-hackers-owner+M37860@postgresql.org Fri Apr 11 15:37:03 2003
Return-path: <pgsql-hackers-owner+M37860@postgresql.org>
Received: from relay3.pgsql.com (relay3.pgsql.com [64.117.224.149])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h3BJaxv13018
	for <pgman@candle.pha.pa.us>; Fri, 11 Apr 2003 15:37:01 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by relay3.pgsql.com (Postfix) with ESMTP
	id 3F9D0EA81E7; Fri, 11 Apr 2003 19:36:56 +0000 (GMT)
X-Original-To: pgsql-hackers@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id D27B2476036
	for <pgsql-hackers@postgresql.org>; Fri, 11 Apr 2003 15:35:32 -0400 (EDT)
Received: from mail1.ihs.com (mail1.ihs.com [170.207.70.222])
	by postgresql.org (Postfix) with ESMTP id 742DD475F5F
	for <pgsql-hackers@postgresql.org>; Fri, 11 Apr 2003 15:35:31 -0400 (EDT)
Received: from css120.ihs.com (css120.ihs.com [170.207.105.120])
	by mail1.ihs.com (8.12.9/8.12.9) with ESMTP id h3BJZHRF027332;
	Fri, 11 Apr 2003 13:35:17 -0600 (MDT)
Date: Fri, 11 Apr 2003 13:31:06 -0600 (MDT)
From: "scott.marlowe" <scott.marlowe@ihs.com>
To: Ron Peacetree <rjpeace@earthlink.net>
cc: <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Anyone working on better transaction locking?
In-Reply-To: <eS0la.16229$ey1.1398978@newsread1.prod.itd.earthlink.net>
Message-ID: <Pine.LNX.4.33.0304111314130.3232-100000@css120.ihs.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-MailScanner: Found to be clean
X-Spam-Status: No, hits=-31.5 required=5.0
	tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT,
       QUOTE_TWICE_1,REPLY_WITH_QUOTES,USER_AGENT_PINE
	autolearn=ham	version=2.50
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

On Wed, 9 Apr 2003, Ron Peacetree wrote:

> "Andrew Sullivan" <andrew@libertyrms.info> wrote in message
> news:20030409170926.GH2255@libertyrms.info...
> > On Wed, Apr 09, 2003 at 05:41:06AM +0000, Ron Peacetree wrote:
> > Nonsense.  You explicitly made the MVCC comparison with Oracle, and
> > are asking for a "better" locking mechanism without providing any
> > evidence that PostgreSQL's is bad.
> >
> Just because someone else's is "better" does not mean PostgreSQL's is
> "bad", and I've never said such.  As I've said, I'll get back to Tom
> and the list on this.

But you didn't identify HOW it was better.  I think that's the point 
being made.

> > > Please see my posts with regards to ...
> >
> > I think your other posts were similar to the one which started this
> > thread: full of mighty big pronouncements which turned out to depend
> > on a bunch of not-so-tenable assumptions.
> >
> Hmmm.  Well, I don't think of algorithm analysis by the likes of
> Knuth, Sedgewick, Gonnet, and Baeza-Yates as being "not so tenable
> assumptions", but YMMV.  As for "mighty pronouncements", that also
> seems a bit misleading since we are talking about quantifiable
> programming and computer science issues, not unquantifiable things
> like politics.

But the real truth is revealed when the rubber hits the pavement.  
Remember that Linux Torvalds was roundly criticized for his choice of a 
monolithic development model for his kernel, and was literally told that 
his choice would restrict to "toy" status and that no commercial OS could 
scale with a monolithic kernel.

There's no shortage of people with good ideas, just people with the skills 
to implement those good ideas.  If you've got a patch to apply that's been 
tested to show something is faster EVERYONE here wants to see it.

If you've got a theory, no matter how well backed up by academic research, 
it's still just a theory.  Until someone writes to code to implement it, 
the gains are theoretical, and many things that MIGHT help don't because 
of the real world issues underlying your database, like I/O bandwidth or 
CPU <-> memory bandwidth.

> > I'm sorry to be so cranky about this, but I get tired of having to
> > defend one of my employer's core technologies from accusations based
> > on half-truths and "everybody knows" assumptions.  For instance,
> >
> Again, "accusations" is a bit strong.  I thought the discussion was
> about the technical merits and costs of various features and various
> ways to implement them, particularly when this product must compete
> for installed base with other solutions.  Being coldly realistic about
> what a product's strengths and weaknesses are is, again, just good
> business.  Sun Tzu's comment about knowing the enemy and yourself
> seems appropriate here...

No, you're wrong.  Postgresql doesn't have to compete.  It doesn't have to 
win.  it doesn't need a marketing department.  All those things are nice, 
and I'm glad if it does them, but doesn't HAVE TO.  Postgresql has to 
work.  It does that well.

Postgresql CAN compete if someone wants to put the effort into competing, 
but it isn't a priority for me.  Working is the priority, and if other 
people aren't smart enough to test Postgresql to see if it works for them, 
all the better, I keep my edge by having a near zero cost database engine, 
while the competition spends money on MSSQL or Oracle.

Tom and Andrew ARE coldly realistic about the shortcomings of postgresql.  
It has issues, and things that need to be fixed.  It needs more coders.  
It doesn't need every feature that Oracle or DB2 have.  Heck some of their 
"features" would be considered a mis-feature in the Postgresql world.

> > > I'll mention thread support in passing,
> >
> > there's actually a FAQ item about thread support, because in the
> > opinion of those who have looked at it, the cost is just not worth
> > the benefit.  If you have evidence to the contrary (specific
> > evidence, please, for this application), and have already read all
> the
> > previous discussion of the topic, perhaps people would be interested
> in
> > opening that debate again (though I have my doubts).
> >
> Zeus had a performance ceiling roughly 3x that of Apache when Zeus
> supported threading as well as pre-forking and Apache only supported
> pre forking.  The Apache folks now support both.  DB2, Oracle, and SQL
> Server all use threads.  Etc, etc.

Yes, and if you configured your apache server to have 20 or 30 spare 
servers, in the real world, it was nearly neck and neck to Zeus, but since 
Zeus cost like $3,000 a copy, it is still cheaper to just overwhelm it 
with more servers running apache than to use zeus.

> That's an awful lot of very bright programmers and some serious $$
> voting that threads are worth it.  

For THAT application.  for what a web server does, threads can be very 
useful, even useful enough to put up with the problems created by running 
threads on multiple threading libs on different OSes.  

Let me ask you, if Zeus scrams and crashes out, and it's installed 
properly so it just comes right back up, how much data can you lose?

If Postgresql scrams and crashes out, how much data can you lost?

> Given all that, if PostgreSQL
> specific
> thread support is =not= showing itself to be a win that's an
> unexpected
> enough outcome that we should be asking hard questions as to why not.

There HAS been testing on threads in Postgresql.  It has been covered to 
death.  The fact that you're still arguing proves you likely haven't read 
the archive (google has it back to way back when, use that to look it up) 
about this subject.

Threads COULD help on multi-sorted results, and a few other areas, but the 
increase in performance really wasn't that great for 95% of all the cases, 
and for the 5% it was, simple query planner improvements have provided far 
greater performance increases.

The problem with threading is that we can either use the one process -> 
many thread design, which I personally don't trust for something like a 
database, or a process per backend connection which can run 
multi-threaded.  This scenario makes Postgresql just as stable and 
reliable as it was as a multi-process app, but allows threaded performance 
in certain areas of the backend that are parallelizable to run in parallel 
on multi-CPU systems.

the gain, again, is minimal, and on a system with many users accessing it, 
there is NO real world gain.

> At their core, threads are a context switching efficiency tweak.

Except that on the two OSes which Postgresql runs on the most, threads are 
really no faster than processes.  In the Linux kernel, the only real 
difference is how the OS treats them, creation, destruction of threads 
versus processes is virtually identical there.

> Certainly it's =possible= that threads have nothing to offer
> PostgreSQL, but IMHO it's not =probable=.  Just another thing for me
> to add to my TODO heap for looking at...

It's been tested, it didn't help a lot, and it made it MUCH harder to 
maintain, as threads in Linux are handled by a different lib than in say 
Solaris, or Windows or any other OS.  I.e. you can't guarantee the thread 
lib you need will be there, and that there are no bugs.  MySQL still has 
thread bug issues pop up, most of which are in the thread libs themselves.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

From pgsql-hackers-owner+M37865@postgresql.org Fri Apr 11 17:34:21 2003
Return-path: <pgsql-hackers-owner+M37865@postgresql.org>
Received: from relay1.pgsql.com (relay1.pgsql.com [64.49.215.129])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h3BLYIv28485
	for <pgman@candle.pha.pa.us>; Fri, 11 Apr 2003 17:34:19 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by relay1.pgsql.com (Postfix) with ESMTP
	id 0AF036F77ED; Fri, 11 Apr 2003 17:34:19 -0400 (EDT)
X-Original-To: pgsql-hackers@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id EBB41476323
	for <pgsql-hackers@postgresql.org>; Fri, 11 Apr 2003 17:33:02 -0400 (EDT)
Received: from filer (12-234-86-219.client.attbi.com [12.234.86.219])
	by postgresql.org (Postfix) with ESMTP id CED7D4762E1
	for <pgsql-hackers@postgresql.org>; Fri, 11 Apr 2003 17:32:57 -0400 (EDT)
Received: from localhost (localhost [127.0.0.1])
  (uid 1000)
  by filer with local; Fri, 11 Apr 2003 14:32:59 -0700
Date: Fri, 11 Apr 2003 14:32:59 -0700
From: Kevin Brown <kevin@sysexperts.com>
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Anyone working on better transaction locking?
Message-ID: <20030411213259.GU1833@filer>
Mail-Followup-To: Kevin Brown <kevin@sysexperts.com>,
	pgsql-hackers@postgresql.org
References: <20030409170926.GH2255@libertyrms.info> <eS0la.16229$ey1.1398978@newsread1.prod.itd.earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
In-Reply-To: <eS0la.16229$ey1.1398978@newsread1.prod.itd.earthlink.net>
User-Agent: Mutt/1.4i
Organization: Frobozzco International
X-Spam-Status: No, hits=-38.0 required=5.0
	tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT,
       REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_MUTT
	autolearn=ham	version=2.50
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

Ron Peacetree wrote:
> Zeus had a performance ceiling roughly 3x that of Apache when Zeus
> supported threading as well as pre-forking and Apache only supported
> pre forking.  The Apache folks now support both.  DB2, Oracle, and SQL
> Server all use threads.  Etc, etc.

You can't use Apache as an example of why you should thread a database
engine, except for the cases where the database is used much like the
web server is: for numerous short transactions.

> That's an awful lot of very bright programmers and some serious $$
> voting that threads are worth it.  Given all that, if PostgreSQL
> specific thread support is =not= showing itself to be a win that's
> an unexpected enough outcome that we should be asking hard questions
> as to why not.

It's not that there won't be any performance benefits to be had from
threading (there surely will, on some platforms), but gaining those
benefits comes at a very high development and maintenance cost.  You
lose a *lot* of robustness when all of your threads share the same
memory space, and make yourself vulnerable to classes of failures that
simply don't happen when you don't have shared memory space.

PostgreSQL is a compromise in this regard: it *does* share memory, but
it only shares memory that has to be shared, and nothing else.  To get
the benefits of full-fledged threads, though, requires that all memory
be shared (otherwise the OS has to tweak the page tables whenever it
switches contexts between your threads).

> At their core, threads are a context switching efficiency tweak.

This is the heart of the matter.  Context switching is an operating
system problem, and *that* is where the optimization belongs.  Threads
exist in large part because operating system vendors didn't bother to
do a good job of optimizing process context switching and
creation/destruction.

Under Linux, from what I've read, process creation/destruction and
context switching happens almost as fast as thread context switching
on other operating systems (Windows in particular, if I'm not
mistaken).

> Since DB's switch context a lot under many circumstances, threads
> should be a win under such circumstances.  At the least, it should be
> helpful in situations where we have multiple CPUs to split query
> execution between.

This is true, but I see little reason that we can't do the same thing
using fork()ed processes and shared memory instead.

There is context switching within databases, to be sure, but I think
you'll be hard pressed to demonstrate that it is anything more than an
insignificant fraction of the total overhead incurred by the database.
I strongly suspect that much larger gains are to be had by optimizing
other areas of the database, such as the planner, the storage manager
(using mmap for file handling may prove useful here), the shared
memory system (mmap may be faster than System V style shared memory),
etc.

The big overhead in the process model on most platforms is in creation
and destruction of processes.  PostgreSQL has a relatively high
connection startup cost.  But there are ways of dealing with this
problem other than threading, namely the use of a connection caching
middleware layer.  Such layers exist for databases other than
PostgreSQL, so the high cost of fielding and setting up a database
connection is *not* unique to PostgreSQL ... which suggests that while
threading may help, it doesn't help *enough*.

I'd rather see some development work go into a connection caching
process that understands the PostgreSQL wire protocol well enough to
look like a PostgreSQL backend to connecting processes, rather than
see a much larger amount of effort be spent on converting PostgreSQL
to a threaded architecture (and then discover that connection caching
is still needed anyway).

> Certainly it's =possible= that threads have nothing to offer
> PostgreSQL, but IMHO it's not =probable=.  Just another thing for me
> to add to my TODO heap for looking at...

It's not that threads don't have anything to offer.  It's that the
costs associated with them are high enough that it's not at all clear
that they're an overall win.


-- 
Kevin Brown					      kevin@sysexperts.com


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

From pgsql-hackers-owner+M37876@postgresql.org Sat Apr 12 06:56:17 2003
Return-path: <pgsql-hackers-owner+M37876@postgresql.org>
Received: from relay3.pgsql.com (relay3.pgsql.com [64.117.224.149])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h3CAuDS20700
	for <pgman@candle.pha.pa.us>; Sat, 12 Apr 2003 06:56:15 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by relay3.pgsql.com (Postfix) with ESMTP
	id 35797EA81FF; Sat, 12 Apr 2003 10:55:59 +0000 (GMT)
X-Original-To: pgsql-hackers@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 7393E4762EF
	for <pgsql-hackers@postgresql.org>; Sat, 12 Apr 2003 06:54:48 -0400 (EDT)
Received: from filer (12-234-86-219.client.attbi.com [12.234.86.219])
	by postgresql.org (Postfix) with ESMTP id 423294762E1
	for <pgsql-hackers@postgresql.org>; Sat, 12 Apr 2003 06:54:44 -0400 (EDT)
Received: from localhost (localhost [127.0.0.1])
  (uid 1000)
  by filer with local; Sat, 12 Apr 2003 03:54:52 -0700
Date: Sat, 12 Apr 2003 03:54:52 -0700
From: Kevin Brown <kevin@sysexperts.com>
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Anyone working on better transaction locking?
Message-ID: <20030412105452.GV1833@filer>
Mail-Followup-To: Kevin Brown <kevin@sysexperts.com>,
	pgsql-hackers@postgresql.org
References: <20030409170926.GH2255@libertyrms.info> <eS0la.16229$ey1.1398978@newsread1.prod.itd.earthlink.net> <20030411213259.GU1833@filer> <200304121221.12377.shridhar_daithankar@nospam.persistent.co.in>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
In-Reply-To: <200304121221.12377.shridhar_daithankar@nospam.persistent.co.in>
User-Agent: Mutt/1.4i
Organization: Frobozzco International
X-Spam-Status: No, hits=-39.4 required=5.0
	tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT,
       QUOTE_TWICE_1,REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_MUTT
	autolearn=ham	version=2.50
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

Shridhar Daithankar wrote:
> Apache does too many things to be a speed daemon and what it offers
> is pretty impressive from performance POV.
>
> But database is not webserver. It is not suppose to handle tons of
> concurrent requests. That is a fundamental difference.

I'm not sure I necessarily agree with this.  A database is just a
tool, a means of reliably storing information in such a way that it
can be retrieved quickly.  Whether or not it "should" handle lots of
concurrent requests is a question that the person trying to use it
must answer.

A better answer is that a database engine that can handle lots of
concurrent requests can also handle a smaller number, but not vice
versa.  So it's clearly an advantage to have a database engine that
can handle lots of concurrent requests because such an engine can be
applied to a larger number of problems.  That is, of course, assuming
that all other things are equal...

There are situations in which a database would have to handle a lot of
concurrent requests.  Handling ATM transactions over a large area is
one such situation.  A database with current weather information might
be another, if it is actively queried by clients all over the country.
Acting as a mail store for a large organization is another.  And, of
course, acting as a filesystem is definitely another.  :-)

> Well. Threading does not necessarily imply one thread per connection
> model. Threading can be used to make CPU work during I/O and taking
> advantage of SMP for things like sort etc. This is especially true
> for 2.4.x linux kernels where async I/O can not be used for threaded
> apps. as threads and signal do not mix together well.

This is true, but whether you choose to limit the use of threads to a
few specific situations or use them throughout the database, the
dangers and difficulties faced by the developers when using threads
will be the same.

> One connection per thread is not a good model for postgresql since
> it has already built a robust product around process paradigm. If I
> have to start a new database project today, a mix of process+thread
> is what I would choose bu postgresql is not in same stage of life.

Certainly there are situations for which it would be advantageous to
have multiple concurrent actions happening on behalf of a single
connection, as you say.  But that doesn't automatically mean that a
thread is the best overall solution.  On systems such as Linux that
have fast process handling, processes are almost certainly the way to
go.  On other systems such as Solaris or Windows, threads might be the
right answer (on Windows they might be the *only* answer).  But my
argument here is simple: the responsibility of optimizing process
handling belongs to the maintainers of the OS.  Application developers
shouldn't have to worry about this stuff.

Of course, back here in the real world they *do* have to worry about
this stuff, and that's why it's important to quantify the problem.
It's not sufficient to say that "processes are slow and threads are
fast".  Processes on the target platform may well be slow relative to
other systems (and relative to threads).  But the question is: for the
problem being solved, how much overhead does process handling
represent relative to the total amount of overhead the solution itself
incurs?

For instance, if we're talking about addressing the problem of
distributing sorts across multiple CPUs, the amount of overhead
involved in doing disk activity while sorting could easily swamp, in
the typical case, the overhead involved in creating parallel processes
to do the sorts themselves.  And if that's the case, you may as well
gain the benefits of using full-fledged processes rather than deal
with the problems that come with the use of threads -- because the
gains to be found by using threads will be small in relative terms.

> > > At their core, threads are a context switching efficiency tweak.
> >
> > This is the heart of the matter.  Context switching is an operating
> > system problem, and *that* is where the optimization belongs.  Threads
> > exist in large part because operating system vendors didn't bother to
> > do a good job of optimizing process context switching and
> > creation/destruction.
> 
> But why would a database need a tons of context switches if it is
> not supposed to service loads to request simaltenously? If there are
> 50 concurrent connections, how much context switching overhead is
> involved regardless of amount of work done in a single connection? 
> Remeber that database state is maintened in shared memory. It does
> not take a context switch to access it.

If there are 50 concurrent connections with one process per
connection, then there are 50 database processes.  The context switch
overhead is incurred whenever the current process blocks (or exhausts
its time slice) and the OS activates a different process.  Since
database handling is generally rather I/O intensive as services go,
relatively few of those 50 processes are likely to be in a runnable
state, so I would expect the overall hit from context switching to be
rather low -- I'd expect the I/O subsystem to fall over well before
context switching became a real issue.

Of course, all of that is independent of whether or not the database
can handle a lot of simultaneous requests.

> > Under Linux, from what I've read, process creation/destruction and
> > context switching happens almost as fast as thread context switching
> > on other operating systems (Windows in particular, if I'm not
> > mistaken).
> 
> I hear solaris also has very heavy processes. But postgresql has
> other issues with solaris as well.

Yeah, I didn't want to mention Solaris because I haven't kept up with
it and thought that perhaps they had fixed this...


-- 
Kevin Brown					      kevin@sysexperts.com


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From pgsql-hackers-owner+M37883@postgresql.org Sat Apr 12 16:09:19 2003
Return-path: <pgsql-hackers-owner+M37883@postgresql.org>
Received: from relay1.pgsql.com (relay1.pgsql.com [64.49.215.129])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id h3CK9HS03520
	for <pgman@candle.pha.pa.us>; Sat, 12 Apr 2003 16:09:18 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by relay1.pgsql.com (Postfix) with ESMTP
	id 507626F768B; Sat, 12 Apr 2003 16:09:01 -0400 (EDT)
X-Original-To: pgsql-hackers@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 06543475AE4
	for <pgsql-hackers@postgresql.org>; Sat, 12 Apr 2003 16:08:03 -0400 (EDT)
Received: from mail.gmx.net (mail.gmx.net [213.165.65.60])
	by postgresql.org (Postfix) with SMTP id C6DC347580B
	for <pgsql-hackers@postgresql.org>; Sat, 12 Apr 2003 16:08:01 -0400 (EDT)
Received: (qmail 31386 invoked by uid 65534); 12 Apr 2003 20:08:13 -0000
Received: from chello062178186201.1.15.tuwien.teleweb.at (EHLO beeblebrox) (62.178.186.201)
  by mail.gmx.net (mp001-rz3) with SMTP; 12 Apr 2003 22:08:13 +0200
Message-ID: <01cc01c3012f$526aaf80$3201a8c0@beeblebrox>
From: "Michael Paesold" <mpaesold@gmx.at>
To: "Neil Conway" <neilc@samurai.com>, "Kevin Brown" <kevin@sysexperts.com>
cc: "PostgreSQL Hackers" <pgsql-hackers@postgresql.org>
References: <20030409170926.GH2255@libertyrms.info> <eS0la.16229$ey1.1398978@newsread1.prod.itd.earthlink.net> <20030411213259.GU1833@filer> <1050175777.392.13.camel@tokyo>
Subject: Re: [HACKERS] Anyone working on better transaction locking?
Date: Sat, 12 Apr 2003 22:08:40 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="Windows-1252"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Spam-Status: No, hits=-25.8 required=5.0
	tests=BAYES_20,EMAIL_ATTRIBUTION,QUOTED_EMAIL_TEXT,REFERENCES,
       REPLY_WITH_QUOTES
	autolearn=ham	version=2.50
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

Neil Conway wrote:

> Furthermore, IIRC PostgreSQL's relatively slow connection creation time
> has as much to do with other per-backend initialization work as it does
> with the time to actually fork() a new backend. If there is interest in
> optimizing backend startup time, my guess would be that there is plenty
> of room for improvement without requiring the replacement of processes
> with threads.

I see there is a whole TODO Chapter devoted to the topic. There is the idea
of pre-forked and persistent backends. That would be very useful in an
environment where it's quite hard to use connection pooling. We are
currently working on a mail system for a free webmail. The mda (mail
delivery agent) written in C connects to the pg database to do some queries
everytime a new mail comes in. I didn't find a solution for connection
pooling yet.

About the TODO items, apache has a nice description of their accept()
serialization:
http://httpd.apache.org/docs-2.0/misc/perf-tuning.html

Perhaps this could be useful if someone decided to start implementing those
features.

Regards,
Michael Paesold


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org