X-Original-To: pgsql-performance@postgresql.org Received: from mail2.fbab.net (spectre.fbab.net [212.214.165.139]) by svr1.postgresql.org (Postfix) with SMTP id 083015E473E for ; Tue, 24 Aug 2004 11:08:33 -0300 (ADT) Received: (qmail 3500 invoked by uid 46); 24 Aug 2004 14:08:37 -0000 Received: from 212.214.165.129 by mail2.fbab.net (envelope-from , uid 133) with qmail-scanner-1.23 (clamdscan: 0.75.1. Clear:RC:0(212.214.165.129):. Processed in 0.088411 secs); 24 Aug 2004 14:08:37 -0000 Received: from unknown (HELO ?10.10.12.248?) (magpool1@212.214.165.129) by mail2.fbab.net with SMTP; 24 Aug 2004 14:08:37 -0000 Message-ID: <412B4BE1.8080808@fbab.net> Date: Tue, 24 Aug 2004 16:08:33 +0200 From: "Magnus Naeslund(t)" User-Agent: Mozilla Thunderbird 0.7.2 (Windows/20040707) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Stef Cc: pgsql-performance@postgresql.org Subject: Re: Query kills machine. References: <20040824103240.4d909777@svb.ucs.co.za> <412B05A2.8070301@familyhealth.com.au> <20040824153014.2a11d228@svb.ucs.co.za> In-Reply-To: <20040824153014.2a11d228@svb.ucs.co.za> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200408/327 X-Sequence-Number: 7963 Stef wrote: > Christopher Kings-Lynne mentioned : > => sort_mem = 4096 > > Reducing sort_mem to 4096 seems to make it run in a reasonable time > again. Any idea why? The database does a whole lot of huge sorts > every day, so I thought upping this parameter would help. > > A couple of queries do seem to run slower now that I reduced > the sort_mem. > > The shared buffers still makes a significant difference when I increase it. > Well you have to take in account that sort_mem is not the total memory allocated for sorting but per connection and in complex expressions serveral times that too. So if you sort a lot it can push your operating system off the cliff and it might start reaping things that shouldn't be reaped and start swapping. If that happens _everything_ on that box will get slow... Shared buffers on the other hand is only allocated once. Regards, Magnus