Re: [HACKERS] Automatically setting work_mem

On Sat, Apr 22, 2006 at 14:20:32 -0700, daveg <[EMAIL PROTECTED]> 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 redu

Re: [PATCHES] [HACKERS] Automatically setting work_mem

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 su

Re: [PATCHES] [HACKERS] Automatically setting work_mem

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

Re: [PATCHES] [HACKERS] Automatically setting work_mem

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 <[EMAIL PROTECTED]> writes: > > > I still do, for multi-user systems. Releasing unused memory from a large > > > CREATE INDEX will allow that memory to be swapped out, e

Re: [PATCHES] [HACKERS] Automatically setting work_mem

On Sat, 2006-04-22 at 13:17 -0400, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> 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

Re: [PATCHES] [HACKERS] Automatically setting work_mem

On Sat, Apr 22, 2006 at 01:17:08PM -0400, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> 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

Re: [PATCHES] [HACKERS] Automatically setting work_mem

Simon Riggs <[EMAIL PROTECTED]> 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 th

Re: [PATCHES] [HACKERS] Automatically setting work_mem

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

Re: [PATCHES] [HACKERS] Automatically setting work_mem

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 sortin

Re: [PATCHES] [HACKERS] Automatically setting work_mem

On Sat, Mar 25, 2006 at 12:24:00PM +, 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 po

Re: [PATCHES] [HACKERS] Automatically setting work_mem

On Wed, 2006-03-22 at 10:03 +, 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

Re: [HACKERS] Automatically setting work_mem

Tom, On 3/21/06 3:06 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote: > The real problem we are facing with a whole lot of our optimization > issues (not only sorting) is that it's not all that trivial to get > credible experimental results that we can expect will hold up across > a range of usage scena

Re: [PATCHES] [HACKERS] Automatically setting work_mem

On Wed, 2006-03-22 at 07:48 +, 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 u

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

Re: [HACKERS] Automatically setting work_mem

"Luke Lonergan" <[EMAIL PROTECTED]> writes: > Experiment should take but a minute to validate or disprove the hypothesis. Only if you're prepared to trust the results of one experiment on one platform with a not-very-large amount of data. Otherwise it's going to take quite a few minutes ... The

Re: [HACKERS] Automatically setting work_mem

Tom, On 3/21/06 2:47 PM, "Tom Lane" <[EMAIL PROTECTED]> 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. If we cut back our memory usage then we'll be forcing a > significantly mor

Re: [HACKERS] Automatically setting work_mem

Martijn van Oosterhout writes: > There is one way to guarentee the memory is released to the OS after > completion. Make the allocator allocate work_mem bytes using mmap() > rather than malloc(). munmap() will then definitly return the memory to > the OS. Unfortunatly, the coding required would pr

Re: [HACKERS] Automatically setting work_mem

On Tue, Mar 21, 2006 at 08:05:50PM +, Simon Riggs wrote: > > Point 2 is actually a serious flaw in Simon's proposal, because there > > is no portable way to make malloc return freed memory to the OS. Some > > mallocs will do that ... in some cases ... but many simply don't ever > > move the br

Re: [HACKERS] Automatically setting work_mem

On Sat, 2006-03-18 at 13:21 -0800, Luke Lonergan wrote: > In short, faster performance through more aggressive runtime compilation. A > JIT for the database kernel. It's not like I'm on shaky ground here - other > commercial DBMS have done it for over a decade. I think what Luke may be referrin

Re: [HACKERS] Automatically setting work_mem

On Fri, 2006-03-17 at 09:46 -0500, Tom Lane wrote: > "Qingqing Zhou" <[EMAIL PROTECTED]> writes: > > So what's the difference between these two strategy? > > (1) Running time: do they use the same amount of memory? Why option 2 is > > better than 1? > > (2) Idle time: after sort done, option 1 will

Re: [HACKERS] Automatically setting work_mem

Thomas Hallgren wrote: Luke Lonergan wrote: Tom, On 3/17/06 9:59 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote: This would buy what exactly? I guess you didn't read the other 80% of the post. In short, faster performance through more aggressive runtime compilation. A JIT for the database k

Re: [HACKERS] Automatically setting work_mem

Luke Lonergan wrote: Tom, On 3/17/06 9:59 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote: This would buy what exactly? I guess you didn't read the other 80% of the post. In short, faster performance through more aggressive runtime compilation. A JIT for the database kernel. It's not like I'm o

Re: [HACKERS] Automatically setting work_mem

Tom, On 3/17/06 12:18 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote: > One user with ability to enter arbitrary SQL commands can *always* blow > your resource planning away. Blaming such things on work_mem is > seriously misguided. Agreed - that's why we need to split this discussion into the two ca

Re: [HACKERS] Automatically setting work_mem

Tom, On 3/17/06 9:59 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote: > This would buy what exactly? I guess you didn't read the other 80% of the post. In short, faster performance through more aggressive runtime compilation. A JIT for the database kernel. It's not like I'm on shaky ground here - o

Re: [HACKERS] Automatically setting work_mem

"Luke Lonergan" <[EMAIL PROTECTED]> writes: > We've looked at using the open source llvm compiler to create an > intermediate representation of the plan, then generate machine code and > dispatch for execution. This would buy what exactly? regards, tom lane --

Re: [HACKERS] Automatically setting work_mem

Csaba, On 3/17/06 7:07 AM, "Csaba Nagy" <[EMAIL PROTECTED]> wrote: > It worths a look at how apache Derby does with query planning, where a > planned query is actually a compiled Java class, i.e. the executable > byte code which will run to fetch the results, created and compiled by > the planner

Re: [HACKERS] Automatically setting work_mem

On Fri, Mar 17, 2006 at 04:45:17PM -0500, Tom Lane wrote: > Hannu Krosing <[EMAIL PROTECTED]> writes: > > So perhaps we could keep the shaded_work_mem in actual shared memory, > > and alloc it to processes from there ? > > No, that's utterly not reasonable, both from an allocation point of view >

Re: [HACKERS] Automatically setting work_mem

Hannu Krosing <[EMAIL PROTECTED]> writes: > So perhaps we could keep the shaded_work_mem in actual shared memory, > and alloc it to processes from there ? No, that's utterly not reasonable, both from an allocation point of view (you'd have to make shared memory enormous, and not all platforms will

Re: [HACKERS] Automatically setting work_mem

Ühel kenal päeval, R, 2006-03-17 kell 09:46, kirjutas Tom Lane: > "Qingqing Zhou" <[EMAIL PROTECTED]> writes: > > So what's the difference between these two strategy? > > (1) Running time: do they use the same amount of memory? Why option 2 is > > better than 1? > > (2) Idle time: after sort done,

Re: [HACKERS] Automatically setting work_mem

Josh Berkus writes: > Mind you, I'm also thinking that on enterprise installations with > multi-department use of the database, the fact that work_mem is > inalienably USERSET is also an allocation problem. One user with a SET > command can blow all of your resource planning away. One user w

Re: [HACKERS] Automatically setting work_mem

Tom, > My own thoughts about the problems with our work_mem arrangement are > that the real problem is the rule that we can allocate work_mem per sort > or hash operation; this makes the actual total memory use per backend > pretty unpredictable for nontrivial queries. I don't know how to fix > t

Re: [HACKERS] Automatically setting work_mem

> My own thoughts about the problems with our work_mem arrangement are > that the real problem is the rule that we can allocate work_mem per sort > or hash operation; this makes the actual total memory use per backend > pretty unpredictable for nontrivial queries. I don't know how to fix > this th

Re: [HACKERS] Automatically setting work_mem

"Qingqing Zhou" <[EMAIL PROTECTED]> writes: > So what's the difference between these two strategy? > (1) Running time: do they use the same amount of memory? Why option 2 is > better than 1? > (2) Idle time: after sort done, option 1 will return all 1024 to the OS and > 2 will still keep 512? Poin

Re: [HACKERS] Automatically setting work_mem

On Fri, 2006-03-17 at 13:29 +0800, Qingqing Zhou wrote: > "Simon Riggs" <[EMAIL PROTECTED]> wrote > > > Interesting, I understand that shared_work_mem is process-wise, > allocate-when-use, request-may-or-may-not-get-it (as you have pointed out, > this may make planner in a hard situation if we are

Re: [HACKERS] Automatically setting work_mem

"Simon Riggs" <[EMAIL PROTECTED]> wrote > > We would only look at dynamically changing work_mem in those few > restricted cases where we track that against the work_mem limit. If we > hit that limit, we would make a request to the central pool: "Can I be > allotted another 2MB please?" (etc). The

[HACKERS] Automatically setting work_mem

One of the key points influencing performance of certain operations is the amount of memory that is available to them. Sorts, Materialize, Hash Joins and Aggs and hashed subquery plans all want lots of memory. Static allocation of memory is good in some situations, but not in others. In many cases