Re: Add index scan progress to pg_stat_progress_vacuum

Поиск
Список
Период
Сортировка
От Imseih (AWS), Sami
Тема Re: Add index scan progress to pg_stat_progress_vacuum
Дата
Msg-id 5C337EEE-F027-4E74-9028-9760E835FE9A@amazon.com
обсуждение исходный текст
Ответ на Re: Add index scan progress to pg_stat_progress_vacuum  ("Imseih (AWS), Sami" <simseih@amazon.com>)
Список pgsql-hackers
My apologies. The last attachment of documentation was the wrong file. Attached is the correct documentation file.

Thanks 

On 1/26/22, 8:07 PM, "Imseih (AWS), Sami" <simseih@amazon.com> wrote:

    Attached is the latest patch and associated documentation.

    This version addresses the index_ordinal_position column confusion. Rather than displaying the index position, the
pg_stat_progress_vacuumview now has 2 new column(s):
 
    index_total - this column will show the total number of indexes to be vacuumed
    index_complete_count - this column will show the total number of indexes processed so far. In order to deal with
theparallel vacuums, the parallel_workers ( planned workers ) value had to be exposed and each backends performing an
indexvacuum/cleanup in parallel had to advertise the number of indexes it vacuumed/cleaned. The # of indexes vacuumed
forthe parallel cleanup can then be derived the pg_stat_progress_vacuum view. 
 

    postgres=# \d pg_stat_progress_vacuum
                View "pg_catalog.pg_stat_progress_vacuum"
            Column        |  Type   | Collation | Nullable | Default
    ----------------------+---------+-----------+----------+---------
     pid                  | integer |           |          |
     datid                | oid     |           |          |
     datname              | name    |           |          |
     relid                | oid     |           |          |
     phase                | text    |           |          |
     heap_blks_total      | bigint  |           |          |
     heap_blks_scanned    | bigint  |           |          |
     heap_blks_vacuumed   | bigint  |           |          |
     index_vacuum_count   | bigint  |           |          |
     max_dead_tuples      | bigint  |           |          |
     num_dead_tuples      | bigint  |           |          |
     index_total          | bigint  |           |          |.                           <<<---------------------
     index_complete_count | numeric |           |          |.           <<<---------------------

    The pg_stat_progress_vacuum_index view includes:

    Indexrelid - the currently vacuumed index
    Leader_pid - the pid of the leader process. NULL if the process is the leader or vacuum is not parallel
    tuples_removed - the amount of indexes tuples removed. The user can use this column to see that the index vacuum
hasmovement.
 

    postgres=# \d pg_stat_progress_vacuum_index
          View "pg_catalog.pg_stat_progress_vacuum_index"
         Column     |  Type   | Collation | Nullable | Default
    ----------------+---------+-----------+----------+---------
     pid            | integer |           |          |
     datid          | oid     |           |          |
     datname        | name    |           |          |
     indexrelid     | bigint  |           |          |
     phase          | text    |           |          |
     leader_pid     | bigint  |           |          |
     tuples_removed | bigint  |           |          |



    On 1/12/22, 9:52 PM, "Imseih (AWS), Sami" <simseih@amazon.com> wrote:

        On 1/12/22, 1:28 PM, "Bossart, Nathan" <bossartn@amazon.com> wrote:

            On 1/11/22, 11:46 PM, "Masahiko Sawada" <sawada.mshk@gmail.com> wrote:
            > Regarding the new pg_stat_progress_vacuum_index view, why do we need
            > to have a separate view? Users will have to check two views. If this
            > view is expected to be used together with and joined to
            > pg_stat_progress_vacuum, why don't we provide one view that has full
            > information from the beginning? Especially, I think it's not useful
            > that the total number of indexes to vacuum (num_indexes_to_vacuum
            > column) and the current number of indexes that have been vacuumed
            > (index_ordinal_position column) are shown in separate views.

         > I suppose we could add all of the new columns to
         > pg_stat_progress_vacuum and just set columns to NULL as appropriate.
         > But is that really better than having a separate view?

        To add, since a vacuum can utilize parallel worker processes + the main vacuum process to perform index
vacuuming,it made sense to separate the backends doing index vacuum/cleanup in a separate view. 
 
        Besides what Nathan suggested, the only other clean option I can think of is to perhaps create a json column in
pg_stat_progress_vacuumwhich will include all the new fields. My concern with this approach is that it will make
usability,to flatten the json, difficult for users.
 

            > Also, I’m not sure how useful index_tuples_removed is; what can we
            > infer from this value (without a total number)?

        >    I think the idea was that you can compare it against max_dead_tuples
        >   and num_dead_tuples to get an estimate of the current cycle progress.
        >    Otherwise, it just shows that progress is being made.

        The main purpose is to really show that the "index vacuum" phase is actually making progress. Note that for
certaintypes of indexes, i.e. GIN/GIST the number of tuples_removed will end up exceeding the number of
num_dead_tuples.

            Nathan

            [0] https://postgr.es/m/7874FB21-FAA5-49BD-8386-2866552656C7%40amazon.com





Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Amit Langote
Дата:
Сообщение: Re: Question on partition pruning involving stable operator: timestamptz_ge_date
Следующее
От: Kyotaro Horiguchi
Дата:
Сообщение: Re: snapper and skink and fairywren (oh my!)