Thread: pg_multixact issues

pg_multixact issues

From
Dev Kumkar
Date:

Hello,

On one my machine the pg_multixact directory size has grown up to 5 GB and am not sure how to clean up this directory.

From the storage-file-layout this directory contains multitransaction status data.
pg_multixactSubdirectory containing multitransaction status data (used for shared row locks)

It would really help if someone can provide some reading material regarding pg_multixact? Would this also result in database slowness by any chance?

Are there any tweaking commands related to this directory settings, also how can I cleanup/truncate this directory without impacting the overall database.

Looking forward to get some insight here.

Regards...

Re: [SQL] pg_multixact issues

From
Adrian Klaver
Date:
On 09/17/2014 05:16 AM, Dev Kumkar wrote:
>
> Hello,
>
> On one my machine the pg_multixact directory size has grown up to 5 GB
> and am not sure how to clean up this directory.
>
>  From the storage-file-layout this directory contains multitransaction
> status data.
> pg_multixact    Subdirectory containing multitransaction status data (used
> for shared row locks)
>
>
> It would really help if someone can provide some reading material
> regarding pg_multixact? Would this also result in database slowness by
> any chance?
>
> Are there any tweaking commands related to this directory settings, also
> how can I cleanup/truncate this directory without impacting the overall
> database.

http://www.postgresql.org/docs/9.3/static/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND

Might also want to take a look at pg_stat_activity to see what queries
maybe hanging up:

http://www.postgresql.org/docs/9.3/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

>
> Looking forward to get some insight here.
>
> Regards...


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: pg_multixact issues

From
Andres Freund
Date:
On 2014-09-17 17:46:05 +0530, Dev Kumkar wrote:
> On one my machine the pg_multixact directory size has grown up to 5 GB and
> am not sure how to clean up this directory.

Which version of postgres are you using?

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Re: [SQL] pg_multixact issues

From
Dev Kumkar
Date:
On Wed, Sep 17, 2014 at 6:51 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

http://www.postgresql.org/docs/9.3/static/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND

Might also want to take a look at pg_stat_activity to see what queries maybe hanging up:

http://www.postgresql.org/docs/9.3/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

Thanks, yes have been looking into pg_stat_activity table and somehow the standard queries are hanging.
Not sure if this is because the database response has become very slow.

Regards...

Re: pg_multixact issues

From
Dev Kumkar
Date:
On Wed, Sep 17, 2014 at 6:53 PM, Andres Freund <andres@2ndquadrant.com> wrote:
On 2014-09-17 17:46:05 +0530, Dev Kumkar wrote:
> On one my machine the pg_multixact directory size has grown up to 5 GB and
> am not sure how to clean up this directory.

Which version of postgres are you using?

Greetings,

Andres Freund

Postgres 9.3.4 (linux-64-bit)

Regards...

Re: [SQL] pg_multixact issues

From
Dev Kumkar
Date:
On Wed, Sep 17, 2014 at 7:20 PM, Dev Kumkar <devdas.kumkar@gmail.com> wrote:
Thanks, yes have been looking into pg_stat_activity table and somehow the standard queries are hanging.
Not sure if this is because the database response has become very slow.

Would having a huge pg_multixact directory have an impact on databse performance?

Regards...

Re: [SQL] pg_multixact issues

From
Dev Kumkar
Date:
On Wed, Sep 17, 2014 at 7:20 PM, Dev Kumkar <devdas.kumkar@gmail.com> wrote:

Looked into these details. Can there be an example explained that will really help to understand this in practice?

Also one additional information here, the database was restarted various times. But still the pg_multixact directory size is increasing.
Actually there were multiple updates happening in different processes which lead to the locking issues and landed up into this situation.

How can I recover the system at this stage and also clean up pg_multixact gracefully?

Regards...

Re: [SQL] pg_multixact issues

From
Adrian Klaver
Date:
On 09/17/2014 01:36 PM, Dev Kumkar wrote:
> On Wed, Sep 17, 2014 at 7:20 PM, Dev Kumkar <devdas.kumkar@gmail.com
> <mailto:devdas.kumkar@gmail.com>> wrote:
>
>     On Wed, Sep 17, 2014 at 6:51 PM, Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>
>         http://www.postgresql.org/__docs/9.3/static/routine-__vacuuming.html#VACUUM-FOR-__MULTIXACT-WRAPAROUND
>         <http://www.postgresql.org/docs/9.3/static/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND>
>
>
> Looked into these details. Can there be an example explained that will
> really help to understand this in practice?
>
> Also one additional information here, the database was restarted various
> times. But still the pg_multixact directory size is increasing.
> Actually there were multiple updates happening in different processes
> which lead to the locking issues and landed up into this situation.
>
> How can I recover the system at this stage and also clean up
> pg_multixact gracefully?

Now I am moving into the deep water:) Could the first item under Changes
in the link below apply?:

http://www.postgresql.org/docs/current/static/release-9-3-5.html

>
> Regards...


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: [SQL] pg_multixact issues

From
Adrian Klaver
Date:
On 09/17/2014 01:36 PM, Dev Kumkar wrote:
> On Wed, Sep 17, 2014 at 7:20 PM, Dev Kumkar <devdas.kumkar@gmail.com
> <mailto:devdas.kumkar@gmail.com>> wrote:
>
>     On Wed, Sep 17, 2014 at 6:51 PM, Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>
>         http://www.postgresql.org/__docs/9.3/static/routine-__vacuuming.html#VACUUM-FOR-__MULTIXACT-WRAPAROUND
>         <http://www.postgresql.org/docs/9.3/static/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND>
>
>
> Looked into these details. Can there be an example explained that will
> really help to understand this in practice?
>
> Also one additional information here, the database was restarted various
> times. But still the pg_multixact directory size is increasing.
> Actually there were multiple updates happening in different processes
> which lead to the locking issues and landed up into this situation.
>
> How can I recover the system at this stage and also clean up
> pg_multixact gracefully?

Aaah, hit enter too soon. Also see the other changes under Changes that
apply to multixact in 9.3.5


>
> Regards...


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: [SQL] pg_multixact issues

From
Dev Kumkar
Date:
On Thu, Sep 18, 2014 at 2:41 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

Aaah, hit enter too soon. Also see the other changes under Changes that apply to multixact in 9.3.5

Thanks for sharing same. Found this one interesting "Truncate pg_multixact during checkpoints, not during VACUUM (Álvaro Herrera)" and also other changes. But am not sure are you suggesting to move to 9.3.5 ?

Actually looking for some guidelines on truncating pg_multixact at this situation.

- Do I need to run vaccum manually here and then the pg_multixact can be truncated?
- Actually looking out for some hints wherein can know the current pg_multixact/members which are active and which one are stale which can be truncated? Is there any query to find this information?

pg_class.relminmxid can be referred but should I change the value of autovacuum_multixact_freeze_max_age which defaults to 400 million multixacts, setting this value to lower limits would help in cleaning up pg_multixact?

Regards...

Re: [SQL] pg_multixact issues

From
Andres Freund
Date:
On 2014-09-18 14:41:07 +0530, Dev Kumkar wrote:
> On Thu, Sep 18, 2014 at 2:41 AM, Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
>
> >
> > Aaah, hit enter too soon. Also see the other changes under Changes that
> > apply to multixact in 9.3.5
>
>
> Thanks for sharing same. Found this one interesting "Truncate pg_multixact
> during checkpoints, not during VACUUM (Álvaro Herrera)" and also other
> changes. But am not sure are you suggesting to move to 9.3.5 ?

I don't think that's relevant for you.

Did you upgrade the database using pg_upgrade?

> Actually looking for some guidelines on truncating pg_multixact at this
> situation.
>
> - Do I need to run vaccum manually here and then the pg_multixact can be
> truncated?
> - Actually looking out for some hints wherein can know the current
> pg_multixact/members which are active and which one are stale which can be
> truncated? Is there any query to find this information?
>
> pg_class.relminmxid can be referred but should I change the value of
> autovacuum_multixact_freeze_max_age which defaults to 400 million
> multixacts, setting this value to lower limits would help in cleaning up
> pg_multixact?

Can you show pg_controldata output and the output of 'SELECT oid,
datname, relfrozenxid, age(relfrozenxid), relminmxid FROM pg_database;'?

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Re: [SQL] pg_multixact issues

From
Dev Kumkar
Date:
On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund <andres@2ndquadrant.com> wrote:
I don't think that's relevant for you.

Did you upgrade the database using pg_upgrade?

That's correct! No, there is no upgrade here.
 
Can you show pg_controldata output and the output of 'SELECT oid,
datname, relfrozenxid, age(relfrozenxid), relminmxid FROM pg_database;'?

Here are the details:
 oid       datname     datfrozenxid    age(datfrozenxid)    datminmxid   
16384     myDB            1673                 10872259             1

Additionally wanted to mention couple more points here:
When I try to run "vacuum full" on this machine then facing following issue:
     INFO:  vacuuming "myDB.mytable"
     ERROR:  MultiXactId 3622035 has not been created yet -- apparent wraparound

No Select statements are working on this table, is the table corrupt?

Regards...

Re: [SQL] pg_multixact issues

From
Dev Kumkar
Date:
On Thu, Sep 18, 2014 at 6:20 PM, Dev Kumkar <devdas.kumkar@gmail.com> wrote:
On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund <andres@2ndquadrant.com> wrote:
I don't think that's relevant for you.

Did you upgrade the database using pg_upgrade?

That's correct! No, there is no upgrade here.
 
Can you show pg_controldata output and the output of 'SELECT oid,
datname, relfrozenxid, age(relfrozenxid), relminmxid FROM pg_database;'?

Here are the details:
 oid       datname     datfrozenxid    age(datfrozenxid)    datminmxid   
16384     myDB            1673                 10872259             1

Additionally wanted to mention couple more points here:
When I try to run "vacuum full" on this machine then facing following issue:
     INFO:  vacuuming "myDB.mytable"
     ERROR:  MultiXactId 3622035 has not been created yet -- apparent wraparound

No Select statements are working on this table, is the table corrupt?

Any inputs/hints/tips here?

Re: [SQL] pg_multixact issues

From
Adrian Klaver
Date:
On 09/18/2014 10:22 AM, Dev Kumkar wrote:
> On Thu, Sep 18, 2014 at 6:20 PM, Dev Kumkar <devdas.kumkar@gmail.com
> <mailto:devdas.kumkar@gmail.com>> wrote:
>
>     On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund
>     <andres@2ndquadrant.com <mailto:andres@2ndquadrant.com>> wrote:
>
>         I don't think that's relevant for you.
>
>         Did you upgrade the database using pg_upgrade?
>
>
>     That's correct! No, there is no upgrade here.

The above sentence is not clear to me.

Did you run pg_upgrade to get the data into the database?

If not, how did the database get populated?


>
>         Can you show pg_controldata output and the output of 'SELECT oid,
>         datname, relfrozenxid, age(relfrozenxid), relminmxid FROM
>         pg_database;'?
>
>
>     Here are the details:
>       oid       datname     datfrozenxid    age(datfrozenxid)    datminmxid
>     16384     myDB            1673                 10872259             1
>
>     Additionally wanted to mention couple more points here:
>     When I try to run "vacuum full" on this machine then facing
>     following issue:
>           INFO:  vacuuming "myDB.mytable"
>           ERROR:  MultiXactId 3622035 has not been created yet --
>     apparent wraparound
>
>     No Select statements are working on this table, is the table corrupt?
>
>
> Any inputs/hints/tips here?

Have you run the query from here?:

http://www.postgresql.org/docs/9.3/interactive/release-9-3-5.html

WITH list(file) AS (SELECT * FROM pg_ls_dir('pg_multixact/offsets'))
SELECT EXISTS (SELECT * FROM list WHERE file = '0000') AND
        NOT EXISTS (SELECT * FROM list WHERE file = '0001') AND
        NOT EXISTS (SELECT * FROM list WHERE file = 'FFFF') AND
        EXISTS (SELECT * FROM list WHERE file != '0000')
        AS file_0000_removal_required;




--
Adrian Klaver
adrian.klaver@aklaver.com


Re: [SQL] pg_multixact issues

From
Alvaro Herrera
Date:
Dev Kumkar wrote:
> On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund <andres@2ndquadrant.com>
> wrote:

> > Can you show pg_controldata output and the output of 'SELECT oid,
> > datname, relfrozenxid, age(relfrozenxid), relminmxid FROM pg_database;'?
> >
>
> Here are the details:
>  oid       datname     datfrozenxid    age(datfrozenxid)    datminmxid
> 16384     myDB            1673                 10872259             1

Can you paste the pg_controldata output please?

Also, what files are there in pg_multixact/offsets/ ?

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


Re: [SQL] pg_multixact issues

From
Andres Freund
Date:
On 2014-09-18 22:52:57 +0530, Dev Kumkar wrote:
> On Thu, Sep 18, 2014 at 6:20 PM, Dev Kumkar <devdas.kumkar@gmail.com> wrote:
>
> > On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund <andres@2ndquadrant.com>
> > wrote:
> >
> >> I don't think that's relevant for you.
> >>
> >> Did you upgrade the database using pg_upgrade?
> >>
> >
> > That's correct! No, there is no upgrade here.
> >
> >
> >> Can you show pg_controldata output and the output of 'SELECT oid,
> >> datname, relfrozenxid, age(relfrozenxid), relminmxid FROM pg_database;'?
> >>
> >
> > Here are the details:
> >  oid       datname     datfrozenxid    age(datfrozenxid)    datminmxid
> > 16384     myDB            1673                 10872259             1
> >
> > Additionally wanted to mention couple more points here:
> > When I try to run "vacuum full" on this machine then facing following
> > issue:
> >      INFO:  vacuuming "myDB.mytable"
> >      ERROR:  MultiXactId 3622035 has not been created yet -- apparent
> > wraparound
> >
> > No Select statements are working on this table, is the table corrupt?
> >
>
> Any inputs/hints/tips here?

Yes: Learning some patience. You'd given the previous answer two hours
before this one. Nobody is paid to work on this list...

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Re: [SQL] pg_multixact issues

From
Dev Kumkar
Date:
On Fri, Sep 19, 2014 at 8:07 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Can you paste the pg_controldata output please?

pg_controldata output as follows:

pg_control version number:            937
Catalog version number:               201306121
Database system identifier:           6023658189132429183
Database cluster state:               in production
pg_control last modified:             Fri Sep 19 12:09:05 2014
Latest checkpoint location:           2D3/5DB461C0
Prior checkpoint location:            2D3/5D08D0D0
Latest checkpoint's REDO location:    2D3/5D68EFE0
Latest checkpoint's REDO WAL file:    00000001000002D30000005D
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/10882952
Latest checkpoint's NextOID:          3443291
Latest checkpoint's NextMultiXactId:  3622064
Latest checkpoint's NextMultiOffset:  4294172074
Latest checkpoint's oldestXID:        1673
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Time of latest checkpoint:            Fri Sep 19 12:06:35 2014
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
Current wal_level setting:            minimal
Current max_connections setting:      100
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
 
 
Also, what files are there in pg_multixact/offsets/ ?
Currently there are about 56 files in  pg_multixact/offsets/.

0000  0002  0004  0006  0008  000A  000C  000E  0010  0012  0014  0016  0018  001A  001C  001E  0020  0022  0024  0026  0028  002A  002C  002E  0030  0032  0034  0036
0001  0003  0005  0007  0009  000B  000D  000F  0011  0013  0015  0017  0019  001B  001D  001F  0021  0023  0025  0027  0029  002B  002D  002F  0031  0033  0035  0037

Regards...

Re: [SQL] pg_multixact issues

From
Dev Kumkar
Date:
On Fri, Sep 19, 2014 at 1:03 PM, Andres Freund <andres@2ndquadrant.com> wrote:
Yes: Learning some patience. You'd given the previous answer two hours
before this one. Nobody is paid to work on this list...

Apologies for the delay, was working/troubleshooting same issue and was away from my emails. :(

Regards...

Re: pg_multixact issues

From
Emanuel Calvo
Date:

El 17/09/14 10:51, Dev Kumkar escribió:
On Wed, Sep 17, 2014 at 6:53 PM, Andres Freund <andres@2ndquadrant.com> wrote:
On 2014-09-17 17:46:05 +0530, Dev Kumkar wrote:
> On one my machine the pg_multixact directory size has grown up to 5 GB and
> am not sure how to clean up this directory.

Which version of postgres are you using?

Greetings,

Andres Freund

Postgres 9.3.4 (linux-64-bit)

Regards...
Could it be related to some fixes on 9.3.5?:

- Fix wraparound handling for pg_multixact/members (Álvaro Herrera)
- Truncate pg_multixact during checkpoints, not during VACUUM (Álvaro Herrera)

-- 
--
Emanuel Calvo                 http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Re: pg_multixact issues

From
Dev Kumkar
Date:
On Fri, Sep 19, 2014 at 7:14 PM, Emanuel Calvo <emanuel.calvo@2ndquadrant.com> wrote:

Could it be related to some fixes on 9.3.5?:

- Fix wraparound handling for pg_multixact/members (Álvaro Herrera)
- Truncate pg_multixact during checkpoints, not during VACUUM (Álvaro Herrera)


Currently won't be possible to upgrade to 9.3.5.

Anything can be done in 9.3.4 to trucate them - vacuum full?

Regards...

Re: [SQL] pg_multixact issues

From
Dev Kumkar
Date:
On Fri, Sep 19, 2014 at 1:23 PM, Dev Kumkar <devdas.kumkar@gmail.com> wrote:
Apologies for the delay, was working/troubleshooting same issue and was away from my emails. :(
Regards...

Received the database with huge pg_multixact directory of size 21G and there are ~82,000 files in "pg_multixact/members" and 202 files in "pg_multixact/offsets" directory.

Did run "vacuum full" on this database and it was successful. However now am not sure about pg_multixact directory. truncating this directory except 0000 file results into database start up issues, of course this is not correct way of truncating.
         FATAL:  could not access status of transaction 13224692

Stumped ! Please provide some comments on how to truncate pg_multixact files and if there is any impact because of these files on database performance.

Regards...

Re: [SQL] pg_multixact issues

From
Dev Kumkar
Date:
On Fri, Sep 26, 2014 at 1:36 PM, Dev Kumkar <devdas.kumkar@gmail.com> wrote:
Received the database with huge pg_multixact directory of size 21G and there are ~82,000 files in "pg_multixact/members" and 202 files in "pg_multixact/offsets" directory.

Did run "vacuum full" on this database and it was successful. However now am not sure about pg_multixact directory. truncating this directory except 0000 file results into database start up issues, of course this is not correct way of truncating.
         FATAL:  could not access status of transaction 13224692

Stumped ! Please provide some comments on how to truncate pg_multixact files and if there is any impact because of these files on database performance.

Facing this issue on couple more machines where pg_multixact is huge and not being cleaned up. Any suggestions / troubleshooting tips?

Regards...

Re: [SQL] pg_multixact issues

From
Alvaro Herrera
Date:
Dev Kumkar wrote:
> On Fri, Sep 26, 2014 at 1:36 PM, Dev Kumkar <devdas.kumkar@gmail.com> wrote:
>
> > Received the database with huge pg_multixact directory of size 21G and
> > there are ~82,000 files in "pg_multixact/members" and 202 files in
> > "pg_multixact/offsets" directory.
> >
> > Did run "vacuum full" on this database and it was successful. However now
> > am not sure about pg_multixact directory. truncating this directory except
> > 0000 file results into database start up issues, of course this is not
> > correct way of truncating.
> >          FATAL:  could not access status of transaction 13224692
> >
> > Stumped ! Please provide some comments on how to truncate pg_multixact
> > files and if there is any impact because of these files on database
> > performance.
> >
>
> Facing this issue on couple more machines where pg_multixact is huge and
> not being cleaned up. Any suggestions / troubleshooting tips?

Did you try decreasing the autovacuum_multixact_freeze_min_age and
autovacuum_multixact_freeze_table_age parameters?

What exact server version are you running?

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


Re: [SQL] pg_multixact issues

From
Dev Kumkar
Date:
On Tue, Sep 30, 2014 at 8:50 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Did you try decreasing the autovacuum_multixact_freeze_min_age and
autovacuum_multixact_freeze_table_age parameters?
As per the docs this set anywhere from zero to 1 billion for vacuum_multixact_freeze_min_age
And zero to 2 billion for vacuum_multixact_freeze_table_age.

Modified this to have value 10 and 15 respectively. Not sure if that's correct way of setting these parameters?

What exact server version are you running?
 Am using PostgreSQL 9.3.4 (linux-64-bit)

Regards...