pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed2GB or not 2GB
12+ messages / 8 participants
[nested] [flat]
* 2GB or not 2GB
@ 2008-05-28 23:59 Josh Berkus <josh@agliodbs.com>
0 siblings, 6 replies; 12+ messages in thread
From: Josh Berkus @ 2008-05-28 23:59 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 2GB or not 2GB
@ 2008-05-29 00:04 Steve Crawford <scrawford@pinpointresearch.com>
parent: Josh Berkus <josh@agliodbs.com>
5 siblings, 0 replies; 12+ messages in thread
From: Steve Crawford @ 2008-05-29 00:04 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance
Josh Berkus wrote:
> Folks,
>
> Subsequent to my presentation of the new annotated.conf at pgCon last week,...
Available online yet? At?...
Cheers,
Steve
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 2GB or not 2GB
@ 2008-05-29 00:25 Gregory Stark <stark@enterprisedb.com>
parent: Josh Berkus <josh@agliodbs.com>
5 siblings, 0 replies; 12+ messages in thread
From: Gregory Stark @ 2008-05-29 00:25 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance
"Josh Berkus" <josh@agliodbs.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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 2GB or not 2GB
@ 2008-05-29 01:06 Greg Smith <gsmith@gregsmith.com>
parent: Josh Berkus <josh@agliodbs.com>
5 siblings, 1 reply; 12+ messages in thread
From: Greg Smith @ 2008-05-29 01:06 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 2GB or not 2GB
@ 2008-05-29 02:54 Jignesh K. Shah <J.K.Shah@Sun.COM>
parent: Josh Berkus <josh@agliodbs.com>
5 siblings, 0 replies; 12+ messages in thread
From: Jignesh K. Shah @ 2008-05-29 02:54 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 2GB or not 2GB
@ 2008-05-29 03:01 Jignesh K. Shah <J.K.Shah@Sun.COM>
parent: Greg Smith <gsmith@gregsmith.com>
0 siblings, 0 replies; 12+ messages in thread
From: Jignesh K. Shah @ 2008-05-29 03:01 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 2GB or not 2GB
@ 2008-05-29 15:45 Joshua D. Drake <jd@commandprompt.com>
parent: Josh Berkus <josh@agliodbs.com>
5 siblings, 1 reply; 12+ messages in thread
From: Joshua D. Drake @ 2008-05-29 15:45 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 2GB or not 2GB
@ 2008-05-29 19:50 Magnus Hagander <magnus@hagander.net>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 0 replies; 12+ messages in thread
From: Magnus Hagander @ 2008-05-29 19:50 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: josh@agliodbs.com; pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 2GB or not 2GB
@ 2008-05-31 07:44 Simon Riggs <simon@2ndquadrant.com>
parent: Josh Berkus <josh@agliodbs.com>
5 siblings, 1 reply; 12+ messages in thread
From: Simon Riggs @ 2008-05-31 07:44 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 2GB or not 2GB
@ 2008-05-31 18:53 Josh Berkus <josh@agliodbs.com>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 2 replies; 12+ messages in thread
From: Josh Berkus @ 2008-05-31 18:53 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 2GB or not 2GB
@ 2008-05-31 19:41 Gregory Stark <stark@enterprisedb.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 12+ messages in thread
From: Gregory Stark @ 2008-05-31 19:41 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-performance
"Josh Berkus" <josh@agliodbs.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!
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 2GB or not 2GB
@ 2008-06-01 09:10 Simon Riggs <simon@2ndquadrant.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 12+ messages in thread
From: Simon Riggs @ 2008-06-01 09:10 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2008-06-01 09:10 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-05-28 23:59 2GB or not 2GB Josh Berkus <josh@agliodbs.com>
2008-05-29 00:04 ` Steve Crawford <scrawford@pinpointresearch.com>
2008-05-29 00:25 ` Gregory Stark <stark@enterprisedb.com>
2008-05-29 01:06 ` Greg Smith <gsmith@gregsmith.com>
2008-05-29 03:01 ` Jignesh K. Shah <J.K.Shah@Sun.COM>
2008-05-29 02:54 ` Jignesh K. Shah <J.K.Shah@Sun.COM>
2008-05-29 15:45 ` Joshua D. Drake <jd@commandprompt.com>
2008-05-29 19:50 ` Magnus Hagander <magnus@hagander.net>
2008-05-31 07:44 ` Simon Riggs <simon@2ndquadrant.com>
2008-05-31 18:53 ` Josh Berkus <josh@agliodbs.com>
2008-05-31 19:41 ` Gregory Stark <stark@enterprisedb.com>
2008-06-01 09:10 ` Simon Riggs <simon@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox