Обсуждение: effective_cache_size
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.6/static/runtime-config-query.html Description: Hi, The effective_cache_size documentation (section 19.7) is: "When setting this parameter you should consider both PostgreSQL's shared buffers and the portion of the kernel's disk cache that will be used for PostgreSQL data files.". This description seems essentially stable since v8.1. As far as I understand it means that the value associated to effective_cache_size is the value of the shared_buffers plus the amount of memory often used by the kernel for the buffercache (in Linux's parlance). This explanation is offered by many published documents, for example https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#effective_cache_size Alas, this seems different to what B. Momjian declared in 2012 ( https://www.enterprisedb.com/node/3374 ): "The value doesn't have to be perfect, but just a rough estimate of how much kernel memory is acting as secondary cache for the shared buffers.", which (as far as I understand) means "effective_cache_size = (approx of the often-observed buffercache size)". In the sourcecode v11b3) the module src/backend/utils/misc/guc.c contains "Sets the planner's assumption about the size of the disk cache. That is, the portion of the kernel's disk cache that will be used for PostgreSQL data files." (so effective_cache_size is more or less the "buffercache" size). However in the module src/backend/optimizer/path/costsize.c a comment states that 'We also use a rough estimate "effective_cache_size" of the number of disk pages in Postgres + OS-level disk cache." (so effective_cache_size is more or less the size of the shared_buffers + the size of the "buffercache"). This seems contradictory to me. Thank you!
On Thu, Sep 13, 2018 at 03:14:08PM +0000, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/9.6/static/runtime-config-query.html > Description: > > Hi, > > The effective_cache_size documentation (section 19.7) is: "When setting this > parameter you should consider both PostgreSQL's shared buffers and the > portion of the kernel's disk cache that will be used for PostgreSQL data > files.". > > This description seems essentially stable since v8.1. > > As far as I understand it means that the value associated to > effective_cache_size is the value of the shared_buffers plus the amount of > memory often used by the kernel for the buffercache (in Linux's parlance). > > This explanation is offered by many published documents, for example > https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#effective_cache_size > > Alas, this seems different to what B. Momjian declared in 2012 ( > https://www.enterprisedb.com/node/3374 ): "The value doesn't have to be > perfect, but just a rough estimate of how much kernel memory is acting as > secondary cache for the shared buffers.", which (as far as I understand) > means "effective_cache_size = (approx of the often-observed buffercache > size)". > > In the sourcecode v11b3) the module src/backend/utils/misc/guc.c contains > "Sets the planner's assumption about the size of the disk cache. That is, > the portion of the kernel's disk cache that will be used for PostgreSQL data > files." (so effective_cache_size is more or less the "buffercache" size). > However in the module src/backend/optimizer/path/costsize.c a comment states > that 'We also use a rough estimate "effective_cache_size" of the number of > disk pages in Postgres + OS-level disk cache." (so effective_cache_size is > more or less the size of the shared_buffers + the size of the > "buffercache"). > This seems contradictory to me. Sorry for the delay in replying. I think you are 100% correct that there is a problem. The PG 11 docs say: https://www.postgresql.org/docs/11/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS effective_cache_size (integer) Sets the planner's assumption about the effective size of the disk -------------------------- cache that is available to a single query. This is factored into ----- estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used. When setting this parameter you should consider both PostgreSQL's shared buffers and the portion of the ------------------------------------------------------- kernel's disk cache that will be used for PostgreSQL data files. Also, ------------------- take into account the expected number of concurrent queries on different tables, since they will have to share the available space. This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache; it is used only for estimation purposes. The system also does not assume data remains in the disk cache between queries. The default is 4 gigabytes (4GB). and the server's description of the parameter is: SELECT extra_desc FROM pg_settings WHERE name = 'effective_cache_size'; extra_desc ----------------------------------------------------------------------- That is, the portion of the kernel's disk cache that will be used for ------------------- PostgreSQL data files. This is measured in disk pages, which are normally 8 kB each. So, there are a few problems here. First, referring to storage as "disk" probably needs to be updated in many places --- for me, "disk" means magnetic disk. SSDs are solid-state _drives_, not _disks_. There is no disk in an SSD. We can call it "drive cache" or "storage cache". Second, and I think this was your point, the server's description of the parameter talks specifically about "kernel's disk cache", while the documentation talks about simply "disk cache", and mentions shared buffers are part of that. Looking at the code, mentions of the C variable effective_cache_size don't add shared buffers to the computation, so the documentation looks to be the most accurate. However, part of the logic is that shared buffers is assumed to be much smaller than the kernel storage cache, _and_ pages in the shared buffers are likely to be also in the kernel storage cache (double-buffering), meaning that the number of data blocks in the shared buffer cache that are not already in the kernel storage cache might be near zero, meaning maybe we should ignore the shared buffers size in computing the storage cache. It is probably this duplication of buffers in both places that has led many to ignore the shared buffer size in computing effective_cache_size. So, I think there are two action items here. First, rename "disk" to "drive" or "storage" in the docs and server variable descriptions. Second, unify the description of effective_cache_size to talk about storage cache size, not just kernel storage cache, and maybe even talk about the double buffering effect. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
On Thu, Oct 11, 2018 at 01:48:49PM -0400, Bruce Momjian wrote: > So, I think there are two action items here. First, rename "disk" to > "drive" or "storage" in the docs and server variable descriptions. > Second, unify the description of effective_cache_size to talk about > storage cache size, not just kernel storage cache, and maybe even talk > about the double buffering effect. I have attached a patch for the second item, which I would like to backpatch. I don't think I want to deal with all the "disk" mentions at this time. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Вложения
On Tue, Oct 16, 2018 at 11:38:20AM -0400, Bruce Momjian wrote: > On Thu, Oct 11, 2018 at 01:48:49PM -0400, Bruce Momjian wrote: > > So, I think there are two action items here. First, rename "disk" to > > "drive" or "storage" in the docs and server variable descriptions. > > Second, unify the description of effective_cache_size to talk about > > storage cache size, not just kernel storage cache, and maybe even talk > > about the double buffering effect. > > I have attached a patch for the second item, which I would like to > backpatch. I don't think I want to deal with all the "disk" mentions at > this time. Patch applied through 9.3. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
On 16/10/2018 17:38, Bruce Momjian wrote:
> diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
> index 2317e8b..e471d7f 100644
> --- a/src/backend/utils/misc/guc.c
> +++ b/src/backend/utils/misc/guc.c
> @@ -2987,10 +2987,9 @@ static struct config_int ConfigureNamesInt[] =
>
> {
> {"effective_cache_size", PGC_USERSET, QUERY_TUNING_COST,
> - gettext_noop("Sets the planner's assumption about the size of the disk cache."),
> - gettext_noop("That is, the portion of the kernel's disk cache that "
> - "will be used for PostgreSQL data files. This is measured in disk "
> - "pages, which are normally 8 kB each."),
> + gettext_noop("Sets the planner's assumption about the size of the data cache."),
> + gettext_noop("That is, the size of the cache used for PostgreSQL data files. "
> + "This is measured in disk pages, which are normally 8 kB each."),
> GUC_UNIT_BLOCKS,
> },
> &effective_cache_size,
This change completely loses the context that this is the kernel's/host
system's memory size. What is "data cache"? I think this is a bad
change. I know it's confusing, but the old description at least had
some basis in terms that are known to the user.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, Nov 5, 2018 at 12:50:01PM +0100, Peter Eisentraut wrote:
> On 16/10/2018 17:38, Bruce Momjian wrote:
> > diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
> > index 2317e8b..e471d7f 100644
> > --- a/src/backend/utils/misc/guc.c
> > +++ b/src/backend/utils/misc/guc.c
> > @@ -2987,10 +2987,9 @@ static struct config_int ConfigureNamesInt[] =
> >
> > {
> > {"effective_cache_size", PGC_USERSET, QUERY_TUNING_COST,
> > - gettext_noop("Sets the planner's assumption about the size of the disk cache."),
> > - gettext_noop("That is, the portion of the kernel's disk cache that "
> > - "will be used for PostgreSQL data files. This is measured in disk "
> > - "pages, which are normally 8 kB each."),
> > + gettext_noop("Sets the planner's assumption about the size of the data cache."),
> > + gettext_noop("That is, the size of the cache used for PostgreSQL data files. "
> > + "This is measured in disk pages, which are normally 8 kB each."),
> > GUC_UNIT_BLOCKS,
> > },
> > &effective_cache_size,
>
> This change completely loses the context that this is the kernel's/host
> system's memory size. What is "data cache"? I think this is a bad
> change. I know it's confusing, but the old description at least had
> some basis in terms that are known to the user.
Well, the change as outlined in the email is that effective_cache_size
is a combination of shared_buffers and kernel cache size, which I think
the docs now make clear. Do you have better wording for the GUC?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
On Mon, Nov 5, 2018 at 3:01 PM Bruce Momjian <bruce@momjian.us> wrote:
> > {"effective_cache_size", PGC_USERSET, QUERY_TUNING_COST,
> > - gettext_noop("Sets the planner's assumption about the size of the disk cache."),
> > - gettext_noop("That is, the portion of the kernel's disk cache that "
> > - "will be used for PostgreSQL data files. This is measured in disk "
> > - "pages, which are normally 8 kB each."),
> > + gettext_noop("Sets the planner's assumption about the size of the data cache."),
> > + gettext_noop("That is, the size of the cache used for PostgreSQL data files. "
> > + "This is measured in disk pages, which are normally 8 kB each."),
...
Well, the change as outlined in the email is that effective_cache_size
is a combination of shared_buffers and kernel cache size, which I think
the docs now make clear. Do you have better wording for the GUC?
Maybe it's better to use this phrase, "a combination of shared_buffers and kernel cache size"?
Or: "a combination of shared_buffers and estimated kernel cache size".
The phrase "the size of the cache" might be very confusing indeed – it sounds like it's about
some single cache, while it's about the combination of two.
Maybe it's also worth to mention that the fact that some pages might be cached twice –
in OS cache + in Postgres shared buffers – should be ignored, when choosing the proper value
for effective_cache_size? I think this would finally eliminate the possibility of confusion.
I see now, the docs chapter "19.7. Query Planning" has "some data might exist in both places"
– this is great, since confusion here is not uncommon. It's worth to propagate this change
everywhere where effective_cache_size is explained.
Nik
On Mon, Nov 5, 2018 at 03:23:03PM +0300, Nikolay Samokhvalov wrote:
>
>
> On Mon, Nov 5, 2018 at 3:01 PM Bruce Momjian <bruce@momjian.us> wrote:
>
> > > {"effective_cache_size", PGC_USERSET, QUERY_TUNING_COST,
> > > - gettext_noop("Sets the planner's assumption about
> the size of the disk cache."),
> > > - gettext_noop("That is, the portion of the kernel's
> disk cache that "
> > > - "will be used for
> PostgreSQL data files. This is measured in disk "
> > > - "pages, which are normally
> 8 kB each."),
> > > + gettext_noop("Sets the planner's assumption about
> the size of the data cache."),
> > > + gettext_noop("That is, the size of the cache used
> for PostgreSQL data files. "
> > > + "This is measured in disk
> pages, which are normally 8 kB each."),
>
> ...
>
> Well, the change as outlined in the email is that effective_cache_size
> is a combination of shared_buffers and kernel cache size, which I think
> the docs now make clear. Do you have better wording for the GUC?
>
>
> Maybe it's better to use this phrase, "a combination of shared_buffers and
> kernel cache size"?
> Or: "a combination of shared_buffers and estimated kernel cache size".
Well, here are the lines in guc.c:
gettext_noop("Sets the planner's assumption about the size of the data cache."),
gettext_noop("That is, the size of the cache used for PostgreSQL data files. "
"This is measured in disk pages, which are normally 8 kB each."),
The first line is already on the long side compared to other entries, as
is the second gettext_noop(). How long can we make it without being too
long? Is this really the place to explain this distinction? I thought
about these issues when I wrote this patch? You can come to a different
conclusion, but please consider these issues.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
On 2018-Nov-05, Bruce Momjian wrote:
> Well, here are the lines in guc.c:
>
> gettext_noop("Sets the planner's assumption about the size of the data cache."),
> gettext_noop("That is, the size of the cache used for PostgreSQL data files. "
> "This is measured in disk pages, which are normally 8 kB each."),
I suggest "the size of data caches", plural, in the first line (two
letters shorter, since I lost the article). And in the second line, use
"... the size of the combined caches used for Pg data files, including
both the kernel cache and shared buffers" -- a few words longer, which
seems worth it to me.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, Nov 5, 2018 at 02:01:12PM -0300, Alvaro Herrera wrote:
> On 2018-Nov-05, Bruce Momjian wrote:
>
> > Well, here are the lines in guc.c:
> >
> > gettext_noop("Sets the planner's assumption about the size of the data cache."),
> > gettext_noop("That is, the size of the cache used for PostgreSQL data files. "
> > "This is measured in disk pages, which are normally 8 kB each."),
>
> I suggest "the size of data caches", plural, in the first line (two
> letters shorter, since I lost the article). And in the second line, use
> "... the size of the combined caches used for Pg data files, including
> both the kernel cache and shared buffers" -- a few words longer, which
> seems worth it to me.
OK, I handled it slightly differently:
https://git.postgresql.org/pg/commitdiff/b43df566b372650a9b9e2a0dd9e695c1f16da14f
I think "the size of" anything plural is confusing so I said "the total
size of".
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
A follow-up on this:
Should documentation also mention that it does not make sense to set effective_cache_size < shared_buffers? And maybe it is worth prohibiting this or at least having a WARNING in logs?
Why: I see setups where with growing RAM and shared_buffers set to 25%, effective_cache_size remains untuned, at some point significantly below the value of shared_buffers.
On Tue, Nov 6, 2018 at 21:42 Bruce Momjian <bruce@momjian.us> wrote:
On Mon, Nov 5, 2018 at 02:01:12PM -0300, Alvaro Herrera wrote:
> On 2018-Nov-05, Bruce Momjian wrote:
>
> > Well, here are the lines in guc.c:
> >
> > gettext_noop("Sets the planner's assumption about the size of the data cache."),
> > gettext_noop("That is, the size of the cache used for PostgreSQL data files. "
> > "This is measured in disk pages, which are normally 8 kB each."),
>
> I suggest "the size of data caches", plural, in the first line (two
> letters shorter, since I lost the article). And in the second line, use
> "... the size of the combined caches used for Pg data files, including
> both the kernel cache and shared buffers" -- a few words longer, which
> seems worth it to me.
OK, I handled it slightly differently:
https://git.postgresql.org/pg/commitdiff/b43df566b372650a9b9e2a0dd9e695c1f16da14f
I think "the size of" anything plural is confusing so I said "the total
size of".
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +