Horton

Thread

Commits

  1. Add pg_buffercache_usage_counts() to contrib/pg_buffercache.

  1. monitoring usage count distribution

    Nathan Bossart <nathandbossart@gmail.com> — 2023-01-30T23:30:40Z

    My colleague Jeremy Schneider (CC'd) was recently looking into usage count
distributions for various workloads, and he mentioned that it would be nice
to have an easy way to do $SUBJECT.  I've attached a patch that adds a
pg_buffercache_usage_counts() function.  This function returns a row per
possible usage count with some basic information about the corresponding
buffers.

    postgres=# SELECT * FROM pg_buffercache_usage_counts();
     usage_count | buffers | dirty | pinned
    -------------+---------+-------+--------
               0 |       0 |     0 |      0
               1 |    1436 |   671 |      0
               2 |     102 |    88 |      0
               3 |      23 |    21 |      0
               4 |       9 |     7 |      0
               5 |     164 |   106 |      0
    (6 rows)

This new function provides essentially the same information as
pg_buffercache_summary(), but pg_buffercache_summary() only shows the
average usage count for the buffers in use.  If there is interest in this
idea, another approach to consider could be to alter
pg_buffercache_summary() instead.

Thoughts?

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

  2. Re: monitoring usage count distribution

    Greg Stark <stark@mit.edu> — 2023-04-04T18:14:36Z

    On Mon, 30 Jan 2023 at 18:31, Nathan Bossart <nathandbossart@gmail.com> wrote:
>
> My colleague Jeremy Schneider (CC'd) was recently looking into usage count
> distributions for various workloads, and he mentioned that it would be nice
> to have an easy way to do $SUBJECT.  I've attached a patch that adds a
> pg_buffercache_usage_counts() function.  This function returns a row per
> possible usage count with some basic information about the corresponding
> buffers.
>
>     postgres=# SELECT * FROM pg_buffercache_usage_counts();
>      usage_count | buffers | dirty | pinned
>     -------------+---------+-------+--------
>                0 |       0 |     0 |      0
>                1 |    1436 |   671 |      0
>                2 |     102 |    88 |      0
>                3 |      23 |    21 |      0
>                4 |       9 |     7 |      0
>                5 |     164 |   106 |      0
>     (6 rows)
>
> This new function provides essentially the same information as
> pg_buffercache_summary(), but pg_buffercache_summary() only shows the
> average usage count for the buffers in use.  If there is interest in this
> idea, another approach to consider could be to alter
> pg_buffercache_summary() instead.

Tom expressed skepticism that there's wide interest here. It seems as
much from the lack of response. But perhaps that's just because people
don't understand what the importance of this info is -- I certainly
don't :)

I feel like the original sin here is having the function return an
aggregate data. If it returned the raw data then people could slice,
dice, and aggregate the data in any ways they want using SQL. And
perhaps people would come up with queries that have more readily
interpretable important information?

Obviously there are performance questions in that but I suspect they
might be solvable given how small the data for each buffer are.

Just as a warning though -- if nobody was interested in this patch
please don't take my comments as a recommendation that you spend a lot
of time developing a more complex version in the same direction
without seeing if anyone agrees with my suggestion :)

-- 
greg

  3. Re: monitoring usage count distribution

    Robert Haas <robertmhaas@gmail.com> — 2023-04-04T18:31:36Z

    On Mon, Jan 30, 2023 at 6:30 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
> My colleague Jeremy Schneider (CC'd) was recently looking into usage count
> distributions for various workloads, and he mentioned that it would be nice
> to have an easy way to do $SUBJECT.  I've attached a patch that adds a
> pg_buffercache_usage_counts() function.  This function returns a row per
> possible usage count with some basic information about the corresponding
> buffers.
>
>     postgres=# SELECT * FROM pg_buffercache_usage_counts();
>      usage_count | buffers | dirty | pinned
>     -------------+---------+-------+--------
>                0 |       0 |     0 |      0
>                1 |    1436 |   671 |      0
>                2 |     102 |    88 |      0
>                3 |      23 |    21 |      0
>                4 |       9 |     7 |      0
>                5 |     164 |   106 |      0
>     (6 rows)
>
> This new function provides essentially the same information as
> pg_buffercache_summary(), but pg_buffercache_summary() only shows the
> average usage count for the buffers in use.  If there is interest in this
> idea, another approach to consider could be to alter
> pg_buffercache_summary() instead.

I'm skeptical that pg_buffercache_summary() is a good idea at all, but
having it display the average usage count seems like a particularly
poor idea. That information is almost meaningless. Replacing that with
a six-element integer array would be a clear improvement and, IMHO,
better than adding yet another function to the extension.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

  4. Re: monitoring usage count distribution

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-04-04T18:40:02Z

    Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Jan 30, 2023 at 6:30 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
>> My colleague Jeremy Schneider (CC'd) was recently looking into usage count
>> distributions for various workloads, and he mentioned that it would be nice
>> to have an easy way to do $SUBJECT.

> I'm skeptical that pg_buffercache_summary() is a good idea at all, but
> having it display the average usage count seems like a particularly
> poor idea. That information is almost meaningless. Replacing that with
> a six-element integer array would be a clear improvement and, IMHO,
> better than adding yet another function to the extension.

I had not realized that pg_buffercache_summary() is new in v16,
but since it is, we still have time to rethink its definition.
+1 for de-aggregating --- I agree that the overall average is
unlikely to have much value.

			regards, tom lane

  5. Re: monitoring usage count distribution

    Melanie Plageman <melanieplageman@gmail.com> — 2023-04-04T23:10:24Z

    On Tue, Apr 4, 2023 at 2:40 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Robert Haas <robertmhaas@gmail.com> writes:
> > On Mon, Jan 30, 2023 at 6:30 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
> >> My colleague Jeremy Schneider (CC'd) was recently looking into usage count
> >> distributions for various workloads, and he mentioned that it would be nice
> >> to have an easy way to do $SUBJECT.
>
> > I'm skeptical that pg_buffercache_summary() is a good idea at all, but
> > having it display the average usage count seems like a particularly
> > poor idea. That information is almost meaningless. Replacing that with
> > a six-element integer array would be a clear improvement and, IMHO,
> > better than adding yet another function to the extension.
>
> I had not realized that pg_buffercache_summary() is new in v16,
> but since it is, we still have time to rethink its definition.
> +1 for de-aggregating --- I agree that the overall average is
> unlikely to have much value.

So, I have used pg_buffercache_summary() to give me a high-level idea of
the usage count when I am benchmarking a particular workload -- and I
would have found it harder to look at 6 rows instead of 1. That being
said, having six rows is more versatile as you could aggregate it
yourself easily.

- Melanie

  6. Re: monitoring usage count distribution

    Andres Freund <andres@anarazel.de> — 2023-04-04T23:25:27Z

    Hi,

On 2023-04-04 14:14:36 -0400, Greg Stark wrote:
> Tom expressed skepticism that there's wide interest here. It seems as
> much from the lack of response. But perhaps that's just because people
> don't understand what the importance of this info is -- I certainly
> don't :)

pg_buffercache has exposed the raw data for a long time. The problem is that
it's way too slow to look at that way.

Greetings,

Andres Freund

  7. Re: monitoring usage count distribution

    Andres Freund <andres@anarazel.de> — 2023-04-04T23:29:19Z

    Hi,

On 2023-04-04 14:31:36 -0400, Robert Haas wrote:
> On Mon, Jan 30, 2023 at 6:30 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
> > My colleague Jeremy Schneider (CC'd) was recently looking into usage count
> > distributions for various workloads, and he mentioned that it would be nice
> > to have an easy way to do $SUBJECT.  I've attached a patch that adds a
> > pg_buffercache_usage_counts() function.  This function returns a row per
> > possible usage count with some basic information about the corresponding
> > buffers.
> >
> >     postgres=# SELECT * FROM pg_buffercache_usage_counts();
> >      usage_count | buffers | dirty | pinned
> >     -------------+---------+-------+--------
> >                0 |       0 |     0 |      0
> >                1 |    1436 |   671 |      0
> >                2 |     102 |    88 |      0
> >                3 |      23 |    21 |      0
> >                4 |       9 |     7 |      0
> >                5 |     164 |   106 |      0
> >     (6 rows)
> >
> > This new function provides essentially the same information as
> > pg_buffercache_summary(), but pg_buffercache_summary() only shows the
> > average usage count for the buffers in use.  If there is interest in this
> > idea, another approach to consider could be to alter
> > pg_buffercache_summary() instead.
> 
> I'm skeptical that pg_buffercache_summary() is a good idea at all

Why? It's about two orders of magnitude faster than querying the equivalent
data by aggregating in SQL. And knowing how many free and dirty buffers are
over time is something quite useful to monitor / correlate with performance
issues.


> but having it display the average usage count seems like a particularly poor
> idea. That information is almost meaningless.

I agree there are more meaningful ways to represent the data, but I don't
agree that it's almost meaningless. It can give you a rough estimate of
whether data in s_b is referenced or not.


> Replacing that with a six-element integer array would be a clear improvement
> and, IMHO, better than adding yet another function to the extension.

I'd have no issue with that.

Greetings,

Andres Freund

  8. Re: monitoring usage count distribution

    Robert Haas <robertmhaas@gmail.com> — 2023-04-05T13:44:58Z

    On Tue, Apr 4, 2023 at 7:29 PM Andres Freund <andres@anarazel.de> wrote:
> > I'm skeptical that pg_buffercache_summary() is a good idea at all
>
> Why? It's about two orders of magnitude faster than querying the equivalent
> data by aggregating in SQL. And knowing how many free and dirty buffers are
> over time is something quite useful to monitor / correlate with performance
> issues.

Well, OK, fair point.

> > but having it display the average usage count seems like a particularly poor
> > idea. That information is almost meaningless.
>
> I agree there are more meaningful ways to represent the data, but I don't
> agree that it's almost meaningless. It can give you a rough estimate of
> whether data in s_b is referenced or not.

I might have overstated my case.

> > Replacing that with a six-element integer array would be a clear improvement
> > and, IMHO, better than adding yet another function to the extension.
>
> I'd have no issue with that.

Cool.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

  9. Re: monitoring usage count distribution

    Nathan Bossart <nathandbossart@gmail.com> — 2023-04-05T17:51:24Z

    On Wed, Apr 05, 2023 at 09:44:58AM -0400, Robert Haas wrote:
> On Tue, Apr 4, 2023 at 7:29 PM Andres Freund <andres@anarazel.de> wrote:
>> > Replacing that with a six-element integer array would be a clear improvement
>> > and, IMHO, better than adding yet another function to the extension.
>>
>> I'd have no issue with that.
> 
> Cool.

The six-element array approach won't show the number of dirty and pinned
buffers for each usage count, but I'm not sure that's a deal-breaker.
Barring objections, I'll post an updated patch shortly with that approach.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

  10. Re: monitoring usage count distribution

    Robert Haas <robertmhaas@gmail.com> — 2023-04-05T19:00:20Z

    On Wed, Apr 5, 2023 at 1:51 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
> On Wed, Apr 05, 2023 at 09:44:58AM -0400, Robert Haas wrote:
> > On Tue, Apr 4, 2023 at 7:29 PM Andres Freund <andres@anarazel.de> wrote:
> >> > Replacing that with a six-element integer array would be a clear improvement
> >> > and, IMHO, better than adding yet another function to the extension.
> >>
> >> I'd have no issue with that.
> >
> > Cool.
>
> The six-element array approach won't show the number of dirty and pinned
> buffers for each usage count, but I'm not sure that's a deal-breaker.
> Barring objections, I'll post an updated patch shortly with that approach.

Right, well, I would personally be OK with 6 rows too, but I don't
know what other people want.  I think either this or that is better
than average.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

  11. Re: monitoring usage count distribution

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-04-05T19:07:10Z

    Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Apr 5, 2023 at 1:51 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
>> The six-element array approach won't show the number of dirty and pinned
>> buffers for each usage count, but I'm not sure that's a deal-breaker.
>> Barring objections, I'll post an updated patch shortly with that approach.

> Right, well, I would personally be OK with 6 rows too, but I don't
> know what other people want.  I think either this or that is better
> than average.

Seems to me that six rows would be easier to aggregate manually.
An array column seems less SQL-ish and harder to manipulate.

			regards, tom lane

  12. Re: monitoring usage count distribution

    Andres Freund <andres@anarazel.de> — 2023-04-05T19:09:21Z

    Hi,

On 2023-04-05 15:00:20 -0400, Robert Haas wrote:
> On Wed, Apr 5, 2023 at 1:51 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
> > On Wed, Apr 05, 2023 at 09:44:58AM -0400, Robert Haas wrote:
> > > On Tue, Apr 4, 2023 at 7:29 PM Andres Freund <andres@anarazel.de> wrote:
> > >> > Replacing that with a six-element integer array would be a clear improvement
> > >> > and, IMHO, better than adding yet another function to the extension.
> > >>
> > >> I'd have no issue with that.
> > >
> > > Cool.
> >
> > The six-element array approach won't show the number of dirty and pinned
> > buffers for each usage count, but I'm not sure that's a deal-breaker.
> > Barring objections, I'll post an updated patch shortly with that approach.
> 
> Right, well, I would personally be OK with 6 rows too, but I don't
> know what other people want.  I think either this or that is better
> than average.

I would not mind having a separate function returning 6 rows, if we really
want that, but making pg_buffercache_summary() return 6 rows would imo make it
less useful for getting a quick overview. At least I am not that quick summing
up multple rows, just to get a quick overview over the number of dirty rows.

Greetings,

Andres Freund

  13. Re: monitoring usage count distribution

    Nathan Bossart <nathandbossart@gmail.com> — 2023-04-05T19:35:05Z

    On Wed, Apr 05, 2023 at 03:07:10PM -0400, Tom Lane wrote:
> Seems to me that six rows would be easier to aggregate manually.
> An array column seems less SQL-ish and harder to manipulate.

+1

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

  14. Re: monitoring usage count distribution

    Nathan Bossart <nathandbossart@gmail.com> — 2023-04-05T19:41:01Z

    On Wed, Apr 05, 2023 at 12:09:21PM -0700, Andres Freund wrote:
> I would not mind having a separate function returning 6 rows, if we really
> want that, but making pg_buffercache_summary() return 6 rows would imo make it
> less useful for getting a quick overview. At least I am not that quick summing
> up multple rows, just to get a quick overview over the number of dirty rows.

This is what v1-0001 does.  We could probably make pg_buffercache_summary a
view on pg_buffercache_usage_counts, too, but that doesn't strike me as
tremendously important.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

  15. Re: monitoring usage count distribution

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-04-05T20:16:24Z

    Nathan Bossart <nathandbossart@gmail.com> writes:
> On Wed, Apr 05, 2023 at 12:09:21PM -0700, Andres Freund wrote:
>> I would not mind having a separate function returning 6 rows, if we really
>> want that, but making pg_buffercache_summary() return 6 rows would imo make it
>> less useful for getting a quick overview. At least I am not that quick summing
>> up multple rows, just to get a quick overview over the number of dirty rows.

> This is what v1-0001 does.  We could probably make pg_buffercache_summary a
> view on pg_buffercache_usage_counts, too, but that doesn't strike me as
> tremendously important.

Having two functions doesn't seem unreasonable to me either.
Robert spoke against it to start with, does he still want to
advocate for that?

			regards, tom lane

  16. Re: monitoring usage count distribution

    Robert Haas <robertmhaas@gmail.com> — 2023-04-06T17:20:53Z

    On Wed, Apr 5, 2023 at 4:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Nathan Bossart <nathandbossart@gmail.com> writes:
> > On Wed, Apr 05, 2023 at 12:09:21PM -0700, Andres Freund wrote:
> >> I would not mind having a separate function returning 6 rows, if we really
> >> want that, but making pg_buffercache_summary() return 6 rows would imo make it
> >> less useful for getting a quick overview. At least I am not that quick summing
> >> up multple rows, just to get a quick overview over the number of dirty rows.
>
> > This is what v1-0001 does.  We could probably make pg_buffercache_summary a
> > view on pg_buffercache_usage_counts, too, but that doesn't strike me as
> > tremendously important.
>
> Having two functions doesn't seem unreasonable to me either.
> Robert spoke against it to start with, does he still want to
> advocate for that?

My position is that if we replace the average usage count with
something that gives a count for each usage count, that's a win. I
don't have a strong opinion on an array vs. a result set vs. some
other way of doing that. If we leave the average usage count in there
and add yet another function to give the detail, I tend to think
that's not a great plan, but I'll desist if everyone else thinks
otherwise.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

  17. Re: monitoring usage count distribution

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-04-06T17:32:35Z

    Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Apr 5, 2023 at 4:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Having two functions doesn't seem unreasonable to me either.
>> Robert spoke against it to start with, does he still want to
>> advocate for that?

> My position is that if we replace the average usage count with
> something that gives a count for each usage count, that's a win. I
> don't have a strong opinion on an array vs. a result set vs. some
> other way of doing that. If we leave the average usage count in there
> and add yet another function to give the detail, I tend to think
> that's not a great plan, but I'll desist if everyone else thinks
> otherwise.

There seems to be enough support for the existing summary function
definition to leave it as-is; Andres likes it for one, and I'm not
excited about trying to persuade him he's wrong.  But a second
slightly-less-aggregated summary function is clearly useful as well.
So I'm now thinking that we do want the patch as-submitted.
(Caveat: I've not read the patch, just the description.)

			regards, tom lane

  18. Re: monitoring usage count distribution

    Nathan Bossart <nathandbossart@gmail.com> — 2023-04-06T18:06:08Z

    On Thu, Apr 06, 2023 at 01:32:35PM -0400, Tom Lane wrote:
> There seems to be enough support for the existing summary function
> definition to leave it as-is; Andres likes it for one, and I'm not
> excited about trying to persuade him he's wrong.  But a second
> slightly-less-aggregated summary function is clearly useful as well.
> So I'm now thinking that we do want the patch as-submitted.
> (Caveat: I've not read the patch, just the description.)

In case we want to do both, here's a 0002 that changes usagecount_avg to an
array of usage counts.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

  19. Re: monitoring usage count distribution

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-04-07T18:29:31Z

    Nathan Bossart <nathandbossart@gmail.com> writes:
> On Thu, Apr 06, 2023 at 01:32:35PM -0400, Tom Lane wrote:
>> There seems to be enough support for the existing summary function
>> definition to leave it as-is; Andres likes it for one, and I'm not
>> excited about trying to persuade him he's wrong.  But a second
>> slightly-less-aggregated summary function is clearly useful as well.
>> So I'm now thinking that we do want the patch as-submitted.
>> (Caveat: I've not read the patch, just the description.)

> In case we want to do both, here's a 0002 that changes usagecount_avg to an
> array of usage counts.

I'm not sure if there is consensus for 0002, but I reviewed and pushed
0001.  I made one non-cosmetic change: it no longer skips invalid
buffers.  Otherwise, the row for usage count 0 would be pretty useless.
Also it seemed to me that sum(buffers) ought to agree with the
shared_buffers setting.

			regards, tom lane

  20. Re: monitoring usage count distribution

    Nathan Bossart <nathandbossart@gmail.com> — 2023-04-08T00:18:16Z

    On Fri, Apr 07, 2023 at 02:29:31PM -0400, Tom Lane wrote:
> I'm not sure if there is consensus for 0002, but I reviewed and pushed
> 0001.  I made one non-cosmetic change: it no longer skips invalid
> buffers.  Otherwise, the row for usage count 0 would be pretty useless.
> Also it seemed to me that sum(buffers) ought to agree with the
> shared_buffers setting.

Makes sense.  Thanks!

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com