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

"Gunnar \"Nick\" Bluth" <gunnar.bluth@xxxxxxxxxxx> · Sun, 04 Nov 2012 09:48:11 +0100

    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.

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