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

"Gunnar \"Nick\" Bluth" <gunnar.bluth@xxxxxxxxxxx> · Sat, 03 Nov 2012 11:31:28 +0100

    Am 02.11.2012 17:12, schrieb Petr
      Praus:

      Your CPUs are indeed pretty oldschool. FSB based, IIRC, not NUMA.
      A process migration would be even more expensive there.

             Might be worth to

              - manually pin (with taskset) the session you test this in
              to a particular CPU (once on each socket) to see if the
              times change

          I tested this and it does not seem to have any effect
            (assuming I used taskset correctly but I think so: taskset
            02 psql to pin down to CPU #1 and taskset 01 psql to pin to
            CPU #0).

    Well, that pinned your _client_ to the CPUs, not the server side
    session ;-)

    You'd have to spot for the PID of the new "IDLE" server process and
    pin that using "taskset -p". Also, 01 and 02 are probably cores in
    the same package/socket. Try "lscpu" first and spot for "NUMA node*"
    lines at the bottom. 

    But anyway... let's try something else first:

             - try reducing
              work_mem in the session you're testing in (so you have
              large SHM, but small work mem)

          Did this and it indicates to me that shared_buffers
            setting actually does not have an effect on this behaviour
            as I previously thought it has. It really boils down to
            work_mem: when I set shared_buffers to something large (say
            4GB) and just play with work_mem the problem persists.

    This only confirms what we've seen before. As soon as your work_mem
    permits an in-memory sort of the intermediate result set (which at
    that point in time is where? In the SHM, or in the private memory of
    the backend? I can't tell, tbth), the sort takes longer than when
    it's using a temp file.

    What if you reduce the shared_buffers to your original value and
    only increase/decrease the session's work_mem? Same behaviour? 

    Cheers,

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