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

["Gunnar \"Nick\" Bluth" <gunnar.bluth@xxxxxxxxxxx>]

Am 01.11.2012 21:40, schrieb Marcos Ortiz:

Regards, Petr.
Tuning PostgreSQL is not just change the postgresql.conf, it includes more things like:
- the filesystem that you are using
- the kernel version that you using (particularly in Linux systems)
- the tuning to kernel variables
- the type of discs that you are using (SSDs are very fast, like you saw in your iMac system)

On 10/30/2012 02:44 PM, Petr Praus wrote:

I just found one particularly interesting fact: when I perform the same test on my mid-2010 iMac (OSX 10.7.5) also with Postgres 9.2.1 and 16GB RAM, I don't experience the slow down.

Specifically:

set work_mem='1MB';

select ...; // running time is ~1800 ms

set work_mem='96MB';

select ...' // running time is ~1500 ms

When I do exactly the same query (the one from my previous post) with exactly the same data on the server:

I get 2100 ms with work_mem=1MB and 3200 ms with 96 MB.

Just some thoughts (interested in this, once seen a Sybase ASE come close to a halt when we threw a huge lot of SHM at it...).

8 cores, so probably on 2 sockets? What CPU generation?

Both explain outputs show an amount of "read" buffers. Did you warm the caches before testing?

Maybe you're hitting a NUMA issue there? If those reads come from the OS' cache, the scheduler might decide to move your process to a different core (that can access the cache better), then moves it back when you access the SHM segment more (the ~4GB get allocated at startup, so probably "close" to the CPU the postmaster ist running on). A migration to a different cacheline is very expensive.

The temp reads/writes (i.e., the OS cache for the temp files) would probably be allocated close to the CPU requesting the temp file.

Just groping about in the dark though... but the iMac is obviously not affected by this, with one socket/memory channel/cache line.

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
- try reducing work_mem in the session you're testing in (so you have large SHM, but small work mem)

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