Обсуждение: Size of pg_multixact/members increases 11355
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?
Thanks in advance.
Regards,
Yogesh
Please share direction to resolve below issue.
Regards,
Yogesh
On Tuesday, December 12, 2017, Yogesh Sharma <yogeshraj95@gmail.com> wrote:
On Tuesday, December 12, 2017, 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?Thanks in advance.Regards,Yogesh
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.
David J.
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: 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
Dear Thomas and All,
Thanks for sharing your input.
How we calculate
autovacuum_multixact_freeze_ max_age paramter limit into system?
autovacuum_multixact_freeze_
Regards,
Yogesh
On Wednesday, December 13, 2017, Thomas Munro <thomas.munro@enterprisedb.com> wrote:
On Wednesday, December 13, 2017, Thomas Munro <thomas.munro@enterprisedb.com> wrote:
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:
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
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@gmail.com> wrote:
On Wednesday, December 13, 2017, Yogesh Sharma <yogeshraj95@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.munro@enterprisedb.com> wrote: 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:
https://www.postgresql.org/docs/9.3/static/release-9-3-7.htm l
https://www.postgresql.org/docs/9.3/static/release-9-3-9.htm l
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
On 12/13/2017 01:42 PM, Yogesh Sharma wrote: > 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. > There are no explicit commands to remove them. When the database decides those files are not needed, it'll remove them automatically. It's strongly discouraged to mess with those files directly, as it may easily lead to data loss or data corruption, particularly when it's unclear why the directory got so large. You could try setting autovacuum_multixact_freeze_max_age=0 and vacuuming the database, but it's hard to say if that will help. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Dear Thomas,
Thanks for your updation.
I have also checked on older version 8.1.3.
In older version , after 32 MB folder size increased, it automatically cleaned files and size is reduced.
This size is dependent on which paramter in older version?
Regards,
Yogesh
On Wednesday, December 13, 2017, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
On Wednesday, December 13, 2017, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
On 12/13/2017 01:42 PM, Yogesh Sharma wrote:
> 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.
>
There are no explicit commands to remove them. When the database decides
those files are not needed, it'll remove them automatically.
It's strongly discouraged to mess with those files directly, as it may
easily lead to data loss or data corruption, particularly when it's
unclear why the directory got so large.
You could try setting autovacuum_multixact_freeze_max_age=0 and
vacuuming the database, but it's hard to say if that will help.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Yogesh Sharma wrote: > 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. If you do not upgrade, you risk getting your data corrupted. The solution is to upgrade, and if the data has any value, the suggestion is to upgrade urgently. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Dear All,
On Thursday, December 14, 2017, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
I have upgraded version from 9.3.6 to 9.3.20 but this issue is not resolved. can you please suggest in which version this file size are not increased.
Regards,
Yogesh
On Thursday, December 14, 2017, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
Yogesh Sharma wrote:
> 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.
If you do not upgrade, you risk getting your data corrupted. The
solution is to upgrade, and if the data has any value, the suggestion is
to upgrade urgently.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services