Re: Treating work_mem as a shared resource (Was: Parallel Hash take II)

On Fri, Nov 17, 2017 at 8:09 AM, Vladimir Rusinov <vrusinov(at)google(dot)com> wrote:
> FWIW, lack of per-connection and/or global memory limit for work_mem is major PITA when running shared and/or large-scale setup.
>
> Currently we are doing a poor job with the work_mem parameter because we don't have a good way to let our customers increase it without also giving them ability to shoot themselves in a foot.
> Even a simple param limiting global total number of work_mem buffers would help here.

I suspect that we can do better here just by allocating memory more
sensibly in a very simple way (something like my hash_mem proposal).
The relationship between aggregate memory usage and aggregate
throughput is very non-linear. One can imagine giving more memory to
hash joins, making each hash join much faster, having the overall
effect of *reducing* aggregate memory usage. The DBA can be more
generous with memory while actually decreasing aggregate memory usage.
This is at least possible with work_mem consuming operations that
involve hashing, like hash join and hash aggregate.

Simple benchmarking tools like pgbench enforce the idea that meeting
throughput requirements is the most important thing, but in reality
workloads are usually very bursty. It is often more important to be
able to stay on a smaller instance size while maintaining less than
excellent (but still acceptable) performance. Again, it's about the
economics.

--
Peter Geoghegan