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