Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

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(dot)bluth(at)pro-open(dot)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