On 1 November 2012 18:25, Gunnar "Nick" Bluth <gunnar.bluth@xxxxxxxxxxx> wrote:
Am 01.11.2012 21:40, schrieb Marcos Ortiz:
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...).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 msset 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.
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?
I did 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.
I made a test with Ubuntu 12.04 VM machine (vmware workstation 4.1.3 on the same iMac) with 4GB memory and shared_buffers=1GB. To my slight surprise, the query is faster on Ubuntu VM machine then on the OSX (~1050ms vs. ~1500ms with work_mem=1MB). This might be caused by effective_io_concurrency which is enabled on Ubuntu but can't be enabled on OSX because postgres does not support it there. The interesting thing is that increasing work_mem to 96MB on Ubuntu slows down the query to about ~1250ms from ~1050ms.
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).
- 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.
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
