Re: Size of pg_multixact/members increases 11355

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(dot)munro(at)enterprisedb(dot)com>
wrote:

> On Wed, Dec 13, 2017 at 5:05 AM, David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> > On Tue, Dec 12, 2017 at 2:52 AM, Yogesh Sharma <yogeshraj95(at)gmail(dot)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
>