First steps with 8.3 and autovacuum launcher

On Tue, Sep 18, 2007 at 06:51:04PM +0200, Guillaume Smet wrote:

Hi all,

As we will soon enter beta, I decided to give a try to 8.3devel. The
first step is of course to load a dump from an existing database.

The dump is a text dump of 1.6 GB (database is approximately 4 GB).

The restore is far slower than with 8.2. From time to time ALTER TABLE
queries creating primary keys are waiting for a long time. After a
while, I discovered that that I had three autovacuum processes which
were running to analyze the created tables while the dump was trying
to create primary keys on these very tables.

While I understand that it's important to have a fully analyzed
database, I usually do it at the end of the restore and the fact that
three tables are analyzed concurrently while primary keys are created
is far from optimal IMHO as primary keys creation often (*really*
often in my case) waits for autovacuum to finish its job.

Odd... I'd expect it to actually be beneficial to run analyze on a table
at roughly the same time as PK building, because you'd make better use
of cache. OTOH, unless something's changed, analyze should read at most
30k pages, which I wouldn't expect to take all that long...
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

On 9/19/07, Decibel! <decibel@decibel.org> wrote:

Odd... I'd expect it to actually be beneficial to run analyze on a table
at roughly the same time as PK building, because you'd make better use
of cache.

Sure if your database fits entirely in RAM (otherwise if two big
tables are analyzed while we create the primary key for a third one,
it won't help us at all). And even in this case, it's not sure the
time lost by waiting the lock is worth it. It could for sure if the
restore could create the other primary keys while waiting for the lock
on the analyzed tables, which is obviously not the case.
In my particular case, the restore stales a lot of times with status
ALTER TABLE waiting.

--
Guillaume

On Sep 19, 2007, at 2:08 AM, Guillaume Smet wrote:

On 9/19/07, Decibel! <decibel@decibel.org> wrote:

Odd... I'd expect it to actually be beneficial to run analyze on a
table
at roughly the same time as PK building, because you'd make better
use
of cache.

Sure if your database fits entirely in RAM (otherwise if two big
tables are analyzed while we create the primary key for a third one,

You missed my point... what we'd want to happen is for the analyze to
take place while that table had a good chance of still being in memory.

it won't help us at all). And even in this case, it's not sure the
time lost by waiting the lock is worth it. It could for sure if the
restore could create the other primary keys while waiting for the lock
on the analyzed tables, which is obviously not the case.
In my particular case, the restore stales a lot of times with status
ALTER TABLE waiting.

It might be worth looking into creating a different lock for ALTERs
that actually change database page layout vs ALTERs that don't, since
there's no reason you couldn't run ANALYZE while adding a PK (for
example).
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

On 9/19/07, Decibel! <decibel@decibel.org> wrote:

You missed my point... what we'd want to happen is for the analyze to
take place while that table had a good chance of still being in memory.

It seems to be a bit too intrusive for 8.3 at this point.

It might be worth looking into creating a different lock for ALTERs
that actually change database page layout vs ALTERs that don't, since
there's no reason you couldn't run ANALYZE while adding a PK (for
example).

It seems to be a good idea.

As I restore a lot my test database (a real life 3.1 GB database) to
test the patches on like/ilike, here are some figures to show the
problem a bit more:

** 8.3 (autovacuum on as it's the default) **
Time taken by psql to load my SQL dump (the database is analyzed at
the end of the restore due to autovacuum launcher launching up to 3
processes in parallel):
real 52m55.398s
user 0m37.727s
sys 0m3.114s

** 8.2 (autovacuum off as it's the default) **
Time taken by psql to load the SQL dump:
real 15m5.032s
user 0m37.881s
sys 0m3.040s
Time to analyze the database:
cityvox=# \timing
Timing is on.
cityvox=# ANALYZE;
ANALYZE
Time: 45882.049 ms
So a total of: 16 minutes for 8.2 compared to 53 minutes for 8.3 to
have the database in the same state.

All settings except autovacuum are identical on both clusters.

--
Guillaume

"Guillaume Smet" <guillaume.smet@gmail.com> writes:

So a total of: 16 minutes for 8.2 compared to 53 minutes for 8.3 to
have the database in the same state.

Please try that experiment with all three configurations on both
versions:
* autovacuum off
* autovacuum on, autovacuum_vacuum_cost_delay = 0
* autovacuum on, autovacuum_vacuum_cost_delay = 20
Comparing apples and oranges isn't real helpful in determining
what's happening.

regards, tom lane

On 9/22/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Guillaume Smet" <guillaume.smet@gmail.com> writes:
Please try that experiment with all three configurations on both
versions:
* autovacuum off
* autovacuum on, autovacuum_vacuum_cost_delay = 0
* autovacuum on, autovacuum_vacuum_cost_delay = 20

I'll do it during the week-end.

Comparing apples and oranges isn't real helpful in determining
what's happening.

I'm not exactly comparing apples and oranges, I'm comparing default
configuration of autovacuum for both versions.
IMHO, the point is not to compare both versions but to see what we can
do to improve the fact that 3 autovacuum processes analyzing the data
while restoring them introduces a lot of overhead.

--
Guillaume

Tom Lane wrote:

"Guillaume Smet" <guillaume.smet@gmail.com> writes:

So a total of: 16 minutes for 8.2 compared to 53 minutes for 8.3 to
have the database in the same state.

Please try that experiment with all three configurations on both
versions:
* autovacuum off
* autovacuum on, autovacuum_vacuum_cost_delay = 0
* autovacuum on, autovacuum_vacuum_cost_delay = 20
Comparing apples and oranges isn't real helpful in determining
what's happening.

for the record I'm seeing similiar dramatic slowdowns(ie takes a few
additional hours) during restoration of a 120GB database here - with the
default autovacuum settings the restore is taking ages to go through the
ALTER TABLE foo ADD PRIMARY KEY bar steps because autovacuum is
analyzing those tables at a very slow rate (ie the box is more or less
idle and the ALTER TABLE is waiting for the lock).

I have not yet experimented with different autovacuum_vacuum_cost_delay
settings but this seems like a step backwards :-(

Stefan

On 9/22/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Please try that experiment with all three configurations on both
versions:
* autovacuum off
* autovacuum on, autovacuum_vacuum_cost_delay = 0
* autovacuum on, autovacuum_vacuum_cost_delay = 20

I've finally found some time to spend on these tests. Here are the results:

* 8.2.5 *
- autovacuum off + ANALYZE: less than 16 minutes (figures previously
posted in this thread) - default configuration of 8.2
- autovacuum on, delay 0: 16m29
- autovacuum on, delay 20: 16m13
(I didn't repeat the run but we can see that autovacuum doesn't
introduce too much slowdown during the restore operation)

* 8.3devel freshly compiled *
- autovacuum off: 14m39
- autovacuum on, delay 0: 15m32
- autovacuum on, delay 20: 51m37 (the box is idle during a large
amount of this time) - default configuration of 8.3devel

--
Guillaume

Guillaume Smet wrote:

On 9/22/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Please try that experiment with all three configurations on both
versions:
* autovacuum off
* autovacuum on, autovacuum_vacuum_cost_delay = 0
* autovacuum on, autovacuum_vacuum_cost_delay = 20

I've finally found some time to spend on these tests. Here are the results:

* 8.2.5 *
- autovacuum off + ANALYZE: less than 16 minutes (figures previously
posted in this thread) - default configuration of 8.2
- autovacuum on, delay 0: 16m29
- autovacuum on, delay 20: 16m13
(I didn't repeat the run but we can see that autovacuum doesn't
introduce too much slowdown during the restore operation)

* 8.3devel freshly compiled *
- autovacuum off: 14m39
- autovacuum on, delay 0: 15m32
- autovacuum on, delay 20: 51m37 (the box is idle during a large
amount of this time) - default configuration of 8.3devel

some additional datapoints:

autovacuum on, delay 20: 8h 40min
autovacuum on, delay 0: 4h 23min

for restoring a database of around 120GB (on disk size) ...
In the delay 20 case the restore is more or less waiting hours for
grabbing locks during PK creation held by autovacuum (which tries to
analyze the tables).

Stefan

On 10/1/07, Guillaume Smet <guillaume.smet@gmail.com> wrote:

* 8.3devel freshly compiled *
- autovacuum off: 14m39
- autovacuum on, delay 0: 15m32
- autovacuum on, delay 20: 51m37 (the box is idle during a large
amount of this time) - default configuration of 8.3devel

I made a few more tests with 8.3devel and different
autovacuum_vacuum_cost_delay:
- autovacuum off: 14m39
- autovacuum on, delay 0: 15m32
- autovacuum on, delay 10: 17m19 (*new*)
- autovacuum on, delay 20: 51m37 - default
- autovacuum on, delay 40: 90m46 (*new*)

--
Guillaume

"Stefan Kaltenbrunner" <stefan@kaltenbrunner.cc> writes:

some additional datapoints:

autovacuum on, delay 20: 8h 40min
autovacuum on, delay 0: 4h 23min

I realize this isn't directly addressing the problem but perhaps part of the
solution would be to start advocating the use of pg_restore -1 ? That would
solve the problem for the narrow case of pg_restore.

In the long run we could think about exposing some kind of command for
pg_restore to use which would disable autovacuum from touching a table. (Or
take a session-level lock on the table -- shudder)

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

Gregory Stark <stark@enterprisedb.com> writes:

I realize this isn't directly addressing the problem but perhaps part of the
solution would be to start advocating the use of pg_restore -1 ? That would
solve the problem for the narrow case of pg_restore.

Well, that would do as a quick workaround, as would disabling autovacuum
during the restore.

In the long run we could think about exposing some kind of command for
pg_restore to use which would disable autovacuum from touching a
table.

Ugh. I think a real solution probably involves a mechanism that kicks
autovacuum off a table when someone else wants an exclusive lock on it.
This is a little bit worrisome because a steady stream of lock requests
could prevent autovac from ever finishing the table, but it seems clear
that not doing this is going to make autovac a lot more intrusive than
people will stand for.

regards, tom lane

Gregory Stark wrote:

"Stefan Kaltenbrunner" <stefan@kaltenbrunner.cc> writes:

some additional datapoints:

autovacuum on, delay 20: 8h 40min
autovacuum on, delay 0: 4h 23min

I realize this isn't directly addressing the problem but perhaps part of the
solution would be to start advocating the use of pg_restore -1 ? That would
solve the problem for the narrow case of pg_restore.

In the long run we could think about exposing some kind of command for
pg_restore to use which would disable autovacuum from touching a table. (Or
take a session-level lock on the table -- shudder)

In my opinion, CREATE INDEX shouldn't need to wait for autovacuum to
finish, regardless of who issued it. This is like priority inversion;
the autovacuum is not urgent, and runs slowly to avoid disturbing
others. But if it keeps the higher priority CREATE INDEX from starting,
it is disturbing others. Could we arrange things so that the effective
cost delay of the autovacuum process that's in the way gets set to 0
(like priority inheritance)?

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

Heikki Linnakangas escribi�:

In my opinion, CREATE INDEX shouldn't need to wait for autovacuum to
finish, regardless of who issued it. This is like priority inversion;
the autovacuum is not urgent, and runs slowly to avoid disturbing
others. But if it keeps the higher priority CREATE INDEX from starting,
it is disturbing others. Could we arrange things so that the effective
cost delay of the autovacuum process that's in the way gets set to 0
(like priority inheritance)?

This is an interesting idea, but I think it's attacking the wrong
problem. To me, the problem here is that an ANALYZE should not block
CREATE INDEX or certain forms of ALTER TABLE.

We do have a mechanism for changing the cost-delay parameters of an
autovac worker, but even if we made it to work, it would still delay the
other operation needlessly until the end of the analyze.

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

Alvaro Herrera <alvherre@commandprompt.com> writes:

This is an interesting idea, but I think it's attacking the wrong
problem. To me, the problem here is that an ANALYZE should not block
CREATE INDEX or certain forms of ALTER TABLE.

I doubt that that will work; in particular I'm pretty dubious that you
can safely make CREATE INDEX and VACUUM run together. Since they'd be
unlikely to be using the identical OldestXmin horizon, you'd likely end
up with dangling index entries (ie, CREATE INDEX indexes a tuple that
the VACUUM removes shortly afterward).

regards, tom lane

Tom Lane wrote:

Alvaro Herrera <alvherre@commandprompt.com> writes:

This is an interesting idea, but I think it's attacking the wrong
problem. To me, the problem here is that an ANALYZE should not block
CREATE INDEX or certain forms of ALTER TABLE.

I doubt that that will work; in particular I'm pretty dubious that you
can safely make CREATE INDEX and VACUUM run together. Since they'd be
unlikely to be using the identical OldestXmin horizon, you'd likely end
up with dangling index entries (ie, CREATE INDEX indexes a tuple that
the VACUUM removes shortly afterward).

I think the main issue is ANALYZE not VACUUM (at least in this thread)
since it's DB load times that are in question. Can CREATE INDEX and
ANALYZE be made to run concurrently?

Matthew T. O'Connor escribi�:

Tom Lane wrote:

Alvaro Herrera <alvherre@commandprompt.com> writes:

This is an interesting idea, but I think it's attacking the wrong
problem. To me, the problem here is that an ANALYZE should not block
CREATE INDEX or certain forms of ALTER TABLE.

I doubt that that will work; in particular I'm pretty dubious that you
can safely make CREATE INDEX and VACUUM run together. Since they'd be
unlikely to be using the identical OldestXmin horizon, you'd likely end
up with dangling index entries (ie, CREATE INDEX indexes a tuple that
the VACUUM removes shortly afterward).

I think the main issue is ANALYZE not VACUUM (at least in this thread)
since it's DB load times that are in question.

Right. Autovac will not issue VACUUM against the freshly restored
tables anyway, since there are no deleted tuples.

Can CREATE INDEX and ANALYZE be made to run concurrently?

I don't see why not (except for the fact that both try to update
reltuples and relpages AFAIR, so we would need to be careful about
that).

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

On Mon, 2007-10-01 at 16:50 -0400, Alvaro Herrera wrote:

Can CREATE INDEX and ANALYZE be made to run concurrently?

I don't see why not (except for the fact that both try to update
reltuples and relpages AFAIR, so we would need to be careful about
that).

This seems like the most desirable solution, everything else I've
thought of is really just a kluge.

We could make ANALYZE use optimistic locking, so it doesn't attempt the
catalog lock until later, so ANALYZE would be two internal transactions.
It should not overwrite the reltuples values from a concurrent CREATE
INDEX either.

We should not allow VACUUM to be concurrent with either CREATE INDEX or
ANALYZE, but then thats not the problem here anyway.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Simon Riggs <simon@2ndquadrant.com> writes:

We should not allow VACUUM to be concurrent with either CREATE INDEX or
ANALYZE, but then thats not the problem here anyway.

I can't believe anyone is short-sighted enough to think that.

The problem here is that autovac takes locks that block foreground
sessions that want exclusive locks. We've always known this and always
ignored it, but if autovac is on by default then it's going to be in
people's faces a lot more than it was before, and they won't be happy.

If you insist on crafting a solution that only fixes this problem for
pg_restore's narrow usage, you'll be back revisiting it before beta1
has been out a month.

regards, tom lane