Thread: how to truncate/purge the pg_xlog directory?
Hi As far as I understand pg_xlog is the transaction log i.e. the WAL. I have a pg 8.2 which has consumed about 113MB in pg_xlog/, while the cluster is virtually empty. There are a couple of tables in the postgres schema, but they contain no data at the moment. My disk is filling up and I want to purge directories that are larger than need to be. I performed a VACUUM FULL, because I thought that would help, but it did not. I searched the net and the forums here and found only one post about tuning the wal parameters to control the pg_xlog directory size, but when looking at the documentation for that I found nothing that could be used for that Also the base/ directory is 268MB big and still not many tables, is ti possible to purge that directory as well?. I would appreciate some help on what to do. regards thomas
tfinneid@student.matnat.uio.no wrote: > Hi > > As far as I understand pg_xlog is the transaction log i.e. the WAL. Correct. And it should have files called e.g. 0000000100000005000000E9 each 16MB long. > I have a pg 8.2 which has consumed about 113MB in pg_xlog/, while the > cluster is virtually empty. OK, so that's 7 files? What is your checkpoint_segments setting in postgresql.conf? If it's 3 then you can indeed expect a maximum of 7 WAL files. For a low-activity system you can always turn the setting down. > There are a couple of tables in the postgres > schema, but they contain no data at the moment. My disk is filling up and > I want to purge directories that are larger than need to be. > I performed a VACUUM FULL, because I thought that would help, but it did not. > I searched the net and the forums here and found only one post about > tuning the wal parameters to control the pg_xlog directory size, but when > looking at the documentation for that I found nothing that could be used > for that > > Also the base/ directory is 268MB big and still not many tables, is ti > possible to purge that directory as well?. You might find the "pg_xxx_size()" functions useful - they are detailed in the functions/system-admin chapter of the manuals. -- Richard Huxton Archonet Ltd
> tfinneid@student.matnat.uio.no wrote: > > OK, so that's 7 files? > What is your checkpoint_segments setting in postgresql.conf? If it's 3 > then you can indeed expect a maximum of 7 WAL files. For a low-activity > system you can always turn the setting down. The setting was not set, so I expect that it used the default value of 3. I tried setting it to 1, and restarted the server, but nothing happened with the directories. Is there something that triggers the cleaning of it? Another thing, I dont understand how a setting of 3 leads to 7 files since one segment is 16MB, 3 segments should be be 3 files i.e. 48MB not 113MB. Am I misunderstanding something? >> Also the base/ directory is 268MB big and still not many tables, is ti >> possible to purge that directory as well?. > > You might find the "pg_xxx_size()" functions useful - they are detailed > in the functions/system-admin chapter of the manuals. Can you be a bit more specific? I tried looking for it, but the sections are too big to just browse unknown information.
tfinneid@student.matnat.uio.no wrote: >> tfinneid@student.matnat.uio.no wrote: >> >> OK, so that's 7 files? >> What is your checkpoint_segments setting in postgresql.conf? If it's 3 >> then you can indeed expect a maximum of 7 WAL files. For a low-activity >> system you can always turn the setting down. > > The setting was not set, so I expect that it used the default value of 3. > I tried setting it to 1, and restarted the server, but nothing happened > with the directories. Is there something that triggers the cleaning of it? > > Another thing, I dont understand how a setting of 3 leads to 7 files since > one segment is 16MB, 3 segments should be be 3 files i.e. 48MB not 113MB. > Am I misunderstanding something? This parameter actually controls when a checkpoint is forced. Indirectly, that affects the number of WAL files kept around. It's actually (2*checkpoint_segments + 1). For those that are interested it's "XLOGfileslop" in backend/access/transam/xlog.c. As checkpoints are issued, old WAL files will either be recycled (renamed) or deleted. Change the setting, then try (as user postgres) issuing a "vacuum full <tbl>" followed by "SELECT pg_switch_xlog()" that should recycle them quicker. That'll only get you down to 3 or 4 files though - that's the minimum. >>> Also the base/ directory is 268MB big and still not many tables, is ti >>> possible to purge that directory as well?. >> You might find the "pg_xxx_size()" functions useful - they are detailed >> in the functions/system-admin chapter of the manuals. > > Can you be a bit more specific? I tried looking for it, but the sections > are too big to just browse unknown information. Try looking again, perhaps? Go to the online manuals, go to the section on "functions and operators" then to the section on "system administration functions". The first ocurrence of the word "size" on that page is a heading for the bit you need. -- Richard Huxton Archonet Ltd
> As checkpoints are issued, old WAL files will either be recycled > (renamed) or deleted. Change the setting, then try (as user postgres) > issuing a "vacuum full <tbl>" followed by "SELECT pg_switch_xlog()" that > should recycle them quicker. That'll only get you down to 3 or 4 files > though - that's the minimum. Nothings changed yet, maybe I need to start using the db again before I something real happens... >>> You might find the "pg_xxx_size()" functions useful - they are detailed >>> in the functions/system-admin chapter of the manuals. >> >> Can you be a bit more specific? I tried looking for it, but the sections >> are too big to just browse unknown information. > > Try looking again, perhaps? > Go to the online manuals, go to the section on "functions and operators" > then to the section on "system administration functions". The first > ocurrence of the word "size" on that page is a heading for the bit you > need. That was not easy to find, your descriptions were not easy to dechiper. When you said sys-admin chapter I started looking for a functions chapter in section 3 (i.e. chapters 14-28), but you were talking about chapter 9... which is in the section about the sql language. Quite confusing. regards thomas
tfinneid@student.matnat.uio.no wrote: >> As checkpoints are issued, old WAL files will either be recycled >> (renamed) or deleted. Change the setting, then try (as user postgres) >> issuing a "vacuum full <tbl>" followed by "SELECT pg_switch_xlog()" that >> should recycle them quicker. That'll only get you down to 3 or 4 files >> though - that's the minimum. > > Nothings changed yet, maybe I need to start using the db again before I > something real happens... The timestamps on the WAL files should be updated if they get recycled. Check those. -- Richard Huxton Archonet Ltd