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