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

On 4 November 2012 02:48, Gunnar "Nick" Bluth <gunnar(dot)bluth(at)pro-open(dot)de>wrote:

> Am 03.11.2012 18:19, schrieb Petr Praus:
>
> On 3 November 2012 12:09, Gunnar "Nick" Bluth <gunnar(dot)bluth(at)pro-open(dot)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.
>
>
>>
>>
>> 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.
>
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).

>
> 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".

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