Re: Auto-tuning work_mem and maintenance_work_mem

* Bruce Momjian (bruce@momjian.us) wrote:
> For servers that are not dedicated, a fixed value can easily be too
> large, and for a larger server, the value can easily be too small.   Not
> sure how you can argue that a fixed value could be better.

There is definitely something to be said for simplicity and just up'ing
the default would have a more dramatic impact with a setting like
work_mem than it would with shared_buffers, imv.  With work_mem, you'll
actually get better plans that can be much more efficient even with
larger amounts of data.  With shared_buffers, you're generally just
going to be saving a bit of time by avoiding the system call to pull the
blocks back from the Linux FS cache.

This is why I'm much more interested in an actual *change* to what our
users who don't configure things will get rather than an approach that
comes up with a complicated way to arrive at the same answer.

> Well, an open item is to add an available_memory GUC and base everything
> on that, including shared_buffers.  That would allow Windows-specific
> adjustments for the default.

I also think that's an interesting idea, but Robert has a good point,
knowing the size of the DB itself is another considerstaion (or perhaps
the size of the "working set") and those numbers aren't static and may
not be easy to figure out.
Thanks,
    Stephen