Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries - Mailing list pgsql-performance

From Petr Praus
Subject Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
Date
Msg-id CACezXZ9SV58CbFM3-MpKGm1z-J0Ho1zERMUBuLH6QhC7RJ5T8Q@mail.gmail.com
Whole thread Raw
In response to Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries  ("Gunnar \"Nick\" Bluth" <gunnar.bluth@pro-open.de>)
List pgsql-performance
On 3 November 2012 05:31, Gunnar "Nick" Bluth <gunnar.bluth@pro-open.de> wrote:
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.
Ah, stupid me :)
 
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?

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.
 

Cheers,
-- 
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bluth@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

pgsql-performance by date:

Previous
From: Petr Praus
Date:
Subject: Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
Next
From: Petr Praus
Date:
Subject: Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries