Re: template1 database is facing high datfrozenxid

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(at)gmail(dot)com
> <mailto:rummandba(at)gmail(dot)com>> 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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> 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(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>> 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][(at)] :
> [13-1] WARNING:
> oldest
> xmin is far in the past
> 2016-02-12 16:51:50.400 CST [19445][(at)] :
> [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][(at)] :
> [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][(at)] :
> [82-1] WARNING:
> oldest
> xmin is far in the past
> 2016-02-12 16:51:50.411 CST [19435][(at)] :
> [83-1] HINT:
> Close open
> transactions soon to avoid wraparound problems.
> 2016-02-12 16:51:50.411 CST [19435][(at)] :
> [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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
>
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com