X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 12F0ED1BB64 for ; Mon, 16 Feb 2004 02:51:18 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 66365-02 for ; Sun, 15 Feb 2004 22:51:18 -0400 (AST) Received: from morework.geizhals.at (home.geizhals.at [213.229.14.34]) by svr1.postgresql.org (Postfix) with ESMTP id 3D1FFD1B4C0 for ; Sun, 15 Feb 2004 22:51:14 -0400 (AST) Received: from localhost (morework [127.0.0.1]) by morework.geizhals.at (Postfix) with ESMTP id 2034B52413D; Mon, 16 Feb 2004 03:51:00 +0100 (CET) Received: from morework.geizhals.at ([127.0.0.1]) by localhost (morework [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 03201-06; Mon, 16 Feb 2004 03:50:59 +0100 (CET) Received: from geizhals.at (chello080110242194.117.11.tuwien.teleweb.at [80.110.242.194]) by morework.geizhals.at (Postfix) with ESMTP id D2E1D52413A; Mon, 16 Feb 2004 03:50:59 +0100 (CET) Message-ID: <4030309B.7090807@geizhals.at> Date: Mon, 16 Feb 2004 03:53:15 +0100 From: "Marinos J. Yannikos" User-Agent: Mozilla Thunderbird 0.5b (Windows/20040201) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Jeff Trout Cc: pgsql-performance@postgresql.org Subject: Re: optimization ideas for frequent, large(ish) updates References: <402C213A.4030105@geizhals.at> <200402122228.41680.josh@agliodbs.com> <402ED348.7000800@geizhals.at> <460CEC02-5FDB-11D8-8142-000D9366F0C4@jefftrout.com> In-Reply-To: <460CEC02-5FDB-11D8-8142-000D9366F0C4@jefftrout.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new-20030616-p5 at geizhals.at X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/168 X-Sequence-Number: 5692 Jeff Trout wrote: > Remember that it is going to allocate 800MB per sort. It is not "you > can allocate up to 800MB, so if you need 1 meg, use one meg". Some > queries may end up having a few sort steps. I didn't know that it always allocates the full amount of memory specificed in the configuration (e.g. the annotated configuration guide says: "Note that for a complex query, several sorts might be running in parallel, and each one _will be allowed to use_ as much memory as this value specifies before it starts to put data into temporary files."). The individual postgres processes don't look like they're using the full amount either (but that could be because the memory isn't written to). > In terms of sort mem it is best to set a system default to a nice good > value for most queries. and then in your reporting queries or other > ones set sort_mem for that session (set sort_mem = 800000) then only > that session will use the looney sort_mem Queries from the web front-end use up to ~130MB sort memory (according to pgsql_tmp), so I set this to 150MB - thanks. > It would be interesting to know if your machine is swapping. It's not being monitored closely (other than with the occasional "top"), but it's highly unlikely: Mem: 12441864k total, 10860648k used, 1581216k free, 84552k buffers Swap: 4008176k total, 2828k used, 4005348k free, 9762628k cached (that's a typical situation - the "2828k used" are probably some rarely used processes that have lower priority than the cache ...) Regards, Marinos