understanding max_wal_size,wal_keep_segments and checkpoints
Hey, I'm trying to understand the logic behind all of these so I would be happy if you can confirm what I understood or correct me if I'm wrong : -The commit command writes all the data in the wal_buffers is written into the wal files. -Checkpoints writes the data itself (blocks that were changed) into the data files in the base dir. Just to make sure, as part of the checkpoint, it needs to read the wal files that were generated since the last checkpoint right ? -max_wal_size is a soft limit for the total size of all the wals that were generated. When the total_size of the pg_xlog dir reaches max_wal_size(can increase it because of peaks and some other issues..) the db will force a checkpoint to write the changes from the wals into the disk and then it will start recycling old wals (all of them ? or only those who were written ?). -wal_keep_segments is meant to help standbys that didn't receive the wals, so it allow us to keep wal_keep_segments wals in our pg_xlog dir. - in case we have a collision between wal_keep_segments and max_wal_size the wal_keep_segments will be the one that be used right ?. For example, lets say my wal_size is default(16MB). I set max_wal_size to 1GB which is 1600/16=100 wals. However, my wal_keep_segments is set to 300. It means that when the total_size of the pg_xlog directory will reach 1GB, checkpoint will be forced but old wal files wont be recycled/deleted ? Thanks.
Re: understanding max_wal_size,wal_keep_segments and checkpoints
Mariel Cherkassky wrote: > I'm trying to understand the logic behind all of these so I would be happy > if you can confirm what I understood or correct me if I'm wrong : > -The commit command writes all the data in the wal_buffers is written into > the wal files. All the transaction log for the transaction has to be written to file, and the files have to be sync'ed to storage before COMMIT completes. That way the transaction can be replayed in case of a crash. > -Checkpoints writes the data itself (blocks that were changed) into the data > files > in the base dir. Just to make sure, as part of the checkpoint, it needs to > read the > wal files that were generated since the last checkpoint right ? No WAL file has to be read during a checkpoint. When data in the database ar modified, they are modified in the "shared buffers" RAM cache. Later, these "direty blocks" are written to disk by the background writer process or the checkpoint. > -max_wal_size is a soft limit for the total size of all the wals that were > generated. > When the total_size of the pg_xlog dir reaches max_wal_size(can increase it > because > of peaks and some other issues..) the db will force a checkpoint to write > the changes > from the wals into the disk and then it will start recycling old wals (all > of them ? > or only those who were written ?). A checkpoint is forced when more than max_wal_size WAL has been written since the last checkpoint. After a checkpoint, unneeded WAL segments are either recycled (renamed and reused) or deleted (if max_wal_size has been exceeded). WAL segments are unneeded if they are older than the checkpoint, have been archived (if archiving is configured), don't need to be kept around because of wal_keep_segments and are older than the position of any active replication slot. > -wal_keep_segments is meant to help standbys that didn't receive the wals, so > it allow > us to keep wal_keep_segments wals in our pg_xlog dir. Yes. > - in case we have a collision between wal_keep_segments and max_wal_size the > wal_keep_segments will be the one that be used right ?. For example, lets > say my > wal_size is default(16MB). I set max_wal_size to 1GB which is 1600/16=100 > wals. > However, my wal_keep_segments is set to 300. It means that when the > total_size of > the pg_xlog directory will reach 1GB, checkpoint will be forced but old wal > files > wont be recycled/deleted ? Checkpoints are not forced by the size of pg_xlog, but by the amount of WAL created since the last checkpoint. The last wal_keep_segments WAL segments are always kept around, even if that exceeds max_wal_size. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: understanding max_wal_size,wal_keep_segments and checkpoints
> > I'm trying to understand the logic behind all of these so I would be > happy > > if you can confirm what I understood or correct me if I'm wrong : > > -The commit command writes all the data in the wal_buffers is written > into the wal files. > > All the transaction log for the transaction has to be written to file, and > the > files have to be sync'ed to storage before COMMIT completes. > That way the transaction can be replayed in case of a crash. > > *Yeah, so basically if we open a transaction and we do some insert queries, until the transaction is commited the changes**(the wal data and not the blocked that are chaned)** are kept in the wal buffers ? . When the user commits the transaction, the wal buffer(only the transaction log of that specific transaction ?) is written to wal files. When the database completes saving the content of the transaction log into the wal files, the commit completes. Did I got it right ?* > -Checkpoints writes the data itself (blocks that were changed) into the > data files > > in the base dir. Just to make sure, as part of the checkpoint, it needs > to read the > > wal files that were generated since the last checkpoint right ? > > No WAL file has to be read during a checkpoint. > > When data in the database ar modified, they are modified in the "shared > buffers" > RAM cache. Later, these "direty blocks" are written to disk by the > background > writer process or the checkpoint. > *What I meant, when checkpoint occurs, it reads the wal files created since last checkpoint, and does those changing on the data blocks on the disk ? I was not talking about dirty blocks from shared_buffer.* > > > -max_wal_size is a soft limit for the total size of all the wals that > were generated. > > When the total_size of the pg_xlog dir reaches max_wal_size(can > increase it because > > of peaks and some other issues..) the db will force a checkpoint to > write the changes > > from the wals into the disk and then it will start recycling old wals > (all of them ? > > or only those who were written ?). > > A checkpoint is forced when more than max_wal_size WAL has been written > since the > last checkpoint. > > After a checkpoint, unneeded WAL segments are either recycled (renamed and > reused) > or deleted (if max_wal_size has been exceeded). > > WAL segments are unneeded if they are older than the checkpoint, have been > archived > (if archiving is configured), don't need to be kept around because of > wal_keep_segments > and are older than the position of any active replication slot. > *so I'f I want have replication slot and wal_keep_segment is 0 after the > archiving of the wal it should be recycled/deleted ?* > > > -wal_keep_segments is meant to help standbys that didn't receive the > wals, so it allow > > us to keep wal_keep_segments wals in our pg_xlog dir. > > Yes. > > > - in case we have a collision between wal_keep_segments and max_wal_size > the > > wal_keep_segments will be the one that be used right ?. For example, > lets say my > > wal_size is default(16MB). I set max_wal_size to 1GB which is > 1600/16=100 wals. > > However, my wal_keep_segments is set to 300. It means that when the > total_size of > > the pg_xlog directory will reach 1GB, checkpoint will be forced but old > wal files > > wont be recycled/deleted ? > > Checkpoints are not forced by the size of pg_xlog, but by the amount of WAL > created since the last checkpoint. > > The last wal_keep_segments WAL segments are always kept around, even if > that > exceeds max_wal_size. > *So basically having wal_keep_segments and replication slot configured > together is a mistake right ? In that case, if you have both configured, > and you set wal_keep_segments to 0, the db should delete all the unused > wals ?* > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > >
Re: understanding max_wal_size,wal_keep_segments and checkpoints
Mariel Cherkassky wrote: > Yeah, so basically if we open a transaction and we do some insert queries, > until the transaction > is commited the changes(the wal data and not the blocked that are chaned) > are kept in the wal buffers ? > . When the user commits the transaction, the wal buffer(only the > transaction log of that specific > transaction ?) is written to wal files. When the database completes saving > the content of the > transaction log into the wal files, the commit completes. Did I got it right > ? WAL can be written to file before the transaction commits. Otherwise the size of a transaction would be limited. Only at commit time, it has to be written out and flushed to disk. > What I meant, when checkpoint occurs, it reads the wal files created since > last checkpoint, > and does those changing on the data blocks on the disk ? I was not talking > about dirty blocks > from shared_buffer. No, PostgreSQL does not read the WAL files when it performs a checkpoint. When data are modified, first WAL is written, then it is written to shared buffers. The checkpoint flushes dirty pages in shared buffers to disk. > > so I'f I want have replication slot and wal_keep_segment is 0 after the > > archiving of > > the wal it should be recycled/deleted ? Only if it is older than the position of the replication slot. > > So basically having wal_keep_segments and replication slot configured > > together is a mistake right ? > > In that case, if you have both configured, and you set wal_keep_segments to > > 0, the db should > > delete all the unused wals ? It is pointless to have both a replication slot and wal_keep_segments, yes. Setting wal_keep_segments to 0 is the right move in that case and should reduce pg_xlog size in time. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
ERROR: unrecognized parameter "autovacuum_analyze_scale_factor"
Hey, I have a very big toasted table in my db(9.2.5). Autovacuum doesnt gather statistics on it because the analyze_scale/threshold are default and as a result autoanalyze is never run and the statistics are wrong : select * from pg_stat_all_Tables where relname='pg_toast_13488395'; -[ RECORD 1 ]-+-- relid | 13388396 schemaname| pg_toast relname | pg_toast_13488395 seq_scan | 42 seq_tup_read | 71163925 idx_scan | 5374497 idx_tup_fetch | 2530272449 n_tup_ins | 1253680014 n_tup_upd | 0 n_tup_del | 1253658363 n_tup_hot_upd | 0 n_live_tup| 49425717 *wrong* n_dead_tup| 7822920 *wrong* last_vacuum | last_autovacuum | 2019-02-12 20:54:44.247083-05 last_analyze | *last_autoanalyze |* vacuum_count | 0 autovacuum_count | 3747 analyze_count | 0 autoanalyze_count | 0 When I try to set the both the scale_factor / threshold I'm getting the next error : alter table orig_table set (toast.autovacuum_analyze_scale_factor=0); ERROR: unrecognized parameter "autovacuum_analyze_scale_factor" Any idea why ? I didn't find in the release documentations a note for a similar bug.
Re: ERROR: unrecognized parameter "autovacuum_analyze_scale_factor"
On 2019-Feb-13, Mariel Cherkassky wrote: > Hey, > I have a very big toasted table in my db(9.2.5). Six years of bugfixes missing there ... you need to think about an update. > Autovacuum doesnt gather > statistics on it because the analyze_scale/threshold are default and as a > result autoanalyze is never run and the statistics are wrong : analyze doesn't process toast tables anyway. I think the best you could do is manually vacuum this table. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: ERROR: unrecognized parameter "autovacuum_analyze_scale_factor"
To be honest, it isnt my db, but I just have access to it ... Either way, so I need to change the vacuum_Analyze_scale/threshold for the original table ? But the value will be too high/low for the original table. For example if my original table has 30,000 rows and my toasted has 100,000,000 rows. I want to analyze every 50K records in the toasted (by the way, sounds legit ?) which is 0.05% of 100m. With this value it means that every 0.05*30,000=1500 updated/deletes on the original table it will run analyze on the original table which is very often... Doesn't it seems a little bit problematic ? בתאריך יום ד׳, 13 בפבר׳ 2019 ב-18:13 מאת Alvaro Herrera < [email protected]>: > On 2019-Feb-13, Mariel Cherkassky wrote: > > > Hey, > > I have a very big toasted table in my db(9.2.5). > > Six years of bugfixes missing there ... you need to think about an > update. > > > Autovacuum doesnt gather > > statistics on it because the analyze_scale/threshold are default and as a > > result autoanalyze is never run and the statistics are wrong : > > analyze doesn't process toast tables anyway. > > I think the best you could do is manually vacuum this table. > > -- > Álvaro Herrerahttps://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
Re: ERROR: unrecognized parameter "autovacuum_analyze_scale_factor"
On 2019-Feb-13, Mariel Cherkassky wrote: > To be honest, it isnt my db, but I just have access to it ... Well, I suggest you forget the password then :-) > Either way, so I need to change the vacuum_Analyze_scale/threshold for the > original table ? But the value will be too high/low for the original table. > For example if my original table has 30,000 rows and my toasted has > 100,000,000 rows. I want to analyze every 50K records in the toasted (by > the way, sounds legit ?) which is 0.05% of 100m. With this value it means > that every 0.05*30,000=1500 updated/deletes on the original table it will > run analyze on the original table which is very often... > Doesn't it seems a little bit problematic ? Autovacuum considers main table and toast table separately for vacuuming, so nothing you do to the parameters for the main table will affect the vacuuming schedule of the toast table. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: ERROR: unrecognized parameter "autovacuum_analyze_scale_factor"
I meant the anaylze, if anaylze will run very often on the original table, arent there disadvantages for it ? בתאריך יום ד׳, 13 בפבר׳ 2019 ב-18:54 מאת Alvaro Herrera < [email protected]>: > On 2019-Feb-13, Mariel Cherkassky wrote: > > > To be honest, it isnt my db, but I just have access to it ... > > Well, I suggest you forget the password then :-) > > > Either way, so I need to change the vacuum_Analyze_scale/threshold for > the > > original table ? But the value will be too high/low for the original > table. > > For example if my original table has 30,000 rows and my toasted has > > 100,000,000 rows. I want to analyze every 50K records in the toasted (by > > the way, sounds legit ?) which is 0.05% of 100m. With this value it means > > that every 0.05*30,000=1500 updated/deletes on the original table it will > > run analyze on the original table which is very often... > > Doesn't it seems a little bit problematic ? > > Autovacuum considers main table and toast table separately for > vacuuming, so nothing you do to the parameters for the main table will > affect the vacuuming schedule of the toast table. > > -- > Álvaro Herrerahttps://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
