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