Re: template1 database is facing high datfrozenxid

[AI Rumman <rummandba@xxxxxxxxx>]

In pg_subtrans, I have files like:

$ ls -lrt | more
total 1269436
-rw------- 1 postgres postgres 262144 Jan 25 18:49 D907
-rw------- 1 postgres postgres 262144 Jan 25 18:54 D908
-rw------- 1 postgres postgres 262144 Jan 25 18:58 D909
-rw------- 1 postgres postgres 262144 Jan 25 18:59 D90A
-rw------- 1 postgres postgres 262144 Jan 25 19:04 D90B
-rw------- 1 postgres postgres 262144 Jan 25 19:09 D90C
-rw------- 1 postgres postgres 262144 Jan 25 19:14 D90D
-rw------- 1 postgres postgres 262144 Jan 25 19:18 D90E
-rw------- 1 postgres postgres 262144 Jan 25 19:19 D90F
-rw------- 1 postgres postgres 262144 Jan 25 19:24 D910
-rw------- 1 postgres postgres 262144 Jan 25 19:29 D911
-rw------- 1 postgres postgres 262144 Jan 25 19:33 D912
-rw------- 1 postgres postgres 262144 Jan 25 19:34 D913
-rw------- 1 postgres postgres 262144 Jan 25 19:39 D914
-rw------- 1 postgres postgres 262144 Jan 25 19:44 D915
-rw------- 1 postgres postgres 262144 Jan 25 19:49 D916
-rw------- 1 postgres postgres 262144 Jan 25 19:53 D917
-rw------- 1 postgres postgres 262144 Jan 25 19:54 D918
-rw------- 1 postgres postgres 262144 Jan 25 19:59 D919
-rw------- 1 postgres postgres 262144 Jan 25 20:04 D91A
-rw------- 1 postgres postgres 262144 Jan 25 20:09 D91B
-rw------- 1 postgres postgres 262144 Jan 25 20:14 D91C
-rw------- 1 postgres postgres 262144 Jan 25 20:19 D91D
-rw------- 1 postgres postgres 262144 Jan 25 20:23 D91E
-rw------- 1 postgres postgres 262144 Jan 25 20:24 D91F
-rw------- 1 postgres postgres 262144 Jan 25 20:29 D920
-rw------- 1 postgres postgres 262144 Jan 25 20:34 D921
-rw------- 1 postgres postgres 262144 Jan 25 20:39 D922
-rw------- 1 postgres postgres 262144 Jan 25 20:44 D923
-rw------- 1 postgres postgres 262144 Jan 25 20:49 D924
-rw------- 1 postgres postgres 262144 Jan 25 20:54 D925
-rw------- 1 postgres postgres 262144 Jan 25 20:59 D926
-rw------- 1 postgres postgres 262144 Jan 25 21:04 D927
.....

Does it mean that I have too many open transactions? If yes, it is not showing in pg_stat_activity. 

On Fri, Feb 12, 2016 at 3:38 PM, AI Rumman <rummandba@xxxxxxxxx> wrote:

Used this query in each of the database::

SELECT t.relname, l.database, l.locktype, l.pid , l.mode, l.granted, p.current_query, p.query_start ,p.waiting 

FROM pg_locks as l 

INNER JOIN pg_stat_all_tables t 

on l.relation = t.relid 

INNER JOIN pg_stat_activity as p 

on l.pid = p.procpid ;

No luck. At present, db is working, but t is going towards wraparound.

On Fri, Feb 12, 2016 at 3:28 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

On 02/12/2016 03:10 PM, AI Rumman wrote:

I checked it and I did not find any log running sql or any open
transaction. Not even in pg_prepared_xacts.
And it looks like pg_catalog database is making the alarm.

Any other idea please, where I need to look into.

Should have added:

select * from pg_database

Thanks.


On Fri, Feb 12, 2016 at 3:05 PM, Adrian Klaver

<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> wrote:

    On 02/12/2016 02:56 PM, AI Rumman wrote:

        Hi,

        I am running Postgresql 9.1 and I can see the datfrozenxid is
        going high
        and vacuum process is not bringing it down. And this has been
        happening
        on template1 database.

             2016-02-12 16:51:50.400 CST [19445][@] : [13-1] WARNING:
        oldest
             xmin is far in the past
             2016-02-12 16:51:50.400 CST [19445][@] : [14-1] HINT:
        Close open
             transactions soon to avoid wraparound problems.


    The above seems to be the contributing factor.

    Does:

    select * from pg_stat_activity

    show long running queries.


             2016-02-12 16:51:50.400 CST [19445][@] : [15-1] LOG:  automatic
             vacuum of table "template1.pg_catalog.pg_database": index
        scans: 0
             pages: 0 removed, 1 remain
             tuples: 0 removed, 9 remain
             system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
             2016-02-12 16:51:50.411 CST [19435][@] : [82-1] WARNING:
        oldest
             xmin is far in the past
             2016-02-12 16:51:50.411 CST [19435][@] : [83-1] HINT:
        Close open
             transactions soon to avoid wraparound problems.
             2016-02-12 16:51:50.411 CST [19435][@] : [84-1] LOG:  automatic
             vacuum of table "template1.pg_catalog.pg_largeobject":
        index scans: 0
             pages: 0 removed, 0 remain
             tuples: 0 removed, 0 remain
             system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec


        I vacuum database manually but nothing is working out.
        Please help,

        Thanks.



    --
    Adrian Klaver

    adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx