Buffer statistics for pg_stat_statements

From: Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Buffer statistics for pg_stat_statements
Date: 2009-12-18 08:44:40
Message-ID: 20091218174439.19C0.52131E4D@oss.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

We have infrastructure to count numbers buffer access in 8.5 Alpha 3.
I'd like to add per-query buffer usage into contrib/pg_stat_statements.

The pg_stat_statements view will have the same contents with
struct BufferUsage. Fields named shared_blks_{hit|read|written},
local_blks_{hit|read|written}, and temp_blks_{read|written}
will be added to the view.

We can determine slow queries not only based on durations but also
based on I/O or memory access count. Also, queries with non-zero
temp_blks_read means DBA need to consider increasing work_mem. Those
information would be useful to find where the server's bottleneck is.

Additional management costs cannot be avoided, but I think it should be
not so high because we accumulate buffer usage only once per query,
while EXPLAIN BUFFERS is slow because we need per-tuple calculation.

I'll submit this pg_stat_statements enhancement to the next commit fest.
Comments welcome.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message KaiGai Kohei 2009-12-18 08:44:42 Re: Largeobject Access Controls (r2460)
Previous Message Pavel Stehule 2009-12-18 06:55:49 Re: COPY IN as SELECT target