Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id C98052E003A for ; Sun, 15 Jun 2008 11:41:29 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 72001-09 for ; Sun, 15 Jun 2008 11:41:20 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.247]) by developer.postgresql.org (Postfix) with ESMTP id B0D982E0035 for ; Sun, 15 Jun 2008 11:41:26 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id d30so1054587and.33 for ; Sun, 15 Jun 2008 07:41:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=NXpAkjS1CrtfXbzkSnWT9BJwFgySKoHu0ScYcX7G9oU=; b=RsuVyKD38btSC53JCpzDDrmw+6cUA+Abl4Ls1sa9KW70HabP6JS7bY8sYudkX2++a1 ST5FMyRd4GH94U8GJ974rTXBx771WR8ovOzO2yx7wVNqwvFfnRshHdqdi8ZY5SVOB9Ux y73s6IAYNBhvZZqfm8gFaa34XsAKT33j+3qC8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=qG1yy3QsEvi7xVWAsdyJ1lulNUBv5Lhofdb5vp8YFyTHrF1/orzGWnJ0lrajQFzu3d 2v4PyWw2O8aq211DHUSF3YvYeVjpX3dIjZfGDy4kG3fnoq1BTnKc2O1kTIIVsPe27hDx 7pZVtuZznSIKZraey3E/jZm15U9LXrNTx9bxw= Received: by 10.100.215.5 with SMTP id n5mr6758935ang.99.1213540885219; Sun, 15 Jun 2008 07:41:25 -0700 (PDT) Received: by 10.100.240.14 with HTTP; Sun, 15 Jun 2008 07:41:25 -0700 (PDT) Message-ID: <8ea917510806150741i1fb1a2dfhb3fe71eac2573dd1@mail.gmail.com> Date: Sun, 15 Jun 2008 16:41:25 +0200 From: "Uwe Bartels" To: "Alexander Staubo" Subject: Re: how to find the sql using most of the i/o in an oltp system Cc: pgsql-performance@postgresql.org In-Reply-To: <88daf38c0806150703s24f7736fuc88aded848186af4@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7092_24812004.1213540885226" References: <48551DB0.8050507@gmail.com> <88daf38c0806150703s24f7736fuc88aded848186af4@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200806/53 X-Sequence-Number: 30403 ------=_Part_7092_24812004.1213540885226 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi Alexander, thanks for you answer. What you wrote in terms of postgres I knew. I just tested to log all statements with statistics. This is a lot of unstructured data in a logfile. But this is the best I found as far. The database is running on a solaris box. So DTrace is no problem. I couldn't find any dtrace scripts for postgres. Do you know any scripts except this sample script? Thanks. Uwe On Sun, Jun 15, 2008 at 4:03 PM, Alexander Staubo wrote: > On Sun, Jun 15, 2008 at 3:48 PM, Uwe Bartels > wrote: > > Tuning a sql statements I'm familiar with. Finding a sql statement which > > takes too long due to i/o is probably easy as well. But how about > statements > > that take about 100 ms, that read a lot and that are executed several > times > > per second? > > Take a look at the PostgreSQL manual chapter on monitoring and statistics: > > http://www.postgresql.org/docs/8.3/interactive/monitoring.html > > If you have access to DTrace (available on Solaris, OS X and possibly > FreeBSD), you could hook the low-level system calls to reads and > writes. If you don't have access to DTrace, the pg_statio_* set of > tables is your main option. In particular, pg_statio_user_tables and > pg_statio_user_indexes. See the documentation for the meaning of the > individual columns. > > Unfortunately, the statistics tables are not transaction-specific > (indeed I believe they only update once you commit the transaction, > and then only after a delay), meaning they capture statistics about > everything currently going on in the database. The only way to capture > statistics about a single query, then, is to run it in complete > isolation. > > Alexander. > ------=_Part_7092_24812004.1213540885226 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi Alexander,

thanks for you answer.
What you wrote in terms of postgres I knew. I just tested to log all statements with statistics. This is a lot of unstructured data in a logfile. But this is the best I found as far.

The database is running on a solaris box. So DTrace is no problem. I couldn't find any dtrace scripts for postgres. Do you know any scripts except this sample script?

Thanks.
Uwe

On Sun, Jun 15, 2008 at 4:03 PM, Alexander Staubo <alex@bengler.no> wrote:
On Sun, Jun 15, 2008 at 3:48 PM, Uwe Bartels <uwe.bartels@gmail.com> wrote:
> Tuning a sql statements I'm familiar with. Finding a sql statement which
> takes too long due to i/o is probably easy as well. But how about statements
> that take about 100 ms, that read a lot and that are executed several times
> per second?

Take a look at the PostgreSQL manual chapter on monitoring and statistics:

 http://www.postgresql.org/docs/8.3/interactive/monitoring.html

If you have access to DTrace (available on Solaris, OS X and possibly
FreeBSD), you could hook the low-level system calls to reads and
writes. If you don't have access to DTrace, the pg_statio_* set of
tables is your main option. In particular, pg_statio_user_tables and
pg_statio_user_indexes. See the documentation for the meaning of the
individual columns.

Unfortunately, the statistics tables are not transaction-specific
(indeed I believe they only update once you commit the transaction,
and then only after a delay), meaning they capture statistics about
everything currently going on in the database. The only way to capture
statistics about a single query, then, is to run it in complete
isolation.

Alexander.

------=_Part_7092_24812004.1213540885226--