pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedavtovacuum queue
6+ messages / 3 participants
[nested] [flat]
* avtovacuum queue
@ 2018-04-02 15:15 Artem Tomyuk <admin@leboutique.com>
2018-04-02 15:18 ` Re: avtovacuum queue Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Artem Tomyuk @ 2018-04-02 15:15 UTC (permalink / raw)
To: pgsql-admin
Hi.
Is there any "queue" in postgres where tables that marked to be vacuumed or
analyzed can be viewed?
Thanks.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: avtovacuum queue
2018-04-02 15:15 avtovacuum queue Artem Tomyuk <admin@leboutique.com>
@ 2018-04-02 15:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-04-02 15:30 ` Re: avtovacuum queue Artem Tomyuk <admin@leboutique.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2018-04-02 15:18 UTC (permalink / raw)
To: Artem Tomyuk <admin@leboutique.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: avtovacuum queue
2018-04-02 15:15 avtovacuum queue Artem Tomyuk <admin@leboutique.com>
2018-04-02 15:18 ` Re: avtovacuum queue Tom Lane <tgl@sss.pgh.pa.us>
@ 2018-04-02 15:30 ` Artem Tomyuk <admin@leboutique.com>
2018-04-02 15:34 ` Re: avtovacuum queue David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Artem Tomyuk @ 2018-04-02 15:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: avtovacuum queue
2018-04-02 15:15 avtovacuum queue Artem Tomyuk <admin@leboutique.com>
2018-04-02 15:18 ` Re: avtovacuum queue Tom Lane <tgl@sss.pgh.pa.us>
2018-04-02 15:30 ` Re: avtovacuum queue Artem Tomyuk <admin@leboutique.com>
@ 2018-04-02 15:34 ` David G. Johnston <david.g.johnston@gmail.com>
2018-04-02 16:43 ` Re: avtovacuum queue Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2018-04-02 15:34 UTC (permalink / raw)
To: Artem Tomyuk <admin@leboutique.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
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.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: avtovacuum queue
2018-04-02 15:15 avtovacuum queue Artem Tomyuk <admin@leboutique.com>
2018-04-02 15:18 ` Re: avtovacuum queue Tom Lane <tgl@sss.pgh.pa.us>
2018-04-02 15:30 ` Re: avtovacuum queue Artem Tomyuk <admin@leboutique.com>
2018-04-02 15:34 ` Re: avtovacuum queue David G. Johnston <david.g.johnston@gmail.com>
@ 2018-04-02 16:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-04-03 08:51 ` Re: avtovacuum queue Artem Tomyuk <admin@leboutique.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2018-04-02 16:43 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Artem Tomyuk <admin@leboutique.com>; pgsql-admin
"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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: avtovacuum queue
2018-04-02 15:15 avtovacuum queue Artem Tomyuk <admin@leboutique.com>
2018-04-02 15:18 ` Re: avtovacuum queue Tom Lane <tgl@sss.pgh.pa.us>
2018-04-02 15:30 ` Re: avtovacuum queue Artem Tomyuk <admin@leboutique.com>
2018-04-02 15:34 ` Re: avtovacuum queue David G. Johnston <david.g.johnston@gmail.com>
2018-04-02 16:43 ` Re: avtovacuum queue Tom Lane <tgl@sss.pgh.pa.us>
@ 2018-04-03 08:51 ` Artem Tomyuk <admin@leboutique.com>
0 siblings, 0 replies; 6+ messages in thread
From: Artem Tomyuk @ 2018-04-03 08:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2018-04-03 08:51 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-04-02 15:15 avtovacuum queue Artem Tomyuk <admin@leboutique.com>
2018-04-02 15:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-04-02 15:30 ` Artem Tomyuk <admin@leboutique.com>
2018-04-02 15:34 ` David G. Johnston <david.g.johnston@gmail.com>
2018-04-02 16:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-04-03 08:51 ` Artem Tomyuk <admin@leboutique.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox