pg_multixact issues

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...

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(dot)klaver(at)aklaver(dot)com

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

On Wed, Sep 17, 2014 at 6:51 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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...

On Wed, Sep 17, 2014 at 6:53 PM, Andres Freund <andres(at)2ndquadrant(dot)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...

On Wed, Sep 17, 2014 at 7:20 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)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...

On Wed, Sep 17, 2014 at 7:20 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)com> wrote:

> On Wed, Sep 17, 2014 at 6:51 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>
>>
>> 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?

Regards...

On 09/17/2014 01:36 PM, Dev Kumkar wrote:
> On Wed, Sep 17, 2014 at 7:20 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)com
> <mailto:devdas(dot)kumkar(at)gmail(dot)com>> wrote:
>
> On Wed, Sep 17, 2014 at 6:51 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com

On 09/17/2014 01:36 PM, Dev Kumkar wrote:
> On Wed, Sep 17, 2014 at 7:20 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)com
> <mailto:devdas(dot)kumkar(at)gmail(dot)com>> wrote:
>
> On Wed, Sep 17, 2014 at 6:51 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com

On Thu, Sep 18, 2014 at 2:41 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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...

On 2014-09-18 14:41:07 +0530, Dev Kumkar wrote:
> On Thu, Sep 18, 2014 at 2:41 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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

On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund <andres(at)2ndquadrant(dot)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...

On Thu, Sep 18, 2014 at 6:20 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)com> wrote:

> On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund <andres(at)2ndquadrant(dot)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?

On 09/18/2014 10:22 AM, Dev Kumkar wrote:
> On Thu, Sep 18, 2014 at 6:20 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)com
> <mailto:devdas(dot)kumkar(at)gmail(dot)com>> wrote:
>
> On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund
> <andres(at)2ndquadrant(dot)com <mailto:andres(at)2ndquadrant(dot)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(dot)klaver(at)aklaver(dot)com

Dev Kumkar wrote:
> On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund <andres(at)2ndquadrant(dot)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

On 2014-09-18 22:52:57 +0530, Dev Kumkar wrote:
> On Thu, Sep 18, 2014 at 6:20 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)com> wrote:
>
> > On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund <andres(at)2ndquadrant(dot)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

On Fri, Sep 19, 2014 at 8:07 AM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)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...

On Fri, Sep 19, 2014 at 1:03 PM, Andres Freund <andres(at)2ndquadrant(dot)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...

El 17/09/14 10:51, Dev Kumkar escribió:
> On Wed, Sep 17, 2014 at 6:53 PM, Andres Freund <andres(at)2ndquadrant(dot)com
> <mailto:andres(at)2ndquadrant(dot)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

On Fri, Sep 19, 2014 at 7:14 PM, Emanuel Calvo <
emanuel(dot)calvo(at)2ndquadrant(dot)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...

On Fri, Sep 19, 2014 at 1:23 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)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...

On Fri, Sep 26, 2014 at 1:36 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)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...

Dev Kumkar wrote:
> On Fri, Sep 26, 2014 at 1:36 PM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)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

On Tue, Sep 30, 2014 at 8:50 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)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...