Re: too many clog files - Mailing list 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@nec-as.nec.com.cn> wrote:
>
> From: Duan Ligong <duanlg@nec-as.nec.com.cn>
> Subject: Re: [PERFORM] too many clog files
> To: "Greg Smith" <gsmith@gregsmith.com>, pgsql-performance@postgresql.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@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@gregsmith.com http://www.gregsmith.com Baltimore, MD
> >
> > --
> > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
>