pgsql: Avoid holding AutovacuumScheduleLock while rechecking table stat

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

pgsql: Avoid holding AutovacuumScheduleLock while rechecking table stat

Tom Lane-2
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@...

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(-)

Previous Thread Next Thread