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