PostgreSQL 10.0 preview 功能增強 - 老闆特性, LONG SQL過程可視 pg_stat_progress_vacuum

postgresql , 10.0 , pg_stat_progress_vacuum

在postgresql中執行sql時，如果sql執行時間比較長，總想知道它執行到哪裡了，還剩餘多少任務，剩餘多少時間。

目前postgresql增加了對vacuum的可視監控(目前涵蓋了autovacuum與手工的vacuum指令，但是不涵蓋vacuum full)，将來還會加入更多的progress動态視圖，友善使用者了解long sql或者任務的執行情況。

動态視圖解釋如下

可以了解每個vacuum worker工作程序在工作過程中，掃描了多少heap page, 回收了多少page，index的vacuum關卡次數（每個關卡的大小取決于maintenance_work_mem可以存儲的heap tuple數量）。

table 28.21. pg_stat_progress_vacuum 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 of the table being vacuumed.

phase

text

current processing phase of vacuum. see table 28.22.

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

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 in 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

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

number of completed index vacuum cycles.

max_dead_tuples

number of dead tuples that we can store before needing to perform an index vacuum cycle, based on maintenance_work_mem.

num_dead_tuples

number of dead tuples collected since the last index vacuum cycle.

過程術語解釋

table 28.22. vacuum phases

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 maintenance_work_mem 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.

這個patch的讨論，詳見郵件組，本文末尾url。

postgresql社群的作風非常嚴謹，一個patch可能在郵件組中讨論幾個月甚至幾年，根據大家的意見反複的修正，patch合并到master已經非常成熟，是以postgresql的穩定性也是遠近聞名的。

<a href="https://www.postgresql.org/docs/devel/static/progress-reporting.html#vacuum-progress-reporting">https://www.postgresql.org/docs/devel/static/progress-reporting.html#vacuum-progress-reporting</a>