Re: [HACKERS] CLUSTER command progress monitor - Mailing list pgsql-hackers
From | Tatsuro Yamada |
---|---|
Subject | Re: [HACKERS] CLUSTER command progress monitor |
Date | |
Msg-id | 6e7d43da-95da-216d-a015-f2563cd3c8de@lab.ntt.co.jp Whole thread Raw |
In response to | Re: [HACKERS] CLUSTER command progress monitor (Robert Haas <robertmhaas@gmail.com>) |
Responses |
Re: [HACKERS] CLUSTER command progress monitor
|
List | pgsql-hackers |
On 2019/03/02 4:15, Robert Haas wrote: > On Thu, Feb 28, 2019 at 11:54 PM Tatsuro Yamada > <yamada.tatsuro@lab.ntt.co.jp> wrote: >> Attached patch is wip patch. I rewrote the current design of the progress monitor and also wrote discussion points in the middle of this email. I'd like to get any feedback from -hackers. === Current design === CLUSTER command uses Index Scan or Seq Scan when scanning the heap. Depending on which one is chosen, the command will proceed in the following sequence of phases: * Scan method: Seq Scan 0. initializing (*2) 1. seq scanning heap (*1) 3. sorting tuples (*2) 4. writing new heap (*1) 5. swapping relation files (*2) 6. rebuilding index (*2) 7. performing final cleanup (*2) * Scan method: Index Scan 0. initializing (*2) 2. index scanning heap (*1) 5. swapping relation files (*2) 6. rebuilding index (*2) 7. performing final cleanup (*2) VACUUM FULL command will proceed in the following sequence of phases: 1. seq scanning heap (*1) 5. swapping relation files (*2) 6. rebuilding index (*2) 7. performing final cleanup (*2) (*1): increasing the value in heap_tuples_scanned column (*2): only shows the phase in the phase column The view provides the information of CLUSTER command progress details as follows # \d pg_stat_progress_cluster View "pg_catalog.pg_stat_progress_cluster" Column | Type | Collation | Nullable | Default ---------------------------+---------+-----------+----------+--------- pid | integer | | | datid | oid | | | datname | name | | | relid | oid | | | command | text | | | phase | text | | | cluster_index_relid | bigint | | | heap_tuples_scanned | bigint | | | heap_tuples_vacuumed | bigint | | | === Discussion points === - Progress counter for "3. sorting tuples" phase - Should we add pgstat_progress_update_param() in tuplesort.c like a "trace_sort"? Thanks to Peter Geoghegan for the useful advice! - Progress counter for "6. rebuilding index" phase - Should we add "index_vacuum_count" in the view like a vacuum progress monitor? If yes, I'll add pgstat_progress_update_param() to reindex_relation() of index.c. However, I'm not sure whether it is okay or not. - pg_stat_progress_rewrite - TBA === My test case === I share my test case of progress monitor. If someone wants to watch the current progress monitor, you can use this test case as a example. [Terminal1] Run this query on psql: select * from pg_stat_progress_cluster; \watch 0.05 [Terminal2] Run these queries on psql: drop table t1; create table t1 as select a, random() * 1000 as b from generate_series(0, 999999) a; create index idx_t1 on t1(a); create index idx_t1_b on t1(b); analyze t1; -- index scan set enable_seqscan to off; cluster verbose t1 using idx_t1; -- seq scan set enable_seqscan to on; set enable_indexscan to off; cluster verbose t1 using idx_t1; -- only given table name to cluster command cluster verbose t1; -- only cluster command cluster verbose; -- vacuum full vacuum full t1; -- vacuum full vacuum full; Thanks, Tatsuro Yamada
pgsql-hackers by date: