On 3 November 2012 05:31, Gunnar "Nick" Bluth <gunnar.bl...@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.bl...@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 > >