Re: [GENERAL] [SQL] pg_multixact issues
On Fri, Sep 26, 2014 at 1:36 PM, Dev Kumkar devdas.kum...@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 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: [GENERAL] [SQL] pg_multixact issues
Dev Kumkar wrote: On Fri, Sep 26, 2014 at 1:36 PM, Dev Kumkar devdas.kum...@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 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 Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [SQL] pg_multixact issues
On Tue, Sep 30, 2014 at 8:50 PM, Alvaro Herrera alvhe...@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...
Re: [GENERAL] [SQL] pg_multixact issues
On Fri, Sep 19, 2014 at 1:23 PM, Dev Kumkar devdas.kum...@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 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: [GENERAL] [SQL] pg_multixact issues
On 2014-09-18 22:52:57 +0530, Dev Kumkar wrote: On Thu, Sep 18, 2014 at 6:20 PM, Dev Kumkar devdas.kum...@gmail.com wrote: On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund and...@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 datfrozenxidage(datfrozenxid)datminmxid 16384 myDB1673 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [SQL] pg_multixact issues
On Fri, Sep 19, 2014 at 8:07 AM, Alvaro Herrera alvhe...@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:000102D3005D 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/. 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: [GENERAL] [SQL] pg_multixact issues
On Fri, Sep 19, 2014 at 1:03 PM, Andres Freund and...@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: [GENERAL] [SQL] pg_multixact issues
On Thu, Sep 18, 2014 at 2:41 AM, Adrian Klaver adrian.kla...@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: [GENERAL] [SQL] pg_multixact issues
On 2014-09-18 14:41:07 +0530, Dev Kumkar wrote: On Thu, Sep 18, 2014 at 2:41 AM, Adrian Klaver adrian.kla...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [SQL] pg_multixact issues
On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund and...@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 datfrozenxidage(datfrozenxid)datminmxid 16384 myDB1673 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: [GENERAL] [SQL] pg_multixact issues
On Thu, Sep 18, 2014 at 6:20 PM, Dev Kumkar devdas.kum...@gmail.com wrote: On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund and...@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 datfrozenxidage(datfrozenxid)datminmxid 16384 myDB1673 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: [GENERAL] [SQL] pg_multixact issues
On 09/18/2014 10:22 AM, Dev Kumkar wrote:
On Thu, Sep 18, 2014 at 6:20 PM, Dev Kumkar devdas.kum...@gmail.com
mailto:devdas.kum...@gmail.com wrote:
On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund
and...@2ndquadrant.com mailto:and...@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 datfrozenxidage(datfrozenxid)datminmxid
16384 myDB1673 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 = '') AND
NOT EXISTS (SELECT * FROM list WHERE file = '0001') AND
NOT EXISTS (SELECT * FROM list WHERE file = '') AND
EXISTS (SELECT * FROM list WHERE file != '')
AS file__removal_required;
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [SQL] pg_multixact issues
Dev Kumkar wrote: On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund and...@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 datfrozenxidage(datfrozenxid)datminmxid 16384 myDB1673 10872259 1 Can you paste the pg_controldata output please? Also, what files are there in pg_multixact/offsets/ ? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [SQL] pg_multixact issues
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_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. 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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [SQL] pg_multixact issues
On Wed, Sep 17, 2014 at 6:51 PM, Adrian Klaver adrian.kla...@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: [GENERAL] [SQL] pg_multixact issues
On Wed, Sep 17, 2014 at 7:20 PM, Dev Kumkar devdas.kum...@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: [GENERAL] [SQL] pg_multixact issues
On Wed, Sep 17, 2014 at 7:20 PM, Dev Kumkar devdas.kum...@gmail.com wrote: On Wed, Sep 17, 2014 at 6:51 PM, Adrian Klaver adrian.kla...@aklaver.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...
Re: [GENERAL] [SQL] pg_multixact issues
On 09/17/2014 01:36 PM, Dev Kumkar wrote: On Wed, Sep 17, 2014 at 7:20 PM, Dev Kumkar devdas.kum...@gmail.com mailto:devdas.kum...@gmail.com wrote: On Wed, Sep 17, 2014 at 6:51 PM, Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [SQL] pg_multixact issues
On 09/17/2014 01:36 PM, Dev Kumkar wrote: On Wed, Sep 17, 2014 at 7:20 PM, Dev Kumkar devdas.kum...@gmail.com mailto:devdas.kum...@gmail.com wrote: On Wed, Sep 17, 2014 at 6:51 PM, Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
