2GB or not 2GB

Folks,

Subsequent to my presentation of the new annotated.conf at pgCon last week,
there's been some argument about the utility of certain memory settings
above 2GB. I'd like to hash those out on this list so that we can make
some concrete recomendations to users.

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.

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?

So, can we have some test evidence here? And workload descriptions?

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

Josh Berkus wrote:
> Folks,
>
> Subsequent to my presentation of the new annotated.conf at pgCon last week,...

Available online yet? At?...

Cheers,
Steve

"Josh Berkus" <josh(at)agliodbs(dot)com> writes:

> 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?

Simon previously pointed out that we have some problems in our tape sort
algorithm with large values of work_mem. If the tape is "large enough" to
generate some number of output tapes then increasing the heap size doesn't buy
us any reduction in the future passes. And managing very large heaps is a
fairly large amount of cpu time itself.

The problem of course is that we never know if it's "large enough". We talked
at one point about having a heuristic where we start the heap relatively small
and double it (adding one row) whenever we find we're starting a new tape. Not
sure how that would work out though.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning

On Wed, 28 May 2008, Josh Berkus wrote:

> 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.

Lies! The only upper-limit for non-Windows platforms I mentioned was
suggesting those recent tests at Sun showed a practical limit in the low
multi-GB range.

I've run with 4GB usefully for one of the multi-TB systems I manage, the
main index on the most frequently used table is 420GB and anything I can
do to keep the most popular parts of that pegged in memory seems to help.
I haven't tried to isolate the exact improvement going from 2GB to 4GB
with benchmarks though.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Josh Berkus wrote:
> Folks,
>
> Subsequent to my presentation of the new annotated.conf at pgCon last week,
> there's been some argument about the utility of certain memory settings
> above 2GB. I'd like to hash those out on this list so that we can make
> some concrete recomendations to users.
>
> 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 certainly seen improvements in performance upto 10GB using
EAStress. The delicate balance is between file system cache and shared
buffers. I think the initial ones are more beneficial at shared buffers
level and after that file system cache.
I am trying to remember Greg's presentation where I think he suggested
more like 50% of available RAM (eg in 4GB system used just for
PostgreSQL, it may not help setting more than 2GB since you need memory
for other stuff also).. Right Greg?

But if you have 32GB RAM .. I dont mind allocating 10GB to PostgreSQL
beyond which I find lots of other things that begin to impact..

BTW I am really +1 for just setting AvailRAM tunable for PostgreSQL
(example that you showed in tutorials) and do default derivations for
all other settings unless overridden manually. So people dont forget to
bump up wal_buffers or one of them while bumping the rest and trying to
fight why the hell they are not seeing what they are expecting.

-Jignesh

Greg Smith wrote:
> On Wed, 28 May 2008, Josh Berkus wrote:
>
>> 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.
>
> Lies! The only upper-limit for non-Windows platforms I mentioned was
> suggesting those recent tests at Sun showed a practical limit in the
> low multi-GB range.
>
> I've run with 4GB usefully for one of the multi-TB systems I manage,
> the main index on the most frequently used table is 420GB and anything
> I can do to keep the most popular parts of that pegged in memory seems
> to help. I haven't tried to isolate the exact improvement going from
> 2GB to 4GB with benchmarks though.
>
Yep its always the index that seems to benefit with high cache hits.. In
one of the recent tests what I end up doing is writing a select
count(*) from trade where t_id >= $1 and t_id < SOMEMAX just to kick in
index scan and get it in memory first. So higher the bufferpool better
the hit for index in it better the performance.

-Jignesh

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).

>
> 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.

>
> So, can we have some test evidence here? And workload descriptions?
>

Its all, tune now buddy :P

Sinceerely,

Joshua D. Drake

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

On Wed, 2008-05-28 at 16:59 -0700, Josh Berkus wrote:

> 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?

There is an optimum for each specific sort.

Your results cannot be used to make a global recommendation about the
setting of work_mem. So not finding any benefit in your tests *and*
Magnus seeing an improvement are not inconsistent events.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon,

> There is an optimum for each specific sort.

Well, if the optimum is something other than "as much as we can get", then we
still have a pretty serious issue with work_mem, no?

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

"Josh Berkus" <josh(at)agliodbs(dot)com> writes:

> Simon,
>
>> There is an optimum for each specific sort.
>
> Well, if the optimum is something other than "as much as we can get", then we
> still have a pretty serious issue with work_mem, no?

With the sort algorithm. The problem is that the database can't predict the
future and doesn't know how many more records will be arriving and how out of
order they will be.

What appears to be happening is that if you give the tape sort a large amount
of memory it keeps a large heap filling that memory. If that large heap
doesn't actually save any passes and doesn't reduce the number of output tapes
then it's just wasted cpu time to maintain such a large heap. If you have any
clever ideas on how to auto-size the heap based on how many output tapes it
will create or avoid then by all means speak up.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

On Sat, 2008-05-31 at 11:53 -0700, Josh Berkus wrote:
> Simon,
>
> > There is an optimum for each specific sort.
>
> Well, if the optimum is something other than "as much as we can get", then we
> still have a pretty serious issue with work_mem, no?

Depends upon your view of serious I suppose. I would say it is an
acceptable situation, but needs further optimization. I threw some ideas
around on Hackers around Dec/New Year, but I don't have time to work on
this further myself in this dev cycle. Further contributions welcome.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support