8000 Add GUC and storage parameter to set the maximum size of GIN pending … · postgres/postgres@a1b395b · GitHub
[go: up one dir, main page]
Skip to content

Commit a1b395b

Browse files
MasaoFujiiMasaoFujii
committed
Add GUC and storage parameter to set the maximum size of GIN pending list.
Previously the maximum size of GIN pending list was controlled only by work_mem. But the reasonable value of work_mem and the reasonable size of the list are basically not the same, so it was not appropriate to control both of them by only one GUC, i.e., work_mem. This commit separates new GUC, pending_list_cleanup_size, from work_mem to allow users to control only the size of the list. Also this commit adds pending_list_cleanup_size as new storage parameter to allow users to specify the size of the list per index. This is useful, for example, when users want to increase the size of the list only for the GIN index which can be updated heavily, and decrease it otherwise. Reviewed by Etsuro Fujita.
1 parent ae667f7 commit a1b395b

File tree

16 files changed

+125
-31
lines changed

16 files changed

+125
-31
lines changed

‎doc/src/sgml/config.sgml‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5911,6 +5911,27 @@ SET XML OPTION { DOCUMENT | CONTENT };
59115911
</listitem>
59125912
</varlistentry>
59135913

5914+
<varlistentry id="guc-pending-list-cleanup-size" xreflabel="pending_list_cleanup_size">
5915+
<term><varname>pending_list_cleanup_size</varname> (<type>integer</type>)
5916+
<indexterm>
5917+
<primary><varname>pending_list_cleanup_size</> configuration parameter</primary>
5918+
</indexterm>
5919+
</term>
5920+
<listitem>
5921+
<para>
5922+
Sets the maximum size of the GIN pending list which is used
5923+
when <literal>fastupdate</> is enabled. If the list grows
5924+
larger than this maximum size, it is cleaned up by moving
5925+
the entries in it to the main GIN data structure in bulk.
5926+
The default is four megabytes (<literal>4MB</>). This setting
5927+
can be overridden for individual GIN indexes by changing
5928+
storage parameters.
5929+
See <xref linkend="gin-fast-update"> and <xref linkend="gin-tips">
5930+
for more information.
5931+
</para>
5932+
</listitem>
5933+
</varlistentry>
5934+
59145935
</variablelist>
59155936
</sect2>
59165937
<sect2 id="runtime-config-client-format">

‎doc/src/sgml/gin.sgml‎

Lines changed: 19 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -728,8 +728,8 @@
728728
from the indexed item). As of <productname>PostgreSQL</productname> 8.4,
729729
<acronym>GIN</> is capable of postponing much of this work by inserting
730730
new tuples into a temporary, unsorted list of pending entries.
731-
When the table is vacuumed, or if the pending list becomes too large
732-
(larger than <xref linkend="guc-work-mem">), the entries are moved to the
731+
When the table is vacuumed, or if the pending list becomes larger than
732+
<xref linkend="guc-pending-list-cleanup-size">, the entries are moved to the
733733
main <acronym>GIN</acronym> data structure using the same bulk insert
734734
techniques used during initial index creation. This greatly improves
735735
<acronym>GIN</acronym> index update speed, even counting the additional
@@ -750,7 +750,7 @@
750750
<para>
751751
If consistent response time is more important than update speed,
752 A3E2 752
use of pending entries can be disabled by turning off the
753-
<literal>FASTUPDATE</literal> storage parameter for a
753+
<literal>fastupdate</literal> storage parameter for a
754754
<acronym>GIN</acronym> index. See <xref linkend="sql-createindex">
755755
for details.
756756
</para>
@@ -812,18 +812,26 @@
812812
</varlistentry>
813813

814814
<varlistentry>
815-
<term><xref linkend="guc-work-mem"></term>
815+
<term><xref linkend="guc-pending-list-cleanup-size"></term>
816816
<listitem>
817817
<para>
818818
During a series of insertions into an existing <acronym>GIN</acronym>
819-
index that has <literal>FASTUPDATE</> enabled, the system will clean up
819+
index that has <literal>fastupdate</> enabled, the system will clean up
820820
the pending-entry list whenever the list grows larger than
821-
<varname>work_mem</>. To avoid fluctuations in observed response time,
822-
it's desirable to have pending-list cleanup occur in the background
823-
(i.e., via autovacuum). Foreground cleanup operations can be avoided by
824-
increasing <varname>work_mem</> or making autovacuum more aggressive.
825-
However, enlarging <varname>work_mem</> means that if a foreground
826-
cleanup does occur, it will take even longer.
821+
<varname>pending_list_cleanup_size</>. To avoid fluctuations in observed
822+
response time, it's desirable to have pending-list cleanup occur in the
823+
background (i.e., via autovacuum). Foreground cleanup operations
824+
can be avoided by increasing <varname>pending_list_cleanup_size</>
825+
or making autovacuum more aggressive.
826+
However, enlarging the threshold of the cleanup operation means that
827+
if a foreground cleanup does occur, it will take even longer.
828+
</para>
829+
<para>
830+
<varname>pending_list_cleanup_size</> can be overridden for individual
831+
GIN indexes by changing storage parameters, and which allows each
832+
GIN index to have its own cleanup threshold.
833+
For example, it's possible to increase the threshold only for the GIN
834+
index which can be updated heavily, and decrease it otherwise.
827835
</para>
828836
</listitem>
829837
</varlistentry>

‎doc/src/sgml/gist.sgml‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -861,7 +861,7 @@ my_distance(PG_FUNCTION_ARGS)
861861
<para>
862862
By default, a GiST index build switches to the buffering method when the
863863
index size reaches <xref linkend="guc-effective-cache-size">. It can
864-
be manually turned on or off by the <literal>BUFFERING</literal> parameter
864+
be manually turned on or off by the <literal>buffering</literal> parameter
865865
to the CREATE INDEX command. The default behavior is good for most cases,
866866
but turning buffering off might speed up the build somewhat if the input
867867
data is ordered.

‎doc/src/sgml/ref/cluster.sgml‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -46,7 +46,7 @@ CLUSTER [VERBOSE]
4646
not clustered. That is, no attempt is made to store new or
4747
updated rows according to their index order. (If one wishes, one can
4848
periodically recluster by issuing the command again. Also, setting
49-
the table's <literal>FILLFACTOR</literal> storage parameter to less than
49+
the table's <literal>fillfactor</literal> storage parameter to less than
5050
100% can aid in preserving cluster ordering during updates, since updated
5151
rows are kept on the same page if enough space is available there.)
5252
</para>

‎doc/src/sgml/ref/create_index.sgml‎

Lines changed: 16 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -300,7 +300,7 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
300300

301301
<variablelist>
302302
<varlistentry>
303-
<term><literal>FILLFACTOR</></term>
303+
<term><literal>fillfactor</></term>
304304
<listitem>
305305
<para>
306306
The fillfactor for an index is a percentage that determines how full
@@ -327,7 +327,7 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
327327

328328
<variablelist>
329329
<varlistentry>
330-
<term><literal>BUFFERING</></term>
330+
<term><literal>buffering</></term>
331331
<listitem>
332332
<para>
333333
Determines whether the buffering build technique described in
@@ -341,12 +341,12 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
341341
</variablelist>
342342

343343
<para>
344-
GIN indexes accept a different parameter:
344+
GIN indexes accept different parameters:
345345
</para>
346346

347347
<variablelist>
348348
<varlistentry>
349-
<term><literal>FASTUPDATE</></term>
349+
<term><literal>fastupdate</></term>
350350
<listitem>
351351
<para>
352352
This setting controls usage of the fast update technique described in
@@ -359,7 +359,7 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
359359

360360
<note>
361361
<para>
362-
Turning <literal>FASTUPDATE</> off via <command>ALTER INDEX</> prevents
362+
Turning <literal>fastupdate</> off via <command>ALTER INDEX</> prevents
363363
future insertions from going into the list of pending index entries,
364364
but does not in itself flush previous entries. You might want to
365365
<command>VACUUM</> the table afterward to ensure the pending list is
@@ -369,6 +369,17 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
369369
</listitem>
370370
</varlistentry>
371371
</variablelist>
372+
<variablelist>
373+
<varlistentry>
374+
<term><literal>pending_list_cleanup_size</></term>
375+
<listitem>
376+
<para>
377+
Custom <xref linkend="guc-pending-list-cleanup-size"> parameter.
67DE 378+
This value is specified in kilobytes.
379+
</para>
380+
</listitem>
381+
</varlistentry>
382+
</variablelist>
372383
</refsect2>
373384

374385
<refsect2 id="SQL-CREATEINDEX-CONCURRENTLY">

‎src/backend/access/common/reloptions.c‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -216,6 +216,14 @@ static relopt_int intRelOpts[] =
216216
RELOPT_KIND_BRIN
217217
}, 128, 1, 131072
218218
},
219+
{
220+
{
221+
"pending_list_cleanup_size",
222+
"Maximum size of the pending list for this GIN index, in kilobytes.",
223+
RELOPT_KIND_GIN
224+
},
225+
-1, 64, MAX_KILOBYTES
226+
},
219227

220228
/* list terminator */
221229
{{NULL}}

‎src/backend/access/gin/ginfast.c‎

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,8 @@
2525
#include "utils/memutils.h"
2626
#include "utils/rel.h"
2727

28+
/* GUC parameter */
29+
int pending_list_cleanup_size = 0;
2830

2931
#define GIN_PAGE_FREESIZE \
3032
( BLCKSZ - MAXALIGN(SizeOfPageHeaderData) - MAXALIGN(sizeof(GinPageOpaqueData)) )
@@ -228,6 +230,7 @@ ginHeapTupleFastInsert(GinState *ginstate, GinTupleCollector *collector)
228230
ginxlogUpdateMeta data;
229231
bool separateList = false;
230232
bool needCleanup = false;
233+
int cleanupSize;
231234

232235
if (collector->ntuples == 0)
233236
return;
@@ -422,11 +425,13 @@ ginHeapTupleFastInsert(GinState *ginstate, GinTupleCollector *collector)
422425
* ginInsertCleanup could take significant amount of time, so we prefer to
423426
* call it when it can do all the work in a single collection cycle. In
424427
* non-vacuum mode, it shouldn't require maintenance_work_mem, so fire it
425-
* while pending list is still small enough to fit into work_mem.
428+
* while pending list is still small enough to fit into
429+
* pending_list_cleanup_size.
426430
*
427431
* ginInsertCleanup() should not be called inside our CRIT_SECTION.
428432
*/
429-
if (metadata->nPendingPages * GIN_PAGE_FREESIZE > work_mem * 1024L)
433+
cleanupSize = GinGetPendingListCleanupSize(index);
434+
if (metadata->nPendingPages * GIN_PAGE_FREESIZE > cleanupSize * 1024L)
430435
needCleanup = true;
431436

432437
UnlockReleaseBuffer(metabuffer);

‎src/backend/access/gin/ginutil.c‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -525,7 +525,9 @@ ginoptions(PG_FUNCTION_ARGS)
525525
GinOptions *rdopts;
526526
int numoptions;
527527
static const relopt_parse_elt tab[] = {
528-
{"fastupdate", RELOPT_TYPE_BOOL, offsetof(GinOptions, useFastUpdate)}
528+
{"fastupdate", RELOPT_TYPE_BOOL, offsetof(GinOptions, useFastUpdate)},
529+
{"pending_list_cleanup_size", RELOPT_TYPE_INT, offsetof(GinOptions,
530+
pendingListCleanupSize)}
529531
};
530532

531533
options = parseRelOptions(reloptions, validate, RELOPT_KIND_GIN,

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

Lines changed: 11 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -96,14 +96,6 @@
9696
#define CONFIG_EXEC_PARAMS_NEW "global/config_exec_params.new"
9797
#endif
9898

99-
/* upper limit for GUC variables measured in kilobytes of memory */
100-
/* note that various places assume the byte size fits in a "long" variable */
101-
#if SIZEOF_SIZE_T > 4 && SIZEOF_LONG > 4
102-
#define MAX_KILOBYTES INT_MAX
103-
#else
104-
#define MAX_KILOBYTES (INT_MAX / 1024)
105-
#endif
106-
10799
#define KB_PER_MB (1024)
108100
#define KB_PER_GB (1024*1024)
109101
#define KB_PER_TB (1024*1024*1024)
@@ -2550,6 +2542,17 @@ static struct config_int ConfigureNamesInt[] =
25502542
NULL, NULL, NULL
25512543
},
25522544

2545+
{
2546+
{"pending_list_cleanup_size", PGC_USERSET, CLIENT_CONN_STATEMENT,
2547+
gettext_noop("Sets the maximum size of the pending list for GIN index."),
2548+
NULL,
2549+
GUC_UNIT_KB
2550+
},
2551+
&pending_list_cleanup_size,
2552+
4096, 64, MAX_KILOBYTES,
2553+
NULL, NULL, NULL
2554+
},
2555+
25532556
/* End-of-list marker */
25542557
{
25552558
{NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL, NULL

‎src/backend/utils/misc/postgresql.conf.sample‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -519,6 +519,7 @@
519519
#bytea_output = 'hex' # hex, escape
520520
#xmlbinary = 'base64'
521521
#xmloption = 'content'
522+
#pending_list_cleanup_size = 4MB
522523

523524
# - Locale and Formatting -
524525

0 commit comments

Comments
 (0)
0