From: Justin Pryzby <pryzby@telsasoft.com>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
To: Sergei Kornilov <sk@zsrv.org>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: checkpoint occurs very often when vacuum full running
Date: Thu, 15 Nov 2018 14:46:13 -0600
Message-ID: <20181115204612.GS16896@telsasoft.com> (raw)
In-Reply-To: <7817341542313720@myt6-fe24916a5562.qloud-c.yandex.net> <CA+t6e1=-OJ8XEitrH9GVDoHXQKNTkYN27O4cKmq5vkiGWbp3zA@mail.gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, mariel.cherkassky@gmail.com, sk@zsrv.org, pgsql-performance@lists.postgresql.org
Subject: Re: checkpoint occurs very often when vacuum full running
In-Reply-To: <20181115204612.GS16896@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox