diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml new file mode 100644 index 77a9303..1200ad6 *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *************** include 'filename' *** 1156,1163 **** Note that when autovacuum runs, up to ! times this memory may be ! allocated, so be careful not to set the default value too high. --- 1156,1181 ---- Note that when autovacuum runs, up to ! times this memory ! may be allocated, so be careful not to set the default value ! too high. It may be useful to control for this by separately ! setting . ! ! ! ! ! ! autovacuum_work_mem (integer) ! ! autovacuum_work_mem configuration parameter ! ! ! ! Specifies the maximum amount of memory to be used by each ! autovacuum worker process. It defaults to -1, indicating that ! the value of should ! be used instead. The setting has no effect on the behavior of ! VACUUM when run in other contexts. diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c new file mode 100644 index a6d5fc5..5bb7c20 *** a/src/backend/commands/vacuumlazy.c --- b/src/backend/commands/vacuumlazy.c *************** static BufferAccessStrategy vac_strategy *** 132,138 **** /* non-export function prototypes */ static void lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats, ! Relation *Irel, int nindexes, bool scan_all); static void lazy_vacuum_heap(Relation onerel, LVRelStats *vacrelstats); static bool lazy_check_needs_freeze(Buffer buf); static void lazy_vacuum_index(Relation indrel, --- 132,139 ---- /* non-export function prototypes */ static void lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats, ! Relation *Irel, int nindexes, bool scan_all, ! bool autovacuum_mem); static void lazy_vacuum_heap(Relation onerel, LVRelStats *vacrelstats); static bool lazy_check_needs_freeze(Buffer buf); static void lazy_vacuum_index(Relation indrel, *************** static int lazy_vacuum_page(Relation one *** 146,152 **** static void lazy_truncate_heap(Relation onerel, LVRelStats *vacrelstats); static BlockNumber count_nondeletable_pages(Relation onerel, LVRelStats *vacrelstats); ! static void lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks); static void lazy_record_dead_tuple(LVRelStats *vacrelstats, ItemPointer itemptr); static bool lazy_tid_reaped(ItemPointer itemptr, void *state); --- 147,154 ---- static void lazy_truncate_heap(Relation onerel, LVRelStats *vacrelstats); static BlockNumber count_nondeletable_pages(Relation onerel, LVRelStats *vacrelstats); ! static void lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks, ! bool autovacuum_mem); static void lazy_record_dead_tuple(LVRelStats *vacrelstats, ItemPointer itemptr); static bool lazy_tid_reaped(ItemPointer itemptr, void *state); *************** lazy_vacuum_rel(Relation onerel, VacuumS *** 178,184 **** int usecs; double read_rate, write_rate; ! bool scan_all; TransactionId freezeTableLimit; BlockNumber new_rel_pages; double new_rel_tuples; --- 180,187 ---- int usecs; double read_rate, write_rate; ! bool scan_all, ! autovacuum_mem = false; TransactionId freezeTableLimit; BlockNumber new_rel_pages; double new_rel_tuples; *************** lazy_vacuum_rel(Relation onerel, VacuumS *** 198,203 **** --- 201,209 ---- else elevel = DEBUG2; + if (vacstmt->options & VACOPT_AUTOMEM) + autovacuum_mem = true; + vac_strategy = bstrategy; vacuum_set_xid_limits(vacstmt->freeze_min_age, vacstmt->freeze_table_age, *************** lazy_vacuum_rel(Relation onerel, VacuumS *** 220,226 **** vacrelstats->hasindex = (nindexes > 0); /* Do the vacuuming */ ! lazy_scan_heap(onerel, vacrelstats, Irel, nindexes, scan_all); /* Done with indexes */ vac_close_indexes(nindexes, Irel, NoLock); --- 226,233 ---- vacrelstats->hasindex = (nindexes > 0); /* Do the vacuuming */ ! lazy_scan_heap(onerel, vacrelstats, Irel, nindexes, scan_all, ! autovacuum_mem); /* Done with indexes */ vac_close_indexes(nindexes, Irel, NoLock); *************** vacuum_log_cleanup_info(Relation rel, LV *** 385,391 **** */ static void lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats, ! Relation *Irel, int nindexes, bool scan_all) { BlockNumber nblocks, blkno; --- 392,399 ---- */ static void lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats, ! Relation *Irel, int nindexes, bool scan_all, ! bool autovacuum_mem) { BlockNumber nblocks, blkno; *************** lazy_scan_heap(Relation onerel, LVRelSta *** 424,430 **** vacrelstats->nonempty_pages = 0; vacrelstats->latestRemovedXid = InvalidTransactionId; ! lazy_space_alloc(vacrelstats, nblocks); /* * We want to skip pages that don't require vacuuming according to the --- 432,438 ---- vacrelstats->nonempty_pages = 0; vacrelstats->latestRemovedXid = InvalidTransactionId; ! lazy_space_alloc(vacrelstats, nblocks, autovacuum_mem); /* * We want to skip pages that don't require vacuuming according to the *************** count_nondeletable_pages(Relation onerel *** 1573,1585 **** * See the comments at the head of this file for rationale. */ static void ! lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks) { long maxtuples; if (vacrelstats->hasindex) { ! maxtuples = (maintenance_work_mem * 1024L) / sizeof(ItemPointerData); maxtuples = Min(maxtuples, INT_MAX); maxtuples = Min(maxtuples, MaxAllocSize / sizeof(ItemPointerData)); --- 1581,1596 ---- * See the comments at the head of this file for rationale. */ static void ! lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks, ! bool autovacuum_mem) { long maxtuples; + int vac_work_mem = autovacuum_mem && autovacuum_work_mem != -1 ? + autovacuum_work_mem : maintenance_work_mem; if (vacrelstats->hasindex) { ! maxtuples = (vac_work_mem * 1024L) / sizeof(ItemPointerData); maxtuples = Min(maxtuples, INT_MAX); maxtuples = Min(maxtuples, MaxAllocSize / sizeof(ItemPointerData)); diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c new file mode 100644 index 8c14d0f..0d184c1 *** a/src/backend/postmaster/autovacuum.c --- b/src/backend/postmaster/autovacuum.c *************** *** 110,115 **** --- 110,116 ---- */ bool autovacuum_start_daemon = false; int autovacuum_max_workers; + int autovacuum_work_mem = -1; int autovacuum_naptime; int autovacuum_vac_thresh; double autovacuum_vac_scale; *************** autovacuum_do_vac_analyze(autovac_table *** 2753,2758 **** --- 2754,2761 ---- vacstmt.options |= VACOPT_VACUUM; if (tab->at_doanalyze) vacstmt.options |= VACOPT_ANALYZE; + /* General preference for autovacuum_work_mem setting */ + vacstmt.options |= VACOPT_AUTOMEM; vacstmt.freeze_min_age = tab->at_freeze_min_age; vacstmt.freeze_table_age = tab->at_freeze_table_age; /* we pass the OID, but might need this anyway for an error message */ diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c new file mode 100644 index 1756b48..8cb1726 *** a/src/backend/utils/misc/guc.c --- b/src/backend/utils/misc/guc.c *************** static const char *show_tcp_keepalives_c *** 193,198 **** --- 193,199 ---- static bool check_maxconnections(int *newval, void **extra, GucSource source); static bool check_max_worker_processes(int *newval, void **extra, GucSource source); static bool check_autovacuum_max_workers(int *newval, void **extra, GucSource source); + static bool check_autovacuum_work_mem(int *newval, void **extra, GucSource source); static bool check_effective_io_concurrency(int *newval, void **extra, GucSource source); static void assign_effective_io_concurrency(int newval, void *extra); static void assign_pgstat_temp_directory(const char *newval, void *extra); *************** static struct config_int ConfigureNamesI *** 2348,2353 **** --- 2349,2365 ---- }, { + {"autovacuum_work_mem", PGC_SIGHUP, RESOURCES_MEM, + gettext_noop("Sets the maximum memory to be used by each autovacuum worker process."), + NULL, + GUC_UNIT_KB + }, + &autovacuum_work_mem, + -1, -1, MAX_KILOBYTES, + check_autovacuum_work_mem, NULL, NULL + }, + + { {"tcp_keepalives_idle", PGC_USERSET, CLIENT_CONN_OTHER, gettext_noop("Time between issuing TCP keepalives."), gettext_noop("A value of 0 uses the system default."), *************** check_autovacuum_max_workers(int *newval *** 8753,8758 **** --- 8765,8793 ---- return true; } + static bool + check_autovacuum_work_mem(int *newval, void **extra, GucSource source) + { + /* + * -1 indicates fallback. + * + * If we haven't yet changed the boot_val default of -1, just let it be. + * Autovacuum will look to maintenance_work_mem instead. + */ + if (*newval == -1) + return true; + + /* + * We clamp manually-set values to at least 1MB. Since + * maintenance_work_mem is always set to at least this value, do the same + * here. + */ + if (*newval < 1024) + *newval = 1024; + + return true; + } + static bool check_max_worker_processes(int *newval, void **extra, GucSource source) { diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample new file mode 100644 index 707edf1..36fd74c *** a/src/backend/utils/misc/postgresql.conf.sample --- b/src/backend/utils/misc/postgresql.conf.sample *************** *** 122,127 **** --- 122,129 ---- # actively intend to use prepared transactions. #work_mem = 1MB # min 64kB #maintenance_work_mem = 16MB # min 1MB + # Note: autovacuum only prefers autovacuum_work_mem over maintenance_work_mem + #autovacuum_work_mem = -1 # min 1MB, or -1 to disable #max_stack_depth = 2MB # min 100kB #dynamic_shared_memory_type = posix # the default is the first option # supported by the operating system: diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h new file mode 100644 index e5235cb..c442917 *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** typedef enum VacuumOption *** 2463,2469 **** VACOPT_VERBOSE = 1 << 2, /* print progress info */ VACOPT_FREEZE = 1 << 3, /* FREEZE option */ VACOPT_FULL = 1 << 4, /* FULL (non-concurrent) vacuum */ ! VACOPT_NOWAIT = 1 << 5 /* don't wait to get lock (autovacuum only) */ } VacuumOption; typedef struct VacuumStmt --- 2463,2470 ---- VACOPT_VERBOSE = 1 << 2, /* print progress info */ VACOPT_FREEZE = 1 << 3, /* FREEZE option */ VACOPT_FULL = 1 << 4, /* FULL (non-concurrent) vacuum */ ! VACOPT_NOWAIT = 1 << 5, /* don't wait to get lock (autovacuum only) */ ! VACOPT_AUTOMEM = 1 << 6 /* prefer autovacuum_work_mem (autovacuum only) */ } VacuumOption; typedef struct VacuumStmt diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h new file mode 100644 index e96f07a..92560fe *** a/src/include/postmaster/autovacuum.h --- b/src/include/postmaster/autovacuum.h *************** *** 18,23 **** --- 18,24 ---- /* GUC variables */ extern bool autovacuum_start_daemon; extern int autovacuum_max_workers; + extern int autovacuum_work_mem; extern int autovacuum_naptime; extern int autovacuum_vac_thresh; extern double autovacuum_vac_scale;