Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CF5FC37C6CB for ; Mon, 12 Nov 2007 06:18:10 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 47075-05 for ; Mon, 12 Nov 2007 06:18:09 -0400 (AST) Received: from pyrrha.dalibo.net (pyrrha.dalibo.net [212.85.154.72]) by postgresql.org (Postfix) with ESMTP id 370DA37C681 for ; Mon, 12 Nov 2007 06:18:08 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by pyrrha.dalibo.net (Postfix) with ESMTP id A0833578072; Mon, 12 Nov 2007 11:18:06 +0100 (CET) Received: from pyrrha.dalibo.net ([127.0.0.1]) by localhost (pyrrha.dalibo.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id chZKsLPVOeat; Mon, 12 Nov 2007 11:18:06 +0100 (CET) Received: by pyrrha.dalibo.net (Postfix, from userid 1011) id 7CEF0578076; Mon, 12 Nov 2007 11:18:06 +0100 (CET) Received: from [192.168.0.12] (lan31-6-82-238-146-117.fbx.proxad.net [82.238.146.117]) by pyrrha.dalibo.net (Postfix) with ESMTP id 5E081578072; Mon, 12 Nov 2007 11:18:03 +0100 (CET) Message-ID: <47382855.1090902@dalibo.com> Date: Mon, 12 Nov 2007 11:17:57 +0100 From: =?ISO-8859-1?Q?C=E9dric_Villemain?= User-Agent: Mozilla-Thunderbird 2.0.0.6 (X11/20071009) MIME-Version: 1.0 To: Bill Moran CC: "Campbell, Lance" , pgsql-performance@postgresql.org Subject: Re: work_mem and shared_buffers References: <47349F4E.5070204@enterprisedb.com> <20071109150809.e75af5b3.wmoran@collaborativefusion.com> In-Reply-To: <20071109150809.e75af5b3.wmoran@collaborativefusion.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/172 X-Sequence-Number: 27587 Bill Moran a écrit : > On Fri, 9 Nov 2007 12:08:57 -0600 > "Campbell, Lance" wrote: > > >> How do you know when you should up the value of work_mem? Just play >> with the number. Is there a query I could do that would tell me if >> PostgreSql is performing SQL that could use more memory for sorting? >> > > 8.2 and older, it can be difficult to know, and I don't have a specific > recommendation. > > I haven't use it in that context before, but perhaps inotify can be used to catch postgresql usage of temp files. ( http://inotify.aiken.cz/ , http://inotify.aiken.cz/?section=incron&page=about&lang=en ) > 8.3 includes a parameter to log the usage of temporary files by Postgres. > When a sort can't fit in the available memory, it uses a temp file, thus > you could use this new feature to track when sorts don't fit in > work_mem. > >