pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcheckpoint occurs very often when vacuum full running
9+ messages / 4 participants
[nested] [flat]
* checkpoint occurs very often when vacuum full running
@ 2018-11-15 18:53 Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Mariel Cherkassky @ 2018-11-15 18:53 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; pgsql-performance@lists.postgresql.org
Hi,
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 ?
Is there any connection ?
Thanks.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: checkpoint occurs very often when vacuum full running
@ 2018-11-15 19:10 Sergei Kornilov <sk@zsrv.org>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Sergei Kornilov @ 2018-11-15 19:10 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi
Checkpoint can be occurs due timeout (checkpoint_timeout) or due amount of WAL (max_wal_size).
Vacuum full does write all data through WAL and therefore may trigger checkpoint more frequently.
regards, Sergei
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: checkpoint occurs very often when vacuum full running
@ 2018-11-15 19:29 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Sergei Kornilov <sk@zsrv.org>
0 siblings, 2 replies; 9+ messages in thread
From: Mariel Cherkassky @ 2018-11-15 19:29 UTC (permalink / raw)
To: Sergei Kornilov <sk@zsrv.org>; +Cc: pgsql-admin@lists.postgresql.org; pgsql-performance@lists.postgresql.org
First of all thank you for the quick answer. In my case checkpoint happened
every one second during the vacuum full so the checkpoint timeout isn't
relevant. My guess was that it writes the changes to the wals but I didn't
find anything about it in the documentation. Can you share a link that
proves it ? I mean basicly the wals should contain the changes, and vacuum
full changes the location of the data and not actually the data.
On Thu, Nov 15, 2018, 9:10 PM Sergei Kornilov <sk@zsrv.org wrote:
> Hi
>
> Checkpoint can be occurs due timeout (checkpoint_timeout) or due amount of
> WAL (max_wal_size).
> Vacuum full does write all data through WAL and therefore may trigger
> checkpoint more frequently.
>
> regards, Sergei
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: checkpoint occurs very often when vacuum full running
@ 2018-11-15 20:28 Sergei Kornilov <sk@zsrv.org>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Sergei Kornilov @ 2018-11-15 20:28 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi
> I mean basicly the wals should contain the changes, and vacuum full changes the location of the data and not actually the data.
Row location is data. For example, index lookup relies on TID (tuple id, hidden ctid column) - physical row address in datafile.
Postgresql WAL - it is about physical changes in datafiles (block level), not logical. Just moving one row to another place without logical changes means: mark row deleted in old place, write to new place and update every index which contains this row.
And vacuum full does not change location, it create copy in different datafile. Then it rebuild every index because TID was obviously changed. Then vacuum full drop old datafiles. Full size of new datafile and indexes should be written to WAL, because all of this is changes and must be reliable written (and then can be replayed on replicas).
> but I didn't find anything about it in the documentation
hmm, i can not found something exact in documentation about it.. It's my knowledge about postgresql internals.
You can read this article: https://www.depesz.com/2011/07/14/write-ahead-log-understanding-postgresql-conf-checkpoint_segments-... Its about WAL logic. All IO operations use pages, and difference between pages written to WAL.
For example, full_page_writes setting ( https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-FULL-PAGE-WRITES ) say about pages too.
> writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint.
If you want change few bytes in page - the whole page (8kb typical) will be written to WAL during first change of this page after checkpoint.
regards, Sergei
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: checkpoint occurs very often when vacuum full running
@ 2018-11-15 20:32 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2018-11-15 20:32 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; Sergei Kornilov <sk@zsrv.org>; +Cc: pgsql-admin@lists.postgresql.org
Mariel Cherkassky wrote:
> First of all thank you for the quick answer. In my case checkpoint happened
> every one second during the vacuum full so the checkpoint timeout isn't relevant.
> My guess was that it writes the changes to the wals but I didn't find anything
> about it in the documentation. Can you share a link that proves it ?
> I mean basicly the wals should contain the changes, and vacuum full changes
> the location of the data and not actually the data.
VACUUM (FULL) completely rewrites all the tables and indexes, so the complete
database will go into the WAL (these data changes have to be replayed in case
of a crash!). WAL contains the physical and not the logical changes, and the
physical data *are* modified.
You should let autovacuum do the job instead of running VACUUM (FULL), unless
your whole database is bloated beyond tolerance. That will cause less WAL
activity and also won't disrupt normal database operation.
If you really need that VACUUM (FULL), you can increase "max_wal_size" to
get fewer checkpoints.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: checkpoint occurs very often when vacuum full running
@ 2018-11-15 20:46 Justin Pryzby <pryzby@telsasoft.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Justin Pryzby @ 2018-11-15 20:46 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; Sergei Kornilov <sk@zsrv.org>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: checkpoint occurs very often when vacuum full running
@ 2018-11-17 11:31 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 9+ messages in thread
From: Mariel Cherkassky @ 2018-11-17 11:31 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Sergei Kornilov <sk@zsrv.org>; pgsql-performance@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: checkpoint occurs very often when vacuum full running
@ 2018-11-17 11:36 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 9+ messages in thread
From: Mariel Cherkassky @ 2018-11-17 11:36 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Sergei Kornilov <sk@zsrv.org>; pgsql-admin@lists.postgresql.org
Hi Laurenz.
thank you for the explanation but I still got a few questions about this
subject :
1.By physical location of the data do you mean the location on disk of the
objects ? I mean i thought that the wal files containing the logical
changes , for example If I run an update then the wal file will contain the
update in some format. Can you explain then what exactly it contains and
what you meant by physical ?
2.So If the vacuum full stopped in some brutal way (kill -9 or the disk run
of space) does all the new data files that are created deleted or left on
the storage as orphan files ?
Thanks, Mariel.
בתאריך יום ה׳, 15 בנוב׳ 2018 ב-22:32 מאת Laurenz Albe <
laurenz.albe@cybertec.at>:
> Mariel Cherkassky wrote:
> > First of all thank you for the quick answer. In my case checkpoint
> happened
> > every one second during the vacuum full so the checkpoint timeout isn't
> relevant.
> > My guess was that it writes the changes to the wals but I didn't find
> anything
> > about it in the documentation. Can you share a link that proves it ?
> > I mean basicly the wals should contain the changes, and vacuum full
> changes
> > the location of the data and not actually the data.
>
> VACUUM (FULL) completely rewrites all the tables and indexes, so the
> complete
> database will go into the WAL (these data changes have to be replayed in
> case
> of a crash!). WAL contains the physical and not the logical changes, and
> the
> physical data *are* modified.
>
> You should let autovacuum do the job instead of running VACUUM (FULL),
> unless
> your whole database is bloated beyond tolerance. That will cause less WAL
> activity and also won't disrupt normal database operation.
>
> If you really need that VACUUM (FULL), you can increase "max_wal_size" to
> get fewer checkpoints.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: checkpoint occurs very often when vacuum full running
@ 2018-11-17 19:28 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Laurenz Albe @ 2018-11-17 19:28 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: Sergei Kornilov <sk@zsrv.org>; pgsql-admin@lists.postgresql.org
Mariel Cherkassky wrote:
> thank you for the explanation but I still got a few questions about this subject :
> 1.By physical location of the data do you mean the location on disk of the objects?
> I mean i thought that the wal files containing the logical changes , for example
> If I run an update then the wal file will contain the update in some format.
> Can you explain then what exactly it contains and what you meant by physical ?
It does *not* contain the SQL executed - how'd that work with functions like
"random"?
Rather, the information is like "replace the 42 bytes from offset 99 of block 12
in file xy with these bytes: ...".
> 2.So If the vacuum full stopped in some brutal way (kill -9 or the disk run of space)
> does all the new data files that are created deleted or left on the storage as orphan files ?
In an out of space scenario, the files would get removed.
If you kill -9 the backend they will be left behind.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2018-11-17 19:28 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-15 18:53 checkpoint occurs very often when vacuum full running Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-11-15 19:10 ` Sergei Kornilov <sk@zsrv.org>
2018-11-15 19:29 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-11-15 20:28 ` Sergei Kornilov <sk@zsrv.org>
2018-11-15 20:32 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-11-17 11:36 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-11-17 19:28 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-11-15 20:46 ` Justin Pryzby <pryzby@telsasoft.com>
2018-11-17 11:31 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
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