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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 4 November 2012 02:48, Gunnar "Nick" Bluth <gunnar.bluth@xxxxxxxxxxx> wrote:
Am 03.11.2012 18:19, schrieb Petr Praus:
On 3 November 2012 12:09, Gunnar "Nick" Bluth <gunnar.bluth@xxxxxxxxxxx> 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.bluth@xxxxxxxxxxx
__________________________________________________________________________
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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux