Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TUhDb-0000VT-Ei for pgsql-performance@postgresql.org; Sat, 03 Nov 2012 17:09:11 +0000 Received: from pro-open.de ([87.106.136.39] helo=www.pro-open.de) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TUhDY-0003CW-6C for pgsql-performance@postgresql.org; Sat, 03 Nov 2012 17:09:10 +0000 Received: from [192.168.1.9] (ip-176-198-223-42.unitymediagroup.de [176.198.223.42]) by www.pro-open.de (Postfix) with ESMTP id 8358C100C061; Sat, 3 Nov 2012 18:09:04 +0100 (CET) Message-ID: <50954FAF.3060905@pro-open.de> Date: Sat, 03 Nov 2012 18:09:03 +0100 From: "Gunnar \"Nick\" Bluth" User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:16.0) Gecko/20121028 Thunderbird/16.0.2 MIME-Version: 1.0 To: Petr Praus CC: Marcos Ortiz , pgsql-performance@postgresql.org Subject: Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries References:

<5092DE42.5060206@uci.cu> <509304DC.1090809@pro-open.de> <5094F280.6010606@pro-open.de> In-Reply-To: Content-Type: multipart/alternative; boundary="------------030900040901020906030905" X-Pg-Spam-Score: -2.5 (--) X-Archive-Number: 201211/16 X-Sequence-Number: 48382 This is a multi-part message in MIME format. --------------030900040901020906030905 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Am 03.11.2012 16:20, schrieb Petr Praus: > > Your CPUs are indeed pretty oldschool. FSB based, IIRC, not NUMA. > A process migration would be even more expensive there. > Ok, I've actually looked these up now... at the time these were current, I was in the lucky situation to only deal with Opterons. And actually, with these CPUs it is pretty possible that Scott Marlowe's hint (check vm.zone_reclaim_mode) was pointing in the right direction. Did you check that? > Yes, same behaviour. I let the shared_buffers be the default > (which is 8MB). With work_mem 1MB the query runs fast, with 96MB > it runs slow (same times as before). It really seems that the > culprit is work_mem. > > Well, I'm pretty sure that having more work_mem is a good thing (tm) normally ;-) -- Gunnar "Nick" Bluth RHCE/SCLA Mobil +49 172 8853339 Email: gunnar.bluth@pro-open.de __________________________________________________________________________ In 1984 mainstream users were choosing VMS over UNIX. Ten years later they are choosing Windows over UNIX. What part of that message aren't you getting? - Tom Payne --------------030900040901020906030905 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit

Am 03.11.2012 16:20, schrieb Petr Praus:

Your CPUs are indeed pretty oldschool. FSB based, IIRC, not NUMA. A process migration would be even more expensive there.

Ok, I've actually looked these up now... at the time these were current, I was in the lucky situation to only deal with Opterons. And actually, with these CPUs it is pretty possible that Scott Marlowe's hint (check vm.zone_reclaim_mode) was pointing in the right direction. Did you check that?

Yes, same behaviour. I let the shared_buffers be the default (which is 8MB). With work_mem 1MB the query runs fast, with 96MB it runs slow (same times as before). It really seems that the culprit is work_mem.

Well, I'm pretty sure that having more work_mem is a good thing (tm) normally ;-)

-- 
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.  Ten years later
they are choosing Windows over UNIX.  What part of that message aren't you
getting? - Tom Payne

--------------030900040901020906030905--