pgsql: autovacuum: Drop orphan temp tables more quickly but with more c
autovacuum: Drop orphan temp tables more quickly but with more caution.
Previously, we only dropped an orphan temp table when it became old
enough to threaten wraparound; instead, doing it immediately. The
only value of waiting is that someone might be able to examine the
contents of the orphan temp table for forensic purposes, but it's
pretty difficult to actually do that and few users will wish to do so.
On the flip side, not performing the drop immediately generates log
spam and bloats pg_class.
In addition, per a report from Grigory Smolkin, if a temporary schema
contains a very large number of temporary tables, a backend attempting
to clear the temporary schema might fail due to lock table exhaustion.
It's helpful for autovacuum to clean up after such cases, and we don't
want it to wait for wraparound to threaten before doing so. To
prevent autovacuum from failing in the same manner as a backend trying
to drop an entire temp schema, remove orphan temp tables in batches of
50, committing after each batch, so that we don't accumulate an
unbounded number of locks. If a drop fails, retry other orphan tables
that need to be dropped up to 10 times before giving up. With this
system, if a backend does fail to clean a temporary schema due to
lock table exhaustion, autovacuum should hopefully put things right
the next time it processes the database.
Discussion: CAB7nPqSbYT6dRwsXVgiKmBdL_ARemfDZMPA+RPeC_ge0GK70hA@mail.gmail.com
Michael Paquier, with a bunch of comment changes by me.
Branch
------
master
Details
-------
http://git.postgresql.org/pg/commitdiff/a734fd5d1c309cc553b7c8c79fba96218af090f7
Modified Files
--------------
src/backend/postmaster/autovacuum.c | 153 ++++++++++++++++++++++++++++++------
1 file changed, 127 insertions(+), 26 deletions(-)
--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers
On Mon, Nov 21, 2016 at 10:05 AM, Robert Haas <rhaas@postgresql.org> wrote:
autovacuum: Drop orphan temp tables more quickly but with more caution.
Previously, we only dropped an orphan temp table when it became old
enough to threaten wraparound; instead, doing it immediately. The
only value of waiting is that someone might be able to examine the
contents of the orphan temp table for forensic purposes, but it's
pretty difficult to actually do that and few users will wish to do so.
On the flip side, not performing the drop immediately generates log
spam and bloats pg_class.
I have found an obvious bug when reading the code this morning:
orphan_failures is not initialized:
diff --git a/src/backend/postmaster/autovacuum.c
b/src/backend/postmaster/autovacuum.c
index 954c1a1..be357e7 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -1908,7 +1908,7 @@ do_autovacuum(void)
BufferAccessStrategy bstrategy;
ScanKeyData key;
TupleDesc pg_class_desc;
- int orphan_failures;
+ int orphan_failures = 0;
int effective_multixact_freeze_max_age; /*
Attached is a patch.
--
Michael
Attachments:
autovac-init-fix.patchapplication/x-patch; name=autovac-init-fix.patchDownload+1-1
Michael Paquier <michael.paquier@gmail.com> writes:
On Mon, Nov 21, 2016 at 10:05 AM, Robert Haas <rhaas@postgresql.org> wrote:
autovacuum: Drop orphan temp tables more quickly but with more caution.
I have found an obvious bug when reading the code this morning:
orphan_failures is not initialized:
My compiler noticed that, too. Will push.
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 Mon, Nov 21, 2016 at 4:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Michael Paquier <michael.paquier@gmail.com> writes:
On Mon, Nov 21, 2016 at 10:05 AM, Robert Haas <rhaas@postgresql.org> wrote:
autovacuum: Drop orphan temp tables more quickly but with more caution.
I have found an obvious bug when reading the code this morning:
orphan_failures is not initialized:My compiler noticed that, too. Will push.
Thanks.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers