Re: 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: Automatically setting work_mem
Date: 2006-03-22 07:48:19
Message-ID: 1143013699.24487.553.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

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 Content-Type Size
reassign2.patch text/x-patch 15.8 KB
mergebuffers.patch text/x-patch 3.1 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Martijn van Oosterhout 2006-03-22 09:17:18 Re: Modular Type Libraries: was A real currency type
Previous Message Dhanaraj M - Sun Microsystems 2006-03-22 06:52:04 How to put back??

Browse pgsql-patches by date

  From Date Subject
Next Message Simon Riggs 2006-03-22 10:03:34 Re: [PATCHES] Automatically setting work_mem
Previous Message Qingqing Zhou 2006-03-22 06:20:39 Re: WAL logging of SELECT ... INTO command