Dear Postgresql experts,
I'm trying to work out why my system spends so much time autovacuuming.
It looks like pg_autovacuum wakes up every few minutes and does a query
on some system tables, and then spins doing no more queries but burning
all available CPU cycles for a a couple of minutes, before sleeping again.
I'm logging all queries to syslog, and the only queries that autovacuum
seems to run are about 3 selecting from pg_class and some stats tables.
They complete in a couple of seconds. I would see VACUUM commands in
there as well (wouldn't I?) if it was actually doing anything, but I
don't. Since not much is happening on the system I'm not suprised that
it decides that nothing needs vacuuming. But instead I think it
"spins"; and it's the pg_autovacuum process, not a postmaster, that is
taking all the CPU.
I wonder if this is because I have a lot of tables (about 50,000 I think
- I have one schema per user and each schema a couple of dozen tables).
Could it be that there is some code in autovacuum that is O(n^2) in
the number of tables, or something like that?
Has anyone seen anything like this before? Any debugging suggestions?
This is with the Debian package of 7.4.2, and all the default autovacuum
settings.
Cheers, Phil.