Re: Adding percentile metrics to pg_stat_statements module

From: Igor Calabria <igor(dot)calabria(at)gmail(dot)com>
To: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Adding percentile metrics to pg_stat_statements module
Date: 2019-11-01 14:11:13
Message-ID: CA+r4MrQmatjHyzm_Pu6SJHhxtJ5TxfeHkQQ8Ja-Z0OTYtBsTyQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Yeah, I agree that there's no reason to store the digests themselves and I
really liked the idea of it being optional.
If it turns out that memory consumption on real workloads is small enough,
it could eventually be turned on by default.

I'll start working on patch

Em qui, 31 de out de 2019 às 16:32, Tomas Vondra <
tomas(dot)vondra(at)2ndquadrant(dot)com> escreveu:

> On Thu, Oct 31, 2019 at 12:51:17PM -0300, Igor Calabria wrote:
> >Hi everyone,
> >
> >I was taking a look at pg_stat_statements module and noticed that it does
> >not collect any percentile metrics. I believe that It would be really
> handy
> >to have those available and I'd love to contribute with this feature.
> >
> >The basic idea is to accumulate the the query execution times using an
> >approximation structure like q-digest or t-digest and add those results to
> >the pg_stat_statements table as fixed columns. Something like this
> >
> >p90_time:
> >p95_time:
> >p99_time:
> >p70_time:
> >...
> >
> >Another solution is to persist de digest structure in a binary column and
> >use a function to extract the desired quantile ilke this SELECT
> >approx_quantile(digest_times, 0.99) FROM pg_stat_statements
> >
>
> IMO having some sort of CDF approximation (being a q-digest or t-digest)
> would be useful, although it'd probably need to be optional (mostly
> becuase of memory consumption).
>
> I don't see why we would not store the digests themselves. Storing just
> some selected percentiles would be pretty problematic due to losing a
> lot of information on restart. Also, pg_stat_statements is not a table
> but a view on in-memory hash table.
>
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2019-11-01 14:17:51 Re: Adding percentile metrics to pg_stat_statements module
Previous Message Michael Paquier 2019-11-01 14:01:43 Re: Problem with synchronous replication