Re: 2GB or not 2GB

Joshua D. Drake wrote:
>
>
> On Wed, 2008-05-28 at 16:59 -0700, Josh Berkus wrote:
> > Folks,
>
> > shared_buffers: according to witnesses, Greg Smith presented at
> > East that based on PostgreSQL's buffer algorithms, buffers above
> > 2GB would not really receive significant use. However, Jignesh
> > Shah has tested that on workloads with large numbers of
> > connections, allocating up to 10GB improves performance.
>
> I have seen multiple production systems where upping the buffers up to
> 6-8GB helps. What I don't know, and what I am guessing Greg is
> referring to is if it helps as much as say upping to 2GB. E.g; the
> scale of performance increase goes down while the actual performance
> goes up (like adding more CPUs).

That could be it. I'm one of the people who recall *something* about
it, but I don't remember any specifics :-)

> > sort_mem: My tests with 8.2 and DBT3 seemed to show that, due to
> > limitations of our tape sort algorithm, allocating over 2GB for a
> > single sort had no benefit. However, Magnus and others have
> > claimed otherwise. Has this improved in 8.3?
>
> I have never see work_mem (there is no sort_mem Josh) do any good
> above 1GB. Of course, I would never willingly use that much work_mem
> unless there was a really good reason that involved a guarantee of
> not calling me at 3:00am.

I have. Not as a system-wide setting, but for a single batch job doing
*large* queries. Don't recall exactly, but it wasn't necessarily for
sort - might have been for hash. I've seen it make a *big* difference.

maintenance_work_mem, however, I didn't see much difference upping it
past 1Gb or so.

//Magnus