Dear Tom,

	The evidence is from the Process Activity Recorder, an Irix utility
similar to strace the reports syscall usage. A number of semop's are performed
in the operation of backend. Luis can send you specifics. --Bob

Luis Alberto Amigo Navarro writes:
> 
> 
> ----- Original Message -----
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
> To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
> Cc: <pgsql-hackers@postgresql.org>; "Robert E. Bruccoleri"
> <bruc@stone.congenomics.com>
> Sent: Monday, March 18, 2002 5:36 PM
> Subject: Re: Fw: [HACKERS] bad performance on irix
> 
> 
> > "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es> forwards:
> > >> It's using the spinlocks
> > >> for some locks, but semaphores for others.
> >
> > That doesn't make any sense to me.  For one thing, if HAS_TEST_AND_SET
> > is defined in the config header, the executable will just plain fail to
> > build if there's no tas implementation, because lmgr/spin.c won't be
> > compiled.  And I sure don't see how some of the locks might be
> > implemented one way and some the other.
> >
> > Which ones do you think are being implemented as semaphores, and what's
> > your evidence?
> >
> > regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
> 
> 

+-----------------------------+------------------------------------+
| Robert E. Bruccoleri, Ph.D. | email: bruc@acm.org                |
| P.O. Box 314                | URL:   http://www.congen.com/~bruc |
| Pennington, NJ 08534        |                                    |
+-----------------------------+------------------------------------+

Mon, 18 Mar 2002 16:04:02 -0500 (EST)
"Robert E. Bruccoleri" <bruc@stone.congenomics.com>
Re: Fw: Fw: bad performance on irix
Hi all
There is no doubt, in fact it uses test_and _set, but it is still doing a
lot of semops, I send u and extract from another execution, it is 6 streams
of read-only queries+a stream of inserts and deletes(with 5 sec between each
stream)+a stream of vacuum on modified tables each 10 secs.
Thanks and regards
Wed, 20 Mar 2002 12:53:01 +0100
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Re: Fw: Fw: bad performance on irix
Hi all:
again on performance, here is an extract from an 8 read-only queries, notice
that total time is 179s and it is expending about 80secs only in semaphores
Isn't there any other way to improve ipc-locks?
thanks and regards.


Wed, 20 Mar 2002 17:48:09 +0100
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Re: Fw: Fw: bad performance on irix
Makes me wonder... perhaps now someone will be convinced to take a look
at the POSIX IPC patch. On some platforms (not on Linux I am afraid)
POSIX mutexes might be quite a bit faster than SYSV semaphores.

Luis Alberto Amigo Navarro wrote:
> 
> Hi all:
> again on performance, here is an extract from an 8 read-only queries, notice
> that total time is 179s and it is expending about 80secs only in semaphores
> Isn't there any other way to improve ipc-locks?
> thanks and regards.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

Wed, 20 Mar 2002 14:33:52 -0600
Igor Kovalenko <Igor.Kovalenko@motorola.com>
Re: Fw: Fw: bad performance on irix
Dear Igor,

Igor Kovalenko writes:

> Makes me wonder... perhaps now someone will be convinced to take a look
> at the POSIX IPC patch. On some platforms (not on Linux I am afraid)
> POSIX mutexes might be quite a bit faster than SYSV semaphores.

Yes, but on the SGI platform, the MIPS test_and_set instructions are
really fast and should be used.

+-----------------------------+------------------------------------+
| Robert E. Bruccoleri, Ph.D. | email: bruc@acm.org                |
| P.O. Box 314                | URL:   http://www.congen.com/~bruc |
| Pennington, NJ 08534        |                                    |
+-----------------------------+------------------------------------+

Wed, 20 Mar 2002 16:52:57 -0500 (EST)
"Robert E. Bruccoleri" <bruc@stone.congenomics.com>
Re: Fw: Fw: bad performance on irix
I am confused to hell. I always thought MIPS does NOT have TAS
instruction ;)

"Robert E. Bruccoleri" wrote:
> 
> Dear Igor,
> 
> Igor Kovalenko writes:
> 
> > Makes me wonder... perhaps now someone will be convinced to take a look
> > at the POSIX IPC patch. On some platforms (not on Linux I am afraid)
> > POSIX mutexes might be quite a bit faster than SYSV semaphores.
> 
> Yes, but on the SGI platform, the MIPS test_and_set instructions are
> really fast and should be used.
> 
> +-----------------------------+------------------------------------+
> | Robert E. Bruccoleri, Ph.D. | email: bruc@acm.org                |
> | P.O. Box 314                | URL:   http://www.congen.com/~bruc |
> | Pennington, NJ 08534        |                                    |
> +-----------------------------+------------------------------------+

Wed, 20 Mar 2002 16:18:57 -0600
Igor Kovalenko <Igor.Kovalenko@motorola.com>
Re: Fw: Fw: bad performance on irix
Dear Igor,

> I am confused to hell. I always thought MIPS does NOT have TAS
> instruction ;)

On the SGI platform, there are very high speed implementations of test
and set which allow large number of processes to safely and quickly
access shared memory. SGI has a hardware team that specifies MIPS
processor variants that are used in their servers so the machines can
scale.

I've tried to get SGI interested in putting some internal engineering
effort to improve PostgreSQL performance on operations which could
benefit from its shared memory parallel architecture (like index
creation and sorting), but without success.

+-----------------------------+------------------------------------+
| Robert E. Bruccoleri, Ph.D. | email: bruc@acm.org                |
| P.O. Box 314                | URL:   http://www.congen.com/~bruc |
| Pennington, NJ 08534        |                                    |
+-----------------------------+------------------------------------+

Wed, 20 Mar 2002 17:30:28 -0500 (EST)
"Robert E. Bruccoleri" <bruc@stone.congenomics.com>
Re: Fw: Fw: bad performance on irix
Okay. Anyway, the semaphores are apparently used for purposes other than
TAS. That can be made faster too, on platforms which support POSIX
mutexes (shared between processes).

"Robert E. Bruccoleri" wrote:
> 
> Dear Igor,
> 
> > I am confused to hell. I always thought MIPS does NOT have TAS
> > instruction ;)
> 
> On the SGI platform, there are very high speed implementations of test
> and set which allow large number of processes to safely and quickly
> access shared memory. SGI has a hardware team that specifies MIPS
> processor variants that are used in their servers so the machines can
> scale.
> 
> I've tried to get SGI interested in putting some internal engineering
> effort to improve PostgreSQL performance on operations which could
> benefit from its shared memory parallel architecture (like index
> creation and sorting), but without success.
> 
> +-----------------------------+------------------------------------+
> | Robert E. Bruccoleri, Ph.D. | email: bruc@acm.org                |
> | P.O. Box 314                | URL:   http://www.congen.com/~bruc |
> | Pennington, NJ 08534        |                                    |
> +-----------------------------+------------------------------------+

Wed, 20 Mar 2002 16:32:38 -0600
Igor Kovalenko <Igor.Kovalenko@motorola.com>
Re: Fw: Fw: bad performance on irix


> Makes me wonder... perhaps now someone will be convinced to take a look
> at the POSIX IPC patch. On some platforms (not on Linux I am afraid)
> POSIX mutexes might be quite a bit faster than SYSV semaphores.
> 
Is there any current patch?
Regards


Thu, 21 Mar 2002 09:35:02 +0100
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Re: Fw: Fw: bad performance on irix
I've done some meditions with timex, it uses sar(System activity register)
to take workloads, it's not very relliable, but it allow us to see how it is
been doing,  it has been taken during an execution of a like tpc-h
benchmark, it performs inserts, deletes(about 5% of the time of the
execution) and a set of 8 continous streams of 22 read only queries, notice
that it only gives idle time (not the cause of idle), notice semafores/sec
is up to 2700!!!!!!!
Regards
12:27:08  %usr  %sys %intr  %wio %idle %sbrk  %wfs %wswp %wphy %wgsw %wfif
12:55:39    32     3         0         9    56         0       100     0
0     0     0                                    9% waiting for I/O which is
100% file system

12:27:08     device %busy  avque  r+w/s  blks/s    w/s wblks/s  avwait
avserv
12:55:39     dks0d5     0    0.0    0.0       0    0.0       0     0.0
0.0
                 dks1d1     1    3.1    0.7      19    0.7      16    27.8
15.0
                 dks1d2     0    1.0    0.0       0    0.0       0     0.0
13.3
                 dks1d3     0    0.0    0.0       0    0.0       0     0.0
0.0
                 dks1d4    23   15.3    9.1    1705    7.8    1553   519.7
24.8

12:27:08 bread/s lread/s %rcach bwrit/s lwrit/s wcncl/s %wcach pread/s
pwrit/s
12:55:39     158    2372     93    1549    9072       1     83       0
0                    93% of read cache hits and 83% of write chache hits

12:27:08     scall/s sread/s swrit/s  fork/s  exec/s rchar/s wchar/s
12:55:39    4618     181     126    0.18    0.06  648854  580354
syscalls averages

12:27:08   msg/s  sema/s
12:55:39    0.00 2704.28

12:27:08  vflt/s         dfill/s cache/s pgswp/s pgfil/s  pflt/s  cpyw/s
steal/s rclm/s        notice that there aren't page swaps, so idle is not
waiting for paging
12:55:39  862.58   58.31  804.24    0.00    0.04    5.70    3.11   60.90
0.00

12:27:08    CPU  %usr  %sys %intr %wio %idle %sbrk  %wfs %wswp %wphy %wgsw
%wfif
12:55:39     0        25     3     0     8    63     0   100     0     0
0     0                                                  per  CPU usage
                  1        25     3     0     9    62     0   100     0
0     0     0
                 2        24     3     0     9    64     0   100     0     0
0     0
                 3        30     3     0     8    59     0   100     0     0
0     0
                 4        30     3     0     8    59     0   100     0     0
0     0
                 5        39     3     0     8    50     0   100     0     0
0     0
                 6        54     3     0     8    34     0   100     0     0
0     0
                 7        33     3     0     8    55     0   100     0     0
0     0



Thu, 21 Mar 2002 09:48:11 +0100
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Re: Fw: Fw: bad performance on irix
No, I've been told it is not gonna be considered for 7.2x and I shall
wait till 7.3.

Luis Alberto Amigo Navarro wrote:
> 
> > Makes me wonder... perhaps now someone will be convinced to take a look
> > at the POSIX IPC patch. On some platforms (not on Linux I am afraid)
> > POSIX mutexes might be quite a bit faster than SYSV semaphores.
> >
> Is there any current patch?
> Regards

Thu, 21 Mar 2002 11:31:12 -0600
Igor Kovalenko <Igor.Kovalenko@motorola.com>
Re: Fw: Fw: bad performance on irix
Igor Kovalenko wrote:

> No, I've been told it is not gonna be considered for 7.2x and I shall
> wait till 7.3.
>
> Luis Alberto Amigo Navarro wrote:
> >
> > > Makes me wonder... perhaps now someone will be convinced to take a look
> > > at the POSIX IPC patch. On some platforms (not on Linux I am afraid)
> > > POSIX mutexes might be quite a bit faster than SYSV semaphores.
> > >
> > Is there any current patch?
> > Regards
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

I've been thinking, and I think it maybe possible that tuning kernel
parameters could help, I'll keep you informed
Thanks and regards
Thu, 21 Mar 2002 19:30:31 +0100
Luis Amigo <lamigo@atc.unican.es>
Re: Fw: Fw: bad performance on irix
Just remember that patches for 7.3 are being accepted at this very moment...

Chris

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org 
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Igor Kovalenko
> Sent: Friday, 22 March 2002 1:31 AM
> To: Luis Alberto Amigo Navarro
> Cc: bruc@acm.org; tgl@sss.pgh.pa.us; pgsql-hackers@postgresql.org
> Subject: Re: Fw: Fw: [HACKERS] bad performance on irix
> 
> 
> No, I've been told it is not gonna be considered for 7.2x and I shall
> wait till 7.3.
> 
> Luis Alberto Amigo Navarro wrote:
> > 
> > > Makes me wonder... perhaps now someone will be convinced to 
> take a look
> > > at the POSIX IPC patch. On some platforms (not on Linux I am afraid)
> > > POSIX mutexes might be quite a bit faster than SYSV semaphores.
> > >
> > Is there any current patch?
> > Regards
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

Fri, 22 Mar 2002 09:59:57 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Fw: Fw: bad performance on irix
On a side note, I thought I would mention that the Next Generation POSIX
Threading (NGPT) Project (IBM --
http://www-124.ibm.com/developerworks/projects/pthreads) patches have
just been accepted to the 2.5.x Linux kernel.  A 2.4.x patch is also
available.  So, it may be possible that POSIX mutexes may be a
performance reality for Linux sometime in the near future...

Greg


On Thu, 2002-03-21 at 19:59, Christopher Kings-Lynne wrote:
> Just remember that patches for 7.3 are being accepted at this very moment...
> 
> Chris
> 
> > -----Original Message-----
> > From: pgsql-hackers-owner@postgresql.org 
> > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Igor Kovalenko
> > Sent: Friday, 22 March 2002 1:31 AM
> > To: Luis Alberto Amigo Navarro
> > Cc: bruc@acm.org; tgl@sss.pgh.pa.us; pgsql-hackers@postgresql.org
> > Subject: Re: Fw: Fw: [HACKERS] bad performance on irix
> > 
> > 
> > No, I've been told it is not gonna be considered for 7.2x and I shall
> > wait till 7.3.
> > 
> > Luis Alberto Amigo Navarro wrote:
> > > 
> > > > Makes me wonder... perhaps now someone will be convinced to 
> > take a look
> > > > at the POSIX IPC patch. On some platforms (not on Linux I am afraid)
> > > > POSIX mutexes might be quite a bit faster than SYSV semaphores.
> > > >
> > > Is there any current patch?
> > > Regards
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> > 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
22 Mar 2002 10:51:32 -0600
Greg Copeland <greg@CopelandConsulting.Net>
Re: Fw: Fw: bad performance on irix
Does that mean I should redo patch for 7.3 as is, or you guys want it to
go farther this time? The last version had compromises intended to make
changes minimal...

Also, does anyone from Darwin or BeOS camp care? You guys should not be
working through emulation of SysV ugliness. If someone is listening, we
could come up with a version suitable for you too...

-- igor

Christopher Kings-Lynne wrote:
> 
> Just remember that patches for 7.3 are being accepted at this very moment...
> 
> Chris
> 
> > -----Original Message-----
> > From: pgsql-hackers-owner@postgresql.org
> > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Igor Kovalenko
> > Sent: Friday, 22 March 2002 1:31 AM
> > To: Luis Alberto Amigo Navarro
> > Cc: bruc@acm.org; tgl@sss.pgh.pa.us; pgsql-hackers@postgresql.org
> > Subject: Re: Fw: Fw: [HACKERS] bad performance on irix
> >
> >
> > No, I've been told it is not gonna be considered for 7.2x and I shall
> > wait till 7.3.
> >
> > Luis Alberto Amigo Navarro wrote:
> > >
> > > > Makes me wonder... perhaps now someone will be convinced to
> > take a look
> > > > at the POSIX IPC patch. On some platforms (not on Linux I am afraid)
> > > > POSIX mutexes might be quite a bit faster than SYSV semaphores.
> > > >
> > > Is there any current patch?
> > > Regards
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >

Fri, 22 Mar 2002 11:46:03 -0600
Igor Kovalenko <Igor.Kovalenko@motorola.com>
Re: Fw: Fw: bad performance on irix
Igor Kovalenko wrote:
> Does that mean I should redo patch for 7.3 as is, or you guys want it to
> go farther this time? The last version had compromises intended to make
> changes minimal...
> 
> Also, does anyone from Darwin or BeOS camp care? You guys should not be
> working through emulation of SysV ugliness. If someone is listening, we
> could come up with a version suitable for you too...

Yes, we should get started.  I think the idea is to have two patches,
one for QNX6 and another to support Posix capabilities.  The changes
don't have to be minimal anymore.  :-)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 22 Mar 2002 13:32:32 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Fw: Fw: bad performance on irix