Auto-tune effective_cache size to be 4x shared buffers

3 files changed, 53 insertions, 4 deletions

diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 0caac75ac5a..82c66f76caf 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -87,6 +87,7 @@
 #include "optimizer/planmain.h"
 #include "optimizer/restrictinfo.h"
 #include "parser/parsetree.h"
+#include "utils/guc.h"
 #include "utils/lsyscache.h"
 #include "utils/selfuncs.h"
 #include "utils/spccache.h"
@@ -95,14 +96,13 @@
 
 #define LOG2(x)  (log(x) / 0.693147180559945)
 
-
 double		seq_page_cost = DEFAULT_SEQ_PAGE_COST;
 double		random_page_cost = DEFAULT_RANDOM_PAGE_COST;
 double		cpu_tuple_cost = DEFAULT_CPU_TUPLE_COST;
 double		cpu_index_tuple_cost = DEFAULT_CPU_INDEX_TUPLE_COST;
 double		cpu_operator_cost = DEFAULT_CPU_OPERATOR_COST;
 
-int			effective_cache_size = DEFAULT_EFFECTIVE_CACHE_SIZE;
+int			effective_cache_size = -1;
 
 Cost		disable_cost = 1.0e10;
 
@@ -456,6 +456,52 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count)
 	path->path.total_cost = startup_cost + run_cost;
 }
 
+void
+set_default_effective_cache_size(void)
+{
+	/*
+	 * If the value of effective_cache_size is -1, use the preferred
+	 * auto-tune value.
+	 */
+	if (effective_cache_size == -1)
+	{
+		char		buf[32];
+
+		snprintf(buf, sizeof(buf), "%d", NBuffers * DEFAULT_EFFECTIVE_CACHE_SIZE_MULTI);
+		SetConfigOption("effective_cache_size", buf, PGC_POSTMASTER, PGC_S_OVERRIDE);
+	}
+	Assert(effective_cache_size > 0);
+}
+
+/*
+ * GUC check_hook for effective_cache_size
+ */
+bool
+check_effective_cache_size(int *newval, void **extra, GucSource source)
+{
+	/*
+	 * -1 indicates a request for auto-tune.
+	 */
+	if (*newval == -1)
+	{
+		/*
+		 * If we haven't yet changed the boot_val default of -1, just let it
+		 * be.	We'll fix it in index_pages_fetched
+		 */
+		if (effective_cache_size == -1)
+			return true;
+
+		/* Otherwise, substitute the auto-tune value */
+		*newval = NBuffers * DEFAULT_EFFECTIVE_CACHE_SIZE_MULTI;
+	}
+
+	/* set minimum? */
+	if (*newval < 1)
+		*newval = 1;
+
+	return true;
+}
+
 /*
  * index_pages_fetched
  *	  Estimate the number of pages actually fetched after accounting for
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index 9f721b76515..c4b54d12cb2 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -118,6 +118,7 @@
 #include "utils/builtins.h"
 #include "utils/datetime.h"
 #include "utils/dynamic_loader.h"
+#include "utils/guc.h"
 #include "utils/memutils.h"
 #include "utils/ps_status.h"
 #include "utils/timeout.h"
@@ -4475,6 +4476,8 @@ SubPostmasterMain(int argc, char *argv[])
 	memset(&port, 0, sizeof(Port));
 	read_backend_variables(argv[2], &port);
 
+	set_default_effective_cache_size();
+
 	/*
 	 * Set reference point for stack-depth checking
 	 */
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index d9a06b4d881..833f34b1ee2 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2410,8 +2410,8 @@ static struct config_int ConfigureNamesInt[] =
 			GUC_UNIT_BLOCKS,
 		},
 		&effective_cache_size,
-		DEFAULT_EFFECTIVE_CACHE_SIZE, 1, INT_MAX,
-		NULL, NULL, NULL
+		-1, -1, INT_MAX,
+		check_effective_cache_size, NULL, NULL
 	},
 
 	{