On Wed, Sep 24, 2014 at 11:10 AM, Etsuro Fujita <fujita.ets...@lab.ntt.co.jp> wrote: > (2014/09/13 2:42), Fujii Masao wrote: >> >> On Wed, Sep 10, 2014 at 10:37 PM, Alvaro Herrera >> <alvhe...@2ndquadrant.com> wrote: >>> >>> Fujii Masao wrote: >>>> >>>> On Wed, Sep 10, 2014 at 12:15 PM, Etsuro Fujita >>>> <fujita.ets...@lab.ntt.co.jp> wrote: >>> >>> >>>>> PENDING_LIST_CLEANUP_SIZE and work_mem, for this setting. >>>>> Wouldn't it be easy-to-use to have only one parameter, >>>>> PENDING_LIST_CLEANUP_SIZE? How about setting PENDING_LIST_CLEANUP_SIZE >>>>> to >>>>> work_mem as the default value when running the CREATE INDEX command? >>>> >>>> >>>> That's an idea. But there might be some users who want to change >>>> the cleanup size per session like they can do by setting work_mem, >>>> and your idea would prevent them from doing that... >>>> >>>> So what about introduing pending_list_cleanup_size also as GUC? >>>> That is, users can set the threshold by using either the reloption or >>>> GUC. >>> >>> >>> Yes, I think having both a GUC and a reloption makes sense -- the GUC >>> applies to all indexes, and can be tweaked for individual indexes using >>> the reloption. >> >> >> Agreed. >> >>> I'm not sure about the idea of being able to change it per session, >>> though. Do you mean that you would like insert processes use a very >>> large value so that they can just append new values to the pending list, >>> and have vacuum use a small value so that it cleans up as soon as it >>> runs? Two things: 1. we could have an "autovacuum_" reloption which >>> only changes what autovacuum does; 2. we could have autovacuum run >>> index cleanup actions separately from actual vacuuming. >> >> >> Yes, I was thinking something like that. But if autovacuum >> has already been able to handle that, it's nice. Anyway, >> as you pointed out, it's better to have both GUC and reloption >> for the cleanup size of pending list. > > > OK, I'd vote for your idea of having both the GUC and the reloption. So, I > think the patch needs to be updated. Fujii-san, what plan do you have about > the patch?
Please see the attached patch. In this patch, I introduced the GUC parameter, pending_list_cleanup_size. I chose 4MB as the default value of the parameter. But do you have any better idea about that default value? BTW, I moved the CommitFest entry of this patch to next CF 2014-10. Regards, -- Fujii Masao
*** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *************** *** 5907,5912 **** SET XML OPTION { DOCUMENT | CONTENT }; --- 5907,5933 ---- </listitem> </varlistentry> + <varlistentry id="guc-pending-list-cleanup-size" xreflabel="pending_list_cleanup_size"> + <term><varname>pending_list_cleanup_size</varname> (<type>integer</type>) + <indexterm> + <primary><varname>pending_list_cleanup_size</> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the maximum size of the GIN pending list which is used + when <literal>FASTUPDATE</> is enabled. If the list grows + larger than this maximum size, it is cleaned up by moving + the entries in it to the main GIN data structure in bulk. + The default is four megabytes (<literal>4MB</>). This setting + can be overridden for individual GIN indexes by changing + storage parameters. + See <xref linkend="gin-fast-update"> and <xref linkend="gin-tips"> + for more information. + </para> + </listitem> + </varlistentry> + </variablelist> </sect2> <sect2 id="runtime-config-client-format"> *** a/doc/src/sgml/gin.sgml --- b/doc/src/sgml/gin.sgml *************** *** 728,735 **** from the indexed item). As of <productname>PostgreSQL</productname> 8.4, <acronym>GIN</> is capable of postponing much of this work by inserting new tuples into a temporary, unsorted list of pending entries. ! When the table is vacuumed, or if the pending list becomes too large ! (larger than <xref linkend="guc-work-mem">), the entries are moved to the main <acronym>GIN</acronym> data structure using the same bulk insert techniques used during initial index creation. This greatly improves <acronym>GIN</acronym> index update speed, even counting the additional --- 728,735 ---- from the indexed item). As of <productname>PostgreSQL</productname> 8.4, <acronym>GIN</> is capable of postponing much of this work by inserting new tuples into a temporary, unsorted list of pending entries. ! When the table is vacuumed, or if the pending list becomes larger than ! <xref linkend="guc-pending-list-cleanup-size">, the entries are moved to the main <acronym>GIN</acronym> data structure using the same bulk insert techniques used during initial index creation. This greatly improves <acronym>GIN</acronym> index update speed, even counting the additional *************** *** 812,829 **** </varlistentry> <varlistentry> ! <term><xref linkend="guc-work-mem"></term> <listitem> <para> During a series of insertions into an existing <acronym>GIN</acronym> index that has <literal>FASTUPDATE</> enabled, the system will clean up the pending-entry list whenever the list grows larger than ! <varname>work_mem</>. To avoid fluctuations in observed response time, ! it's desirable to have pending-list cleanup occur in the background ! (i.e., via autovacuum). Foreground cleanup operations can be avoided by ! increasing <varname>work_mem</> or making autovacuum more aggressive. ! However, enlarging <varname>work_mem</> means that if a foreground ! cleanup does occur, it will take even longer. </para> </listitem> </varlistentry> --- 812,837 ---- </varlistentry> <varlistentry> ! <term><xref linkend="guc-pending-list-cleanup-size"></term> <listitem> <para> During a series of insertions into an existing <acronym>GIN</acronym> index that has <literal>FASTUPDATE</> enabled, the system will clean up the pending-entry list whenever the list grows larger than ! <literal>pending_list_cleanup_size</>. To avoid fluctuations in observed ! response time, it's desirable to have pending-list cleanup occur in the ! background (i.e., via autovacuum). Foreground cleanup operations ! can be avoided by increasing <literal>pending_list_cleanup_size</> ! or making autovacuum more aggressive. ! However, enlarging the threshold of the cleanup operation means that ! if a foreground cleanup does occur, it will take even longer. ! </para> ! <para> ! <literal>pending_list_cleanup_size</> can be overridden for individual ! GIN indexes by changing storage parameters, and which allows each ! GIN index to have its own cleanup threshold. ! For example, it's possible to increase the threshold only for the GIN ! index which can be updated heavily, and decrease it otherwise. </para> </listitem> </varlistentry> *** a/doc/src/sgml/ref/create_index.sgml --- b/doc/src/sgml/ref/create_index.sgml *************** *** 356,361 **** CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ <replaceable class="parameter">name</ --- 356,372 ---- </listitem> </varlistentry> </variablelist> + <variablelist> + <varlistentry> + <term><literal>PENDING_LIST_CLEANUP_SIZE</></term> + <listitem> + <para> + Custom <xref linkend="guc-pending-list-cleanup-size"> parameter. + This value is specified in kilobytes. + </para> + </listitem> + </varlistentry> + </variablelist> </refsect2> <refsect2 id="SQL-CREATEINDEX-CONCURRENTLY"> *** a/src/backend/access/common/reloptions.c --- b/src/backend/access/common/reloptions.c *************** *** 209,214 **** static relopt_int intRelOpts[] = --- 209,222 ---- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST }, -1, 0, 2000000000 }, + { + { + "pending_list_cleanup_size", + "Maximum size of the pending list for this GIN index, in kilobytes.", + RELOPT_KIND_GIN + }, + -1, 0, MAX_KILOBYTES + }, /* list terminator */ {{NULL}} *** a/src/backend/access/gin/ginfast.c --- b/src/backend/access/gin/ginfast.c *************** *** 24,29 **** --- 24,31 ---- #include "utils/memutils.h" #include "utils/rel.h" + /* GUC parameter */ + int pending_list_cleanup_size = 0; #define GIN_PAGE_FREESIZE \ ( BLCKSZ - MAXALIGN(SizeOfPageHeaderData) - MAXALIGN(sizeof(GinPageOpaqueData)) ) *************** *** 227,232 **** ginHeapTupleFastInsert(GinState *ginstate, GinTupleCollector *collector) --- 229,235 ---- ginxlogUpdateMeta data; bool separateList = false; bool needCleanup = false; + int cleanupSize; if (collector->ntuples == 0) return; *************** *** 421,431 **** ginHeapTupleFastInsert(GinState *ginstate, GinTupleCollector *collector) * ginInsertCleanup could take significant amount of time, so we prefer to * call it when it can do all the work in a single collection cycle. In * non-vacuum mode, it shouldn't require maintenance_work_mem, so fire it ! * while pending list is still small enough to fit into work_mem. * * ginInsertCleanup() should not be called inside our CRIT_SECTION. */ ! if (metadata->nPendingPages * GIN_PAGE_FREESIZE > work_mem * 1024L) needCleanup = true; UnlockReleaseBuffer(metabuffer); --- 424,436 ---- * ginInsertCleanup could take significant amount of time, so we prefer to * call it when it can do all the work in a single collection cycle. In * non-vacuum mode, it shouldn't require maintenance_work_mem, so fire it ! * while pending list is still small enough to fit into ! * pending_list_cleanup_size. * * ginInsertCleanup() should not be called inside our CRIT_SECTION. */ ! cleanupSize = GinGetPendingListCleanupSize(index); ! if (metadata->nPendingPages * GIN_PAGE_FREESIZE > cleanupSize * 1024L) needCleanup = true; UnlockReleaseBuffer(metabuffer); *** a/src/backend/access/gin/ginutil.c --- b/src/backend/access/gin/ginutil.c *************** *** 524,530 **** ginoptions(PG_FUNCTION_ARGS) GinOptions *rdopts; int numoptions; static const relopt_parse_elt tab[] = { ! {"fastupdate", RELOPT_TYPE_BOOL, offsetof(GinOptions, useFastUpdate)} }; options = parseRelOptions(reloptions, validate, RELOPT_KIND_GIN, --- 524,532 ---- GinOptions *rdopts; int numoptions; static const relopt_parse_elt tab[] = { ! {"fastupdate", RELOPT_TYPE_BOOL, offsetof(GinOptions, useFastUpdate)}, ! {"pending_list_cleanup_size", RELOPT_TYPE_INT, offsetof(GinOptions, ! pendingListCleanupSize)} }; options = parseRelOptions(reloptions, validate, RELOPT_KIND_GIN, *** a/src/backend/utils/misc/guc.c --- b/src/backend/utils/misc/guc.c *************** *** 96,109 **** #define CONFIG_EXEC_PARAMS_NEW "global/config_exec_params.new" #endif - /* upper limit for GUC variables measured in kilobytes of memory */ - /* note that various places assume the byte size fits in a "long" variable */ - #if SIZEOF_SIZE_T > 4 && SIZEOF_LONG > 4 - #define MAX_KILOBYTES INT_MAX - #else - #define MAX_KILOBYTES (INT_MAX / 1024) - #endif - #define KB_PER_MB (1024) #define KB_PER_GB (1024*1024) #define KB_PER_TB (1024*1024*1024) --- 96,101 ---- *************** *** 2567,2572 **** static struct config_int ConfigureNamesInt[] = --- 2559,2575 ---- NULL, NULL, NULL }, + { + {"pending_list_cleanup_size", PGC_USERSET, CLIENT_CONN_STATEMENT, + gettext_noop("Sets the maximum size of the pending list for GIN index."), + NULL, + GUC_UNIT_KB + }, + &pending_list_cleanup_size, + 4096, 0, MAX_KILOBYTES, + NULL, NULL, NULL + }, + /* End-of-list marker */ { {NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL, NULL *** a/src/backend/utils/misc/postgresql.conf.sample --- b/src/backend/utils/misc/postgresql.conf.sample *************** *** 519,524 **** --- 519,525 ---- #bytea_output = 'hex' # hex, escape #xmlbinary = 'base64' #xmloption = 'content' + #pending_list_cleanup_size = 4MB # - Locale and Formatting - *** a/src/bin/psql/tab-complete.c --- b/src/bin/psql/tab-complete.c *************** *** 1172,1178 **** psql_completion(const char *text, int start, int end) pg_strcasecmp(prev_wd, "(") == 0) { static const char *const list_INDEXOPTIONS[] = ! {"fillfactor", "fastupdate", NULL}; COMPLETE_WITH_LIST(list_INDEXOPTIONS); } --- 1172,1178 ---- pg_strcasecmp(prev_wd, "(") == 0) { static const char *const list_INDEXOPTIONS[] = ! {"fillfactor", "fastupdate", "pending_list_cleanup_size", NULL}; COMPLETE_WITH_LIST(list_INDEXOPTIONS); } *** a/src/include/access/gin.h --- b/src/include/access/gin.h *************** *** 66,71 **** typedef char GinTernaryValue; --- 66,72 ---- /* GUC parameter */ extern PGDLLIMPORT int GinFuzzySearchLimit; + extern int pending_list_cleanup_size; /* ginutil.c */ extern void ginGetStats(Relation index, GinStatsData *stats); *** a/src/include/access/gin_private.h --- b/src/include/access/gin_private.h *************** *** 314,325 **** typedef struct GinOptions --- 314,331 ---- { int32 vl_len_; /* varlena header (do not touch directly!) */ bool useFastUpdate; /* use fast updates? */ + int pendingListCleanupSize; /* maximum size of pending list */ } GinOptions; #define GIN_DEFAULT_USE_FASTUPDATE true #define GinGetUseFastUpdate(relation) \ ((relation)->rd_options ? \ ((GinOptions *) (relation)->rd_options)->useFastUpdate : GIN_DEFAULT_USE_FASTUPDATE) + #define GinGetPendingListCleanupSize(relation) \ + ((relation)->rd_options && \ + ((GinOptions *) (relation)->rd_options)->pendingListCleanupSize != -1 ? \ + ((GinOptions *) (relation)->rd_options)->pendingListCleanupSize : \ + pending_list_cleanup_size) /* Macros for buffer lock/unlock operations */ *** a/src/include/utils/guc.h --- b/src/include/utils/guc.h *************** *** 18,23 **** --- 18,31 ---- #include "utils/array.h" + /* upper limit for GUC variables measured in kilobytes of memory */ + /* note that various places assume the byte size fits in a "long" variable */ + #if SIZEOF_SIZE_T > 4 && SIZEOF_LONG > 4 + #define MAX_KILOBYTES INT_MAX + #else + #define MAX_KILOBYTES (INT_MAX / 1024) + #endif + /* * Certain options can only be set at certain times. The rules are * like this: *** a/src/test/regress/expected/create_index.out --- b/src/test/regress/expected/create_index.out *************** *** 2235,2240 **** SELECT COUNT(*) FROM array_gin_test WHERE a @> '{2}'; --- 2235,2253 ---- DROP TABLE array_gin_test; -- + -- Test GIN index's reloptions + -- + CREATE INDEX gin_relopts_test ON array_index_op_test USING gin (i) + WITH (FASTUPDATE=on, PENDING_LIST_CLEANUP_SIZE=32); + \d+ gin_relopts_test + Index "public.gin_relopts_test" + Column | Type | Definition | Storage + --------+---------+------------+--------- + i | integer | i | plain + gin, for table "public.array_index_op_test" + Options: fastupdate=on, pending_list_cleanup_size=32 + + -- -- HASH -- CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops); *** a/src/test/regress/sql/create_index.sql --- b/src/test/regress/sql/create_index.sql *************** *** 651,656 **** SELECT COUNT(*) FROM array_gin_test WHERE a @> '{2}'; --- 651,663 ---- DROP TABLE array_gin_test; -- + -- Test GIN index's reloptions + -- + CREATE INDEX gin_relopts_test ON array_index_op_test USING gin (i) + WITH (FASTUPDATE=on, PENDING_LIST_CLEANUP_SIZE=32); + \d+ gin_relopts_test + + -- -- HASH -- CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers