On 3/3/16 3:54 AM, Kyotaro HORIGUCHI wrote:
I wonder why there haven't been discussions so far on what kind
of information we want by this feature. For example I'd be happy
to see the time of last autovacuum trial and the cause if it has
been skipped for every table. Such information would (maybe)
naturally be shown in pg_stat_*_tables.
=====
=# select relid, last_completed_autovacuum, last_completed_autovacv_status,
last_autovacuum_trial, last_autovacuum_result from pg_stat_user_tables;
-[ RECORD 1 ]-----------------+------
relid | 16390
last_completed_autovacuum | 2016-03-01 01:25:00.349074+09
last_completed_autovac_status | Completed in 4 seconds. Scanned 434 pages,
skipped 23 pages
last_autovacuum_trial | 2016-03-03 17:33:04.004322+09
last_autovac_traial_status | Canceled by PID 2355. Processed 144/553 pages.
-[ RECORD 2 ]----------+------
...
last_autovacuum_trial | 2016-03-03 07:25:00.349074+09
last_autovac_traial_status | Completed in 4 seconds. Scanned 434 pages,
skipped 23 pages
-[ RECORD 3 ]----------+------
...
last_autovacuum_trial | 2016-03-03 17:59:12.324454+09
last_autovac_trial_status | Processing by PID 42334, 564 / 32526 pages done.
-[ RECORD 4 ]----------+------
...
last_autovacuum_trial | 2016-03-03 17:59:12.324454+09
last_autovac_trial_status | Skipped by dead-tuple threashold.
=====
I kinda like where you're going here, but I certainly don't think the
stats system is the way to do it. Stats bloat is already a problem on
bigger systems. More important, I don't think having just the last
result is very useful. If you've got a vacuum problem, you want to see
history, especially history of the vacuum runs themselves.
The good news is that vacuum is a very low-frequency operation, so it
has none of the concerns that the generic stats system does. I think it
would be reasonable to provide event triggers that fire on every
launcher loop, after a worker has built it's "TODO list", and after
every (auto)vacuum.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
