Re: Buffer statistics for pg_stat_statements

Le vendredi 18 décembre 2009 09:44:40, Takahiro Itagaki a écrit :
> 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.

Very good idea.
Perhaps it can be usefull to have the percentage for hit/read ratio computed
in the view ?

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

--
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org