Search Postgresql Archives

Re: template1 database is facing high datfrozenxid

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Yes. And we solved the problem.
We looked into the pg_subtrans and found that we had subrans pending from January 25th. We investigated more and found that I large sql was executed on Streaming standby around that date.
More digging we found the date of the below alert is also near:
WARNING:  oldest xmin is far in the past
We stopped standby and the problem solved. :)

Thanks.

On Fri, Feb 12, 2016 at 4:11 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 02/12/2016 04:03 PM, AI Rumman wrote:
In pg_subtrans, I have files like:

Are you sure you are looking at the same database cluster in all the cases?

What does:

SELECT datname, age(datfrozenxid) FROM pg_database;

give you?



    $ 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
<mailto: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 <mailto: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>
            <mailto: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>
            <mailto:adrian.klaver@xxxxxxxxxxx
            <mailto:adrian.klaver@xxxxxxxxxxx>>




        --
        Adrian Klaver
        adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>





--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux