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

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

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

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

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

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

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

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

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

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

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

I haven't tested with the new 7.2 betas, but here are some results from
7.1.

We have a developement computer, IBM x series 250, with 4 processors
(PIII Xeon 750Mhz), 1 Gb memory and 2SCSI disks (u160).

The software is writing new rows to a table, and after this it reads the
id from that row. There are currently about 50 connections doing the
same thing.

When I run this test with the Redhat 7.1 SMP kernel, I noticed, that the
processors are more than 90% idle. Disks utilisation is not the
bottleneck either, since there is very low disk usage. Some data is
written to disks every 4-5 seconds. Fsync is turned of. In transactions,
this means about 200 inserted rows per second. The software that is used
to give the feed, is capable of several thousand rows per second.

Okey, so I tried this also with the same computer, but using the not SMP
supported kernel. So only with one processor. The result was about 600
rows per second. The configuration file was unchanged. Now, the
processor is about 100% utilized.

I didn't find any parameters that should help in this, but if you have a
version of 7.2 that you would like to get information about, let me
know, so I'll test.

Jussi

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

Might there also be a difference in chosen query plans ?
Wasn't 7.1 more willing to choose an index over seq scan,
even though the scan would be faster in the single user case ?
Or was that change after 7.0 ?

The seq scan would be slower that the index in the case of
many concurrent accesses.

Andreas

I haven't tested with the new 7.2 betas, but here are some results from
7.1.

We have a developement computer, IBM x series 250, with 4 processors
(PIII Xeon 750Mhz), 1 Gb memory and 2SCSI disks (u160).

The software is writing new rows to a table, and after this it reads the
id from that row. There are currently about 50 connections doing the
same thing.

When I run this test with the Redhat 7.1, with SMP kernel, I noticed, that
the
processors are more than 90% idle. Disks utilisation is not the
bottleneck either, since there is very low disk usage. Some data is
written to disks every 4-5 seconds. Fsync is turned of. In transactions,
this means about 200 inserted rows per second. The software that is used
to give the feed, is capable of several thousand rows per second.

Okey, so I tried this also with the same computer, but using the not SMP
supported kernel. So only with one processor. The result was about 600
rows per second. The configuration file was unchanged. Now, the
processor is about 100% utilized.

I didn't find any parameters that should help in this, but if you have a
version of 7.2 that you would like to get information about, let me
know, so I'll test.

Jussi

Zeugswetter Andreas SB SD wrote:

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

Might there also be a difference in chosen query plans ?
Wasn't 7.1 more willing to choose an index over seq scan,
even though the scan would be faster in the single user case ?
Or was that change after 7.0 ?

The seq scan would be slower that the index in the case of
many concurrent accesses.

Andreas

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

--
Jussi Mikkola Project Manager
Bonware Oy gsm +358 40 830 7561
Tekniikantie 12 tel +358 9 2517 5570
02150 Espoo fax +358 9 2517 5571
Finland www.bonware.com

I haven't tested with the new 7.2 betas, but here are some results from
7.1.

We have a developement computer, IBM x series 250, with 4 processors
(PIII Xeon 750Mhz), 1 Gb memory and 2SCSI disks (u160).

The software is writing new rows to a table, and after this it reads the
id from that row. There are currently about 50 connections doing the
same thing.

When I run this test with the Redhat 7.1, with SMP kernel, I noticed, that
the
processors are more than 90% idle. Disks utilisation is not the
bottleneck either, since there is very low disk usage. Some data is
written to disks every 4-5 seconds. Fsync is turned of. In transactions,
this means about 200 inserted rows per second. The software that is used
to give the feed, is capable of several thousand rows per second.

Okey, so I tried this also with the same computer, but using the not SMP
supported kernel. So only with one processor. The result was about 600
rows per second. The configuration file was unchanged. Now, the
processor is about 100% utilized.

I didn't find any parameters that should help in this, but if you have a
version of 7.2 that you would like to get information about, let me
know, so I'll test.

Yes! This sleeping case is the problem we expected to see on SMP
machines in >= 7.1 because of lock contention and a select() that can't
sleep for less than 1/100 second. Please try the current 7.2 snapshot
and let us know what performance you get.

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

Hi !

Yes, now I've tested with 7.2b4. The result is about the same as with 7.1.

About 200 messages with four processors and about 600 messages with one
processor.

Jussi

Yes! This sleeping case is the problem we expected to see on SMP
machines in >= 7.1 because of lock contention and a select() that can't
sleep for less than 1/100 second. Please try the current 7.2 snapshot
and let us know what performance you get.

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

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

--
Jussi Mikkola Project Manager
Bonware Oy gsm +358 40 830 7561
Tekniikantie 12 tel +358 9 2517 5570
02150 Espoo fax +358 9 2517 5571
Finland www.bonware.com

"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:

Might there also be a difference in chosen query plans ?

If so, it'd affect the results across-the-board, but AFAICT
Tatsuo is seeing comparable results for small numbers of clients.

regards, tom lane

Hi All,

I have experienced similar problems after moving my main server from UP
(PII 300 box) to SMP (PII400 box). I remeber someone said look at the
iostat figures for the different runs, but I haven't had time to check
it out.

Out of curosity, what does iostat say when run in SMP vs UP?

Ashley Cambrell

Jussi Mikkola wrote:

We're getting similar problem.
We're currently working on TPC-H benchmarking using postgresql 7.2b3.

From one up to 8 paralell conexions (we've got 8 MIPS processors), uptime

increases from 1 to 8,
but increasing above 8 makes performance drop rapidly to uptimes even lower
than 2 for 22 conexions.
As we've could trail, when we have more processes than processors, we're
getting an increasing number of collisions.
when collision happens both processes get idle for a while, then collision may
happen again and so...
Regards
Luis Amigo
Universidad de Cantabria