Skip site navigation (1) Skip section navigation (2)

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

From: "Gunnar \"Nick\" Bluth" <gunnar(dot)bluth(at)pro-open(dot)de>
To: Petr Praus <petr(at)praus(dot)net>
Cc: Marcos Ortiz <mlortiz(at)uci(dot)cu>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
Date: 2012-11-04 08:48:11
Message-ID: 50962BCB.7020709@pro-open.de (view raw or flat)
Thread:
Lists: pgsql-performance
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 <mailto: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.

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

In response to

Responses

pgsql-performance by date

Next:From: Claudio FreireDate: 2012-11-04 17:32:56
Subject: Re: Constraint exclusion in views
Previous:From: Jeff JanesDate: 2012-11-03 23:53:38
Subject: Re: How to keep queries low latency as concurrency increases

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group