public inbox for [email protected]help / color / mirror / Atom feed
VacuumDB generating huge WAL filed 7+ messages / 4 participants [nested] [flat]
* VacuumDB generating huge WAL filed @ 2024-12-12 06:08 SASIKUMAR Devaraj <[email protected]> 0 siblings, 2 replies; 7+ messages in thread From: SASIKUMAR Devaraj @ 2024-12-12 06:08 UTC (permalink / raw) To: Pgsql-admin <[email protected]> Hi All When we are running vacuumdb for our database of 1.5TB it is generating approximately 60GB of WAL files? Any way we can reduce this WAL file generation? RegardsSasi Sent from Yahoo Mail for iPhone ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VacuumDB generating huge WAL filed @ 2024-12-12 06:25 David G. Johnston <[email protected]> parent: SASIKUMAR Devaraj <[email protected]> 1 sibling, 1 reply; 7+ messages in thread From: David G. Johnston @ 2024-12-12 06:25 UTC (permalink / raw) To: SASIKUMAR Devaraj <[email protected]>; +Cc: Pgsql-admin <[email protected]> On Wednesday, December 11, 2024, SASIKUMAR Devaraj <[email protected]> wrote: > > When we are running vacuumdb for our database of 1.5TB it is generating > approximately 60GB of WAL files? Any way we can reduce this WAL file > generation? > > Set a much more aggressive autovacuum so you don’t accumulate as much dead tuples between runs? David J. ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VacuumDB generating huge WAL filed @ 2024-12-12 06:35 Laurenz Albe <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Laurenz Albe @ 2024-12-12 06:35 UTC (permalink / raw) To: David G. Johnston <[email protected]>; SASIKUMAR Devaraj <[email protected]>; +Cc: Pgsql-admin <[email protected]> On Wed, 2024-12-11 at 23:25 -0700, David G. Johnston wrote: > On Wednesday, December 11, 2024, SASIKUMAR Devaraj <[email protected]> wrote: > > When we are running vacuumdb for our database of 1.5TB it is generating approximately 60GB > > of WAL files? Any way we can reduce this WAL file generation? > > Set a much more aggressive autovacuum so you don’t accumulate as much dead tuples between runs? You can also activate "wal_compression" for smaller full-page images and increase "max_wal_size" to get fewer of them. But 60GB of WAL shouldn't be a problem. Why are they a problem for you? Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VacuumDB generating huge WAL filed @ 2024-12-12 12:27 SASIKUMAR Devaraj <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: SASIKUMAR Devaraj @ 2024-12-12 12:27 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; David G. Johnston <[email protected]>; +Cc: Pgsql-admin <[email protected]> Hi Since it is generating 60GB in few mins, my replication lag is increasing upto 10Gb for around 10 mins, which may seen as possible data loss in case of primary node failure RegardsSasi Sent from Yahoo Mail for iPhone On Thursday, December 12, 2024, 12:06 PM, Laurenz Albe <[email protected]> wrote: On Wed, 2024-12-11 at 23:25 -0700, David G. Johnston wrote: > On Wednesday, December 11, 2024, SASIKUMAR Devaraj <[email protected]> wrote: > > When we are running vacuumdb for our database of 1.5TB it is generating approximately 60GB > > of WAL files? Any way we can reduce this WAL file generation? > > Set a much more aggressive autovacuum so you don’t accumulate as much dead tuples between runs? You can also activate "wal_compression" for smaller full-page images and increase "max_wal_size" to get fewer of them. But 60GB of WAL shouldn't be a problem. Why are they a problem for you? Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VacuumDB generating huge WAL filed @ 2024-12-12 15:07 vignesh kumar <[email protected]> parent: SASIKUMAR Devaraj <[email protected]> 1 sibling, 1 reply; 7+ messages in thread From: vignesh kumar @ 2024-12-12 15:07 UTC (permalink / raw) To: SASIKUMAR Devaraj <[email protected]>; Pgsql-admin <[email protected]> Check for the max walsize allocated in postgresql.conf and also check on checkpoint completion target default is 0.9 this says how frequent the checkpoint can be applied that adds to wal lan generation. First do a table level vaccuum and see what's causing it to write more wal files. Sent from Outlook for Android<https://aka.ms/AAb9ysg; ________________________________ From: SASIKUMAR Devaraj <[email protected]> Sent: Thursday, December 12, 2024 11:38:26 AM To: Pgsql-admin <[email protected]> Subject: VacuumDB generating huge WAL filed Hi All When we are running vacuumdb for our database of 1.5TB it is generating approximately 60GB of WAL files? Any way we can reduce this WAL file generation? Regards Sasi Sent from Yahoo Mail for iPhone<https://mail.onelink.me/107872968?pid=nativeplacement&c=Global_Acquisition_YMktg_315_Internal_Em...; ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VacuumDB generating huge WAL filed @ 2024-12-13 10:03 SASIKUMAR Devaraj <[email protected]> parent: vignesh kumar <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: SASIKUMAR Devaraj @ 2024-12-13 10:03 UTC (permalink / raw) To: vignesh kumar <[email protected]>; Pgsql-admin <[email protected]> We were able to identify the table generating around 40GB in few mins by vaccum process. It is partition table with dead tuple less than 0.5 percent. What may be next steps? Sent from Yahoo Mail for iPhone On Thursday, December 12, 2024, 8:37 PM, vignesh kumar <[email protected]> wrote: Check for the max walsize allocated in postgresql.conf and also check on checkpoint completion target default is 0.9 this says how frequent the checkpoint can be applied that adds to wal lan generation. First do a table level vaccuum and see what's causing it to write more wal files. Sent from Outlook for AndroidFrom: SASIKUMAR Devaraj <[email protected]> Sent: Thursday, December 12, 2024 11:38:26 AM To: Pgsql-admin <[email protected]> Subject: VacuumDB generating huge WAL filed Hi All When we are running vacuumdb for our database of 1.5TB it is generating approximately 60GB of WAL files? Any way we can reduce this WAL file generation? RegardsSasi Sent from Yahoo Mail for iPhone ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VacuumDB generating huge WAL filed @ 2024-12-13 10:12 Laurenz Albe <[email protected]> parent: SASIKUMAR Devaraj <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Laurenz Albe @ 2024-12-13 10:12 UTC (permalink / raw) To: SASIKUMAR Devaraj <[email protected]>; vignesh kumar <[email protected]>; Pgsql-admin <[email protected]> On Fri, 2024-12-13 at 10:03 +0000, SASIKUMAR Devaraj wrote: > > When we are running vacuumdb for our database of 1.5TB it is > > generating approximately 60GB of WAL files? Any way we can > > reduce this WAL file generation? > > We were able to identify the table generating around 40GB in > few mins by vaccum process. It is partition table with dead > tuple less than 0.5 percent. What may be next steps? I think I told you: increase "max_wal_size" and enable "wal_compression". That should reduce the amount of WAL. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-12-13 10:12 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-12-12 06:08 VacuumDB generating huge WAL filed SASIKUMAR Devaraj <[email protected]> 2024-12-12 06:25 ` David G. Johnston <[email protected]> 2024-12-12 06:35 ` Laurenz Albe <[email protected]> 2024-12-12 12:27 ` SASIKUMAR Devaraj <[email protected]> 2024-12-12 15:07 ` vignesh kumar <[email protected]> 2024-12-13 10:03 ` SASIKUMAR Devaraj <[email protected]> 2024-12-13 10:12 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox