Thread: Does the work made by vaccum in the current pass is lost when interrupted?
Does the work made by vaccum in the current pass is lost when interrupted?
I have a large table (billions of records) which has not been vacuum and bloated.
Vacuum scale factor was left at the default.
I ran a vacuum on a DEV system and it makes several passes (scanning heap/vacuuming indexes/vacumming heap) which take more than an hour each.
On a PROD system, I may have to kill the job midway.
Should I reduce the autovacuum_work_mem of my session? Currently 1GB
Does the work made in the current pass is lost when interrupted?
Correct me if I am not right, but vacuumed indexes and heap made by the current pass will go to disk, but the heap should be rescan on the next vacuum.
I guess that the visibility map is updated only at the end of each pass.
My best regards
Thank you
Jean-Marc Lessard
Administrateur de base de données / Database Administrator
Ultra Electronics Forensic Technology Inc.
T +1 514 489 4247 x4164
www.ultra-forensictechnology.com
Attachment
Re: Does the work made by vaccum in the current pass is lost when interrupted?
I have a large table (billions of records) which has not been vacuum and bloated.
Vacuum scale factor was left at the default.
I ran a vacuum on a DEV system and it makes several passes (scanning heap/vacuuming indexes/vacumming heap) which take more than an hour each.
On a PROD system, I may have to kill the job midway.
Should I reduce the autovacuum_work_mem of my session? Currently 1GB
Does the work made in the current pass is lost when interrupted?
Correct me if I am not right, but vacuumed indexes and heap made by the current pass will go to disk, but the heap should be rescan on the next vacuum.
I guess that the visibility map is updated only at the end of each pass.
My best regards
Thank you
Jean-Marc Lessard
Administrateur de base de données / Database Administrator
Ultra Electronics Forensic Technology Inc.
T +1 514 489 4247 x4164
www.ultra-forensictechnology.com
Attachment
Re: Does the work made by vaccum in the current pass is lost when interrupted?
I have a large table (billions of records) which has not been vacuum and bloated.
Vacuum scale factor was left at the default.
I ran a vacuum on a DEV system and it makes several passes (scanning heap/vacuuming indexes/vacumming heap) which take more than an hour each.
On a PROD system, I may have to kill the job midway.
Should I reduce the autovacuum_work_mem of my session? Currently 1GB
RE: Does the work made by vaccum in the current pass is lost when interrupted?
Thank you Michael for your suggestion,
I will reduce vacuum_cost_delay and increasing vacuum_cost_limit to speedup vacuum.
But the I/O system is very solicitated on PROD system and I do not want to impact end user performance.
If aggressive cost_delay and vacuum_cost_limit slow down significatively end user performance, I may have to kill the vacuum, but would like to keep as much vacuum work as possible.
My concern is about the size of the max_dead_tuples buffer determined by the autovacuum_work_mem
- autovacuum_work_mem is currently 1GB (default to maintenance_work_mem)
When I ran a vacuum on a DEV system and it made a single “scanning heap/vacuuming indexes/vacuuming heap” cycle of about 12 hours.
The vacuuming indexes phase is half the total time (6 hrs).
Q1: I guess that if I have to kill the vacuum midway, I will lose all the work it did during the scanning heap step? Right?
Q2: If I reduce autovacuum_work_mem to 32MB, is there any side effect performance on the vacuum?
thanks