Revert "Auto-tune effective_cache size to be 4x shared buffers"

tglsfdc · tglsfdc · commit a16d421ca4fc · 2014-05-08T20:49:38.000-04:00
This reverts commit ee1e566, as well as a remarkably large number of followup commits, which were mostly concerned with the fact that the implementation didn't work terribly well. It still doesn't: we probably need some rather basic work in the GUC infrastructure if we want to fully support GUCs whose default varies depending on the value of another GUC. Meanwhile, it also emerged that there wasn't really consensus in favor of the definition the patch tried to implement (ie, effective_cache_size should default to 4 times shared_buffers). So whack it all back to where it was. In a followup commit, I'll do what was recently agreed to, which is to simply change the default to a higher value.
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
@@ -3194,16 +3194,8 @@ include 'filename'
         memory allocated by <productname>PostgreSQL</productname>, 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.
-       </para>
-
-       <para>
-        If <varname>effective_cache_size</> is set to -1, which is the
-        default, the value is replaced by an automatically selected value,
-        currently four times the size of <xref linkend="guc-shared-buffers">.
-        For recommended settings of <varname>shared_buffers</>, this should
-        give reasonable results if this database cluster can use most of the
-        memory on the server.
+        the disk cache between queries.  The default is 128 megabytes
+        (<literal>128MB</>).
        </para>
       </listitem>
      </varlistentry>
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
@@ -71,7 +71,6 @@
 #ifdef _MSC_VER
 #include <float.h>				/* for _isnan */
 #endif
-#include <limits.h>
 #include <math.h>
 
 #include "access/htup_details.h"
@@ -88,7 +87,6 @@
 #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"
@@ -104,7 +102,7 @@ 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 = -1;	/* will get replaced */
+int			effective_cache_size = DEFAULT_EFFECTIVE_CACHE_SIZE;
 
 Cost		disable_cost = 1.0e10;
 
@@ -4093,50 +4091,3 @@ page_size(double tuples, int width)
 {
 	return ceil(relation_byte_size(tuples, width) / BLCKSZ);
 }
-
-/*
- * GUC check_hook for effective_cache_size
- */
-bool
-check_effective_cache_size(int *newval, void **extra, GucSource source)
-{
-	/*
-	 * -1 is the documented way of requesting auto-tune, but we also treat
-	 * zero as meaning that, since we don't consider zero a valid setting.
-	 */
-	if (*newval <= 0)
-	{
-		/*
-		 * Substitute the auto-tune value, being wary of overflow.
-		 */
-		if (NBuffers < INT_MAX / 4)
-			*newval = NBuffers * 4;
-		else
-			*newval = INT_MAX;
-	}
-
-	Assert(*newval > 0);
-
-	return true;
-}
-
-/*
- * Initialize effective_cache_size at the end of GUC startup, or when
- * a setting in postgresql.conf is removed.
- *
- * Note: check_effective_cache_size() will have been called when the boot_val
- * was installed, but we will not have known the final value of NBuffers at
- * that time, which is why this has to be called at the end of GUC startup.
- */
-void
-set_default_effective_cache_size(void)
-{
-	/*
-	 * We let check_effective_cache_size() compute the actual setting.  Note
-	 * that this call is a no-op if the user has supplied a setting (since
-	 * that will have a higher priority than PGC_S_DYNAMIC_DEFAULT).
-	 */
-	SetConfigOption("effective_cache_size", "-1",
-					PGC_POSTMASTER, PGC_S_DYNAMIC_DEFAULT);
-	Assert(effective_cache_size > 0);
-}
diff --git a/src/backend/utils/misc/guc-file.l b/src/backend/utils/misc/guc-file.l
@@ -298,7 +298,6 @@ ProcessConfigFile(GucContext context)
 	{
 		InitializeGUCOptionsFromEnvironment();
 		pg_timezone_abbrev_initialize();
-		set_default_effective_cache_size();
 		/* this selects SQL_ASCII in processes not connected to a database */
 		SetConfigOption("client_encoding", GetDatabaseEncodingName(),
 						PGC_BACKEND, PGC_S_DYNAMIC_DEFAULT);
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
@@ -2512,8 +2512,8 @@ static struct config_int ConfigureNamesInt[] =
 			GUC_UNIT_BLOCKS,
 		},
 		&effective_cache_size,
-		-1, -1, INT_MAX,
-		check_effective_cache_size, NULL, NULL
+		DEFAULT_EFFECTIVE_CACHE_SIZE, 1, INT_MAX,
+		NULL, NULL, NULL
 	},
 
 	{
@@ -4372,9 +4372,6 @@ SelectConfigFiles(const char *userDoption, const char *progname)
 	 */
 	pg_timezone_abbrev_initialize();
 
-	/* Also install the correct value for effective_cache_size */
-	set_default_effective_cache_size();
-
 	/*
 	 * Figure out where pg_hba.conf is, and make sure the path is absolute.
 	 */
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
@@ -283,7 +283,7 @@
 #cpu_tuple_cost = 0.01			# same scale as above
 #cpu_index_tuple_cost = 0.005		# same scale as above
 #cpu_operator_cost = 0.0025		# same scale as above
-#effective_cache_size = -1		# -1 selects auto-tuned default
+#effective_cache_size = 128MB
 
 # - Genetic Query Optimizer -
 
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
@@ -27,6 +27,8 @@
 #define DEFAULT_CPU_INDEX_TUPLE_COST 0.005
 #define DEFAULT_CPU_OPERATOR_COST  0.0025
 
+#define DEFAULT_EFFECTIVE_CACHE_SIZE  16384		/* measured in pages */
+
 typedef enum
 {
 	CONSTRAINT_EXCLUSION_OFF,	/* do not use c_e */
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
@@ -389,8 +389,4 @@ extern void assign_search_path(const char *newval, void *extra);
 extern bool check_wal_buffers(int *newval, void **extra, GucSource source);
 extern void assign_xlog_sync_method(int new_sync_method, void *extra);
 
-/* in optimizer/path/costsize.c */
-extern bool check_effective_cache_size(int *newval, void **extra, GucSource source);
-extern void set_default_effective_cache_size(void);
-
 #endif   /* GUC_H */
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
@@ -2759,7 +2759,6 @@ select * from tenk1 a join tenk1 b on
 --
 -- test placement of movable quals in a parameterized join tree
 --
-set effective_cache_size = '128MB';
 explain (costs off)
 select * from tenk1 t1 left join
   (tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2)
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
@@ -721,8 +721,6 @@ select * from tenk1 a join tenk1 b on
 -- test placement of movable quals in a parameterized join tree
 --
 
-set effective_cache_size = '128MB';
-
 explain (costs off)
 select * from tenk1 t1 left join
   (tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2)

Original file line number	Diff line number	Diff line change
`@@ -298,7 +298,6 @@ ProcessConfigFile(GucContext context)`
`298`	`298`	`{`
`299`	`299`	`InitializeGUCOptionsFromEnvironment();`
`300`	`300`	`pg_timezone_abbrev_initialize();`
`301`		`- set_default_effective_cache_size();`
`302`	`301`	`/* this selects SQL_ASCII in processes not connected to a database */`
`303`	`302`	`SetConfigOption("client_encoding", GetDatabaseEncodingName(),`
`304`	`303`	`PGC_BACKEND, PGC_S_DYNAMIC_DEFAULT);`
Original file line number	Diff line number	Diff line change
`@@ -27,6 +27,8 @@`
`27`	`27`	`#define DEFAULT_CPU_INDEX_TUPLE_COST 0.005`
`28`	`28`	`#define DEFAULT_CPU_OPERATOR_COST 0.0025`
`29`	`29`
	`30`	`+#define DEFAULT_EFFECTIVE_CACHE_SIZE 16384 /* measured in pages */`
	`31`	`+`
`30`	`32`	`typedef enum`
`31`	`33`	`{`
`32`	`34`	`CONSTRAINT_EXCLUSION_OFF, /* do not use c_e */`