pgsql: Avoid holding AutovacuumScheduleLock while rechecking tablestat - Mailing list pgsql-committers

From Tom Lane
Subject pgsql: Avoid holding AutovacuumScheduleLock while rechecking tablestat
Date
Msg-id E1evmnC-0000MY-2m@gemulon.postgresql.org
Whole thread Raw
List pgsql-committers
Avoid holding AutovacuumScheduleLock while rechecking table statistics.

In databases with many tables, re-fetching the statistics takes some time,
so that this behavior seriously decreases the available concurrency for
multiple autovac workers.  There's discussion afoot about more complete
fixes, but a simple and back-patchable amelioration is to claim the table
and release the lock before rechecking stats.  If we find out there's no
longer a reason to process the table, re-taking the lock to un-claim the
table is cheap enough.

(This patch is quite old, but got lost amongst a discussion of more
aggressive fixes.  It's not clear when or if such a fix will be
accepted, but in any case it'd be unlikely to get back-patched.
Let's do this now so we have some improvement for the back branches.)

In passing, make the normal un-claim step take AutovacuumScheduleLock
not AutovacuumLock, since that is what is documented to protect the
wi_tableoid field.  This wasn't an actual bug in view of the fact that
readers of that field hold both locks, but it creates some concurrency
penalty against operations that need only AutovacuumLock.

Back-patch to all supported versions.

Jeff Janes

Discussion: https://postgr.es/m/26118.1520865816@sss.pgh.pa.us

Branch
------
REL9_4_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/95f08d32dec4428192834d233d86d4b0546765f5

Modified Files
--------------
src/backend/postmaster/autovacuum.c | 53 ++++++++++++++++++++++++++-----------
1 file changed, 37 insertions(+), 16 deletions(-)


pgsql-committers by date:

Previous
From: Michael Meskes
Date:
Subject: pgsql: Set connection back to NULL after freeing it.
Next
From: Tom Lane
Date:
Subject: pgsql: When updating reltuples after ANALYZE,just extrapolate from our