8.2 is 30% better in pgbench than 8.3

"Pavel Stehule" <pavel.stehule@gmail.com> writes:

I was little bit surprised. Is any reason for it?

Are you sure you're comparing apples to apples? In particular the
default autovacuuming setup is entirely different. With autovac off
I see 8.3 as faster than 8.2 in pgbench.

Also, remember a couple rules of thumb for choosing pgbench parameters:
keep -c less than the -s scale factor you used for pgbench -i (otherwise
you're mostly measuring update contention, because there are only -s
different rows in the branches table); and use -t at least 1000 or so
(otherwise startup transients are significant).

Note to all: we ***HAVE TO*** settle on some reasonable default
vacuum_cost_delay settings before we can ship 8.3. With no cost delay
and two or three workers active, 8.3's autovac does indeed send
performance into the tank.

regards, tom lane

2007/7/21, Tom Lane <tgl@sss.pgh.pa.us>:

"Pavel Stehule" <pavel.stehule@gmail.com> writes:

I was little bit surprised. Is any reason for it?

Are you sure you're comparing apples to apples? In particular the
default autovacuuming setup is entirely different. With autovac off
I see 8.3 as faster than 8.2 in pgbench.

I am not sure. But this (or similar) test will do more persons, and
the difference have to be explained.

Also, remember a couple rules of thumb for choosing pgbench parameters:
keep -c less than the -s scale factor you used for pgbench -i (otherwise
you're mostly measuring update contention, because there are only -s
different rows in the branches table); and use -t at least 1000 or so
(otherwise startup transients are significant).

Ok, I have to do more tests.

Note to all: we ***HAVE TO*** settle on some reasonable default
vacuum_cost_delay settings before we can ship 8.3. With no cost delay
and two or three workers active, 8.3's autovac does indeed send
performance into the tank.

Thank you for reply

Pavel Stehule

On Sat, 21 Jul 2007, Tom Lane wrote:

With autovac off I see 8.3 as faster than 8.2 in pgbench.

Indeed. I'm seeing much better pgbench results from HEAD than 8.2 when I
set the configurations up identically. I'm hoping to have a comparison
set to show everyone this week.

and use -t at least 1000 or so (otherwise startup transients are
significant).

I personally consider any pgbench run that lasts less than several minutes
noise. On a system that hits 500 TPS like Pavel's, I'd want to see around
100,000 transactions before I consider the results significant. And then
I'd want a set of 3 at each configuration because even with longer runs,
you occasionally get really odd results. Until you have 3 it can be
unclear which is the weird one.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Hello,

I checked my tests again I have different results. Now I tested
PostgreSQL on dedicated server. Now 8.3 is about 20% faster. I didn't
see strong impression of autovacuum. All numbers are approximate
only. I did pgbench 3x for folowing configuration: (autovacuum on,
autovacuum off, statistics off) and for -tntransaction (100, 1000,
4000)

-t 100 629/638/639 630/630/646 581/654/656 -- 8.3
443/519/519 542/555/566 670/692/694 -- 8.2

-t 1000 622/626/653 630/635/653 631/631/652 -- 8.3
523/528/541 522/535/550 679/681/690 -- 8.2

-t 4000 632/635/644 248/385/651* 191/401/641* -- 8.3
465/472/520 237/336/538* 249/379/702* -- 8.2

8.2 is faster only if statistics are off

Note:
*I didn't use parametr v (do_vacuum_accounts)

I am sorry for noise

Regards
Pavel Stehule

Pavel Stehule wrote:

Hello,

I checked my tests again I have different results. Now I tested
PostgreSQL on dedicated server. Now 8.3 is about 20% faster. I didn't
see strong impression of autovacuum. All numbers are approximate
only. I did pgbench 3x for folowing configuration: (autovacuum on,
autovacuum off, statistics off) and for -tntransaction (100, 1000,
4000)

In other news, 8.3 with current HOT is 13% faster than 8.2 at TPCE in
the first 1/2 hour. Performance does not fall over 5 hours of test run,
and most of the main tables never have autovacuum triggered at all.
Unfortnately, we don't yet have a 5-hour 8.2 run to compare
last-half-hour performance.

--Josh

Tom,

Note to all: we ***HAVE TO*** settle on some reasonable default
vacuum_cost_delay settings before we can ship 8.3. With no cost delay
and two or three workers active, 8.3's autovac does indeed send
performance into the tank.

I've been using 20ms for most of my setups. That's aimed at reducing
autovac to almost no impact at all, but taking a long time. Maybe 10ms?

--Josh

Greg Smith <gsmith@gregsmith.com> writes:

I'd want a set of 3 at each configuration because even with longer runs,
you occasionally get really odd results. Until you have 3 it can be
unclear which is the weird one.

Yeah, pgbench results are notoriously unrepeatable. One issue is that
the first run after pgbench -i sees conditions a lot different from
subsequent runs (no free space in tables, indexes are more tightly
packed than they will be later, etc). The recently added option to
initialize the tables with a selected fillfactor might help here,
but personally I've not experimented with it.

There seems to be also some of the "good average but bad worst case"
behavior that Josh and others have pointed out in bigger benchmarks.
I've always assumed this was due to checkpointing (and autovac if
enabled). If your test run isn't long enough to cover one full
checkpoint cycle then the results will be quite variable depending
on whether it included a checkpoint or not. 8.3 might alleviate
this effect to some extent.

regards, tom lane

On Sun, 22 Jul 2007, Tom Lane wrote:

There seems to be also some of the "good average but bad worst case"
behavior that Josh and others have pointed out in bigger benchmarks.
I've always assumed this was due to checkpointing

At lot of variation is from checkpoints, some comes from the index
fullness/fragmentation, some from vacuuming, a bit is added based on
what's in the buffer cache from previous runs, and there's a touch of
randomness thrown on top even if you control all those things. My tests
suggest the "bad worst case" results in pgbench tests are almost always
from checkpoints that happen when the buffer cache is filled with almost
exclusively dirty buffers (which can happen very easily with pgbench).

I am in the process of squashing these issues when running pgbench against
8.3. A before/after look at pg_stat_bgwriter lets you measure the
checkpoint variation. The ability to directly play with the index
fillfactor lets you test/control the impact of that. Wrap all that into a
scripting framework that runs the tests many times for you in a consistant
fashion and then summarizes the results, and pgbench becomes a rough but
completely servicable tool. Expect to see a pgbench-tools project that
does all that from me soon, my copy works but it's not ready for public
consumption yet.

I've also got a working backport of all the pg_stat_bgwriter instrumenting
that applies to 8.2, so people can run all this against the current
version as well if they're in a position where they can hack a custom
build. The hold-up on that is that the code that measures buffers
allocated and those written by back-ends for 8.3 is wrapped into the
"Automatic adjustment of bgwriter_lru_maxpages" patch, and I'm not going
to finalize my unofficial backport until that gets applied. Once HOT
wraps up that loose end should get snipped easily enough.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Sun, 2007-07-22 at 08:53 -0700, Josh Berkus wrote:

Pavel Stehule wrote:

Hello,

I checked my tests again I have different results. Now I tested
PostgreSQL on dedicated server. Now 8.3 is about 20% faster. I didn't
see strong impression of autovacuum. All numbers are approximate
only. I did pgbench 3x for folowing configuration: (autovacuum on,
autovacuum off, statistics off) and for -tntransaction (100, 1000,
4000)

In other news, 8.3 with current HOT is 13% faster than 8.2 at TPCE in
the first 1/2 hour. Performance does not fall over 5 hours of test run,
and most of the main tables never have autovacuum triggered at all.
Unfortnately, we don't yet have a 5-hour 8.2 run to compare
last-half-hour performance.

I think the rule of thumb is if the workload doesn't have enough UPDATEs
to trigger VACUUMs then HOT will have a low benefit.

With any workload, we should run it *until* we see some autovacuums
kick-in, so we can compare the overall situation of HOT v non-HOT. HOT
is designed for longer term benefit; fillfactor benefits fade over time
(as defined).

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

On Sat, 2007-07-21 at 13:30 -0400, Tom Lane wrote:

Note to all: we ***HAVE TO*** settle on some reasonable default
vacuum_cost_delay settings before we can ship 8.3. With no cost delay
and two or three workers active, 8.3's autovac does indeed send
performance into the tank.

Couple of thoughts here:

HOT will reduce the need for VACUUMs quite considerably, so multiple
concurrent VACUUMs becomes a quite rare situation. We should re-evaluate
this thought once we have taken the go/no-go decision for HOT in 8.3

The good thing about having multiple autovacuum daemons active is that
this reduces the possibility of having small tables starved while a
single large VACUUM runs to completion. My experience is that a single
large table can take many hours possibly frequently preventing 10,000
VACUUMs of small tables within that time.

The bad thing about having multiple autovacuum daemons active is that
you can get two large VACUUMs running at the same time. This gives you
the same small-VACUUM-starvation problem we had before, but now the
effects of two VACUUMs kill performance even more. I would suggest that
we look at ways of queueing, so that multiple large VACUUMs cannot
occur. Setting vacuum_cost_delay will still allow multiple large VACUUMs
but will make the starvation problem even worse as well. If we allow
that situation to occur, I think I'd rather stick to autovac_workers=1.
We will still have this potential problem even with HOT.

Potential solution: Each autovac worker gets a range of table sizes they
are allowed to VACUUM. This is set with an additional parameter which is
an array of gating values (i.e. one less gating value than number of
autovac workers). That way small VACUUMs are never starved out by large
ones. This is the same as having a Small:Medium:Large style queueing
system. We can work out how to make the queueing system self-tune by
observation of autovacuum frequency.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Simon Riggs wrote:

The bad thing about having multiple autovacuum daemons active is that
you can get two large VACUUMs running at the same time. This gives you
the same small-VACUUM-starvation problem we had before, but now the
effects of two VACUUMs kill performance even more. I would suggest that
we look at ways of queueing, so that multiple large VACUUMs cannot
occur. Setting vacuum_cost_delay will still allow multiple large VACUUMs
but will make the starvation problem even worse as well. If we allow
that situation to occur, I think I'd rather stick to autovac_workers=1.
We will still have this potential problem even with HOT.

Potential solution: Each autovac worker gets a range of table sizes they
are allowed to VACUUM. This is set with an additional parameter which is
an array of gating values (i.e. one less gating value than number of
autovac workers). That way small VACUUMs are never starved out by large
ones. This is the same as having a Small:Medium:Large style queueing
system. We can work out how to make the queueing system self-tune by
observation of autovacuum frequency.

default autovac_workers is 3, so wouldn't you need three, not two, large
VACUUMs to starvate a smaller table?

Instead of queuing, how about increasing autovac_workers if starvation
is a concern?

I'd like to set a default autovacuum_vacuum_cost_delay anyway. Without
it, autovacuum is a performance hit when it kicks in, even if there's
only one of them running, and even if it only lasts for a short time.
It's an unpleasant surprise for someone who's new to PostgreSQL and
doesn't yet understand how vacuum and autovacuum works.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Mon, 2007-07-23 at 10:04 +0100, Heikki Linnakangas wrote:

Simon Riggs wrote:

The bad thing about having multiple autovacuum daemons active is that
you can get two large VACUUMs running at the same time. This gives you
the same small-VACUUM-starvation problem we had before, but now the
effects of two VACUUMs kill performance even more. I would suggest that
we look at ways of queueing, so that multiple large VACUUMs cannot
occur. Setting vacuum_cost_delay will still allow multiple large VACUUMs
but will make the starvation problem even worse as well. If we allow
that situation to occur, I think I'd rather stick to autovac_workers=1.
We will still have this potential problem even with HOT.

Potential solution: Each autovac worker gets a range of table sizes they
are allowed to VACUUM. This is set with an additional parameter which is
an array of gating values (i.e. one less gating value than number of
autovac workers). That way small VACUUMs are never starved out by large
ones. This is the same as having a Small:Medium:Large style queueing
system. We can work out how to make the queueing system self-tune by
observation of autovacuum frequency.

default autovac_workers is 3, so wouldn't you need three, not two, large
VACUUMs to starvate a smaller table?

Instead of queuing, how about increasing autovac_workers if starvation
is a concern?

Neither of those things prevent the problem, they just make it less
likely. I don't think thats a good answer for production systems that
have response time service level agreements to meet.

I'd like to set a default autovacuum_vacuum_cost_delay anyway. Without
it, autovacuum is a performance hit when it kicks in, even if there's
only one of them running, and even if it only lasts for a short time.
It's an unpleasant surprise for someone who's new to PostgreSQL and
doesn't yet understand how vacuum and autovacuum works.

I agree, but only if we can prevent the starvation problem while we do
it, otherwise it just gets worse.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Simon Riggs wrote:

The bad thing about having multiple autovacuum daemons active is that
you can get two large VACUUMs running at the same time. This gives you
the same small-VACUUM-starvation problem we had before, but now the
effects of two VACUUMs kill performance even more. I would suggest that
we look at ways of queueing, so that multiple large VACUUMs cannot
occur. Setting vacuum_cost_delay will still allow multiple large VACUUMs
but will make the starvation problem even worse as well. If we allow
that situation to occur, I think I'd rather stick to autovac_workers=1.
We will still have this potential problem even with HOT.

We already discussed all this to death before feature freeze. I'm not
sure if it's a good idea to try to come up with new heuristics for the
thing this late. Feel free to work on it for 8.4 though!

I also wonder whether you have noticed the "balancing" code in autovac.
Whenever more than one autovac workers are running, they split the
available I/O allocated to them fairly, so that each one delays more
frequently than if it was running alone. The net effect is supposed to
be that no matter how many workers are running, your vacuum delay
settings are respected.

In any case, I think a better solution to the starvation problem caused
by huge tables is not skipping the vacuuming of them, but making it less
wasteful, for example with the DSM.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, 2007-07-23 at 12:00 -0400, Alvaro Herrera wrote:

Simon Riggs wrote:

The bad thing about having multiple autovacuum daemons active is that
you can get two large VACUUMs running at the same time. This gives you
the same small-VACUUM-starvation problem we had before, but now the
effects of two VACUUMs kill performance even more. I would suggest that
we look at ways of queueing, so that multiple large VACUUMs cannot
occur. Setting vacuum_cost_delay will still allow multiple large VACUUMs
but will make the starvation problem even worse as well. If we allow
that situation to occur, I think I'd rather stick to autovac_workers=1.
We will still have this potential problem even with HOT.

We already discussed all this to death before feature freeze.

...and starvation has still not been avoided. I like what you have done,
but we still have a problem, whichever release it gets fixed in.

I'm not
sure if it's a good idea to try to come up with new heuristics for the
thing this late. Feel free to work on it for 8.4 though!

I also wonder whether you have noticed the "balancing" code in autovac.
Whenever more than one autovac workers are running, they split the
available I/O allocated to them fairly, so that each one delays more
frequently than if it was running alone. The net effect is supposed to
be that no matter how many workers are running, your vacuum delay
settings are respected.

I did and I like it, many thanks.

In any case, I think a better solution to the starvation problem caused
by huge tables is not skipping the vacuuming of them, but making it less
wasteful, for example with the DSM.

Neither of those things prevent starvation though.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Simon Riggs wrote:

On Mon, 2007-07-23 at 12:00 -0400, Alvaro Herrera wrote:

Simon Riggs wrote:

The bad thing about having multiple autovacuum daemons active is that
you can get two large VACUUMs running at the same time. This gives you
the same small-VACUUM-starvation problem we had before, but now the
effects of two VACUUMs kill performance even more.

We already discussed all this to death before feature freeze.

...and starvation has still not been avoided. I like what you have done,
but we still have a problem, whichever release it gets fixed in.

Oh I will the first to admit that autovacuum is still not "good enough".

In any case, I think a better solution to the starvation problem caused
by huge tables is not skipping the vacuuming of them, but making it less
wasteful, for example with the DSM.

Neither of those things prevent starvation though.

Certainly it doesn't prevent starvation completely -- really there is no
way to completely prevent starvation unless you have as many workers as
you have tables, and one disk for each. What DSM does do is let the big
tables be vacuumed quickly which makes most of the problem go away.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On 7/23/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:

Certainly it doesn't prevent starvation completely -- really there is no
way to completely prevent starvation unless you have as many workers as
you have tables, and one disk for each. What DSM does do is let the big
tables be vacuumed quickly which makes most of the problem go away.

Frankly I haven't seen DSM results very closely, but DSM can help
us avoid full heap scans (and thats a big thing!), but it can't avoid the
associated index scans and that might limit our ability to vacuum very
large tables frequently.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

On Jul 22, 2007, at 8:54 AM, Josh Berkus wrote:

Tom,

Note to all: we ***HAVE TO*** settle on some reasonable default
vacuum_cost_delay settings before we can ship 8.3. With no cost
delay
and two or three workers active, 8.3's autovac does indeed send
performance into the tank.

I've been using 20ms for most of my setups. That's aimed at
reducing autovac to almost no impact at all, but taking a long
time. Maybe 10ms?

I've found 20ms to be a pretty good number for run-of-the-mill IO
capability, and 10ms to be good for a good RAID setup (RAID10, 8+
drives, BBU).

For a default setting, I think it'd be better to lean towards 20ms.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On Jul 23, 2007, at 1:40 AM, Simon Riggs wrote:

Potential solution: Each autovac worker gets a range of table sizes
they
are allowed to VACUUM.

Anyone putting thought into this should check the archives; there was
quite a bit of discussion around it. For 8.3 we decided to KISS so
that we'd get in the release, but we really do need to tackle the
starvation issue for 8.4.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Pavan Deolasee wrote:

On 7/23/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:

Certainly it doesn't prevent starvation completely -- really there is no
way to completely prevent starvation unless you have as many workers as
you have tables, and one disk for each. What DSM does do is let the big
tables be vacuumed quickly which makes most of the problem go away.

Frankly I haven't seen DSM results very closely, but DSM can help
us avoid full heap scans (and thats a big thing!), but it can't avoid the
associated index scans and that might limit our ability to vacuum very
large tables frequently.

I haven't seen DSM either so IMBFoS. You are right about index scans
though. Fortunately they are not as expensive as they used to be thanks
to Heikki's changes to allow physical order scanning.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support