Обсуждение: 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@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
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
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@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!
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