Hi,

I have made a new version of pgbench which allows not to update
branches and tellers tables, which should significantly reduce the
contentions.  (See attached patches against current. Note that the
paches also includes changes removing CHECKPOINT command while
running in initialization mode (pgbench -i)). With the patches you
could specify -N option not to update branches and tellers tables.

With the new pgbench, I ran a test with current and 7.1 and saw
not-so-small differences. Any idea to get better performance on 7.2
and AIX 5L combo?

7.2 with lwlock.patch rev.2
7.1.3
AIX 5L 4way with 4GB RAM
testing script is same as my previous postings (except -N for pgbench,
of course).
Wed, 09 Jan 2002 10:35:36 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
7.1 vs. 7.2 on AIX 5L 
Tatsuo Ishii wrote:
> 
> Hi,
> 
> I have made a new version of pgbench which allows not to update
> branches and tellers tables, which should significantly reduce the
> contentions.  (See attached patches against current. Note that the
> paches also includes changes removing CHECKPOINT command while
> running in initialization mode (pgbench -i)). With the patches you
> could specify -N option not to update branches and tellers tables.
> 
> With the new pgbench, I ran a test with current and 7.1 and saw
> not-so-small differences. Any idea to get better performance on 7.2
> and AIX 5L combo?
> 
> 7.2 with lwlock.patch rev.2
> 7.1.3
> AIX 5L 4way with 4GB RAM
> testing script is same as my previous postings (except -N for pgbench,
> of course).
> 
>   ------------------------------------------------------------------------
>                     Name: pgbench.patch
>    pgbench.patch    Type: Plain Text (Text/Plain)
>                 Encoding: 7bit
> 
>                         Name: result-Jan-09.png
>    result-Jan-09.png    Type: PNG Image (image/png)
>                     Encoding: base64

Could you add some labels to lines as Tom did ?

We can only guess which line is which.

--------------
Hannu

Wed, 09 Jan 2002 12:03:06 +0200
Hannu Krosing <hannu@tm.ee>
Re: 7.1 vs. 7.2 on AIX 5L
> Could you add some labels to lines as Tom did ?
> 
> We can only guess which line is which.

I thought I already added labels. 7.1 is "+"(green one), and 7.2 is
"rhombus"(red one).
--
Tatsuo Ishii

Wed, 09 Jan 2002 21:25:15 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: 7.1 vs. 7.2 on AIX 5L
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> I have made a new version of pgbench which allows not to update
> branches and tellers tables, which should significantly reduce the
> contentions.

I used this version of pgbench in some fresh runs on RedHat's 4-way SMP
Linux box.  I did several test runs under varying conditions (pgbench
scale 500 or 50, checkpoint_segments/wal_files either default 3/0 or
30/5, fsync on or off).  I compared current CVS tip (including the
now-committed lwlock rev 2 patch) to 7.1.3.  The results are attached.
As you can see, current beats 7.1 pretty much across the board on that
hardware.  The reason seems to be revealed by looking at vmstat output.
Typical "vmstat 5" output for 7.1.3 (here in a 6-client pgbench -N
run) is

   procs                      memory    swap          io     system         cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
 1  0  0      0 108444   8920 4917092   0   0   213     0  170  4814   0   1  99
 1  0  0      0 103592   8948 4921912   0   0   234   357  230  4811   1   1  98
 0  0  0      0  98776   8968 4926704   0   0   233   428  235  4854   1   1  97
 0  0  0      0  94300   8980 4931168   0   0   216   423  229  4809   1   2  97
 0  0  0      0  89960   8984 4935504   0   0   209   771  421  4723   2   2  96
 0  0  0      0  69280   9016 4956140   0   0   205   842  457  4645   1   2  96

The system is capable of much greater I/O rates, so neither disks nor
CPUs are exactly exerting themselves here.  In contrast, 7.2 shows:

  procs                      memory    swap          io     system         cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
 2  0  0      0 2927344   9148 1969356   0   0     0  5772  102 13753  61  32   7
 7  0  0      0 3042272   9148 1969716   0   0     0  2267 2400 14083  58  32  10
 5  0  0      0 3042168   9148 1970100   0   0     0  2734 1028 12994  53  37  11

I think that 7.1's poor showing here is undoubtedly due to the spinlock
backoff algorithm it used --- there is no other way to explain 99% idle
CPU than that all of the backends are caught in 10-msec select() waits.

> With the new pgbench, I ran a test with current and 7.1 and saw
> not-so-small differences. Any idea to get better performance on 7.2
> and AIX 5L combo?

I'm thinking more and more that there must be something weird about the
cs() routine that we use for spinlocks on AIX.  Could someone dig into
that and find exactly what it does and whether it's got any performance
issues?

			regards, tom lane
Thu, 10 Jan 2002 12:43:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.1 vs. 7.2 on AIX 5L 
Tom Lane wrote:
> Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> > I have made a new version of pgbench which allows not to update
> > branches and tellers tables, which should significantly reduce the
> > contentions.
> 
> I used this version of pgbench in some fresh runs on RedHat's 4-way SMP
> Linux box.  I did several test runs under varying conditions (pgbench
> scale 500 or 50, checkpoint_segments/wal_files either default 3/0 or
> 30/5, fsync on or off).  I compared current CVS tip (including the
> now-committed lwlock rev 2 patch) to 7.1.3.  The results are attached.
> As you can see, current beats 7.1 pretty much across the board on that
> hardware.  The reason seems to be revealed by looking at vmstat output.
> Typical "vmstat 5" output for 7.1.3 (here in a 6-client pgbench -N
> run) is

Those are dramatic graphs.  Is it the WAL increase that made 7.2 much
faster?

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

Thu, 10 Jan 2002 15:08:26 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 7.1 vs. 7.2 on AIX 5L
Tom,

Can I use the fourth graph (scale=50, fsync=on) to show how 7.2 could
outperform 7.1 on SMP boxes? I'm going to make a presentation at 
Net&Com 2002 (http://expo.nikkeibp.co.jp/netcom/web/e/index.html) the
day after tomorrow.
--
Tatsuo Ishii

> I used this version of pgbench in some fresh runs on RedHat's 4-way SMP
> Linux box.  I did several test runs under varying conditions (pgbench
> scale 500 or 50, checkpoint_segments/wal_files either default 3/0 or
> 30/5, fsync on or off).  I compared current CVS tip (including the
> now-committed lwlock rev 2 patch) to 7.1.3.  The results are attached.
> As you can see, current beats 7.1 pretty much across the board on that
> hardware.  The reason seems to be revealed by looking at vmstat output.
> Typical "vmstat 5" output for 7.1.3 (here in a 6-client pgbench -N
> run) is
> 
>    procs                      memory    swap          io     system         cpu
>  r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
>  1  0  0      0 108444   8920 4917092   0   0   213     0  170  4814   0   1  99
>  1  0  0      0 103592   8948 4921912   0   0   234   357  230  4811   1   1  98
>  0  0  0      0  98776   8968 4926704   0   0   233   428  235  4854   1   1  97
>  0  0  0      0  94300   8980 4931168   0   0   216   423  229  4809   1   2  97
>  0  0  0      0  89960   8984 4935504   0   0   209   771  421  4723   2   2  96
>  0  0  0      0  69280   9016 4956140   0   0   205   842  457  4645   1   2  96
> 
> The system is capable of much greater I/O rates, so neither disks nor
> CPUs are exactly exerting themselves here.  In contrast, 7.2 shows:
> 
>   procs                      memory    swap          io     system         cpu
>  r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
>  2  0  0      0 2927344   9148 1969356   0   0     0  5772  102 13753  61  32   7
>  7  0  0      0 3042272   9148 1969716   0   0     0  2267 2400 14083  58  32  10
>  5  0  0      0 3042168   9148 1970100   0   0     0  2734 1028 12994  53  37  11
> 
> I think that 7.1's poor showing here is undoubtedly due to the spinlock
> backoff algorithm it used --- there is no other way to explain 99% idle
> CPU than that all of the backends are caught in 10-msec select() waits.
> 
> > With the new pgbench, I ran a test with current and 7.1 and saw
> > not-so-small differences. Any idea to get better performance on 7.2
> > and AIX 5L combo?
> 
> I'm thinking more and more that there must be something weird about the
> cs() routine that we use for spinlocks on AIX.  Could someone dig into
> that and find exactly what it does and whether it's got any performance
> issues?
> 
> 			regards, tom lane
> 

Tue, 05 Feb 2002 14:01:00 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: 7.1 vs. 7.2 on AIX 5L 