Thread: Re: checkpoint occurs very often when vacuum full running
Hi, Please don't cross post to multiple lists. On Thu, Nov 15, 2018 at 08:53:14PM +0200, Mariel Cherkassky wrote: > Can someone explain the logic behind it ? I know that vacuum full isnt > something recommended but I found out that whenever I run vacuum full on my > database checkpoint occurs during that time every second ! well I know that > VACUUM FULL duplicates the data into new data files and then it deletes the > old data files. The writing the vacuum does, is it with the checkpoint > process ? It's a good question. What version postgres are you using, and what is the setting of wal_level ? On Thu, Nov 15, 2018 at 11:28:40PM +0300, Sergei Kornilov wrote: > Row location is data. For example, index lookup relies on TID (tuple id, hidden ctid column) - physical row address indatafile. But, since VAC FULL has an exclusive lock, and since it's atomic (it's either going to succeed and use the new table or interrupted or otherwise fail and continue using the old table data), I it doesn't need to write to WAL, except if needed for physical replication. Same as CREATE TABLE AS and similar. In my test, setting wal_level=minimal seemed to avoid WAL writes from vac full. https://www.postgresql.org/docs/current/populate.html#POPULATE-PITR Justin
I'm just trying to under the logic in some environments that I faced (some has 9.6 version and wal level is replica and some has 9.2v and wal_level is set to archive. I'm not sure regarding your answer because I believe that there is a connection between the VACUUM FULL and the checkpoints that I saw during the vacuum full. Laurenz Albe from cybertec sent a good explanation about it to the pgsql-admins list You should check it out.
בתאריך יום ה׳, 15 בנוב׳ 2018 ב-22:46 מאת Justin Pryzby <pryzby@telsasoft.com>:
Hi,
Please don't cross post to multiple lists.
On Thu, Nov 15, 2018 at 08:53:14PM +0200, Mariel Cherkassky wrote:
> Can someone explain the logic behind it ? I know that vacuum full isnt
> something recommended but I found out that whenever I run vacuum full on my
> database checkpoint occurs during that time every second ! well I know that
> VACUUM FULL duplicates the data into new data files and then it deletes the
> old data files. The writing the vacuum does, is it with the checkpoint
> process ?
It's a good question. What version postgres are you using, and what is the
setting of wal_level ?
On Thu, Nov 15, 2018 at 11:28:40PM +0300, Sergei Kornilov wrote:
> Row location is data. For example, index lookup relies on TID (tuple id, hidden ctid column) - physical row address in datafile.
But, since VAC FULL has an exclusive lock, and since it's atomic (it's either
going to succeed and use the new table or interrupted or otherwise fail and
continue using the old table data), I it doesn't need to write to WAL, except
if needed for physical replication. Same as CREATE TABLE AS and similar. In
my test, setting wal_level=minimal seemed to avoid WAL writes from vac full.
https://www.postgresql.org/docs/current/populate.html#POPULATE-PITR
Justin