Subject: effective_cache_size

From: PG Doc comments form <[email-masked]>
Date: Thu, 13 Sep 2018 15:14:08 +0000

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!

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 11 Oct 2018 13:48:49 -0400

> 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  <[email-masked]>        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 +

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 16 Oct 2018 11:38:20 -0400

> 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.

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 7554cba..b8e32d7 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -4147,7 +4147,8 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
         used. When setting this parameter you should consider both
         <productname>PostgreSQL</productname>'s shared buffers and the
         portion of the kernel's disk cache that will be used for
-        <productname>PostgreSQL</productname> data files.  Also, take
+        <productname>PostgreSQL</productname> data files, though some
+        data might exist in both places. 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
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,

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 2 Nov 2018 09:11:13 -0400

> backpatch.  I don't think I want to deal with all the "disk" mentions at

Patch applied through 9.3.

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 5 Nov 2018 12:50:01 +0100

> +						 "This is measured in disk pages, which are normally 8 kB each."),

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

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 5 Nov 2018 07:01:04 -0500

> 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?

---

From: Nikolay Samokhvalov <[email-masked]>
Date: Mon, 5 Nov 2018 15:23:03 +0300

On Mon, Nov 5, 2018 at 3:01 PM Bruce Momjian <[email-masked]> wrote:

> > > -                                            "will be used for
> PostgreSQL data files. This is measured in disk "
> > > -                                            "pages, which are
> normally 8 kB each."),
...

> 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

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 5 Nov 2018 11:12:31 -0500

> 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.

---

From: Alvaro Herrera <[email-masked]>
Date: Mon, 5 Nov 2018 14:01:12 -0300

>                          "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

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 6 Nov 2018 13:42:20 -0500

> both the kernel cache and shared buffers" -- a few words longer, which

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".

---

From: Nikolay Samokhvalov <[email-masked]>
Date: Mon, 4 Nov 2019 02:35:51 +0300

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.