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 >
