Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct 9, 2013 at 03:57:14PM -0700, Peter Geoghegan wrote:
> On Wed, Oct 9, 2013 at 3:31 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Splitting out vacuum_work_mem from maintenance_work_mem is a separate
> > issue. I assume they were combined because the memory used for vacuum
> > index scans is similar to creating an index.
>
> Is it similar? Doesn't maintenance_work_mem just bound the size of the
> array of tids to kill there? So you'd expect it to be just a fraction
> of the amount of memory used by initial index creation.

Well, the point is it is how much memory you can expect a maintenance
operation to use, not what it is being used for. You are right they are
used differently, but they are both index-related.

> > I am not sure if having
> > two settings makes something more likely to be set --- I would think the
> > opposite.
>
> Well, if a person does not use vacuum_work_mem, then the cost to that
> person is low. If they do, the benefits could be immense. At the
> Heroku office, I've had people wonder why creating an index took what
> seemed like way too long. I told them to increase
> maintenance_work_mem, and then the index creation was almost
> instantaneous. Now, you can attribute some of that to the I/O of temp
> files on EC2's ephemeral storage, and you'd probably have a point, but
> that certainly isn't the whole story there.

I am unclear what you are suggesting here. Are you saying you want a
separate vacuum_work_mem and maintenance_work_mem so they can have
different defaults?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +