With the freshly retrieved current source, now PostgreSQL is running
fine on an AIX 5L box. Thanks Tom.

BTW, I have done some benchmarking using pgbench on this machine and
found that 7.2 is almost two times slower than 7.1. The hardware is a
4way machine. Since I thought that 7.2 improves the performance for
SMP machines, I'm now wondering why 7.2 is so slow.

postgresql.conf paramters changed from default values are:

max_connections = 1024
wal_sync_method = fdatasync
shared_buffers = 4096
deadlock_timeout = 1000000

configure option is: --enable-multibyte=EUC_JP

Of cousre, these setting are identical for both 7.1 and 7.2.

See attached graph...
Mon, 17 Dec 2001 15:46:37 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
7.2 is slow?
> With the freshly retrieved current source, now PostgreSQL is running
> fine on an AIX 5L box. Thanks Tom.
> 
> BTW, I have done some benchmarking using pgbench on this machine and
> found that 7.2 is almost two times slower than 7.1. The hardware is a
> 4way machine. Since I thought that 7.2 improves the performance for
  ^^^^
> SMP machines, I'm now wondering why 7.2 is so slow.

Ewe.  I will remind people that this multi-cpu setup is exactly the type
of machine we wanted to speed up with the new light-weight locking code
that reduced spinlock looping.

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

Mon, 17 Dec 2001 02:40:42 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 7.2 is slow?
Tatsuo Ishii wrote:
> 
> With the freshly retrieved current source, now PostgreSQL is running
> fine on an AIX 5L box. Thanks Tom.
> 
> BTW, I have done some benchmarking using pgbench on this machine and
> found that 7.2 is almost two times slower than 7.1.

Is this an AIX specific problem or do all/all SMP/all 4way computers 
have it ?

Is this a bug that needs to be addresse before release of final ?

Or would we just prominently warn people that the new release is 2x 
slower and advise upgrading only if they have powerful enough computers 
(system load < 0.5 during normal operation)?

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

Mon, 17 Dec 2001 10:54:14 +0200
Hannu Krosing <hannu@tm.ee>
Re: 7.2 is slow?
> > BTW, I have done some benchmarking using pgbench on this machine and
> > found that 7.2 is almost two times slower than 7.1.
> 
> Is this an AIX specific problem or do all/all SMP/all 4way computers 
> have it ?

Not sure. As far as I can tell, nobody except me has tested 7.2 on big
boxes.

> Is this a bug that needs to be addresse before release of final ?

I hope this would be solved before final. At least I would like to
know what's going on.

Anyway, I will do some testings on a smaller machine (that is my
laptop) to see if I see the same performance degration on it.
--
Tatsuo Ishii

Mon, 17 Dec 2001 18:26:44 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: 7.2 is slow?
Tatsuo Ishii wrote:
> 
> > > BTW, I have done some benchmarking using pgbench on this machine and
> > > found that 7.2 is almost two times slower than 7.1.
> >
> > Is this an AIX specific problem or do all/all SMP/all 4way computers
> > have it ?
> 
> Not sure. As far as I can tell, nobody except me has tested 7.2 on big
> boxes.
> 
> > Is this a bug that needs to be addresse before release of final ?
> 
> I hope this would be solved before final. At least I would like to
> know what's going on.
> 
> Anyway, I will do some testings on a smaller machine (that is my
> laptop) to see if I see the same performance degration on it.

How did you test ?

I could do the same test on Dual Pentium III / 800 w/1024 MB
with IBM 45 G/7200 IDE disk.

So we could compare different platforms as well :)

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

Mon, 17 Dec 2001 12:43:05 +0200
Hannu Krosing <hannu@tm.ee>
Re: 7.2 is slow?
On Mon, Dec 17, 2001 at 12:43:05PM +0200, Hannu Krosing allegedly wrote:
> Tatsuo Ishii wrote:
> > 
> > > > BTW, I have done some benchmarking using pgbench on this machine and
> > > > found that 7.2 is almost two times slower than 7.1.
> > >
> > > Is this an AIX specific problem or do all/all SMP/all 4way computers
> > > have it ?
> > 
> > Not sure. As far as I can tell, nobody except me has tested 7.2 on big
> > boxes.
> > 
> > > Is this a bug that needs to be addresse before release of final ?
> > 
> > I hope this would be solved before final. At least I would like to
> > know what's going on.
> > 
> > Anyway, I will do some testings on a smaller machine (that is my
> > laptop) to see if I see the same performance degration on it.
> 
> How did you test ?
> 
> I could do the same test on Dual Pentium III / 800 w/1024 MB
> with IBM 45 G/7200 IDE disk.
> 
> So we could compare different platforms as well :)

I could do some testing on a Sun 450 / 4x400 MHz / 4 GB, if that's helpful.

Cheers,

Mathijs

Mon, 17 Dec 2001 13:30:02 +0100
Mathijs Brands <mathijs@ilse.nl>
Re: 7.2 is slow?
> > > > Is this an AIX specific problem or do all/all SMP/all 4way computers
> > > > have it ?

I'll have 4 way and 8 way xeon boxes tues evening that I can test this
against (though I won't get to test till wed unless I don't sleep)

- Brandon

----------------------------------------------------------------------------
 c: 646-456-5455                                            h: 201-798-4983
 b. palmer,  bpalmer@crimelabs.net           pgp:crimelabs.net/bpalmer.pgp5


Mon, 17 Dec 2001 08:06:54 -0500 (EST)
bpalmer <bpalmer@crimelabs.net>
Re: 7.2 is slow?
> > How did you test ?
> > 
> > I could do the same test on Dual Pentium III / 800 w/1024 MB
> > with IBM 45 G/7200 IDE disk.
> > 
> > So we could compare different platforms as well :)
> 
> I could do some testing on a Sun 450 / 4x400 MHz / 4 GB, if that's helpful.
> 
> Cheers,
> 
> Mathijs
> 

> I'll have 4 way and 8 way xeon boxes tues evening that I can test this
> against (though I won't get to test till wed unless I don't sleep)
>
> - Brandon

Thanks to everyone. Here are the methods I used for testings including
generating graphs (actually very simple).

(1) Tweak postgresql.conf to allow large concurrent users. I tested up
    to 1024 on AIX, but for the comparison I think testing up to 128
    users is enough. Here are example settings:

    max_connections = 128
    shared_buffers = 4096
    deadlock_timeout = 100000

    You might want to tweak wal_sync_method to get the best
    performance. However this should not affect the comparison between
    7.1 and 7.2.

(2) Run:

    sh bench.sh

    It will invoke pgbench for various concurrent users. So you need
    to install pgbench beforehand (it's in contrib/pgbench. Just type
    make install there to install pgbench).

    This will take while.

(3) (2) will generate a file named "bench.data". The file have rows
        where the first column is the number of concurrent users and
        second one is the tps. Rename it to bench-7.2.data.

(4) Do (1) and (2) for PostgreSQL 7.1 and rename bench.data to
    bench-7.1.data.

(5) Run plot.sh to see the result graph. Note that plot.sh requires
    gnuplot.
---
Tatsuo Ishii
Mon, 17 Dec 2001 23:12:53 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: 7.2 is slow?

It seems that on dual PIII we are indeed faster than 7.1.3 for 
small number of clients but slower for large number (~ 40)

My initial results on dual PIII/800 are as follows

                                      7.1.3      7.2b4  7.2b4-FULL
==================================================================
./pgbench -i -p 5433 
./pgbench -p 5433 -c  1 -t 100       240/251    217/223    177/181
./pgbench -p 5433 -c  5 -t 100        93/ 94    211/217    207/212
./pgbench -p 5433 -c 10 -t 100        57/ 58    145/148    160/163
------------------------------------------------------------------
./pgbench -i -s 10 -p 5433 
./pgbench -p 5433 -c   1 -t 100      171/177    162/166    169/173
./pgbench -p 5433 -c   5 -t 100      140/143    191/196    202/207
./pgbench -p 5433 -c  10 -t 100      132/135    165/168    159/163
./pgbench -p 5433 -c  25 -t 100       65/ 66     60/ 60     75/ 76
./pgbench -p 5433 -c  50 -t 100       60/ 61     43/ 43     55/ 59
./pgbench -p 5433 -c 100 -t 100       48/ 48     23/ 23     34/ 34
------------------------------------------------------------------

One of thereasons seems to be that vacuum has chaged

after oding 

psql -p 5433 -c 'vacuum full'

the result of

./pgbench -p 5433 -c 100 -t 100

was 34/34 - still ~25% slower than 7.1.3 but much better 
than with non-full vacuum (which I guess is used by pgbench

The third column 7.2b4-FULL is done by running 
  "psql -p 5433 -c 'vacuum full'"
between each pgbench run - now the lines cross somwhere 
between 25 and 50 concurrent users

One of the reasons pg is slower on last limes of my test is that 
postgres is slower when vacuum is not done often enough - 
on fresh db
"./pgbench -p 5433 -c 100 -t 10"  gives   67/75 as result
indicating that one reason is just our non-overwriting storage manager.


I also tried to outsmart pg by running the new vacuum 
concurrently, but was disappointed.

vacuuming in 'normal' psql gave me 20/20 tps and running 
with nice psql gave 21/21 tps
running ./pgbench -p 5433 -c 100 -t 100 as first benchmark gave the 
same result as running it after vacuum full


-----------------------------------------------------------------------
PS. I hope to get single-processor results from the same computer in 
about 6 hours as well (after my co-worker arrives home and can reboot 
his computer to single-user)

Inxc - after you have rebooted to single-processor mode, pleas start 
the postgres daemon by

su - hannu
cd db/7.2b4/
bin/pg_ctl -D data -l logfile

and ther run above pgbench commands from 
cd /home/hannu/src/postgresql-7.1.3/contrib/pgbench/
-----------------------------------------------------------------------

Mon, 17 Dec 2001 17:18:18 +0200
Hannu Krosing <hannu@tm.ee>
Re: 7.2 is slow?
Tatsuo Ishii wrote:
> 
> 
> Thanks to everyone. Here are the methods I used for testings including
> generating graphs (actually very simple).
> 
> (1) Tweak postgresql.conf to allow large concurrent users. I tested up
>     to 1024 on AIX, but for the comparison I think testing up to 128
>     users is enough. Here are example settings:
> 
>     max_connections = 128
>     shared_buffers = 4096
>     deadlock_timeout = 100000
> 
>     You might want to tweak wal_sync_method to get the best
>     performance. However this should not affect the comparison between
>     7.1 and 7.2.
>
> (2) Run:
> 
>     sh bench.sh

I have no more time today, but I'll redo the tests with your script
tomorrow
(after I have found where to stick database name and port :)

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

Mon, 17 Dec 2001 17:37:16 +0200
Hannu Krosing <hannu@tm.ee>
Re: 7.2 is slow?
Hannu Krosing <hannu@tm.ee> writes:
> ./pgbench -i -s 10 -p 5433 
> ./pgbench -p 5433 -c   1 -t 100      171/177    162/166    169/173
> ./pgbench -p 5433 -c   5 -t 100      140/143    191/196    202/207
> ./pgbench -p 5433 -c  10 -t 100      132/135    165/168    159/163
> ./pgbench -p 5433 -c  25 -t 100       65/ 66     60/ 60     75/ 76
> ./pgbench -p 5433 -c  50 -t 100       60/ 61     43/ 43     55/ 59
> ./pgbench -p 5433 -c 100 -t 100       48/ 48     23/ 23     34/ 34

You realize, of course, that when the number of clients exceeds the
scale factor you're not really measuring anything except update
contention on the "branch" rows?  Every transaction tries to update
the balance for its branch, so if you have more clients than branches
then there will be lots of transactions blocked waiting for someone
else to commit.  With a 10:1 ratio, there will be several transactions
blocked waiting for *each* active transaction; and when that guy
commits, all the others will waken simultaneously and contend for the
chance to update the branch row.  One will win, the others will go
back to sleep, having done nothing except wasting CPU time.  Thus a
severe falloff in measured TPS is inevitable when -c >> -s.  I don't
think this scenario has all that much to do with real-world loads,
however.

I think you are right that the difference between 7.1 and 7.2 may have
more to do with the change in VACUUM strategy than anything else.  Could
you retry the test after changing all the "vacuum" commands in pgbench.c
to "vacuum full"?

			regards, tom lane

Mon, 17 Dec 2001 10:53:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.2 is slow? 
Tom Lane wrote:
> 
> Hannu Krosing <hannu@tm.ee> writes:
> > ./pgbench -i -s 10 -p 5433
> > ./pgbench -p 5433 -c   1 -t 100      171/177    162/166    169/173
> > ./pgbench -p 5433 -c   5 -t 100      140/143    191/196    202/207
> > ./pgbench -p 5433 -c  10 -t 100      132/135    165/168    159/163
> > ./pgbench -p 5433 -c  25 -t 100       65/ 66     60/ 60     75/ 76
> > ./pgbench -p 5433 -c  50 -t 100       60/ 61     43/ 43     55/ 59
> > ./pgbench -p 5433 -c 100 -t 100       48/ 48     23/ 23     34/ 34
> 
> You realize, of course, that when the number of clients exceeds the
> scale factor you're not really measuring anything except update
> contention on the "branch" rows? 

Oops! I thought that the deciding table would be tellers and this -s 10 
would be ok for up to 100 users

I will retry this with Tatsuos using -s 128(if it still fits on disk 
- taking about 160MB/1Mtuple needs 1.6GB for test with -s 100 and 
I currently have only 1.3G free)

I re-run some of them with -s 50  (on 7.2b4)

each one after running "psql -p 5433 -c 'vacuum full;checkpoint;'"

                                      tps 
./pgbench -p 5433 -i -s 50
./pgbench -p 5433 -c   1 -t 1000     93/ 93
./pgbench -p 5433 -c   3 -t  333    106/107
./pgbench -p 5433 -c   5 -t  200    106/107
./pgbench -p 5433 -c   8 -t  125    112/113
./pgbench -p 5433 -c  10 -t  100     94/ 95
./pgbench -p 5433 -c  25 -t   40     98/ 91
./pgbench -p 5433 -c  50 -t   20     70/ 74

> Every transaction tries to update
> the balance for its branch, so if you have more clients than branches
> then there will be lots of transactions blocked waiting for someone
> else to commit.  With a 10:1 ratio, there will be several transactions
> blocked waiting for *each* active transaction; and when that guy
> commits, all the others will waken simultaneously and contend for the
> chance to update the branch row.  One will win, the others will go
> back to sleep, having done nothing except wasting CPU time.  Thus a
> severe falloff in measured TPS is inevitable when -c >> -s.  I don't
> think this scenario has all that much to do with real-world loads,
> however.

It probably models a real-world ill-tuned database :)

And it seems that we fall off more rapidly on 7.2 than we did on 7.1 , 
even so much so that we will be slower in the end.

> I think you are right that the difference between 7.1 and 7.2 may have
> more to do with the change in VACUUM strategy than anything else.  Could
> you retry the test after changing all the "vacuum" commands in pgbench.c
> to "vacuum full"?

The third column should be the equivalent of doing so (I did run 
'vacuum full' between each pgbench and AFACT pgbencg runs vacuun only 
before each run)

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

Mon, 17 Dec 2001 18:57:03 +0200
Hannu Krosing <hannu@tm.ee>
Re: 7.2 is slow?
Tom Lane wrote:
> 
> Hannu Krosing <hannu@tm.ee> writes:
> > ./pgbench -i -s 10 -p 5433
> > ./pgbench -p 5433 -c   1 -t 100      171/177    162/166    169/173
> > ./pgbench -p 5433 -c   5 -t 100      140/143    191/196    202/207
> > ./pgbench -p 5433 -c  10 -t 100      132/135    165/168    159/163
> > ./pgbench -p 5433 -c  25 -t 100       65/ 66     60/ 60     75/ 76
> > ./pgbench -p 5433 -c  50 -t 100       60/ 61     43/ 43     55/ 59
> > ./pgbench -p 5433 -c 100 -t 100       48/ 48     23/ 23     34/ 34
> 
> You realize, of course, that when the number of clients exceeds the
> scale factor you're not really measuring anything except update
> contention on the "branch" rows?  Every transaction tries to update
> the balance for its branch, so if you have more clients than branches
> then there will be lots of transactions blocked waiting for someone
> else to commit.  With a 10:1 ratio, there will be several transactions
> blocked waiting for *each* active transaction; and when that guy
> commits, all the others will waken simultaneously and contend for the
> chance to update the branch row.  One will win, the others will go
> back to sleep, having done nothing except wasting CPU time.  Thus a
> severe falloff in measured TPS is inevitable when -c >> -s.  I don't
> think this scenario has all that much to do with real-world loads,
> however.

I did some benchmarking and the interesting part is that 7.2b4 is up to 
2.5X faster than 7.1.3 for _small_ scale factors and up to 25% slower 
when there is no contention (-s128, clients <= 128)

Perhaps the waiting on lock somehow organizes things to happen in some 
order that avoids some stupidity in some other locking logic ?

I run benchmark (with added vacuum full for 7.2b4) on Dual PIII 800MHz 
with 1 G of RAM and an IDE disk. The results are mean from six runs 
with two slowes removed (there was other activity going on sometimes)

they are for scale factors 1, 10 and 128 

in order to measure real performance of roughly the _same_ dataset each
test run did the same total number of transactions 512 with each client 
doing 512/nr_of_trx.
Fri, 21 Dec 2001 14:21:12 +0200
Hannu Krosing <hannu@tm.ee>
Re: 7.2 is slow?
Hannu Krosing <hannu@tm.ee> writes:
> in order to measure real performance of roughly the _same_ dataset each
> test run did the same total number of transactions 512 with each client 
> doing 512/nr_of_trx.

That means you're only measuring a few transactions per backend (as few
as 4, near the upper end of the scale).  I think the results may say
more about backend-startup transients than true peak throughput.
Could you try it again with a run about ten times that long?

			regards, tom lane

Fri, 21 Dec 2001 11:00:01 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.2 is slow? 


Tom Lane wrote:

>Hannu Krosing <hannu@tm.ee> writes:
>
>>in order to measure real performance of roughly the _same_ dataset each
>>test run did the same total number of transactions 512 with each client 
>>doing 512/nr_of_trx.
>>
>
>That means you're only measuring a few transactions per backend (as few
>as 4, near the upper end of the scale).  I think the results may say
>more about backend-startup transients than true peak throughput.
>Could you try it again with a run about ten times that long?
>
I did run 4096trx on 7.2b4 with  -s 1, best 4-of-6

    512trx 4096trx ratio
1    180.59    90.15    2.00
2    221.52    80.92    2.74
4    203.72    75.60    2.69
8    179.54    69.29    2.59
16    156.68    63.15    2.48
32    123.48    57.73    2.14
64    89.99    54.14    1.66
128    61.84    48.97    1.26

so it seems that large number of of transactions degrades tps 
performance faster than
connection setup overhead.

the

I'll try running the whole suite again with higher number of 
transactions i  a few days

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



Sat, 22 Dec 2001 02:31:12 +0500
Hannu Krosing <hannu@tm.ee>
Re: 7.2 is slow?