curious vacuum full behavior
I have 2 identical systems A + B.
B being a clone of A.
The table pg_catalog.pg_largeobject was identical on both systems: 300GB in total size; 100GB bloated.
I did following on A:
? vacuum full pg_catalog.pg_largeobject;
(using the default maintenance_work_mem of 64MB)
It took around 45 minutes and increased the diskspace by around 125% until the vacuum had been finished.
I did following on B:
? set maintenance_work_mem = '256MB';
? vacuum full pg_catalog.pg_largeobject;
This took around 5 minutes. I don't know if the diskspace ever increased.
I was really surprised.
Is there any explanation on this behavior?
Is vacuum full heavily using on-disk sort areas if maintenance_work_mem is too low?
Postgres Version 9.6
Thanks, Markus
On Thu, 2021-02-04 at 10:03 +0000, Zwettler Markus (OIZ) wrote:
I have 2 identical systems A + B.
B being a clone of A.The table pg_catalog.pg_largeobject was identical on both systems: 300GB in total size; 100GB bloated.
I did following on A:
ð vacuum full pg_catalog.pg_largeobject;
(using the default maintenance_work_mem of 64MB)
It took around 45 minutes and increased the diskspace by around 125% until the vacuum had been finished.I did following on B:
ð set maintenance_work_mem = '256MB';
ð vacuum full pg_catalog.pg_largeobject;
This took around 5 minutes. I don't know if the diskspace ever increased.I was really surprised.
Is there any explanation on this behavior?
Is vacuum full heavily using on-disk sort areas if maintenance_work_mem is too low?Postgres Version 9.6
VACUUM (FULL) will re-create the indexes too, and "maintenance_work_mem"
has an impact on index build speed.
But I have no explanation for such a large difference.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com