Hello, hackers!
I'm a technical support engineer of PostgreSQL.
In my much experience up until now,I have an idea that we can add
2 new vacuum statistics into pg_stat_xxx_tables.
Features I hope is following.
MORE VACUUM STATISTICS
==========================
Design & Motivation
---
1. Show about how long did vacuum spend for one table
This is a very important information for tuning vacuum & redesigning table,
but to see this information, we should check pg_log files and it cannot
show if log_autovacuum_min_duration=-1.We can already show vacuum end
time in current statistics, so we only have to add vacuum start time.
Vacuum execution time will be able to estimate by time between start
and end ( it is no longer need to check pg_log file ).
Furthermore, vacuum execution interval time will be also able to estimate.
To implement this feature, at least we need to modify pgstat_report_vacuum.
2. Page visibility rate of each table
There is no way to know how many page-bits are them of each tables stored
in their visibility maps. If we can show this information, then we will be
able to guess vacuum overhead for the table. For example, if this table is a
very big table but page visibility rate is high, then we can advise pg-users
that vacuum for this table will execute faster than they think by low I/O overhead.
Furthermore, this information can also be used in order to inform pg-users
about "real" index-only-scan usability.
To implement this feature, at least we need to count either number of
skipping or setting visible blocks at lazy_scan_heap.
I/F
---
Pg-users can show this information by select pg_stat_xxx_tables.
pg_stat_xxx_tables
-----------------------
relid
schemaname
relname
seq_scan
seq_tup_read
idx_scan
idx_tup_fetch
n_tup_ins
n_tup_upd
n_tup_del
n_tup_hot_upd
n_live_tup
n_mod_since_analyze
page_visibility----------------# add
last_vacuum_start--------------# add
last_vacuum_end----------------# rename
last_autovacuum_start----------# add
last_autovacuum_end------------# rename
last_analyze
last_autoanalyze
vacuum_count
autovacuum_count
analyze_count
autoanalyze_count
If hackers agree with my point,
I'd like to make a patch for these features.
Any comments are welcome.
Best Regards,
Naoya Anzai
---
Naoya Anzai
Engineering Department
NEC Solution Inovetors, Ltd.
E-Mail: nao-anzai@xc.jp.nec.com bench.coffee@gmail.com
---