7.4b1 vs 7.3.4 performance

On Tue, 19 Aug 2003 12:47:38 -0400
"P.J. \"Josh\" Rovero" <rovero@sonalysts.com> wrote:

Ran sets of 150 pgbench runs, with clients (1, 2, 4, 8, 16) and

Where does pgbench come from?

On Tue, 19 Aug 2003 12:47:38 -0400
"P.J. \"Josh\" Rovero" <rovero@sonalysts.com> wrote:

Ran sets of 150 pgbench runs, with clients (1, 2, 4, 8, 16) and
transactions (5 each at 125, 250, 500, 1000, 2000, 4000),
then parsed and plotted with gnuplot. Breaks between number
of clients are at run index 31, 61, 91, etc. A vacuum
full was performed at each change of number of clients.
Graph attached.

Machine was a quad xeon, two separate database spaces
on a 3 disk software RAID-0 array. Config file parameters
(buffers, sort mem, logging, etc) were the same.

What were the config settings?

expect wrote:

Where does pgbench come from?

pgbench is one of the modules in the postgresql
contrib directory. After building postgresql,
cd to contrib/pgbench, gmake, su to root,
gmake install.

What were the config settings?

Both 7.3.4 and 7.4 had:

3072 buffers
4096K sort memory
16384K vacuum memory
fsync false
16 WAL buffers
statistics on

"P.J. \"Josh\" Rovero" <rovero@sonalysts.com> writes:

7.4b1 is significantly faster (i.e., the higher curve)
over this range of clients and transactions.

Cool. I wonder though why the 7.4 curve is so much noisier.

regards, tom lane

Tom Lane wrote:

"P.J. \"Josh\" Rovero" <rovero@sonalysts.com> writes:

7.4b1 is significantly faster (i.e., the higher curve)
over this range of clients and transactions.

Cool. I wonder though why the 7.4 curve is so much noisier.

The occurence of postgresql events (WAL recycles, etc) and
system events (reiserfs journal writes, software raid0) isn't
synchronized for each pgbench run. So one or more of the set
of 5 runs for each number of clients/transactions combination
may "suffer" statistically because the backend or system is
performing some other task. The only other user apps
running were "tail -f" on the postgresql and pgbench
logs.

Here is a set (clients (1:1:10), transactions (100:100:1000))
where a vacuum full was done prior to each set of 5 runs.
7.4 is still significantly faster, even though results
are noisier and "degradation" as number of transactions
per client increases is steeper.

On Wed, 20 Aug 2003 13:13:38 -0400
rovero <rovero@sonalysts.com> wrote:

expect wrote:

Where does pgbench come from?

pgbench is one of the modules in the postgresql
contrib directory. After building postgresql,
cd to contrib/pgbench, gmake, su to root,
gmake install.

Thank you.

What were the config settings?

Both 7.3.4 and 7.4 had:

3072 buffers
4096K sort memory
16384K vacuum memory
fsync false
16 WAL buffers
statistics on

I don't know a lot about TPC but I understand it's meant to mimic
multiple teller xactions and that's what you did. This is not a
request mind you but it would be interesting to see how changing
those configs would alter the performance behavior.

On Wednesday 20 August 2003 22:43, rovero wrote:

expect wrote:

Where does pgbench come from?

pgbench is one of the modules in the postgresql
contrib directory. After building postgresql,
cd to contrib/pgbench, gmake, su to root,
gmake install.

What were the config settings?

Both 7.3.4 and 7.4 had:

3072 buffers
4096K sort memory
16384K vacuum memory

Since you weren't running any huge sorts thr. pgbench and vacuum, canyou drop
last two to half of these and check?

fsync false
16 WAL buffers
statistics on

I would put more WAL buffers. 32-64 maybe..

Shridhar

On Thu, 21 Aug 2003 12:55:35 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in> wrote:

On Wednesday 20 August 2003 22:43, rovero wrote:

expect wrote:

Where does pgbench come from?

pgbench is one of the modules in the postgresql
contrib directory. After building postgresql,
cd to contrib/pgbench, gmake, su to root,
gmake install.

What were the config settings?

Both 7.3.4 and 7.4 had:

3072 buffers
4096K sort memory
16384K vacuum memory

Since you weren't running any huge sorts thr. pgbench and vacuum, canyou drop
last two to half of these and check?

Just trying to learn a little more about pg perf. behavior....
Are you suggesting that with less memory to "manage" that pg
will run more efficiently?

fsync false
16 WAL buffers
statistics on

I would put more WAL buffers. 32-64 maybe..

It would be interesting to see 10 or 20 plateaus for that number or
at least up to the point of diminishing returns for the load that's applied.
Or maybe it wouldn't? Maybe it scales.

On Thu, 21 Aug 2003 06:53:42 -0400
rovero <rovero@sonalysts.com> wrote:

expect wrote:

I don't know a lot about TPC but I understand it's meant to mimic
multiple teller xactions and that's what you did. This is not a
request mind you but it would be interesting to see how changing
those configs would alter the performance behavior.

Actually, those config settings were chosen in much earlier
(pgsql 7.1, 7.2) rounds of pgbench testing.

I see, certainly makes comparisons much simpler between versions.

There is no substitute for testing with the real
applications you will be running, but that can get
very complicated. Tools like pgbench and osdb
are still useful in determining good ranges for
the config parameters.

Sure, agreed.

On 21 Aug 2003 at 8:22, expect wrote:

On Thu, 21 Aug 2003 12:55:35 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in> wrote:

Both 7.3.4 and 7.4 had:

3072 buffers
4096K sort memory
16384K vacuum memory

Since you weren't running any huge sorts thr. pgbench and vacuum, canyou drop
last two to half of these and check?

Just trying to learn a little more about pg perf. behavior....
Are you suggesting that with less memory to "manage" that pg
will run more efficiently?

Yes. If that is not required, bumping them unnecessarily could have unwanted
side effects at times.

fsync false
16 WAL buffers
statistics on

I would put more WAL buffers. 32-64 maybe..

It would be interesting to see 10 or 20 plateaus for that number or
at least up to the point of diminishing returns for the load that's applied.
Or maybe it wouldn't? Maybe it scales.

WAL is where are transactions are channelled. Pgbench is heavy on transactions
I understand. So if you are trying to tight-loop pgbench for exceedingly large
number of transactions, WAL will be hit like anything and likely become
bottleneck.

If you have bit bigger WAL buffers, then the bottleneck would be slightly eases
but but still it will eb limited by disk IO bandwidth.

Having too many smalll transactions too rapidly can not deliver performance
unless you have proportionate disk bandwidth. You need to size them
appropriately if possible.

HTH
Bye
Shridhar

--
It is more rational to sacrifice one life than six. -- Spock, "The Galileo
Seven", stardate 2822.3

Tom Lane wrote:

"P.J. \"Josh\" Rovero" <rovero@sonalysts.com> writes:

7.4b1 is significantly faster (i.e., the higher curve)
over this range of clients and transactions.

Cool. I wonder though why the 7.4 curve is so much noisier.

I wonder why pgbench is _so_ much faster on 7.4. I can't think of
anything that would make that large an improvement.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

"P.J. \"Josh\" Rovero" <rovero@sonalysts.com> writes:

7.4b1 is significantly faster (i.e., the higher curve)
over this range of clients and transactions.

I wonder why pgbench is _so_ much faster on 7.4. I can't think of
anything that would make that large an improvement.

I was wondering that too. I don't think any of the optimizer work I did
for 7.4 would have the slightest impact on pgbench, because its queries
are so simple. I don't see anything else in the HISTORY file that looks
very promising either. Perhaps it's just the cumulative result of small
tweaks like optimizing palloc0() and using bitmap sets in the planner.

We should try profiling pgbench runs to learn more...

regards, tom lane

"P.J. \"Josh\" Rovero" <rovero@sonalysts.com> writes:

Ran sets of 150 pgbench runs, with clients (1, 2, 4, 8, 16) and
transactions (5 each at 125, 250, 500, 1000, 2000, 4000),

BTW, what was the pgbench "scale factor"?

regards, tom lane

It's quite a bit faster for me. I've been doing a lot of reading and
comparing the latest versions of PostgreSQL (7.3.4), Firebird
(1.5RC5), MySQL (InnoDB, 4.0.14) on a dual P3-850 using Apache2.0.47
and PHP 4.3.3RC4 on Redhat 9. I loaded the databases up with identical
schemas and a couple of tables with 70k rows each.

I've been a MySQL user for a long time, but I need/want a lot of the
features that it doesn't have for my next project. Plus, stuff like
referential integrity and transactions in MySQL are pretty weak
compared to the others.

I've been trying to get an idea of how the DBs would perform, since
(SELECT) speed is a primary concern. My tests consist of looping
various simple operations I'll be doing in a PHP script, e.g.
connecting, grabbing a single (random for this test) row, and
disconnecting, say 1000 times. Or I've got one that will grab a block
of 1000 rows (from a random position) with a left join. Pretty simple
stuff.

This weekend I upgraded to 7.4b1 and ran my tests. There was
significant improvements across the board. PostgreSQL's times went
down some 25-50% for these things. And this is using a stock config (I
know, I know, I'm going to fix it). (BTW, should changing config
parameters show any improvements the tests I'm doing aren't causing it
to swap? I know it's mandatory when I place it under load...)

With 7.3.4, PostgreSQL generally fell midway between MySQL and
Firebird. With 7.4b1, (I can't recall many specifics right now, but)
some operations were actually faster that MySQL! Some operations were
slower (inserting xxxxx records, I believe). But for the majority of
my tests, PostgreSQL was neck-and-neck with MySQL. Very impressive.

Unfortunately, Firebird was consistently third. I had high hopes for
it, but it was definitely slower. I know they're looking heavily at
performance for 2.0, which will be interesting to see.

Granted, these tests didn't accurately simulate a real-world load, but
that won't be doable until the project is done. Anyway, I'm going to
use PostgreSQL!

Jacob Hanson
jacdx@jacobhanson.com

Tom Lane wrote:

"P.J. \"Josh\" Rovero" <rovero@sonalysts.com> writes:

Ran sets of 150 pgbench runs, with clients (1, 2, 4, 8, 16) and
transactions (5 each at 125, 250, 500, 1000, 2000, 4000),

BTW, what was the pgbench "scale factor"?

regards, tom lane

1 for these cases.

"P. Joshua Rovero" <rovero@sonalysts.com> writes:

Ran sets of 150 pgbench runs, with clients (1, 2, 4, 8, 16) and
transactions (5 each at 125, 250, 500, 1000, 2000, 4000),

BTW, what was the pgbench "scale factor"?

1 for these cases.

Hm. You really want scale factor >= number of clients, else you will
mostly be measuring the effects of contention. The scale factor is the
same as the number of rows created in the "branches" table, and every
transaction wants to update one of the "branches" rows. So if you have
just one branch, all the clients try to update that same row every time,
effectively serializing their transactions.

I now suspect that what you measured is just some marginal improvement
we made in the lock manager. Not sure what, though.

regards, tom lane

On Tue, Aug 26, 2003 at 02:57:29PM -0600, Jacob Hanson wrote:

This weekend I upgraded to 7.4b1 and ran my tests. There was
significant improvements across the board. PostgreSQL's times went
down some 25-50% for these things. And this is using a stock config (I
know, I know, I'm going to fix it). (BTW, should changing config
parameters show any improvements the tests I'm doing aren't causing it
to swap? I know it's mandatory when I place it under load...)

With 7.3.4, PostgreSQL generally fell midway between MySQL and
Firebird. With 7.4b1, (I can't recall many specifics right now, but)
some operations were actually faster that MySQL! Some operations were
slower (inserting xxxxx records, I believe). But for the majority of
my tests, PostgreSQL was neck-and-neck with MySQL. Very impressive.

Well, a possible explanation is that 7.4b1 uses a different "default
configuration". The amount of memory it uses is actually determined at
install time (initdb time to be exact), so you will have a much more
reasonable default values than with previous versions. It's very
possible that it affected your benchmark. See how many shared_buffers
you got, for example (probably 1000 - it was 64 in previous versions
IIRC).

It's very nice to know that it actually performs within MySQL's numbers,
even for such a simple test.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
One man's impedance mismatch is another man's layer of abstraction.
(Lincoln Yeoh)

On 27 Aug 2003 at 1:14, Alvaro Herrera wrote:

Well, a possible explanation is that 7.4b1 uses a different "default
configuration". The amount of memory it uses is actually determined at
install time (initdb time to be exact), so you will have a much more
reasonable default values than with previous versions. It's very
possible that it affected your benchmark. See how many shared_buffers
you got, for example (probably 1000 - it was 64 in previous versions
IIRC).

I doubt that. I have CVS head installed and I don't recalling seeing a
different default. Need to confirm though..

Bye
Shridhar

--
Johnson's First Law: When any mechanical contrivance fails, it will do so at
the most inconvenient possible time.

On 26 Aug 2003 at 14:57, Jacob Hanson wrote:

This weekend I upgraded to 7.4b1 and ran my tests. There was
significant improvements across the board. PostgreSQL's times went
down some 25-50% for these things. And this is using a stock config (I
know, I know, I'm going to fix it). (BTW, should changing config
parameters show any improvements the tests I'm doing aren't causing it
to swap? I know it's mandatory when I place it under load...)

It makes difference for the plan it chooses. Setting effective cache and shared
buffers right is a must. Shared buffers is a hit-n-miss game. You need to find
your own sweet spot but effective cache is pretty straight forward and should
be set properly at all times..

Granted, these tests didn't accurately simulate a real-world load, but
that won't be doable until the project is done. Anyway, I'm going to
use PostgreSQL!

If postgresql works fastest for your own load, would you really care if mysql
wins performance bechmark over network RAID with one disk placed on moon?

Maybe the results aren't representative but they are far more valuable to you
than any other representative bechmarks..

Bye
Shridhar

--
Your job is being a professor and researcher: That's one hell of a good
excusefor some of the brain-damages of minix.(Linus Torvalds to Andrew
Tanenbaum)