Skip to content

Commit ee1e566

Browse files
bmomjianbmomjian
committed
Auto-tune effective_cache size to be 4x shared buffers
1 parent d29a031 commit ee1e566

File tree

8 files changed

+75
-11
lines changed

8 files changed

+75
-11
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 16 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -2758,7 +2758,7 @@ include 'filename'
27582758

27592759
<para>
27602760
Random access to mechanical disk storage is normally much more expensive
2761-
than four-times sequential access. However, a lower default is used
2761+
than four times sequential access. However, a lower default is used
27622762
(4.0) because the majority of random accesses to disk, such as indexed
27632763
reads, are assumed to be in cache. The default value can be thought of
27642764
as modeling random access as 40 times slower than sequential, while
@@ -2841,9 +2841,17 @@ include 'filename'
28412841
<listitem>
28422842
<para>
28432843
Sets the planner's assumption about the effective size of the
2844-
disk cache that is available to a single query. This is
2845-
factored into estimates of the cost of using an index; a
2846-
higher value makes it more likely index scans will be used, a
2844+
disk cache that is available to a single query. The default
2845+
setting of -1 selects a size equal to four times the size of <xref
2846+
linkend="guc-shared-buffers">, but not less than the size of one
2847+
shared buffer page, typically <literal>8kB</literal>. This value
2848+
can be set manually if the automatic choice is too large or too
2849+
small.
2850+
</para>
2851+
2852+
<para>
2853+
This value is factored into estimates of the cost of using an index;
2854+
a higher value makes it more likely index scans will be used, a
28472855
lower value makes it more likely sequential scans will be
28482856
used. When setting this parameter you should consider both
28492857
<productname>PostgreSQL</productname>'s shared buffers and the
@@ -2855,8 +2863,10 @@ include 'filename'
28552863
memory allocated by <productname>PostgreSQL</productname>, nor
28562864
does it reserve kernel disk cache; it is used only for estimation
28572865
purposes. The system also does not assume data remains in
2858-
the disk cache between queries. The default is 128 megabytes
2859-
(<literal>128MB</>).
2866+
the disk cache between queries. The auto-tuning
2867+
selected by the default setting of -1 should give reasonable
2868+
results if this database cluster is can utilize most of the memory
2869+
on this server.
28602870
</para>
28612871
</listitem>
28622872
</varlistentry>

‎src/backend/optimizer/path/costsize.c

Lines changed: 48 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -87,6 +87,7 @@
8787
#include "optimizer/planmain.h"
8888
#include "optimizer/restrictinfo.h"
8989
#include "parser/parsetree.h"
90+
#include "utils/guc.h"
9091
#include "utils/lsyscache.h"
9192
#include "utils/selfuncs.h"
9293
#include "utils/spccache.h"
@@ -95,14 +96,13 @@
9596

9697
#define LOG2(x) (log(x) / 0.693147180559945)
9798

98-
9999
double seq_page_cost = DEFAULT_SEQ_PAGE_COST;
100100
double random_page_cost = DEFAULT_RANDOM_PAGE_COST;
101101
double cpu_tuple_cost = DEFAULT_CPU_TUPLE_COST;
102102
double cpu_index_tuple_cost = DEFAULT_CPU_INDEX_TUPLE_COST;
103103
double cpu_operator_cost = DEFAULT_CPU_OPERATOR_COST;
104104

105-
int effective_cache_size = DEFAULT_EFFECTIVE_CACHE_SIZE;
105+
int effective_cache_size = -1;
106106

107107
Cost disable_cost = 1.0e10;
108108

@@ -456,6 +456,52 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count)
456456
path->path.total_cost = startup_cost + run_cost;
457457
}
458458

459+
void
460+
set_default_effective_cache_size(void)
461+
{
462+
/*
463+
* If the value of effective_cache_size is -1, use the preferred
464+
* auto-tune value.
465+
*/
466+
if (effective_cache_size == -1)
467+
{
468+
char buf[32];
469+
470+
snprintf(buf, sizeof(buf), "%d", NBuffers * DEFAULT_EFFECTIVE_CACHE_SIZE_MULTI);
471+
SetConfigOption("effective_cache_size", buf, PGC_POSTMASTER, PGC_S_OVERRIDE);
472+
}
473+
Assert(effective_cache_size > 0);
474+
}
475+
476+
/*
477+
* GUC check_hook for effective_cache_size
478+
*/
479+
bool
480+
check_effective_cache_size(int *newval, void **extra, GucSource source)
481+
{
482+
/*
483+
* -1 indicates a request for auto-tune.
484+
*/
485+
if (*newval == -1)
486+
{
487+
/*
488+
* If we haven't yet changed the boot_val default of -1, just let it
489+
* be. We'll fix it in index_pages_fetched
490+
*/
491+
if (effective_cache_size == -1)
492+
return true;
493+
494+
/* Otherwise, substitute the auto-tune value */
495+
*newval = NBuffers * DEFAULT_EFFECTIVE_CACHE_SIZE_MULTI;
496+
}
497+
498+
/* set minimum? */
499+
if (*newval < 1)
500+
*newval = 1;
501+
502+
return true;
503+
}
504+
459505
/*
460506
* index_pages_fetched
461507
* Estimate the number of pages actually fetched after accounting for

‎src/backend/postmaster/postmaster.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -118,6 +118,7 @@
118118
#include "utils/builtins.h"
119119
#include "utils/datetime.h"
120120
#include "utils/dynamic_loader.h"
121+
#include "utils/guc.h"
121122
#include "utils/memutils.h"
122123
#include "utils/ps_status.h"
123124
#include "utils/timeout.h"
@@ -4475,6 +4476,8 @@ SubPostmasterMain(int argc, char *argv[])
44754476
memset(&port, 0, sizeof(Port));
44764477
read_backend_variables(argv[2], &port);
44774478

4479+
set_default_effective_cache_size();
4480+
44784481
/*
44794482
* Set reference point for stack-depth checking
44804483
*/

‎src/backend/utils/misc/guc.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2410,8 +2410,8 @@ static struct config_int ConfigureNamesInt[] =
24102410
GUC_UNIT_BLOCKS,
24112411
},
24122412
&effective_cache_size,
2413-
DEFAULT_EFFECTIVE_CACHE_SIZE, 1, INT_MAX,
2414-
NULL, NULL, NULL
2413+
-1, -1, INT_MAX,
2414+
check_effective_cache_size, NULL, NULL
24152415
},
24162416

24172417
{

‎src/include/optimizer/cost.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -27,7 +27,7 @@
2727
#define DEFAULT_CPU_INDEX_TUPLE_COST 0.005
2828
#define DEFAULT_CPU_OPERATOR_COST 0.0025
2929

30-
#define DEFAULT_EFFECTIVE_CACHE_SIZE 16384 /* measured in pages */
30+
#define DEFAULT_EFFECTIVE_CACHE_SIZE_MULTI 4
3131

3232
typedef enum
3333
{

‎src/include/utils/guc.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -386,6 +386,8 @@ extern void assign_search_path(const char *newval, void *extra);
386386

387387
/* in access/transam/xlog.c */
388388
extern bool check_wal_buffers(int *newval, void **extra, GucSource source);
389+
extern bool check_effective_cache_size(int *newval, void **extra, GucSource source);
390+
extern void set_default_effective_cache_size(void);
389391
extern void assign_xlog_sync_method(int new_sync_method, void *extra);
390392

391393
#endif /* GUC_H */

‎src/test/regress/expected/join.out

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2713,6 +2713,7 @@ where thousand = (q1 + q2);
27132713
--
27142714
-- test placement of movable quals in a parameterized join tree
27152715
--
2716+
set effective_cache_size = '128MB';
27162717
explain (costs off)
27172718
select * from tenk1 t1 left join
27182719
(tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2)

‎src/test/regress/sql/join.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -711,6 +711,8 @@ where thousand = (q1 + q2);
711711
-- test placement of movable quals in a parameterized join tree
712712
--
713713

714+
set effective_cache_size = '128MB';
715+
714716
explain (costs off)
715717
select * from tenk1 t1 left join
716718
(tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2)

0 commit comments

Comments
 (0)