Thread: Queries causing highest I/O load since pg_stat_reset?
Hi, is it possible to retrieve a list of queries that has caused the highest i/o load? Something like (1) select pg_stat_reset(); (2) run some queries for a while (3) list the queries that caused the highest i/o since step (1) ? /Mikael
On 11/14/05, Mikael Carneholm <Mikael.Carneholm@wirelesscar.com> wrote: > Hi, > > is it possible to retrieve a list of queries that has caused the highest i/o load? Something like > > (1) select pg_stat_reset(); > (2) run some queries for a while > (3) list the queries that caused the highest i/o since step (1) > > ? > > /Mikael > select * from pg_stat_activity; -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;)
> select * from pg_stat_activity; Nope, doesn't cut it. First, it only gives you the current_query (and current_query is just that - the current query for*active* connections, not historical sessions). Second, it doesn't provide any info on blocks read/written. And pg_stat_foo_tablesgives you neither blocks read/written nor query strings, and pg_stat_database and pg_statio_foo_tablesonly give you the *total* number of blocks read (not connected to query strings). I'd like to be able to do something like this: -- list the 10 highest i/o stressing queries since last pg_stat_reset() select s.query_string, so.blks_read from sometable s, someothertable so where s.foo = so.foo order by so.blks_read desc limit 10; /Mikael -----Original Message----- From: Jaime Casanova [mailto:systemguards@gmail.com] Sent: den 14 november 2005 21:08 To: Mikael Carneholm Cc: 'Pgsql-General (E-mail) Subject: Re: [GENERAL] Queries causing highest I/O load since pg_stat_reset? On 11/14/05, Mikael Carneholm <Mikael.Carneholm@wirelesscar.com> wrote: > Hi, > > is it possible to retrieve a list of queries that has caused the highest i/o load? Something like > > (1) select pg_stat_reset(); > (2) run some queries for a while > (3) list the queries that caused the highest i/o since step (1) > > ? > > /Mikael > select * from pg_stat_activity; -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;)
I don't think there's any way to do this currently. Maybe if you wrote an external program that polled pg_stat_activity and then correlated procpid to the output of top, etc. On Mon, Nov 14, 2005 at 08:59:19PM +0100, Mikael Carneholm wrote: > Hi, > > is it possible to retrieve a list of queries that has caused the highest i/o load? Something like > > (1) select pg_stat_reset(); > (2) run some queries for a while > (3) list the queries that caused the highest i/o since step (1) > > ? > > /Mikael > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461