Skip site navigation (1) Skip section navigation (2)

Re: [PATCHES] Automatically setting work_mem

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Luke Lonergan <llonergan(at)greenplum(dot)com>,pgsql-patches(at)postgresql(dot)org,Martijn van Oosterhout <kleptog(at)svana(dot)org>,Qingqing Zhou <zhouqq(at)cs(dot)toronto(dot)edu>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [PATCHES] Automatically setting work_mem
Date: 2006-03-22 10:03:34
Message-ID: 1143021814.24487.571.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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: reassign3.patch
Description: text/x-patch (16.7 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Jim C. NasbyDate: 2006-03-22 12:25:45
Subject: Re: Poor performance o
Previous:From: Martijn van OosterhoutDate: 2006-03-22 09:17:18
Subject: Re: Modular Type Libraries: was A real currency type

pgsql-patches by date

Next:From: Jim C. NasbyDate: 2006-03-22 12:47:32
Subject: Re: WAL logging of SELECT ... INTO command
Previous:From: Simon RiggsDate: 2006-03-22 07:48:19
Subject: Re: Automatically setting work_mem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group