Обсуждение: avtovacuum queue
Hi.
Is there any "queue" in postgres where tables that marked to be vacuumed or analyzed can be viewed?
Thanks.
Artem Tomyuk <admin@leboutique.com> writes:
> Is there any "queue" in postgres where tables that marked to be vacuumed or
> analyzed can be viewed?
No, there's no queue as such. You can look at the contents of
pg_stat_all_tables, particularly the n_dead_tup and n_mod_since_analyze
columns, to get a sense of which tables are likely to seem highest
priority to the autovacuum code.
regards, tom lane
Hi, Tom,
thanks for your reply, the problem is that our app use temp tables a lot so the pg_attribute getting "bloated",
i've noticed that during business hours autovacuum vacuum is not being triggered on pg_attribute,
but during night time it triggered every two hours, both vacuum and analyze, maybe you know why?
my autovacuum conf is:
autovacuum_max_workers = 16 # max number of autovacuum subprocesses
# (change requires restart)
autovacuum_naptime = 51min # time between autovacuum runs
autovacuum_vacuum_threshold = 500 # min number of row updates before
# vacuum
autovacuum_analyze_threshold = 500 # min number of row updates before
# analyze
autovacuum_vacuum_scale_factor = 0.002 # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.01 # fraction of table size before analyze
2018-04-02 18:18 GMT+03:00 Tom Lane <tgl@sss.pgh.pa.us>:
Artem Tomyuk <admin@leboutique.com> writes:
> Is there any "queue" in postgres where tables that marked to be vacuumed or
> analyzed can be viewed?
No, there's no queue as such. You can look at the contents of
pg_stat_all_tables, particularly the n_dead_tup and n_mod_since_analyze
columns, to get a sense of which tables are likely to seem highest
priority to the autovacuum code.
regards, tom lane
i've noticed that during business hours autovacuum vacuum is not being triggered on pg_attribute,but during night time it triggered every two hours, both vacuum and analyze, maybe you know why?
Sounds like someone added a "cron job" somewhere.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Apr 2, 2018 at 8:30 AM, Artem Tomyuk <admin@leboutique.com> wrote:
>> i've noticed that during business hours autovacuum vacuum is not being
>> triggered on pg_attribute,
>> but during night time it triggered every two hours, both vacuum and
>> analyze, maybe you know why?
> Sounds like someone added a "cron job" somewhere.
... and, perhaps, explicitly disabled autovacuum on that table?
regards, tom lane
Hi, Tom, no its not, because during night hours its running autovacuum vacuum several times automatically,
other strange thing that autovacuum analyze process started today at 5 A.M. and its still running, maybe thats why autovacuum vacuum never occurs? as i understand there cant be vacuum and analyze on the same relation at the same time?
What can be done to speed up analyze on pg_attribute?
Thanks
2018-04-02 19:43 GMT+03:00 Tom Lane <tgl@sss.pgh.pa.us>:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Apr 2, 2018 at 8:30 AM, Artem Tomyuk <admin@leboutique.com> wrote:
>> i've noticed that during business hours autovacuum vacuum is not being
>> triggered on pg_attribute,
>> but during night time it triggered every two hours, both vacuum and
>> analyze, maybe you know why?
> Sounds like someone added a "cron job" somewhere.
... and, perhaps, explicitly disabled autovacuum on that table?
regards, tom lane