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

On 3 November 2012 05:31, Gunnar "Nick" Bluth <gunnar(dot)bluth(at)pro-open(dot)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(dot)bluth(at)pro-open(dot)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
>
>