Why do we let autovacuum give up?

"Joshua D. Drake" <jd@commandprompt.com> writes:

I have run into yet again another situation where there was an
assumption that autovacuum was keeping up and it wasn't. It was caused
by autovacuum quitting because another process requested a lock.

In turn we received a ton of bloat on pg_attribute which caused all
kinds of other issues (as can be expected).

The more I run into it, the more it seems like autovacuum should behave
like vacuum, in that it gets precedence when it is running. First come,
first serve as they say.

1. Back when it worked like that, things were worse.

2. What have you got that is requesting exclusive lock on pg_attribute?
That seems like a pretty unfriendly behavior in itself.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 01/23/2014 12:34 PM, Joshua D. Drake wrote:

Hello,

I have run into yet again another situation where there was an
assumption that autovacuum was keeping up and it wasn't. It was caused
by autovacuum quitting because another process requested a lock.

In turn we received a ton of bloat on pg_attribute which caused all
kinds of other issues (as can be expected).

The more I run into it, the more it seems like autovacuum should behave
like vacuum, in that it gets precedence when it is running. First come,
first serve as they say.

Thoughts?

If we let autovacuum block user activity, a lot more people would turn
it off.

Now, if you were to argue that we should have some way to monitor the
tables which autovac can never touch because of conflicts, I would agree
with you.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jan 23, 2014 at 12:53 PM, Josh Berkus <josh@agliodbs.com> wrote:

On 01/23/2014 12:34 PM, Joshua D. Drake wrote:

Hello,

I have run into yet again another situation where there was an
assumption that autovacuum was keeping up and it wasn't. It was caused
by autovacuum quitting because another process requested a lock.

In turn we received a ton of bloat on pg_attribute which caused all
kinds of other issues (as can be expected).

The more I run into it, the more it seems like autovacuum should behave
like vacuum, in that it gets precedence when it is running. First come,
first serve as they say.

Thoughts?

If we let autovacuum block user activity, a lot more people would turn
it off.

Now, if you were to argue that we should have some way to monitor the
tables which autovac can never touch because of conflicts, I would agree
with you.

Agree completely. Easy ways to monitor this would be great. Once you
know there's a problem, tweaking autovacuum settings is very hard and
misunderstood, and explaining how to be effective at it is a dark art
too.

-Harold

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 24/01/14 09:49, Tom Lane wrote:

2. What have you got that is requesting exclusive lock on
pg_attribute? That seems like a pretty unfriendly behavior in itself.
regards, tom lane

I've seen this sort of problem where every db session was busily
creating temporary tables. I never got to the find *why* they needed to
make so many, but it seemed like a bad idea.

Regards

Mark

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jan 23, 2014 at 4:03 PM, Mark Kirkwood
<mark.kirkwood@catalyst.net.nz> wrote:

On 24/01/14 09:49, Tom Lane wrote:

2. What have you got that is requesting exclusive lock on pg_attribute?
That seems like a pretty unfriendly behavior in itself. regards, tom lane

I've seen this sort of problem where every db session was busily creating
temporary tables. I never got to the find *why* they needed to make so many,
but it seemed like a bad idea.

But... how does that result on a vacuum-incompatible lock request
against pg_attribute?

I see that it'll insert lots of rows into pg_attribute, and maybe
later delete them, but none of that blocks vacuum.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 01/23/2014 01:03 PM, Mark Kirkwood wrote:

On 24/01/14 09:49, Tom Lane wrote:

2. What have you got that is requesting exclusive lock on
pg_attribute? That seems like a pretty unfriendly behavior in itself.
regards, tom lane

I've seen this sort of problem where every db session was busily
creating temporary tables. I never got to the find *why* they needed to
make so many, but it seemed like a bad idea.

Yep... that's the one. They are creating lots and lots of temp tables.

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Dne 23.1.2014 22:04 "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
napsal(a):

On 24/01/14 09:49, Tom Lane wrote:

2. What have you got that is requesting exclusive lock on pg_attribute?

That seems like a pretty unfriendly behavior in itself. regards, tom lane

I've seen this sort of problem where every db session was busily creating

temporary tables. I never got to the find *why* they needed to make so
many, but it seemed like a bad idea.

Our customer had same problem with temp tables by intensively plpgsql
functions. For higher load a temp tables are performance and stability
killer. Vacuum of pg attrib has very ugly impacts :(

Regars

Pavel

After redesign - without tmp tables - his applications works well.

We needs a global temp tables

Mark Kirkwood <mark.kirkwood@catalyst.net.nz> writes:

On 24/01/14 09:49, Tom Lane wrote:

2. What have you got that is requesting exclusive lock on
pg_attribute? That seems like a pretty unfriendly behavior in itself.

I've seen this sort of problem where every db session was busily
creating temporary tables. I never got to the find *why* they needed to
make so many, but it seemed like a bad idea.

That shouldn't result in any table-level exclusive locks on system
catalogs, though.

[ thinks... ] It's possible that what you saw is not the
kick-out-autovacuum-entirely behavior, but the behavior added in commit
bbb6e559c, whereby vacuum (auto or regular) will skip over pages that it
can't immediately get an exclusive buffer lock on. On a heavily used
table, we might skip the same page repeatedly, so that dead tuples don't
get cleaned for a long time.

To add insult to injury, despite having done that, vacuum would reset the
pgstats dead-tuple count to zero, thus postponing the next autovacuum.
I think commit 115f41412 may have improved the situation, but I'd want
to see some testing of this theory before I'd propose back-patching it.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 24/01/14 10:09, Robert Haas wrote:

On Thu, Jan 23, 2014 at 4:03 PM, Mark Kirkwood
<mark.kirkwood@catalyst.net.nz> wrote:

On 24/01/14 09:49, Tom Lane wrote:

2. What have you got that is requesting exclusive lock on pg_attribute?
That seems like a pretty unfriendly behavior in itself. regards, tom lane

I've seen this sort of problem where every db session was busily creating
temporary tables. I never got to the find *why* they needed to make so many,
but it seemed like a bad idea.

But... how does that result on a vacuum-incompatible lock request
against pg_attribute?

I see that it'll insert lots of rows into pg_attribute, and maybe
later delete them, but none of that blocks vacuum.

That was my thought too - if I see it happening again here (was a year
or so ago that I saw some serious pg_attribute bloat) I'll dig deeper.

regards

Mark

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 24/01/14 10:16, Mark Kirkwood wrote:

On 24/01/14 10:09, Robert Haas wrote:

On Thu, Jan 23, 2014 at 4:03 PM, Mark Kirkwood
<mark.kirkwood@catalyst.net.nz> wrote:

On 24/01/14 09:49, Tom Lane wrote:

2. What have you got that is requesting exclusive lock on
pg_attribute?
That seems like a pretty unfriendly behavior in itself. regards,
tom lane

I've seen this sort of problem where every db session was busily
creating
temporary tables. I never got to the find *why* they needed to make
so many,
but it seemed like a bad idea.

But... how does that result on a vacuum-incompatible lock request
against pg_attribute?

I see that it'll insert lots of rows into pg_attribute, and maybe
later delete them, but none of that blocks vacuum.

That was my thought too - if I see it happening again here (was a year
or so ago that I saw some serious pg_attribute bloat) I'll dig deeper.

Actually not much digging required. Running the attached script via
pgbench (8 sessions) against a default configured postgres 8.4 sees
pg_attribute get to 1G after about 15 minutes.

On Thu, Jan 23, 2014 at 10:00 PM, Harold Giménez <harold@heroku.com> wrote:

On Thu, Jan 23, 2014 at 12:53 PM, Josh Berkus <josh@agliodbs.com> wrote:

On 01/23/2014 12:34 PM, Joshua D. Drake wrote:

Hello,

I have run into yet again another situation where there was an
assumption that autovacuum was keeping up and it wasn't. It was caused
by autovacuum quitting because another process requested a lock.

In turn we received a ton of bloat on pg_attribute which caused all
kinds of other issues (as can be expected).

The more I run into it, the more it seems like autovacuum should behave
like vacuum, in that it gets precedence when it is running. First come,
first serve as they say.

Thoughts?

If we let autovacuum block user activity, a lot more people would turn
it off.

Now, if you were to argue that we should have some way to monitor the
tables which autovac can never touch because of conflicts, I would agree
with you.

Agree completely. Easy ways to monitor this would be great. Once you
know there's a problem, tweaking autovacuum settings is very hard and
misunderstood, and explaining how to be effective at it is a dark art
too.

FWIW, I have a patch around somewhere that I never cleaned up properly for
submissions that simply added a counter to pg_stat_user_tables indicating
how many times vacuum had aborted on that specific table. If that's enough
info (it was for my case) to cover this case, I can try to dig it out
again and clean it up...

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On 01/23/2014 02:17 PM, Magnus Hagander wrote:

FWIW, I have a patch around somewhere that I never cleaned up properly for
submissions that simply added a counter to pg_stat_user_tables indicating
how many times vacuum had aborted on that specific table. If that's enough
info (it was for my case) to cover this case, I can try to dig it out
again and clean it up...

It would be 100% more information than we currently have. How much more
difficult would it be to count completed autovacuums as well? It's
really the ratio of the two which matters ...

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 01/23/2014 02:55 PM, Josh Berkus wrote:

On 01/23/2014 02:17 PM, Magnus Hagander wrote:

FWIW, I have a patch around somewhere that I never cleaned up properly for
submissions that simply added a counter to pg_stat_user_tables indicating
how many times vacuum had aborted on that specific table. If that's enough
info (it was for my case) to cover this case, I can try to dig it out
again and clean it up...

It would be 100% more information than we currently have. How much more
difficult would it be to count completed autovacuums as well? It's
really the ratio of the two which matters ...

Actually, now that I think about it, the ratio of the two doesn't matter
as much as whether the most recent autovacuum aborted or not.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jan 23, 2014 at 1:41 PM, Mark Kirkwood <
mark.kirkwood@catalyst.net.nz> wrote:

On 24/01/14 10:16, Mark Kirkwood wrote:

On 24/01/14 10:09, Robert Haas wrote:

On Thu, Jan 23, 2014 at 4:03 PM, Mark Kirkwood
<mark.kirkwood@catalyst.net.nz> wrote:

On 24/01/14 09:49, Tom Lane wrote:

2. What have you got that is requesting exclusive lock on pg_attribute?
That seems like a pretty unfriendly behavior in itself. regards, tom
lane

I've seen this sort of problem where every db session was busily
creating
temporary tables. I never got to the find *why* they needed to make so
many,
but it seemed like a bad idea.

But... how does that result on a vacuum-incompatible lock request
against pg_attribute?

I see that it'll insert lots of rows into pg_attribute, and maybe
later delete them, but none of that blocks vacuum.

That was my thought too - if I see it happening again here (was a year or
so ago that I saw some serious pg_attribute bloat) I'll dig deeper.

Actually not much digging required. Running the attached script via
pgbench (8 sessions) against a default configured postgres 8.4 sees
pg_attribute get to 1G after about 15 minutes.

At that rate, with default throttling, it will be a close race whether
autovac can vacuum pages as fast as they are being added. Even if it never
gets cancelled, it might not ever finish.

Cheers,

Jeff

On 24/01/14 12:13, Jeff Janes wrote:

On Thu, Jan 23, 2014 at 1:41 PM, Mark Kirkwood <
mark.kirkwood@catalyst.net.nz> wrote:

On 24/01/14 10:16, Mark Kirkwood wrote:

On 24/01/14 10:09, Robert Haas wrote:

On Thu, Jan 23, 2014 at 4:03 PM, Mark Kirkwood
<mark.kirkwood@catalyst.net.nz> wrote:

On 24/01/14 09:49, Tom Lane wrote:

2. What have you got that is requesting exclusive lock on pg_attribute?
That seems like a pretty unfriendly behavior in itself. regards, tom
lane

I've seen this sort of problem where every db session was busily
creating
temporary tables. I never got to the find *why* they needed to make so
many,
but it seemed like a bad idea.

But... how does that result on a vacuum-incompatible lock request
against pg_attribute?

I see that it'll insert lots of rows into pg_attribute, and maybe
later delete them, but none of that blocks vacuum.

That was my thought too - if I see it happening again here (was a year or
so ago that I saw some serious pg_attribute bloat) I'll dig deeper.

Actually not much digging required. Running the attached script via
pgbench (8 sessions) against a default configured postgres 8.4 sees
pg_attribute get to 1G after about 15 minutes.

At that rate, with default throttling, it will be a close race whether
autovac can vacuum pages as fast as they are being added. Even if it never
gets cancelled, it might not ever finish.

Yes - I should have set the cost delay to 0 first (checking that now).

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 24/01/14 12:28, Mark Kirkwood wrote:

On 24/01/14 12:13, Jeff Janes wrote:

On Thu, Jan 23, 2014 at 1:41 PM, Mark Kirkwood <
mark.kirkwood@catalyst.net.nz> wrote:

On 24/01/14 10:16, Mark Kirkwood wrote:

On 24/01/14 10:09, Robert Haas wrote:

On Thu, Jan 23, 2014 at 4:03 PM, Mark Kirkwood
<mark.kirkwood@catalyst.net.nz> wrote:

On 24/01/14 09:49, Tom Lane wrote:

2. What have you got that is requesting exclusive lock on
pg_attribute?
That seems like a pretty unfriendly behavior in itself. regards,
tom
lane

I've seen this sort of problem where every db session was busily
creating
temporary tables. I never got to the find *why* they needed to
make so
many,
but it seemed like a bad idea.

But... how does that result on a vacuum-incompatible lock request
against pg_attribute?

I see that it'll insert lots of rows into pg_attribute, and maybe
later delete them, but none of that blocks vacuum.

That was my thought too - if I see it happening again here (was a
year or
so ago that I saw some serious pg_attribute bloat) I'll dig deeper.

Actually not much digging required. Running the attached script via
pgbench (8 sessions) against a default configured postgres 8.4 sees
pg_attribute get to 1G after about 15 minutes.

At that rate, with default throttling, it will be a close race whether
autovac can vacuum pages as fast as they are being added. Even if it
never
gets cancelled, it might not ever finish.

Yes - I should have set the cost delay to 0 first (checking that now).

Doing that (and a few other autovac tweaks):

autovacuum_max_workers = 4
autovacuum_naptime = 10s
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.1
autovacuum_vacuum_cost_delay = 0ms

Stops excessive bloat - clearly autovacuum *is* able to vacuum
pg_attribute in this case. Back to drawing board for a test case.

Regards

Mark

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2014-01-24 12:49:57 +1300, Mark Kirkwood wrote:

autovacuum_max_workers = 4
autovacuum_naptime = 10s
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.1
autovacuum_vacuum_cost_delay = 0ms

Stops excessive bloat - clearly autovacuum *is* able to vacuum pg_attribute
in this case. Back to drawing board for a test case.

Well, I think quite many people don't realize it might be necessary to
tune autovac on busy workloads. As it very well might be the case in
Josh's case.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2014-01-23 16:15:50 -0500, Tom Lane wrote:

[ thinks... ] It's possible that what you saw is not the
kick-out-autovacuum-entirely behavior, but the behavior added in commit
bbb6e559c, whereby vacuum (auto or regular) will skip over pages that it
can't immediately get an exclusive buffer lock on. On a heavily used
table, we might skip the same page repeatedly, so that dead tuples don't
get cleaned for a long time.

I don't think it's too likely as an explanation here. Such workloads are
likely to fill a page with only dead tuples, right? Once all tuples are
safely dead they will be killed from the btree which should cause the
page not to be visited anymore and thus safely vacuumable.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andres Freund <andres@2ndquadrant.com> writes:

On 2014-01-23 16:15:50 -0500, Tom Lane wrote:

[ thinks... ] It's possible that what you saw is not the
kick-out-autovacuum-entirely behavior, but the behavior added in commit
bbb6e559c, whereby vacuum (auto or regular) will skip over pages that it
can't immediately get an exclusive buffer lock on. On a heavily used
table, we might skip the same page repeatedly, so that dead tuples don't
get cleaned for a long time.

I don't think it's too likely as an explanation here. Such workloads are
likely to fill a page with only dead tuples, right? Once all tuples are
safely dead they will be killed from the btree which should cause the
page not to be visited anymore and thus safely vacuumable.

I added some instrumentation to vacuumlazy.c to count the number of pages
skipped in this way. You're right, it seems to be negligible, at least
with Mark's test case. I saw at most two pages skipped per vacuum, and
usually none; so there's no way that a whole lot of tuples are going
unvacuumed because of this. (I wonder though if we ought to add such
counting as a permanent feature ...)

I concur with the other reports that the main problem in this test case is
just that the default cost delay settings throttle autovacuum so hard that
it has no chance of keeping up. If I reduce autovacuum_vacuum_cost_delay
from the default 20ms to 2ms, it seems to keep up quite nicely, on my
machine anyway. Probably other combinations of changes would do it too.

Perhaps we need to back off the default cost delay settings a bit?
We've certainly heard more than enough reports of table bloat in
heavily-updated tables. A system that wasn't hitting the updates as hard
as it could might not need this, but on the other hand it probably
wouldn't miss the I/O cycles from a more aggressive autovacuum, either.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers