Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TVoz3-0004tH-NU for pgsql-performance@postgresql.org; Tue, 06 Nov 2012 19:38:50 +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 1TVoz1-0001Qc-Tw for pgsql-performance@postgresql.org; Tue, 06 Nov 2012 19:38:49 +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 0B89B100A0A4; Tue, 6 Nov 2012 20:38:46 +0100 (CET) Message-ID: <50996746.5010508@pro-open.de> Date: Tue, 06 Nov 2012 20:38:46 +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, klaussfreire@gmail.com, jeff.janes@gmail.com 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> <50962BCB.7020709@pro-open.de> In-Reply-To: Content-Type: multipart/alternative; boundary="------------030901050307090302080602" X-Pg-Spam-Score: -2.3 (--) X-Archive-Number: 201211/70 X-Sequence-Number: 48436 This is a multi-part message in MIME format. --------------030901050307090302080602 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Am 06.11.2012 18:38, schrieb Petr Praus: > > Yes, but note that this happens only in Linux. Increasing work_mem on > my iMac increases performance (but the queries are slower under OSX > than on virtualized Ubuntu on the same machine). Over the weekend, I > tried the same test on my Ubuntu home machine with Ivy Bridge i5 3570K > and it also slows down (from ~900ms with work_mem=1MB to ~1200ms with > work_mem=96MB). OS X is rather different from a memory access point of view, IIRC. So the direct comparison actually only shows how well the Linux FS cache works (for the temp files created with small work_mem ;-). The i5 puzzles me a bit though... > > 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? > > I made a pgbench test with the same query and run it 25 times (5 > clients, 5 transactions each): > work_mem speed > 1MB 1794ms > 2MB 1877ms > 4MB 2084ms > 8MB 2141ms > 10MB 2124ms > 12MB 3018ms > 16MB 3004ms > 32MB 2999ms > 64MB 3015ms > > It seems that there is some sort of "plateau". Two, afaics. The 1->2 change hints towards occasionally breaching your L2 cache, so it can probably be ignored. The actual plateaus thus seem to be 0-2, 2-12, >= 12. It'd be interesting to see the EXPLAIN ANALYSE outputs for these levels, the buckets and batches in particular. I'd reckon we'll see significant changes at 2->4 and 10->12MB work_mem. > 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. See other post... it actually does tell us (# of buckets/batches). However, the result is not good and could potentially be improved be twealing the statistic_targets of the joined tables/columns. I wonder why noone actually understanding the implementation chipped in yet... Andres, Greg, Tom, whoever actually understands what's happening here, anyone reading this? ;-) Cheers, -- 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 --------------030901050307090302080602 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Am 06.11.2012 18:38, schrieb Petr Praus:

Yes, but note that this happens only in Linux. Increasing work_mem on my iMac increases performance (but the queries are slower under OSX than on virtualized Ubuntu on the same machine). Over the weekend, I tried the same test on my Ubuntu home machine with Ivy Bridge i5 3570K and it also slows down (from ~900ms with work_mem=3D1MB to ~1200ms with work_mem=3D96M= B).

OS X is rather different from a memory access point of view, IIRC. So the direct comparison actually only shows how well the Linux FS cache works (for the temp files created with small work_mem ;-).

The i5 puzzles me a bit though...


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?
I made a pgbench test with the same query and run it 25 times (5 clients, 5 transactions each):
work_mem =C2=A0 speed<= /font>
1MB =C2=A0 =C2=A0 =C2=A0= =C2=A01794ms
2MB =C2=A0 =C2=A0 =C2=A0= =C2=A01877ms
4MB =C2=A0 =C2=A0 =C2=A0= =C2=A02084ms
8MB =C2=A0 =C2=A0 =C2=A0= =C2=A02141ms
10MB =C2=A0 =C2=A0 =C2= =A0 2124ms
12MB =C2=A0 =C2=A0 =C2= =A0 3018ms
16MB =C2=A0 =C2=A0 =C2= =A0 3004ms
32MB =C2=A0 =C2=A0 =C2= =A0 2999ms
64MB =C2=A0 =C2=A0 =C2= =A0 3015ms

It seems that there is some sort of "plateau".
Two, afaics. The 1->2 change hints towards occasionally breaching your L2 cache, so it can probably be ignored. The actual plateaus thus seem to be 0-2, 2-12, >=3D 12.
It'd be interesting to see the EXPLAIN ANALYSE outputs for these levels, the buckets and batches in particular. I'd reckon we'll see significant changes at 2->4 and 10->12MB work_mem.

So, to sum this up (and m= ake 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.
See other post... it actually does tell us (# of buckets/batches). However, the result is not good and could potentially be improved be twealing the statistic_targets of the joined tables/columns.

I wonder why noone actually understanding the implementation chipped in yet... Andres, Greg, Tom, whoever actually understands what's happening here, anyone reading this? ;-)

Cheers,
--=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
--------------030901050307090302080602--