X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 37A10D7E62 for ; Tue, 22 Nov 2005 10:37:53 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 50821-01 for ; Tue, 22 Nov 2005 14:37:56 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from soufre.accelance.net (soufre.accelance.net [213.162.48.15]) by svr1.postgresql.org (Postfix) with ESMTP id 7C475DAFA6 for ; Tue, 22 Nov 2005 10:37:49 -0400 (AST) Received: from [213.162.49.207] (gs.team.openwide.fr [213.162.49.207]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by soufre.accelance.net (Postfix) with ESMTP id 686FA5D23; Tue, 22 Nov 2005 15:37:53 +0100 (CET) Message-ID: <43832D40.7020309@openwide.fr> Date: Tue, 22 Nov 2005 15:37:52 +0100 From: Guillaume Smet User-Agent: Mozilla Thunderbird 1.0.7-1.1.fc3 (X11/20050929) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Claus Guttesen CC: Andrew Sullivan , pgsql-performance@postgresql.org Subject: Re: weird performances problem References: <437CC21D.8070402@openwide.fr> <20051117221314.GC26696@phlogiston.dyndns.org> <437D13AA.4010309@openwide.fr> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/441 X-Sequence-Number: 15698 Claus and Andrew, Claus Guttesen wrote: > Isn't sort_mem quite high? Remember that sort_mem size is allocated > for each sort, not for each connection. Mine is 4096 (4 MB). My > effective_cache_size is set to 27462. I tested sort mem from 4096 to 32768 (4096, 8192, 16384, 32768) this afternoon and 32768 is definitely the best value for us. We still have free memory using it, we don't have any swap and queries are generally faster (I log all queries taking more than 500ms and the log is growing far faster with lower values of sort_mem). > What OS are you running? # cat /etc/redhat-release CentOS release 3.6 (Final) so it's a RHEL 3 upd 6. # uname -a Linux our.host 2.4.21-37.ELsmp #1 SMP Wed Sep 28 14:05:46 EDT 2005 i686 i686 i386 GNU/Linux # cat /proc/cpuinfo 4x processor : 3 vendor_id : GenuineIntel cpu family : 15 model : 2 model name : Intel(R) Xeon(TM) MP CPU 2.20GHz stepping : 6 cpu MHz : 2192.976 cache size : 512 KB # cat /proc/meminfo total: used: free: shared: buffers: cached: Mem: 3949797376 3478376448 471420928 0 83410944 2679156736 Swap: 4384022528 9797632 4374224896 MemTotal: 3857224 kB MemFree: 460372 kB MemShared: 0 kB Buffers: 81456 kB Cached: 2610764 kB HTH Regards, -- Guillaume