Thread: 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_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

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

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

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

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

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

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

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

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

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