Re: Size of pg_multixact/members increases 11355

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 <yogeshraj95(at)gmail(dot)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(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
>>
>