From: | Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> |
---|---|
To: | "Syed, Rahila" <Rahila(dot)Syed(at)nttdata(dot)com> |
Cc: | Thom Brown <thom(at)linux(dot)com>, "PostgreSQL-development (pgsql-hackers(at)postgresql(dot)org)" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [PROPOSAL] VACUUM Progress Checker. |
Date: | 2015-09-24 12:37:46 |
Message-ID: | CAD21AoDkxcmRBGvYuCO+XxtST_sj_AHGnqCRH++uwxasWN4X=Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, Sep 23, 2015 at 12:24 AM, Syed, Rahila <Rahila(dot)Syed(at)nttdata(dot)com> wrote:
> Hello,
>
> Please find attached patch with bugs reported by Thom and Sawada-san solved.
>
>>* The progress of vacuum by autovacuum seems not to be displayed.
> The progress is stored in shared variables during autovacuum. I guess the reason they are not visible is that the entries are deleted as soon as the process exits.
> But the progress can be viewed while autovacuum worker is running.
>
Thank you for updating the patch.
I tested the latest version patch.
The followings are my review comments and questions.
* pg_stat_vacuum_progress should have the oid of relation being vacuumed.
When we run "VACUUM;", the all tables of current database will be vacuumed.
So pg_stat_vacuum_progress should have these oid in order to show
which table is vacuumed now.
* progress_message variable in PgBackendStatus is not used at all.
IIRC, progress_message variable is set the description of processing.
* The progress of VACUUM FULL seems wrong.
When I run VACUUM FULL for a table, I got following progress.
postgres(1)=# select * from pg_stat_vacuum_progress ;
-[ RECORD 1 ]-------+------
pid | 19190
total_pages | 1
scanned_pages | 1
total_heap_pages | 1
scanned_heap_pages | 1
total_index_pages |
scanned_index_pages |
percent_complete | 100
The table being vacuumed is 400MB, so it's not 1 page table.
* The vacuum by autovacuum is not displayed.
I tested about this by the executing the following queries in a row,
but the vacuum by autovacuum is not displayed,
postgres(1)=# select datname, pid, backend_start, query, state from
pg_stat_activity ;
datname | pid | backend_start |
query | state
----------+-------+-------------------------------+--------------------------------------------------------------------------+--------
postgres | 20123 | 2015-09-24 17:44:26.467021+09 | autovacuum: VACUUM
ANALYZE public.hoge | active
postgres | 19779 | 2015-09-24 17:42:31.57918+09 | select datname,
pid, backend_start, query, state from pg_stat_activity ; | active
(3 rows)
postgres(1)=# selecT * from pg_stat_vacuum_progress ;
pid | total_pages | scanned_pages | total_heap_pages |
scanned_heap_pages | total_index_pages | scanned_index_pages |
percent_complete
-----+-------------+---------------+------------------+--------------------+-------------------+---------------------+------------------
(0 rows)
postgres(1)=# select datname, pid, backend_start, query, state from
pg_stat_activity ;
datname | pid | backend_start |
query | state
----------+-------+-------------------------------+--------------------------------------------------------------------------+--------
postgres | 20123 | 2015-09-24 17:44:26.467021+09 | autovacuum: VACUUM
ANALYZE public.hoge | active
postgres | 19779 | 2015-09-24 17:42:31.57918+09 | select datname,
pid, backend_start, query, state from pg_stat_activity ; | active
(3 rows)
The vacuuming for hoge table took about 2min, but the progress of
vacuum is never displayed.
Could you check this on your environment?
Regards,
--
Masahiko Sawada
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2015-09-24 12:56:13 | pgsql: Lower *_freeze_max_age minimum values. |
Previous Message | Thom Brown | 2015-09-24 12:12:47 | Re: 9.5: Can't connect with PGSSLMODE=require on Windows |