monitoring usage count distribution
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
Attachments:
v1-0001-introduce-pg_buffercache_usage_counts.patchtext/x-diff; charset=us-asciiDownload+176-3
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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