Thread: Re: [HACKERS] Automatically setting work_mem
On Tue, 2006-03-21 at 17:47 -0500, Tom Lane wrote: > I'm fairly unconvinced about Simon's underlying premise --- that we > can't make good use of work_mem in sorting after the run building phase > --- anyway. We can make good use of memory, but there does come a point in final merging where too much is of no further benefit. That point seems to be at about 256 blocks per tape; patch enclosed for testing. (256 blocks per tape roughly doubles performance over 32 blocks at that stage). That is never the case during run building - more is always better. > If we cut back our memory usage Simon inserts the words: "too far" > then we'll be forcing a > significantly more-random access pattern to the temp file(s) during > merging, because we won't be able to pre-read as much at a time. Yes, thats right. If we have 512MB of memory that gives us enough for 2000 tapes, yet the initial runs might only build a few runs. There's just no way that all 512MB of memory is needed to optimise the performance of reading in a few tapes at time of final merge. I'm suggesting we always keep 2MB per active tape, or the full allocation, whichever is lower. In the above example that could release over 500MB of memory, which more importantly can be reused by subsequent sorts if/when they occur. Enclose two patches: 1. mergebuffers.patch allows measurement of the effects of different merge buffer sizes, current default=32 2. reassign2.patch which implements the two kinds of resource deallocation/reassignment proposed. Best Regards, Simon Riggs
Attachment
On Wed, 2006-03-22 at 07:48 +0000, Simon Riggs wrote: > On Tue, 2006-03-21 at 17:47 -0500, Tom Lane wrote: > > > I'm fairly unconvinced about Simon's underlying premise --- that we > > can't make good use of work_mem in sorting after the run building phase > > --- anyway. > > We can make good use of memory, but there does come a point in final > merging where too much is of no further benefit. That point seems to be > at about 256 blocks per tape; patch enclosed for testing. (256 blocks > per tape roughly doubles performance over 32 blocks at that stage). > > That is never the case during run building - more is always better. > > > If we cut back our memory usage > Simon inserts the words: "too far" > > then we'll be forcing a > > significantly more-random access pattern to the temp file(s) during > > merging, because we won't be able to pre-read as much at a time. > > Yes, thats right. > > If we have 512MB of memory that gives us enough for 2000 tapes, yet the > initial runs might only build a few runs. There's just no way that all > 512MB of memory is needed to optimise the performance of reading in a > few tapes at time of final merge. > > I'm suggesting we always keep 2MB per active tape, or the full > allocation, whichever is lower. In the above example that could release > over 500MB of memory, which more importantly can be reused by subsequent > sorts if/when they occur. > > > Enclose two patches: > 1. mergebuffers.patch allows measurement of the effects of different > merge buffer sizes, current default=32 > > 2. reassign2.patch which implements the two kinds of resource > deallocation/reassignment proposed. Missed couple of minor points in patch: reassign3.patch attached ro completely replace reassign2.patch. Recent test results show that with a 512MB test sort we can reclaim 97% of memory during final merge with only a noise level (+2%) increase in overall elapsed time. (Thats just an example, your mileage may vary). So a large query would use and keep about 536MB memory rather than 1536MB. Best Regards, Simon Riggs
Attachment
On Wed, 2006-03-22 at 10:03 +0000, Simon Riggs wrote:
> Recent test results show that with a 512MB test sort we can reclaim
> 97% of memory during final merge with only a noise level (+2%)
> increase in overall elapsed time. (Thats just an example, your mileage
> may vary). So a large query would use and keep about 536MB memory
> rather than 1536MB.
Large performance test output, credit to Ayush Parashar, Greenplum.
We test a very common case for large sorts with high work_mem: High
work_mem significantly reduces the number of runs required, whereas high
work_mem significantly increases MaxTapes, so there will frequently be
the situation that Nruns << MaxTapes and this patch seeks to optimise
the final merge (only) for that case.
elapsed final merge CPU for final merge
with patch 385 s 100.65 s 5.48s/71.05u s
w/o patch 377 s 84.73 s 4.79s/72.32u s
So looking at just the final merge in isolation we have a 19% increase
in elapsed time from a 97% reduction in memory usage (based upon the
assumption that reducing available slots by 97% will lead to an overall
97% reduction in memory usage from slots+tuples). This uses an earlier
result that the optimal merge buffer size for the final merge is 8 times
larger than the overall optimal merge buffer size of 32 blocks; altering
this ratio would bring down elapsed time at the cost of increasing
memory. Using too much memory could also impact overall elapsed time
when we have concurrent users, so the question is should we optimise
resources for the multi-user case or for the single user case? Where is
the right balance point?
Resource usage: (resource usage) multiplied by (time in use)
with patch: 147,000 MB.secs (512 MB fir 285s, then 15MB for 100s)
w/o patch: 189,000 MB.secs (512 MB for 377s)
so overall resource consumption reduced to 77% of current usage, or the
other way up 45% additional users on a throughput basis.
Increase in final merge time is likely due to increased I/O. If this
final merge were input to other nodes in a complex query we may not
consume the tuples at maximum speed, so the additional time might easily
be covered by other actions.
Non final merge test results were within 3% of each other; the patch
doesn't touch that aspect at all, so from that we can say that the test
results are reasonably useful comparison.
- - - -
With patch:
LOG: switching to external sort with 1831 tapes: CPU 2.86s/1.96u sec
elapsed 7.58 sec\
LOG: finished writing run 1 to tape 0: CPU 7.36s/27.67u sec elapsed
42.05 sec\
LOG: finished writing run 2 to tape 1: CPU 12.55s/56.85u sec elapsed
79.78 sec\
LOG: finished writing run 3 to tape 2: CPU 17.88s/86.42u sec elapsed
120.94 sec\
LOG: finished writing run 4 to tape 3: CPU 23.06s/116.46u sec elapsed
159.06 sec\
LOG: finished writing run 5 to tape 4: CPU 28.57s/146.25u sec elapsed
201.59 sec\
LOG: finished writing run 6 to tape 5: CPU 33.76s/176.14u sec elapsed
239.87 sec\
LOG: performsort starting: CPU 38.13s/200.71u sec elapsed 272.83 sec\
LOG: finished writing run 7 to tape 6: CPU 38.23s/204.51u sec elapsed
276.76 sec\
LOG: finished writing final run 8 to tape 7: CPU 38.50s/211.93u sec
elapsed 284.51 sec\
LOG: shrinking resources to 3% (from 4194304 to 146686 slots): CPU
38.52s/211.93u sec elapsed 284.69 sec\
LOG: performsort done (except 8-way final merge): CPU 38.53s/212.00u
sec elapsed 284.85 sec\
LOG: final merge: tape 7 exhausted: CPU 42.70s/270.65u sec elapsed
368.06 sec\
LOG: reassigning resources; each tape gets: +2619 slots, +6770980 mem:
CPU 42.70s/270.70u sec elapsed 368.12 sec\
LOG: final merge: tape 2 exhausted: CPU 43.68s/283.05u sec elapsed
385.00 sec\
LOG: final merge: tape 3 exhausted: CPU 43.68s/283.05u sec elapsed
385.00 sec\
LOG: final merge: tape 5 exhausted: CPU 43.68s/283.05u sec elapsed
385.00 sec\
LOG: final merge: tape 0 exhausted: CPU 43.68s/283.05u sec elapsed
385.00 sec\
LOG: final merge: tape 6 exhausted: CPU 43.68s/283.05u sec elapsed
385.00 sec\
LOG: final merge: tape 1 exhausted: CPU 43.68s/283.05u sec elapsed
385.00 sec\
LOG: final merge: tape 4 exhausted: CPU 43.68s/283.05u sec elapsed
385.00 sec\
LOG: external sort ended, 293182 disk blocks used: CPU 44.01s/283.05u
sec elapsed 385.50 sec\
Without patch:
LOG: switching to external sort with 1873 tapes: CPU 2.72s/2.03u sec
elapsed 7.07 sec\
LOG: finished writing run 1 to tape 0: CPU 7.08s/28.42u sec elapsed
39.96 sec\
LOG: finished writing run 2 to tape 1: CPU 12.10s/58.47u sec elapsed
79.37 sec\
LOG: finished writing run 3 to tape 2: CPU 17.35s/89.39u sec elapsed
120.18 sec\
LOG: finished writing run 4 to tape 3: CPU 22.50s/120.55u sec elapsed
161.24 sec\
LOG: finished writing run 5 to tape 4: CPU 27.84s/151.41u sec elapsed
202.11 sec\
LOG: finished writing run 6 to tape 5: CPU 33.15s/182.57u sec elapsed
243.34 sec\
LOG: performsort starting: CPU 37.53s/208.36u sec elapsed 277.51 sec\
LOG: finished writing run 7 to tape 6: CPU 37.63s/212.03u sec elapsed
281.33 sec\
LOG: finished writing final run 8 to tape 7: CPU 37.87s/219.39u sec
elapsed 288.97 sec\
LOG: performsort done (except 8-way final merge): CPU 38.23s/221.33u
sec elapsed 292.27 sec\
LOG: external sort ended, 293182 disk blocks used: CPU 43.02s/293.65u
sec elapsed 377.00 sec\
On Sat, Mar 25, 2006 at 12:24:00PM +0000, Simon Riggs wrote: > memory. Using too much memory could also impact overall elapsed time > when we have concurrent users, so the question is should we optimise > resources for the multi-user case or for the single user case? Where is > the right balance point? Sounds like what we need is a GUC... I know I certainly have cases where I'll take faster and using more memory over the alternative. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Where are we on this patch? --------------------------------------------------------------------------- Simon Riggs wrote: > On Tue, 2006-03-21 at 17:47 -0500, Tom Lane wrote: > > > I'm fairly unconvinced about Simon's underlying premise --- that we > > can't make good use of work_mem in sorting after the run building phase > > --- anyway. > > We can make good use of memory, but there does come a point in final > merging where too much is of no further benefit. That point seems to be > at about 256 blocks per tape; patch enclosed for testing. (256 blocks > per tape roughly doubles performance over 32 blocks at that stage). > > That is never the case during run building - more is always better. > > > If we cut back our memory usage > Simon inserts the words: "too far" > > then we'll be forcing a > > significantly more-random access pattern to the temp file(s) during > > merging, because we won't be able to pre-read as much at a time. > > Yes, thats right. > > If we have 512MB of memory that gives us enough for 2000 tapes, yet the > initial runs might only build a few runs. There's just no way that all > 512MB of memory is needed to optimise the performance of reading in a > few tapes at time of final merge. > > I'm suggesting we always keep 2MB per active tape, or the full > allocation, whichever is lower. In the above example that could release > over 500MB of memory, which more importantly can be reused by subsequent > sorts if/when they occur. > > > Enclose two patches: > 1. mergebuffers.patch allows measurement of the effects of different > merge buffer sizes, current default=32 > > 2. reassign2.patch which implements the two kinds of resource > deallocation/reassignment proposed. > > Best Regards, Simon Riggs > [ Attachment, skipping... ] [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Fri, 2006-04-21 at 23:07 -0400, Bruce Momjian wrote: > Where are we on this patch? Well the patches work and have been performance tested, with results posted. Again, the title of this thread doesn't precisely describe the patch any longer. The question is do people believe there is benefit in reducing the amount of memory for the final sort phase, and if so, to what level? I still do, for multi-user systems. Releasing unused memory from a large CREATE INDEX will allow that memory to be swapped out, even if the brk point can't be changed. For large queries with multiple sorts the memory can be reused immediately. The patch does sound somewhat obscure and a corner case, I grant you, but the more memory you give a sort the smaller number of runs you are likely to have. So the situation of having enough memory to, say, merge 500 runs at the same time as having less than 10 runs is actually IMHO the common case. Patch now is: "Reducing memory usage in sort final merge phase." [I've also completed Cascade Merge sort ready for unit testing, but will not be completing that for a few weeks yet] -- Simon Riggs EnterpriseDB http://www.enterprisedb.com/
Simon Riggs <simon@2ndquadrant.com> writes:
> I still do, for multi-user systems. Releasing unused memory from a large
> CREATE INDEX will allow that memory to be swapped out, even if the brk
> point can't be changed.
Say what? It can get "swapped out" anyway, whether we free() it or not.
More to the point, though: I don't believe that the proposed patch is a
good idea --- it does not reduce the peak sortmem use, which I think is
the critical factor for a multiuser system, and what it does do is
reduce the locality of access to the sort temp file during the merge
phases. That will definitely have some impact; maybe small, but some;
and I don't see where the benefit comes in.
regards, tom lane
On Sat, Apr 22, 2006 at 01:17:08PM -0400, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > I still do, for multi-user systems. Releasing unused memory from a large > > CREATE INDEX will allow that memory to be swapped out, even if the brk > > point can't be changed. > > Say what? It can get "swapped out" anyway, whether we free() it or not. > > More to the point, though: I don't believe that the proposed patch is a > good idea --- it does not reduce the peak sortmem use, which I think is > the critical factor for a multiuser system, and what it does do is > reduce the locality of access to the sort temp file during the merge > phases. That will definitely have some impact; maybe small, but some; > and I don't see where the benefit comes in. Do we have any info on how long the final phase of a sort typically takes compared to the rest of the sort? If it can take a substantial amount of time, then reducing the memory usage during that time will at least allow the OS to use that memory for caching again. In the future, if we have a better means of controlling sort memory usage, then freeing the memory earlier would also put it back in the pool earlier, which would benefit the multiple concurrent sorts case. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Sat, 2006-04-22 at 13:17 -0400, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > I still do, for multi-user systems. Releasing unused memory from a large > > CREATE INDEX will allow that memory to be swapped out, even if the brk > > point can't be changed. > > Say what? It can get "swapped out" anyway, whether we free() it or not. Of course it can, but if the memory is not actively used by the sort then it will be OK if that happens and fairly likely also. If we actively use the memory for the sort it would is less likely to be swapped out and a bad thing if it did. > More to the point, though: I don't believe that the proposed patch is a > good idea --- it does not reduce the peak sortmem use, which I think is > the critical factor for a multiuser system, I agree peak memory use is the critical factor. There is only one performsort in progress at any one time, though there can be many final merges/retrievals in progress concurrently. If the majority of the memory used by performsoft is released afterwards then it can be made available for subsequent sorts/hashes etc without increasing further the peak mem use. > and what it does do is > reduce the locality of access to the sort temp file during the merge > phases. That will definitely have some impact; maybe small, but some; > and I don't see where the benefit comes in. That I already accept. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com/
On Sat, Apr 22, 2006 at 06:38:53PM +0100, Simon Riggs wrote: > On Sat, 2006-04-22 at 13:17 -0400, Tom Lane wrote: > > Simon Riggs <simon@2ndquadrant.com> writes: > > > I still do, for multi-user systems. Releasing unused memory from a large > > > CREATE INDEX will allow that memory to be swapped out, even if the brk > > > point can't be changed. > > > > Say what? It can get "swapped out" anyway, whether we free() it or not. > > Of course it can, but if the memory is not actively used by the sort > then it will be OK if that happens and fairly likely also. If we > actively use the memory for the sort it would is less likely to be > swapped out and a bad thing if it did. > > > More to the point, though: I don't believe that the proposed patch is a > > good idea --- it does not reduce the peak sortmem use, which I think is > > the critical factor for a multiuser system, > > I agree peak memory use is the critical factor. There is only one > performsort in progress at any one time, though there can be many final > merges/retrievals in progress concurrently. If the majority of the > memory used by performsoft is released afterwards then it can be made > available for subsequent sorts/hashes etc without increasing further the > peak mem use. > > > and what it does do is > > reduce the locality of access to the sort temp file during the merge > > phases. That will definitely have some impact; maybe small, but some; > > and I don't see where the benefit comes in. > > That I already accept. I'd like to add a user perspective: we run dual Opteron servers with 16 Gb of memory and 16 Gb of swap. When we are busy we can have 20 to thirty substantial queries running at one time. It is very common for us to have several sorts and also hash joins running concurrently, some for a minute or two, some for much longer. To avoid running out of swap and triggering the oom killer we have had to reduce work_mem below what we prefer. We could add more swap, but at some point this has diminishing returns. The proposed patch seems as if it would be helpful in our situation. -dg -- David Gould daveg@sonic.net If simplicity worked, the world would be overrun with insects.
On Sat, Apr 22, 2006 at 01:14:42PM -0700, David Gould wrote: > To avoid running out of swap and triggering the oom killer we have > had to reduce work_mem below what we prefer. Dunno about your work_mem, but you can make sure the OOM killer doesn't kill you as follows <http://lwn.net/Articles/104185/>. HTH :) Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
On Sat, Apr 22, 2006 at 01:49:25PM -0700, David Fetter wrote: > On Sat, Apr 22, 2006 at 01:14:42PM -0700, David Gould wrote: > > > To avoid running out of swap and triggering the oom killer we have > > had to reduce work_mem below what we prefer. > > Dunno about your work_mem, but you can make sure the OOM killer > doesn't kill you as follows <http://lwn.net/Articles/104185/>. Or I could run with overcommit turned off, but we like overcommit because things like vaccuum appear to allocate maint_work_mem when they start, so if that is set at say 100 Mb it will allocate 100 Mb even to vacuum a 2 page table. Overcommit lets this sort of thing get by without createing a need for even more swap. -dg -- David Gould daveg@sonic.net If simplicity worked, the world would be overrun with insects.
On Sat, Apr 22, 2006 at 14:20:32 -0700, daveg <daveg@sonic.net> wrote: > On Sat, Apr 22, 2006 at 01:49:25PM -0700, David Fetter wrote: > > On Sat, Apr 22, 2006 at 01:14:42PM -0700, David Gould wrote: > > > > > To avoid running out of swap and triggering the oom killer we have > > > had to reduce work_mem below what we prefer. > > > > Dunno about your work_mem, but you can make sure the OOM killer > > doesn't kill you as follows <http://lwn.net/Articles/104185/>. > > Or I could run with overcommit turned off, but we like overcommit because > things like vaccuum appear to allocate maint_work_mem when they start, so > if that is set at say 100 Mb it will allocate 100 Mb even to vacuum a 2 > page table. Overcommit lets this sort of thing get by without createing > a need for even more swap. I would expect that you would still come out ahead commiting some disk space to swap, that will probably never be used, that allows you to better configure your memory usage.