Dear Thomas , Thanks for sharing information.
Is it possible to remove mentioned folder files in some time intervals by some DB command? Currently i can not upgrade to 3.6.20. So please share if any solution is available. Regards, Yogesh On Wednesday, December 13, 2017, Yogesh Sharma <yogeshra...@gmail.com> wrote: > Dear Thomas and All, > > > Thanks for sharing your input. > How we calculate > autovacuum_multixact_freeze_max_age paramter limit into system? > > Regards, > Yogesh > On Wednesday, December 13, 2017, Thomas Munro < > thomas.mu...@enterprisedb.com> wrote: > >> On Wed, Dec 13, 2017 at 5:05 AM, David G. Johnston >> <david.g.johns...@gmail.com> wrote: >> > On Tue, Dec 12, 2017 at 2:52 AM, Yogesh Sharma <yogeshra...@gmail.com> >> > wrote: >> >> >> >> Dear All, >> >> >> >> I am using PostgreSQL 9.3.6 version and PGDATA pg_multixact.members >> folder >> >> size is increased to around 3GB. How to reduce this folder size and >> how to >> >> fix this issue? >> >> Is it realted to poatgres issue? If yes how to reproduce this issue? >> >> >> > >> > I'm not familiar enough with this aspect of PostgreSQL but unless you >> > upgrade and find this is still broken in 9.3.20 the willingness and >> ability >> > to help is going to be impacted. >> >> Between .6 and .20 there were indeed many bugfixes relating to multixacts: >> >> https://www.postgresql.org/docs/9.3/static/release-9-3-7.html >> https://www.postgresql.org/docs/9.3/static/release-9-3-9.html >> https://www.postgresql.org/docs/9.3/static/release-9-3-10.html >> https://www.postgresql.org/docs/9.3/static/release-9-3-14.html >> https://www.postgresql.org/docs/9.3/static/release-9-3-18.html >> >> If your subject line means that you have "only" 11355 files under >> pg_multixact/member then I doubt you've hit any of the problems >> covered by those bugs, but still, yeah, you should upgrade. >> >> The number of 256kb files under pg_multixact/offsets should normally >> be limited by autovacuum_multixact_freeze_max_age, where each file >> holds 65536 multixacts. The number of 256kb files under >> pg_multixact/members is more complicated: it depends on the number of >> members in each multixact, which could be as low as 2, or much higher, >> depending how many shared lockers you tend to have. Each file holds >> 52352 members. The manual says we default to a "relatively low 400 >> million multixacts", but that's actually enough to eat a lot of disk >> space, especially if you have large multixacts. 400 million >> multixacts = 6103 files = ~1.5GB of pg_multixact/offsets, and clearly >> the member directory will be even bigger. If you want to use less >> disk space, you should set autovacuum_multixact_freeze_max_age lower. >> You don't want to set it too low though, or your system will spend too >> much time doing freeze vacuums trying to garbage collect multixacts. >> You can work out the peak size of pg_multixact/offsets for a given >> autovacuum_multixact_freeze_max_age setting as I showed, and you can >> assume that your current ratio of offsets:members size will be >> maintained to estimate the peak member space usage. >> >> 9.3 is also prone to eating more multixact space than recent major >> versions in cases involving subtransactions. >> >> -- >> Thomas Munro >> http://www.enterprisedb.com >> >
