Обсуждение: [GENERAL] Detailed progress reporting for "vacuuming indexes" stage
Hi all, We've recently upgraded to 9.6.1 and now playing with newly available pg_stat_progress_vacuum view. There are 7 stages for standard VACUUM process which is reportable via this view. For time consuming stages like "scanning heap" or "vacuuming heap" you are able to get stage progress idea by doing: (heap_blks_scanned | heap_blks_vacuumed) / heap_blks_total * 100% However stage "vacuuming indexes" does not give any idea of how long this stage might take. We have some rather big (500M+ records) with 5-7 indexes. Sometimes it takes us 20 hours+ to get table vacuumed and all progress reporting we have for stage 3 is that it is stage 3. According to code (https://github.com/postgres/postgres/blob/1d25779284fe1ba08ecd57e647292a9deb241376/src/backend/commands/vacuumlazy.c#L708-L712) looks like there might be a way to report number of indexes left to be vacuumed. The question is: is it a right thing to expect a patch which adds more detailed stage 3 reporting? Say index number being vacuumed. Thanks. -- Alexander Shchapov
On Mon, Jan 23, 2017 at 8:32 PM, Alexander Shchapov <alexanderad@gmail.com> wrote: > There are 7 stages for standard VACUUM process which is reportable via > this view. For time consuming stages like "scanning heap" or > "vacuuming heap" you are able to get stage progress idea by doing: > > (heap_blks_scanned | heap_blks_vacuumed) / heap_blks_total * 100% > > However stage "vacuuming indexes" does not give any idea of how long > this stage might take. We have some rather big (500M+ records) with > 5-7 indexes. Sometimes it takes us 20 hours+ to get table vacuumed and > all progress reporting we have for stage 3 is that it is stage 3. Yes, things could be improved here. > According to code > (https://github.com/postgres/postgres/blob/1d25779284fe1ba08ecd57e647292a9deb241376/src/backend/commands/vacuumlazy.c#L708-L712) > looks like there might be a way to report number of indexes left to be > vacuumed. Indexes may vary in size. For example if a table has a bunch of BRIN indexes and one huge btree most of the time would be spent on the btree part. So the number would not make much sense. > The question is: is it a right thing to expect a patch which adds more > detailed stage 3 reporting? Say index number being vacuumed. What would be more interesting would be like the heap to get information on the index block being cleaned up with reports being done via index_bulk_delete(). That's more work, and that would be more helpful than just a number. So patches welcome. -- Michael
On Tue, Jan 24, 2017 at 8:57 AM, Michael Paquier <michael.paquier@gmail.com> wrote: > What would be more interesting would be like the heap to get > information on the index block being cleaned up with reports being > done via index_bulk_delete(). That's more work, and that would be more > helpful than just a number. > > So patches welcome. Thanks for the reply and a tip, Michael. Do I get it correctly, that you suggesting to calculate something like index_blks_total and increment index_blks_vacuumed? I'll try to look into this. -- Alexander Shchapov
On 24 January 2017 at 06:57, Michael Paquier <michael.paquier@gmail.com> wrote: > On Mon, Jan 23, 2017 at 8:32 PM, Alexander Shchapov > <alexanderad@gmail.com> wrote: >> There are 7 stages for standard VACUUM process which is reportable via >> this view. For time consuming stages like "scanning heap" or >> "vacuuming heap" you are able to get stage progress idea by doing: >> >> (heap_blks_scanned | heap_blks_vacuumed) / heap_blks_total * 100% >> >> However stage "vacuuming indexes" does not give any idea of how long >> this stage might take. We have some rather big (500M+ records) with >> 5-7 indexes. Sometimes it takes us 20 hours+ to get table vacuumed and >> all progress reporting we have for stage 3 is that it is stage 3. > > Yes, things could be improved here. Yes, it seems that VACUUM progress reporting feature is only about 40% complete. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Tue, Jan 24, 2017 at 5:37 PM, Alexander Shchapov <alexanderad@gmail.com> wrote: > On Tue, Jan 24, 2017 at 8:57 AM, Michael Paquier > <michael.paquier@gmail.com> wrote: >> What would be more interesting would be like the heap to get >> information on the index block being cleaned up with reports being >> done via index_bulk_delete(). That's more work, and that would be more >> helpful than just a number. >> >> So patches welcome. > > Thanks for the reply and a tip, Michael. > Do I get it correctly, that you suggesting to calculate something like > index_blks_total and increment index_blks_vacuumed? Something like that may be helpful as vacuum is basically triggering a bulk deletion on the indexes vacuumed. What makes the thing tricky is how to integrate that correctly with index AMs. So while it can be done, it deserves careful thoughts. -- Michael