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

Re: database profiling

From: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
To: Orion Henry <orion(at)trustcommerce(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: database profiling
Date: 2004-01-21 00:24:55
Message-ID: 400DC6D7.1040705@familyhealth.com.au (view raw or flat)
Thread:
Lists: pgsql-performance
> 1) Statistics kept on all the tables and indexes showing the number of
> times and the number of milliseconds spent sequential scanning / index
> scanning them.  You could easily identify an index that's not being used
> or a table that's being sequential scanned too often.  Also supply a
> database command to reset the statistic gather to zero.

Enable the stats collector in your postgresql.conf, and then look at the 
pg_stat_* views.  They are your friend.

> 2) Statics that show count/total times to insert into/updating/deleting
> from a table with time times spent in table and index separated so you
> could see how much the indexes are effecting the write performance on
> the table.

Lots of that is in the pg_statio_* tables (if you have the collector 
running)

> 3) The ability to view the current running time, plan and SQL text of
> all currently running queries.

That's in the pg_stat_activity table.

There's also a new postgresql.conf setting in 7.4 that lets you log any 
query that runs more than a certain number of milliseconds - which is 
very useful for tracking down your slow queries.

Chris


In response to

pgsql-performance by date

Next:From: Jeroen BaekelandtDate: 2004-01-21 09:34:10
Subject: Really slow even after vacuum
Previous:From: Orion HenryDate: 2004-01-20 19:47:09
Subject: database profiling

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