On Wed, Sep 24, 2014 at 11:10 AM, Etsuro Fujita
<[email protected]> wrote:
> (2014/09/13 2:42), Fujii Masao wrote:
>>
>> On Wed, Sep 10, 2014 at 10:37 PM, Alvaro Herrera
>> <[email protected]> wrote:
>>>
>>> Fujii Masao wrote:
>>>>
>>>> On Wed, Sep 10, 2014 at 12:15 PM, Etsuro Fujita
>>>> <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
