autovacuum_work_mem
There has recently been considerable discussion around auto-tuning.
Throughout the course of this discussion, I raised the idea of
creating a new GUC to separately control autovacuum's usage of
maintenance_work_mem [1]/messages/by-id/CAM3SWZTr1uu+7KR1ZOuGwcJriw9NVBQdjqyDMRWypEvDFi4a6Q@mail.gmail.com, explaining the rationale in some detail [2]/messages/by-id/CAM3SWZTYoD0YCLA-4nRb4S8-UGJyr514aEy+8O6VJQwvbzszGQ@mail.gmail.com.
At the time Magnus seemed to think this a good idea [3]/messages/by-id/CABUevEzVrd36yeFzYBzad0=r09eqRqNoMwX8r=URikG9DrfUkw@mail.gmail.com.
Attached simple patch adds a new GUC, autovacuum_work_mem, which when
set to a value other than -1 (the default) is preferred by autovacuum
over maintenance_work_mem. All other usage of VACUUM is unaffected.
I won't repeat the rationale for the patch here. Appropriate
documentation changes are included. I don't think it's a problem that
autovacuum_work_mem is kind of similar to vacuum_mem in name.
maintenance_work_mem was last spelt vacuum_mem about 10 years ago.
Enough time has passed that I think it very unlikely that someone
might conflate the two.
I have decided to have the default value of -1 carry, and not have it
automatically take the same value as maintenance_work_mem, because
doing so could create the impression that it needs to be set
explicitly, which of course it does not - this is not the same
situation as exists for wal_buffers. We just check if its set when
going to VACUUM, if VACUUM is requested from a worker process.
It seemed neater to me to create a new flag, so that in principle any
vacuum() code path can request autovacuum_work_mem, rather than having
lazyvacuum.c code call IsAutoVacuumWorkerProcess() for the same
purpose. To date, that's only been done within vacuumlazy.c for things
like logging.
[1]: /messages/by-id/CAM3SWZTr1uu+7KR1ZOuGwcJriw9NVBQdjqyDMRWypEvDFi4a6Q@mail.gmail.com
[2]: /messages/by-id/CAM3SWZTYoD0YCLA-4nRb4S8-UGJyr514aEy+8O6VJQwvbzszGQ@mail.gmail.com
[3]: /messages/by-id/CABUevEzVrd36yeFzYBzad0=r09eqRqNoMwX8r=URikG9DrfUkw@mail.gmail.com
--
Peter Geoghegan
Attachments:
autovacuum_work_mem.v1.2013_10_19.patchapplication/octet-stream; name=autovacuum_work_mem.v1.2013_10_19.patchDownload
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 ****
</para>
<para>
Note that when autovacuum runs, up to
! <xref linkend="guc-autovacuum-max-workers"> times this memory may be
! allocated, so be careful not to set the default value too high.
</para>
</listitem>
</varlistentry>
--- 1156,1181 ----
</para>
<para>
Note that when autovacuum runs, up to
! <xref linkend="guc-autovacuum-max-workers"> 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 <xref linkend="guc-autovacuum-work-mem">.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry id="guc-autovacuum-work-mem" xreflabel="autovacuum_work_mem">
! <term><varname>autovacuum_work_mem</varname> (<type>integer</type>)</term>
! <indexterm>
! <primary><varname>autovacuum_work_mem</> configuration parameter</primary>
! </indexterm>
! <listitem>
! <para>
! Specifies the maximum amount of memory to be used by each
! autovacuum worker process. It defaults to -1, indicating that
! the value of <xref linkend="guc-maintenance-work-mem"> should
! be used instead. The setting has no effect on the behavior of
! <command>VACUUM</command> when run in other contexts.
</para>
</listitem>
</varlistentry>
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;
On Sun, Oct 20, 2013 at 2:22 AM, Peter Geoghegan <pg@heroku.com> wrote:
There has recently been considerable discussion around auto-tuning.
Throughout the course of this discussion, I raised the idea of
creating a new GUC to separately control autovacuum's usage of
maintenance_work_mem [1], explaining the rationale in some detail [2].
At the time Magnus seemed to think this a good idea [3].Attached simple patch adds a new GUC, autovacuum_work_mem, which when
set to a value other than -1 (the default) is preferred by autovacuum
over maintenance_work_mem. All other usage of VACUUM is unaffected.I won't repeat the rationale for the patch here. Appropriate
documentation changes are included. I don't think it's a problem that
autovacuum_work_mem is kind of similar to vacuum_mem in name.
maintenance_work_mem was last spelt vacuum_mem about 10 years ago.
Enough time has passed that I think it very unlikely that someone
might conflate the two.
My rationale for liking the idea is also in those threads :)
And I definitely don't think there is any problem whatsoever in having
a name that's close to something we had 10 years ago. I think it would
be hard enough to make a case for not reusing the *same* name that
long after, but here it's actually different.
I have decided to have the default value of -1 carry, and not have it
automatically take the same value as maintenance_work_mem, because
doing so could create the impression that it needs to be set
explicitly, which of course it does not - this is not the same
situation as exists for wal_buffers. We just check if its set when
going to VACUUM, if VACUUM is requested from a worker process.
It is the same that exists for autovacuum_vacuum_cost_limit and
autovacuum_vacuum_cost_delay, so that makes perfect sense.
It seemed neater to me to create a new flag, so that in principle any
vacuum() code path can request autovacuum_work_mem, rather than having
lazyvacuum.c code call IsAutoVacuumWorkerProcess() for the same
purpose. To date, that's only been done within vacuumlazy.c for things
like logging.
Hmm. I'm not entirely sure I agree that that makes it neater :)
We could also look at autovacuum_vacuum_cost_limit etc above, but
those just override what the non-autovac parameters do. But since the
parameter is called maintenance_work_mem in that case, I think that
would make it harder to read.
But I'd suggest just a:
int vac_work_mem = (IsAutoVacuumWorkerProcess() && autovacuum_work_mem
!= -1) ? autovacuum_work_mem : maintenance_work_mem;
and not sending around a boolean flag through a bunch of places when
it really means just the same thing,
Another option would be to just have vac_work_mem actually being the
global,and have it set by the maintenance_work_mem parameter guc, and
then overwritten by the vacuum work mem parameter guc in autovacuum
worker startup. Then you wouldn't put either of those two in the
codepath.
Also, isn't this quite confusing:
+ # Note: autovacuum only prefers autovacuum_work_mem over maintenance_work_mem
+ #autovacuum_work_mem = -1 # min 1MB, or -1 to disableWhere does the "only" come from? And we don't really use the term
"prefers over" anywhere else there. And -1 doesn't actually disable
it. I suggest following the pattern of the other parameters that work
the same way, which would then just be:
#autovacuum_work_mem = -1 # amount of memory for each autovacuum
worker. -1 means use maintenance_work_mem
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 10/19/13 8:22 PM, Peter Geoghegan wrote:
I don't think it's a problem that
autovacuum_work_mem is kind of similar to vacuum_mem in name.
maintenance_work_mem was last spelt vacuum_mem about 10 years ago.
Enough time has passed that I think it very unlikely that someone
might conflate the two.
What is more confusing, however, is that autovacuum_work_mem looks like
it's work_mem as used by autovacuum, where it's really
maintenance_work_mem as used by autovacuum. So maybe it should be
called autovacuum_maintenance_work_mem.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Oct 21, 2013 at 9:36 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
On 10/19/13 8:22 PM, Peter Geoghegan wrote:
I don't think it's a problem that
autovacuum_work_mem is kind of similar to vacuum_mem in name.
maintenance_work_mem was last spelt vacuum_mem about 10 years ago.
Enough time has passed that I think it very unlikely that someone
might conflate the two.What is more confusing, however, is that autovacuum_work_mem looks like
it's work_mem as used by autovacuum, where it's really
maintenance_work_mem as used by autovacuum. So maybe it should be
called autovacuum_maintenance_work_mem.
I think I prefer autovacuum_work_mem. I don't think sticking the word
maintenance in there is really adding much in the way of clarity.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Oct 21, 2013 at 3:42 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Oct 21, 2013 at 9:36 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
On 10/19/13 8:22 PM, Peter Geoghegan wrote:
I don't think it's a problem that
autovacuum_work_mem is kind of similar to vacuum_mem in name.
maintenance_work_mem was last spelt vacuum_mem about 10 years ago.
Enough time has passed that I think it very unlikely that someone
might conflate the two.What is more confusing, however, is that autovacuum_work_mem looks like
it's work_mem as used by autovacuum, where it's really
maintenance_work_mem as used by autovacuum. So maybe it should be
called autovacuum_maintenance_work_mem.I think I prefer autovacuum_work_mem. I don't think sticking the word
maintenance in there is really adding much in the way of clarity.
+1. If changing at all, then maybe just "autovacuum_mem"? It's not
like there's a different parameter to control a different kind of
memory in autovac, is there?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
* Magnus Hagander (magnus@hagander.net) wrote:
+1. If changing at all, then maybe just "autovacuum_mem"? It's not
like there's a different parameter to control a different kind of
memory in autovac, is there?
+1 on that, for my 2c.
Thanks,
Stephen
On Mon, Oct 21, 2013 at 6:44 AM, Magnus Hagander <magnus@hagander.net> wrote:
+1. If changing at all, then maybe just "autovacuum_mem"?
I would like to stick with autovacuum_work_mem - it reflects the fact
that it's a setting shadowed by maintenance_work_mem, without being
too verbose.
--
Peter Geoghegan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 19 October 2013 19:22, Peter Geoghegan <pg@heroku.com> wrote:
I won't repeat the rationale for the patch here.
I can't see the problem that this patch is trying to solve. I'm having
trouble understanding when I would use this.
VACUUM uses 6 bytes per dead tuple. And autovacuum regularly removes
dead tuples, limiting their numbers.
In what circumstances will the memory usage from multiple concurrent
VACUUMs become a problem? In those circumstances, reducing
autovacuum_work_mem will cause more passes through indexes, dirtying
more pages and elongating the problem workload.
I agree that multiple concurrent VACUUMs could be a problem but this
doesn't solve that, it just makes things worse.
Freezing doesn't require any memory at all, so wraparound vacuums
won't be controlled by this parameter.
Can we re-state what problem actually is here and discuss how to solve
it. (The reference [2] didn't provide a detailed explanation of the
problem, only the reason why we want a separate parameter).
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sun, Nov 24, 2013 at 9:06 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
VACUUM uses 6 bytes per dead tuple. And autovacuum regularly removes
dead tuples, limiting their numbers.In what circumstances will the memory usage from multiple concurrent
VACUUMs become a problem? In those circumstances, reducing
autovacuum_work_mem will cause more passes through indexes, dirtying
more pages and elongating the problem workload.
Yes, of course, but if we presume that the memory for autovacuum
workers to do everything in one pass simply isn't there, it's still
better to do multiple passes. Similarly, it's also sometimes (roughly
speaking) locally suboptimal but globally optimal to do tapesorts in
preference to in-memory quicksorts, even though, as you know, very
frequently tapesort is very considerably slower than quicksort.
Look at the folk wisdom for sizing maintenance_work_mem that is
floating around (for example, take a look at Greg Smith's
recommendations in his book). Setting it within postgresql.conf is
assumed. You can end up with a conservative value because you're
worrying about the worst case. The average case suffers. Especially
since, as you say, autovacuum only uses 6 bytes per tuple, and so
probably isn't all that likely to run out of working memory, making
that worst case (that is, maintenance_work_mem over-allocation by
autovacuum workers) very unlikely.
So on larger Heroku Postgres plans, the generic maintenance_work_mem
is on the low side, and I sometimes have to manually increase it when
I'm doing something like creating a new index. I would like to not
have to do that, and I would like to not require users to be aware of
this issue, especially since external sorting is so much slower.
I am inclined to think that we need an altogether more sophisticated
model, but this is an incremental improvement.
Can we re-state what problem actually is here and discuss how to solve
it. (The reference [2] didn't provide a detailed explanation of the
problem, only the reason why we want a separate parameter).
It's principally a DBA feature, in that it allows the DBA to
separately control the memory used by very routine vacuuming, while
also having a less conservative default value for maintenance
operations that typically are under direct human control. Yes, this is
no better than just having maintenance_work_mem be equal to your
would-be autovacuum_work_mem setting in the first place, and having
everyone remember to set maintenance_work_mem dynamically. However,
sometimes people are ill-informed (more ill-informed than the person
that puts the setting in postgresql.conf), and other times they're
forgetful, and other times still they're using a tool like pg_restore
with no convenient way to dynamically set maintenance_work_mem. So, to
answer your question, yes: it is entirely possible that you or someone
like you may have no use for this.
It's often reasonable to assume that autovacuum workers are the only
processes that can allocate memory bound in size by
maintenance_work_mem that are not under the direct control of a human
performing maintenance. Autovacuum workers are in a sense just
servicing regular application queries (consider how Oracle handles
ROLLBACK garbage collection), and things that service regular
application queries are already bound separately by work_mem.
--
Peter Geoghegan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sun, Oct 20, 2013 at 7:21 AM, Magnus Hagander <magnus@hagander.net> wrote:
It seemed neater to me to create a new flag, so that in principle any
vacuum() code path can request autovacuum_work_mem, rather than having
lazyvacuum.c code call IsAutoVacuumWorkerProcess() for the same
purpose. To date, that's only been done within vacuumlazy.c for things
like logging.Hmm. I'm not entirely sure I agree that that makes it neater :)
We could also look at autovacuum_vacuum_cost_limit etc above, but
those just override what the non-autovac parameters do. But since the
parameter is called maintenance_work_mem in that case, I think that
would make it harder to read.But I'd suggest just a:
int vac_work_mem = (IsAutoVacuumWorkerProcess() && autovacuum_work_mem
!= -1) ? autovacuum_work_mem : maintenance_work_mem;and not sending around a boolean flag through a bunch of places when
it really means just the same thing,
+1 for that change.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 25 November 2013 21:51, Peter Geoghegan <pg@heroku.com> wrote:
On Sun, Nov 24, 2013 at 9:06 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
VACUUM uses 6 bytes per dead tuple. And autovacuum regularly removes
dead tuples, limiting their numbers.In what circumstances will the memory usage from multiple concurrent
VACUUMs become a problem? In those circumstances, reducing
autovacuum_work_mem will cause more passes through indexes, dirtying
more pages and elongating the problem workload.Yes, of course, but if we presume that the memory for autovacuum
workers to do everything in one pass simply isn't there, it's still
better to do multiple passes.
That isn't clear to me. It seems better to wait until we have the memory.
My feeling is this parameter is a fairly blunt approach to the
problems of memory pressure on autovacuum and other maint tasks. I am
worried that it will not effectively solve the problem. I don't wish
to block the patch; I wish to get to an effective solution to the
problem.
A better aproach to handling memory pressure would be to globally
coordinate workers so that we don't oversubscribe memory, allocating
memory from a global pool.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Dec 11, 2013 at 9:43 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
On 25 November 2013 21:51, Peter Geoghegan <pg@heroku.com> wrote:
On Sun, Nov 24, 2013 at 9:06 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
VACUUM uses 6 bytes per dead tuple. And autovacuum regularly removes
dead tuples, limiting their numbers.In what circumstances will the memory usage from multiple concurrent
VACUUMs become a problem? In those circumstances, reducing
autovacuum_work_mem will cause more passes through indexes, dirtying
more pages and elongating the problem workload.Yes, of course, but if we presume that the memory for autovacuum
workers to do everything in one pass simply isn't there, it's still
better to do multiple passes.That isn't clear to me. It seems better to wait until we have the memory.
My feeling is this parameter is a fairly blunt approach to the
problems of memory pressure on autovacuum and other maint tasks. I am
worried that it will not effectively solve the problem. I don't wish
to block the patch; I wish to get to an effective solution to the
problem.A better aproach to handling memory pressure would be to globally
coordinate workers so that we don't oversubscribe memory, allocating
memory from a global pool.
This is doubtless true, but that project is at least two if not three
orders of magnitude more complex than what's being proposed here, and
I don't think we should make the perfect the enemy of the good.
Right now, maintenance_work_mem controls the amount of memory that
we're willing to use for either a vacuum operation or an index build.
Those things don't have much to do with each other, so it's not hard
for me to imagine that someone might want to configure different
memory usage for one than the other. This patch would allow that, and
I think that's good.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 11 December 2013 14:50, Robert Haas <robertmhaas@gmail.com> wrote:
On Wed, Dec 11, 2013 at 9:43 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
On 25 November 2013 21:51, Peter Geoghegan <pg@heroku.com> wrote:
On Sun, Nov 24, 2013 at 9:06 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
VACUUM uses 6 bytes per dead tuple. And autovacuum regularly removes
dead tuples, limiting their numbers.In what circumstances will the memory usage from multiple concurrent
VACUUMs become a problem? In those circumstances, reducing
autovacuum_work_mem will cause more passes through indexes, dirtying
more pages and elongating the problem workload.Yes, of course, but if we presume that the memory for autovacuum
workers to do everything in one pass simply isn't there, it's still
better to do multiple passes.That isn't clear to me. It seems better to wait until we have the memory.
My feeling is this parameter is a fairly blunt approach to the
problems of memory pressure on autovacuum and other maint tasks. I am
worried that it will not effectively solve the problem. I don't wish
to block the patch; I wish to get to an effective solution to the
problem.A better aproach to handling memory pressure would be to globally
coordinate workers so that we don't oversubscribe memory, allocating
memory from a global pool.This is doubtless true, but that project is at least two if not three
orders of magnitude more complex than what's being proposed here, and
I don't think we should make the perfect the enemy of the good.
It looks fairly easy to estimate the memory needed for an auto vacuum,
since we know the scale factor and the tuple estimate. We can then use
the memory estimate to alter the scheduling of work. And/or we can use
actual memory usage and block auto vac workers if they need more
memory than is currently available because of other workers.
We would still benefit from a new parameter in the above sketch, but
it would achieve something useful in practice.
That's about 2-3 days work and I know Peter can hack it. So the
situation is not perfection-sought-blocking-good, this is more like
fairly poor solution being driven through when a better solution is
available within the time and skills available.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Dec 11, 2013 at 10:41 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
It looks fairly easy to estimate the memory needed for an auto vacuum,
since we know the scale factor and the tuple estimate. We can then use
the memory estimate to alter the scheduling of work. And/or we can use
actual memory usage and block auto vac workers if they need more
memory than is currently available because of other workers.We would still benefit from a new parameter in the above sketch, but
it would achieve something useful in practice.That's about 2-3 days work and I know Peter can hack it. So the
situation is not perfection-sought-blocking-good, this is more like
fairly poor solution being driven through when a better solution is
available within the time and skills available.
I don't agree with that assessment. Anything that involves changing
the scheduling of autovacuum is a major project that will legitimately
provoke much controversy. Extensive testing will be needed to prove
that the new algorithm doesn't perform worse than the current
algorithm in any important cases. I have my doubts about whether that
can be accomplished in an entire release cycle, let alone 2-3 days.
In contrast, the patch proposed does something that is easy to
understand, clearly safe, and an improvement over what we have now.
Quite apart from the amount of development time required, I think that
the idea that we would use the availability of memory to schedule work
is highly suspect. You haven't given any details on what you think
that algorithm might look like, and I doubt that any simple solution
will do. If running more autovacuum workers drives the machine into
swap, then we shouldn't, but we have no way of calculating what size
memory allocation will cause that to happen. But NOT running
autovacuum workers isn't safe either, because it could cause table
bloat that them drives the machine into swap. We have no way of
knowing whether that will happen either. So I think your contention
that we have the necessary information available to make an
intelligent decision is incorrect.
Regardless, whether or not a more complex change is within Peter's
technical capabilities is utterly irrelevant to whether we should
adopt the proposed patch. Your phrasing seems to imply that you would
not ask such a thing of a less-talented individual, and I think that
is utterly wrong. Peter's technical acumen does not give us the right
to ask him to write a more complex patch as a condition of getting a
simpler one accepted.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/11/2013 09:57 AM, Robert Haas wrote:
I don't agree with that assessment. Anything that involves changing
the scheduling of autovacuum is a major project that will legitimately
provoke much controversy. Extensive testing will be needed to prove
that the new algorithm doesn't perform worse than the current
algorithm in any important cases. I have my doubts about whether that
can be accomplished in an entire release cycle, let alone 2-3 days.
In contrast, the patch proposed does something that is easy to
understand, clearly safe, and an improvement over what we have now.
+1
There is an inherent tuning and troubleshooting challenge in anything
involving a feedback loop.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WM6a2d9f4a8ed913a481160a143490e1b64aa84e20b84f461b76c057e4081140bb659249672ddb84eb69e5879825699417@asav-3.01.com
On Wed, Dec 11, 2013 at 7:41 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
That's about 2-3 days work and I know Peter can hack it. So the
situation is not perfection-sought-blocking-good, this is more like
fairly poor solution being driven through when a better solution is
available within the time and skills available.
I think that that's a very optimistic assessment of the amount of work
required. Even by the rose-tinted standards of software project time
estimation. A ton of data is required to justify fundamental
infrastructural changes like that.
--
Peter Geoghegan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 11 December 2013 17:57, Robert Haas <robertmhaas@gmail.com> wrote:
Extensive testing will be needed to prove
that the new algorithm doesn't perform worse than the current
algorithm in any important cases.
Agreed, but the amount of testing seems equivalent in both cases,
assuming we weren't going to skip it for this patch.
Let me repeat the question, so we are clear...
In what circumstances will the memory usage from multiple concurrent
VACUUMs become a problem? In those circumstances, reducing
autovacuum_work_mem will cause more passes through indexes, dirtying
more pages and elongating the problem workload. I agree that multiple
concurrent VACUUMs could be a problem but this
doesn't solve that, it just makes things worse.
The *only* time this parameter would have any effect looks like when
it will make matters worse.
With considerable regret, I don't see how this solves the problem at
hand. We can and should do better.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Dec 11, 2013 at 2:37 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
On 11 December 2013 17:57, Robert Haas <robertmhaas@gmail.com> wrote:
Extensive testing will be needed to prove
that the new algorithm doesn't perform worse than the current
algorithm in any important cases.Agreed, but the amount of testing seems equivalent in both cases,
assuming we weren't going to skip it for this patch.Let me repeat the question, so we are clear...
In what circumstances will the memory usage from multiple concurrent
VACUUMs become a problem? In those circumstances, reducing
autovacuum_work_mem will cause more passes through indexes, dirtying
more pages and elongating the problem workload. I agree that multiple
concurrent VACUUMs could be a problem but this
doesn't solve that, it just makes things worse.
That's not the problem the patch is designed to solve. It's intended
for the case where you want to allow more or less memory to autovacuum
than you do for index builds. There's no principled reason that
anyone should want those things to be the same. It is not difficult
to imagine situations in which you would want one set to a very
different value than the other. In particular it seems quite likely
to me that the amount of memory appropriate for index builds might be
vastly more than is needed by autovacuum. For example, in a
data-warehousing environment where updates are rare but large index
builds by the system's sole user are frequent, someone might want to
default index builds to 64GB of RAM (especially after Noah's patch to
allow huge allocations for the tuple array while sorting) but only
need 256MB for autovacuum.
In general, I'm reluctant to believe that Peter proposed this patch
just for fun. I assume this is a real-world problem that Heroku
encounters in their environment. If not, well then that's different.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/11/2013 11:37 AM, Simon Riggs wrote:> On 11 December 2013 17:57,
Robert Haas <robertmhaas@gmail.com> wrote:
Extensive testing will be needed to prove
that the new algorithm doesn't perform worse than the current
algorithm in any important cases.Agreed, but the amount of testing seems equivalent in both cases,
assuming we weren't going to skip it for this patch.
No performance testing is required for this patch. The effect of memory
limits on vacuum are already well-known and well-understood.
With considerable regret, I don't see how this solves the problem at
hand. We can and should do better.
I strongly disagree. The problem we are dealing with currently is that
two resource limits which should have *always* been independent of each
other are currently conflated into a single GUC variable. This forces
users to remember to set maintenance_work_mem interactively every time
they want to run a manual VACUUM, because the setting in postgresql.conf
is needed to tune autovacuum.
In other words, we are having an issue with *non-atomic data*, and this
patch partially fixes that.
Would it be better to have an admissions-control policy engine for
launching autovacuum which takes into account available RAM, estimated
costs of concurrent vacuums, current CPU activity, and which tables are
in cache? Yes. And if you started on that now, you might have it ready
for 9.5.
And, for that matter, accepting this patch by no means blocks doing
something more sophisticated in the future.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WMcb1d956660381239aa5465c9ae003358430ed32a1cb14c6779c8521f3e02bf5b6c5c0f28a070778723aaa37eca9b2a91@asav-1.01.com
Josh Berkus <josh@agliodbs.com> writes:
And, for that matter, accepting this patch by no means blocks doing
something more sophisticated in the future.
Yeah. I think the only real argument against it is "do we really need
yet another knob?". Since Josh, who's usually the voicer of that
argument, is for this one, I don't have a problem with it.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/11/2013 12:40 PM, Tom Lane wrote:
Josh Berkus <josh@agliodbs.com> writes:
And, for that matter, accepting this patch by no means blocks doing
something more sophisticated in the future.Yeah. I think the only real argument against it is "do we really need
yet another knob?". Since Josh, who's usually the voicer of that
argument, is for this one, I don't have a problem with it.
This passes the "is it a chronic problem not to have a knob for this?" test.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WM2852ffd35cfef78c8f9f2f2fcd074f68d327bc09e149c6655fbb5455479d1cd4905351234a0c6e96c4f8f78e718158b8@asav-1.01.com
On 11 December 2013 19:54, Josh Berkus <josh@agliodbs.com> wrote:
On 12/11/2013 11:37 AM, Simon Riggs wrote:> On 11 December 2013 17:57,
Robert Haas <robertmhaas@gmail.com> wrote:Extensive testing will be needed to prove
that the new algorithm doesn't perform worse than the current
algorithm in any important cases.Agreed, but the amount of testing seems equivalent in both cases,
assuming we weren't going to skip it for this patch.No performance testing is required for this patch. The effect of memory
limits on vacuum are already well-known and well-understood.
Yes, I wrote the patch that you use to tune autovacuum. Not everybody
agreed with me then either about whether we need it, so I'm used to
people questioning my thinking and am happy people do.
With considerable regret, I don't see how this solves the problem at
hand. We can and should do better.I strongly disagree. The problem we are dealing with currently is that
two resource limits which should have *always* been independent of each
other are currently conflated into a single GUC variable. This forces
users to remember to set maintenance_work_mem interactively every time
they want to run a manual VACUUM, because the setting in postgresql.conf
is needed to tune autovacuum.
I understand the general argument and it sounds quite cool, I agree. I
am all for user control.
But nobody has given a sensible answer to my questions, other than to
roll out the same general points again. In practice, its a knob that
does not do very much of use for us. At best it is addressing the
symptoms, not the cause. IMHO.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Dec 11, 2013 at 1:28 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
But nobody has given a sensible answer to my questions, other than to
roll out the same general points again. In practice, its a knob that
does not do very much of use for us. At best it is addressing the
symptoms, not the cause. IMHO.
It's just a usability feature. It lessens the intellectual overhead of
managing maintenance_work_mem. I understand that it isn't very
impressive from a technical point of view. However, in many
environments, it actually will make a significant difference, because
non-autovacuum maintenance operations are very rare compared to
autovacuum workers vacuuming, and therefore I can now afford to be
much less conservative in setting maintenance_work_mem globally on
each of 8 Postgres instances hosted on a single box. These are
precisely the kinds of Postgres instances where users are very
unlikely to have heard of maintenance_work_mem at all. These users are
not even using an admin tool in many cases, preferring to rely on ORM
migrations. Having said that, it's also something I'll find useful on
a day to day basis, because it's a chore to set maintenace_work_mem
manually, and sometimes I forget to do so, particularly when under
pressure to relieve a production performance issues on a random
customer's database.
--
Peter Geoghegan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 11 December 2013 22:23, Peter Geoghegan <pg@heroku.com> wrote:
On Wed, Dec 11, 2013 at 1:28 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
But nobody has given a sensible answer to my questions, other than to
roll out the same general points again. In practice, its a knob that
does not do very much of use for us. At best it is addressing the
symptoms, not the cause. IMHO.It's just a usability feature. It lessens the intellectual overhead of
managing maintenance_work_mem. I understand that it isn't very
impressive from a technical point of view. However, in many
environments, it actually will make a significant difference, because
non-autovacuum maintenance operations are very rare compared to
autovacuum workers vacuuming, and therefore I can now afford to be
much less conservative in setting maintenance_work_mem globally on
each of 8 Postgres instances hosted on a single box. These are
precisely the kinds of Postgres instances where users are very
unlikely to have heard of maintenance_work_mem at all. These users are
not even using an admin tool in many cases, preferring to rely on ORM
migrations. Having said that, it's also something I'll find useful on
a day to day basis, because it's a chore to set maintenace_work_mem
manually, and sometimes I forget to do so, particularly when under
pressure to relieve a production performance issues on a random
customer's database.
My view remains that, yes, we have a problem setting maint work men
usefully, my conclusion is that having two parameters we don't know
how to set won't improve things and doesn't constitute an improvement
in usability.
That being said, I acknowledge everybody else's viewpoints here and
will commit this feature as is.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Dec 11, 2013 at 10:35:32AM -0800, Josh Berkus wrote:
On 12/11/2013 09:57 AM, Robert Haas wrote:
I don't agree with that assessment. Anything that involves changing
the scheduling of autovacuum is a major project that will legitimately
provoke much controversy. Extensive testing will be needed to prove
that the new algorithm doesn't perform worse than the current
algorithm in any important cases. I have my doubts about whether that
can be accomplished in an entire release cycle, let alone 2-3 days.
In contrast, the patch proposed does something that is easy to
understand, clearly safe, and an improvement over what we have now.+1
There is an inherent tuning and troubleshooting challenge in anything
involving a feedback loop.
We have avoided feedback loops in the past. I think eventually we are
going to need to tackle them, but it is a big job, and vacuum memory
usage would be at the bottom of my list of feedback loop tasks.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/13/2013 08:24 PM, Bruce Momjian wrote:
On Wed, Dec 11, 2013 at 10:35:32AM -0800, Josh Berkus wrote:
On 12/11/2013 09:57 AM, Robert Haas wrote:
I don't agree with that assessment. Anything that involves changing
the scheduling of autovacuum is a major project that will legitimately
provoke much controversy. Extensive testing will be needed to prove
that the new algorithm doesn't perform worse than the current
algorithm in any important cases. I have my doubts about whether that
can be accomplished in an entire release cycle, let alone 2-3 days.
In contrast, the patch proposed does something that is easy to
understand, clearly safe, and an improvement over what we have now.+1
There is an inherent tuning and troubleshooting challenge in anything
involving a feedback loop.We have avoided feedback loops in the past. I think eventually we are
going to need to tackle them, but it is a big job, and vacuum memory
usage would be at the bottom of my list of feedback loop tasks.
I haven't been following this thread in detail, but would it help if we
implemented a scheme to reduce (auto)vacuum's memory usage? Such schemes
have been discussed in the past, packing the list of dead items more
tightly.
I guess you'd still want to have a limit on autovacuum's memory usage. A
much lower limit than you'd want to allow for one-off CREATE INDEX
operations and such.
(Personally, I don't care whether we add this new option or not. And -1
for feedback loops.)
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Heikki Linnakangas escribi�:
I haven't been following this thread in detail, but would it help if
we implemented a scheme to reduce (auto)vacuum's memory usage? Such
schemes have been discussed in the past, packing the list of dead
items more tightly.
Well, it would help some, but it wouldn't eliminate the problem
completely. Autovacuum scales its memory usage based on the size of the
table. There will always be a table so gigantic that a maximum
allocated memory is to be expected; and DBAs will need a way to limit
the memory consumption even if we pack dead items more densely.
I was playing with keeping item pointers for each page in a bitmapset.
This was pretty neat and used a lot less memory than currently, except
that I needed to allocate a large chunk of memory and then have
bitmapsets use words within that large allocation space. It turned out
to be too ugly so I abandoned it. With the "varbit encoding" thingy in
the recent GIN patchset, maybe it would be workable.
I think a more dense packing is desired regardless of this patch.
Maybe we can have a generic module for "item pointer arrays" which could
be useful for both autovacuum and GIN.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/13/2013 08:40 PM, Alvaro Herrera wrote:
Heikki Linnakangas escribi�:
I haven't been following this thread in detail, but would it help if
we implemented a scheme to reduce (auto)vacuum's memory usage? Such
schemes have been discussed in the past, packing the list of dead
items more tightly.Well, it would help some, but it wouldn't eliminate the problem
completely. Autovacuum scales its memory usage based on the size of the
table. There will always be a table so gigantic that a maximum
allocated memory is to be expected; and DBAs will need a way to limit
the memory consumption even if we pack dead items more densely.I was playing with keeping item pointers for each page in a bitmapset.
This was pretty neat and used a lot less memory than currently, except
that I needed to allocate a large chunk of memory and then have
bitmapsets use words within that large allocation space. It turned out
to be too ugly so I abandoned it. With the "varbit encoding" thingy in
the recent GIN patchset, maybe it would be workable.
The varbyte encoding is actually a very poor fit for vacuum. Vacuum
needs fast random access into the array when scanning indexes, and the
varbyte encoded item pointer lists used in gin don't allow that.
I couldn't find it in the archives now, but when we last discussed this,
Tom suggested that we divide the large chunk of memory that vacuum
allocates into two parts. The first part grows from the bottom up, and
the second part from top down, until there is no free space in the
middle anymore. For each heap page, there is one entry in the first
part, with the block number, and a pointer to an entry in the second
part. In the second part, there's a list of offset numbers on that page
(or a bitmap).
Another idea: Store only the least significant 20 bits the block number
of each item pointer, and use the remaining 12 bits for the offset
number. So each item pointer is stored as a single 32 bit integer. For
the top 12 bits of the block number, build a separate lookup table of
4096 entries, indexed by the top bits. Each entry in the lookup table
points to the beginning and end index in the main array where the
entries for that page range is stored. That would reduce the memory
usage by about 1/3, which isn't as good as the bitmap method when there
is a lot of dead tuples same pages, but would probably be a smaller patch.
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 16 December 2013 10:12, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:
On 12/13/2013 08:40 PM, Alvaro Herrera wrote:
Heikki Linnakangas escribió:
I haven't been following this thread in detail, but would it help if
we implemented a scheme to reduce (auto)vacuum's memory usage? Such
schemes have been discussed in the past, packing the list of dead
items more tightly.Well, it would help some, but it wouldn't eliminate the problem
completely. Autovacuum scales its memory usage based on the size of the
table. There will always be a table so gigantic that a maximum
allocated memory is to be expected; and DBAs will need a way to limit
the memory consumption even if we pack dead items more densely.
The problem is allocation of memory, not one of efficient usage once
we have been allocated.
Another idea: Store only the least significant 20 bits the block number of
each item pointer, and use the remaining 12 bits for the offset number. So
each item pointer is stored as a single 32 bit integer. For the top 12 bits
of the block number, build a separate lookup table of 4096 entries, indexed
by the top bits. Each entry in the lookup table points to the beginning and
end index in the main array where the entries for that page range is stored.
That would reduce the memory usage by about 1/3, which isn't as good as the
bitmap method when there is a lot of dead tuples same pages, but would
probably be a smaller patch.
We would do better to just use memory from shared_buffers and then we
wouldn't need a memory allocation or limit.
If we split the allocation into a series of BLCKSZ blocks of memory,
we can use your compression down to 4 bytes/row and then index the
blocks.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers