From c16dc1aca5e01e6acaadfcf38f5fc964a381dc62 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Tue, 15 Mar 2016 13:31:18 -0400 Subject: Add simple VACUUM progress reporting. There's a lot more that could be done here yet - in particular, this reports only very coarse-grained information about the index vacuuming phase - but even as it stands, the new pg_stat_progress_vacuum can tell you quite a bit about what a long-running vacuum is actually doing. Amit Langote and Robert Haas, based on earlier work by Vinayak Pokale and Rahila Syed. --- doc/src/sgml/monitoring.sgml | 207 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 207 insertions(+) (limited to 'doc/src') diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index ec5328ea8fd..de79fde6be3 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -507,6 +507,12 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser yet included in pg_stat_user_functions). + + pg_stat_progress_vacuumpg_stat_progress_vacuum + One row for each backend (including autovacuum worker processes) running + VACUUM, showing current progress. + See . + @@ -2490,6 +2496,207 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid, + + Progress Reporting + + + PostgreSQL has the ability to report the progress of + certain commands during command execution. Currently, the only command + which supports progress reporting is VACUUM. This may be + expanded in the future. + + + + VACUUM Progress Reporting + + + Whenever VACUUM is running, the + pg_stat_progress_vacuum view will contain + one row for each backend (including autovacuum worker processes) that is + currently vacuuming. The tables below describe the information + that will be reported and provide information about how to interpret it. + Progress reporting is not currently supported for VACUUM FULL + and backends running VACUUM FULL will not be listed in this + view. + + + + <structname>pg_stat_progress_vacuum</structname> View + + + + Column + Type + Description + + + + + + pid + integer + Process ID of backend. + + + datid + oid + OID of the database to which this backend is connected. + + + datname + name + Name of the database to which this backend is connected. + + + relid + oid + OID of the table being vacuumed. + + + phase + text + + Current processing phase of vacuum. See . + + + + heap_blks_total + bigint + + Total number of heap blocks in the table. This number is reported + as of the beginning of the scan; blocks added later will not be (and + need not be) visited by this VACUUM. + + + + heap_blks_scanned + bigint + + Number of heap blocks scanned. Because the + visibility map is used to optimize scans, + some blocks will be skipped without inspection; skipped blocks are + included this total, so that this number will eventually become + equal to heap_blks_total when the vacuum is complete. + This counter only advances when the phase is scanning heap. + + + + heap_blks_vacuumed + bigint + + Number of heap blocks vacuumed. Unless the table has no indexes, this + counter only advances when the phase is vacuuming heap. + Blocks that contain no dead tuples are skipped, so the counter may + sometimes skip forward in large increments. + + + + index_vacuum_count + bigint + + Number of completed index vacuums cycles. + + + + max_dead_tuples + bigint + + Number of dead tuples that we can store before needing to perform + an index vacuum cycle, based on + . + + + + num_dead_tuples + bigint + + Number of dead tuples collected since the last index vacuum cycle. + + + + +
+ + + VACUUM phases + + + + Phase + Description + + + + + + initializing + + VACUUM is preparing to begin scanning the heap. This + phase is expected to be very brief. + + + + scanning heap + + VACUUM is currently scanning the heap. It will prune and + defragment each page if required, and possibly perform freezing + activity. The heap_blks_scanned column can be used + to monitor the progress of the scan. + + + + vacuuming indexes + + VACUUM is currently vacuuming the indexes. If a table has + any indexes, this will happen at least once per vacuum, after the heap + has been completely scanned. It may happen multiple times per vacuum + if is insufficient to + store the number of dead tuples found. + + + + vacuuming heap + + VACUUM is currently vacuuming the heap. Vacuuming the heap + is distinct from scanning the heap, and occurs after each instance of + vacuuming indexes. If heap_blks_scanned is less than + heap_blks_total, the system will return to scanning + the heap after this phase is completed; otherwise, it will begin + cleaning up indexes after this phase is completed. + + + + cleaning up indexes + + VACUUM is currently cleaning up indexes. This occurs after + the heap has been completely scanned and all vacuuming of the indexes + and the heap has been completed. + + + + truncating heap + + VACUUM is currently truncating the heap so as to return + empty pages at the end of the relation to the operating system. This + occurs after cleaning up indexes. + + + + performing final cleanup + + VACUUM is performing final cleanup. During this phase, + VACUUM will vacuum the free space map, update statistics + in pg_class, and report statistics to the statistics + collector. When this phase is completed, VACUUM will end. + + + + +
+ + + + Dynamic Tracing -- cgit v1.2.3