Skip site navigation (1) Skip section navigation (2)

Re: too many clog files

From: Duan Ligong <duanlg(at)nec-as(dot)nec(dot)com(dot)cn>
To: rexmabry(at)yahoo(dot)com, pgsql-performance(at)postgresql(dot)org
Subject: Re: too many clog files
Date: 2008-09-08 02:45:02
Message-ID: 20080908101344.1E05.DUANLG@nec-as.nec.com.cn (view raw or flat)
Thread:
Lists: pgsql-performance
Rex Mabry wrote:
> Try this query for looking at queries.

Thanks so much for your reply.

> SELECT procpid, usename , (now() - query_start) as age, query_start, l.mode, l.granted
>    FROM pg_stat_activity a LEFT OUTER JOIN pg_locks l ON (a.procpid = l.pid)
>    LEFT OUTER JOIN pg_class c ON (l.relation = c.oid)
>    WHERE current_query NOT IN ('<IDLE>') 
>    and a.usename <> 'postgres'
>    ORDER BY pid;

I tried it and the output are as follows:
- - - -
mydb=> SELECT procpid, usename , (now() - query_start) 
as age, query_start, l.mode, l.granted FROM pg_stat_activity a
LEFT UTER JOIN pg_locks l ON (a.procpid = l.pid) LEFT 
OUTER JOIN pg_class c N (l.relation = c.oid) WHERE 
current_query NOT IN ('<IDLE>') and .usename <> 
'postgres' ORDER BY pid;
procpid | usename  | age | query_start |      mode       | granted
---------+----------+-----+-------------+-----------------+---------
    4134 | myname |     |             | AccessShareLock | t
    4134 |myname |     |             | AccessShareLock | t
    4134 | myname |     |             | ExclusiveLock   | t
    4134 | myname |     |             | AccessShareLock | t
    4134 | mynamei |     |             | AccessShareLock | t
    4134 | myname |     |             | AccessShareLock | t
    4134 | myname |     |             | AccessShareLock | t
(7 rows)
- - - -
Are these 7 rows the long-running transactions? 
If they are, what should we do with them? just killing the processes?
#It seems  that the 7 rows are caused by logining db and executing 
the select clause. and they are not long-running transactions, aren't
they? because the procpid changes when I logout and login again.

Thanks
Duan

> --- On Thu, 9/4/08, Duan Ligong <duanlg(at)nec-as(dot)nec(dot)com(dot)cn> wrote:
> 
> From: Duan Ligong <duanlg(at)nec-as(dot)nec(dot)com(dot)cn>
> Subject: Re: [PERFORM] too many clog files
> To: "Greg Smith" <gsmith(at)gregsmith(dot)com>, pgsql-performance(at)postgresql(dot)org
> Date: Thursday, September 4, 2008, 9:58 PM
> 
> Thanks for your reply.
> 
> Greg wrote:
> > On Tue, 2 Sep 2008, Duan Ligong wrote:
> > > - Does Vacuum delete the old clog files?
> > 
> > Yes, if those transactions are all done.  One possibility here is that 
> > you've got some really long-running transaction floating around that
> is 
> > keeping normal clog cleanup from happening.  Take a look at the output 
> > from "select * from pg_stat_activity" and see if there are any
> really old 
> > transactions floating around.
> 
> Well, we could not wait so long and just moved the old clog files.
> The postgresql system is running well.
> But now the size of pg_clog has exceeded 50MB and there 
> are 457 clog files.
> - - - -
> [root(at)set19:AN0101 hydragui]# du -sh pgdata/pg_clog
> 117M    pgdata/pg_clog
> - - - -
> 
> My question is:
> - How to determine whether there is a long-running transactions
> or not based on the output of "select * from pg_stat_activity"?
> It seems there is not the start time of transactions.
> - How should we deal with it if there is a long-running transactin?
> Can we do something to avoid long-running transactions?
> 
> The following is  the output of "select * from pg_stat_activity".
> #It seems there are no query_start time information.
> - - - - 
> xxxdb=> select * from pg_stat_activity;
>  datid |   datname   | procpid | usesysid | usename  | current_query |
> query_start
> -------+-------------+---------+----------+----------+---------------+-------------
>  92406 | xxxdb |   17856 |      100 | myname |               |
>  92406 | xxxdb |   31052 |      100 | myname |               |
> (2 rows)
> - - - -
> After about 6minutes, I execute it again and the output is
> - - - - 
> xxxdb=> select * from pg_stat_activity;
>  datid |   datname   | procpid | usesysid | usename  | current_query |
> query_start
> -------+-------------+---------+----------+----------+---------------+-------------
>  92406 | xxxdb |    5060 |      100 |myname |               |
>  92406 | xxxdb |    5626 |      100 |myname |               |
> (2 rows)
> 
> - - - -
> #my postgresql version is 8.1
> 
> 
> > > - Can we controll the maximum number of the clog files?
> > 
> > The reference Alvaro suggested at 
> >
> http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
> 
> > goes over that.  If you reduce autovacuum_freeze_max_age, that reduces the
> 
> > expected maximum size of the clog files.  "The default, 200 million 
> > transactions, translates to about 50MB of pg_clog storage."  If
> you've got 
> > significantly more than 50MB of files in there, normally that means that 
> > either you're not running vacuum to clean things up usefully, or
> there's 
> > an old open transaction still floating around.
> > 
> > > - When, or in what case is  a new clog file produced?
> > 
> > Every 32K transactions.  See http://wiki.postgresql.org/wiki/Hint_Bits for
> 
> > some clarification about what the clog files actually do.  I recently 
> > collected some notes from this list and from the source code README files 
> > to give a high-level view there of what actually goes on under the hood in
> 
> > this area.
> 
> It seems that one transaction occupies 2bit and 32K transactions should 
> occupy 8K, which is the size of one page.
> The size of each clog file is 256KB.
> Is there other information which is in clog files except the Hint_bits?
> And Do the other information and Hint_bit of 32K transactions occupy 
> 256KB?
> 
> > > - Is there a mechanism that the clog files are recycled? If there is
> ,
> > > what is the mechanism?
> > 
> > The old ones should get wiped out by vacuum's freezing mechanism.
> 
> Does Wiping out clog files has something to do with the configuration 
> except Vacuum? Does  we have to set some parameter to enable
> the function of Vacuum's wipping out old clog files?
> 
> The following is my postgresql.conf file:
> - - - -
> tcpip_socket = true
> max_connections = 500
> port = 5432
> shared_buffers = 1000 
> syslog = 2 
> log_min_messages = fatal 
> - - - -
> #my postgresql database is very small.
> 
> Thanks
> Duan
> 
> > --
> > * Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
> > 
> > -- 
> > Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-performance
> 
> 
> 
> 
> -- 
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
> 
> 
> 
>       




In response to

pgsql-performance by date

Next:From: Duan LigongDate: 2008-09-08 03:07:45
Subject: Re: too many clog files
Previous:From: Axel RauDate: 2008-09-07 10:50:30
Subject: performance impact of non-C locale

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group