Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TUvsO-0008Sp-SH for pgsql-performance@postgresql.org; Sun, 04 Nov 2012 08:48:17 +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 1TUvsM-0008GM-Ai for pgsql-performance@postgresql.org; Sun, 04 Nov 2012 08:48:16 +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 DBE57100C13A; Sun, 4 Nov 2012 09:48:11 +0100 (CET) Message-ID: <50962BCB.7020709@pro-open.de> Date: Sun, 04 Nov 2012 09:48:11 +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> <50954FAF.3060905@pro-open.de> In-Reply-To: Content-Type: multipart/alternative; boundary="------------090905070509040101020302" X-Pg-Spam-Score: -2.3 (--) X-Archive-Number: 201211/20 X-Sequence-Number: 48386 This is a multi-part message in MIME format. --------------090905070509040101020302 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Am 03.11.2012 18:19, schrieb Petr Praus: > On 3 November 2012 12:09, Gunnar "Nick" Bluth > > wrote: > > 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? > > > I did check that, it's zero. I responded to his message, but my > messages to the mailing list are getting delayed by ~24 hours because > somebody has to always bless them. > > > >> 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 ;-) > > > Well, that's what I always thought too! :-) > So, to sum this up (and make someone more competent bite on it maybe ;-), on your SMP, FSB, "fake-multicore" system all "hash"-related works that potentially switch to different implementations internally (but w/out telling us so) when given more work_mem are slower. I'm pretty sure you're hitting some subtle, memory-access-related cornercase here. The L2 cache of your X7350 CPUs is 2MB, could you run the tests with, say, 1, 2, 4 and 8MB of work_mem and post the results? -- 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 --------------090905070509040101020302 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Am 03.11.2012 18:19, schrieb Petr Praus:
On 3 November 2012 12:09, Gunnar "Nick" Bluth <gunn= ar.bluth@pro-open.de> wrote:
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?

I did check that, it's zero. I responded to his message, but my messages to the mailing list are getting delayed by ~24 hours because somebody has to always bless them.
=C2=A0


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 ;-)

Well, that's what I always thought too! :-)=C2=A0
=C2=A0

So, to sum this up (and make someone more competent bite on it maybe ;-), on your SMP, FSB, "fake-multicore" system all "hash"-related works that potentially switch to different implementations internally (but w/out telling us so) when given more work_mem are slower.

I'm pretty sure you're hitting some subtle, memory-access-related cornercase here.

The L2 cache of your X7350 CPUs is 2MB, could you run the tests with, say, 1, 2, 4 and 8MB of work_mem and post the results?

--=20
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 yo=
u
getting? - Tom Payne
--------------090905070509040101020302--