pg_multixact issues

[Dev Kumkar]

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

[Adrian Klaver]

Post by Dev Kumkar
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

Post by Dev Kumkar
Looking forward to get some insight here.
Regards...

[Dev Kumkar]

Post by Adrian Klaver
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
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...

[Dev Kumkar]

Post by Dev Kumkar
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...

[Dev Kumkar]

Post by Adrian Klaver
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...

[Adrian Klaver]

On Wed, Sep 17, 2014 at 6:51 PM, Adrian Klaver
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]

On Wed, Sep 17, 2014 at 6:51 PM, Adrian Klaver
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...

[Dev Kumkar]

Post by Adrian Klaver
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...

[Andres Freund]

Post by Dev Kumkar

Post by Adrian Klaver
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?

Post by Dev Kumkar
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

[Dev Kumkar]

Post by Andres Freund
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.

Post by Andres Freund
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...

[Dev Kumkar]

Post by Dev Kumkar

Post by Andres Freund
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.

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

oid datname datfrozenxid age(datfrozenxid) datminmxid
16384 myDB 1673 10872259 1
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?

[Adrian Klaver]

On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund
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;'?
oid datname datfrozenxid age(datfrozenxid) datminmxid
16384 myDB 1673 10872259 1
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;

[Andres Freund]

Post by Dev Kumkar

Post by Dev Kumkar

Post by Andres Freund
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.

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

oid datname datfrozenxid age(datfrozenxid) datminmxid
16384 myDB 1673 10872259 1
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

[Dev Kumkar]

Post by Andres Freund
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...

[Dev Kumkar]

Post by Dev Kumkar
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...

[Dev Kumkar]

Post by Dev Kumkar
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...

[Alvaro Herrera]

Post by Dev Kumkar

Post by Dev Kumkar
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?

[Dev Kumkar]

Post by Alvaro Herrera
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...

[Alvaro Herrera]

Post by Dev Kumkar

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

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/ ?

[Dev Kumkar]

Post by Alvaro Herrera
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

Post by Alvaro Herrera
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...

[Andres Freund]

Post by Dev Kumkar
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

[Dev Kumkar]

Post by Andres Freund

Post by Dev Kumkar
On one my machine the pg_multixact directory size has grown up to 5 GB

and

Post by Dev Kumkar
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...

[Emanuel Calvo]

Post by Dev Kumkar

Post by Dev Kumkar
On one my machine the pg_multixact directory size has grown up

to 5 GB and

Post by Dev Kumkar
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)

[Dev Kumkar]

On Fri, Sep 19, 2014 at 7:14 PM, Emanuel Calvo <

Post by Emanuel Calvo
- 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...