On Wed, 30 May 2012, Merlin Moncure wrote:
>> How big is idt_match? What if you drop all indexes on idt_match,
>> encouraging all the backends to do hash joins against it, which occur
>> in local memory and so don't have contention?
>
> You just missed his post -- it's only 3G. can you run your 'small'
> working set against 48gb shared buffers?
Just tried 3times and it actually got much worse ~ 70-80 seconds per
query in the parallel setup ( i.e. >10 times slower than the single run)
The oprofile then looks like this:
CPU: Intel Architectural Perfmon, speed 1862 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000
samples % linenr info symbol name
-------------------------------------------------------------------------------
883523 46.3676 (no location information) s_lock 883523 100.000 (no location information) s_lock [self]
-------------------------------------------------------------------------------
303984 15.9532 (no location information) PinBuffer 303984 100.000 (no location information) PinBuffer [self]
The problem is that since there is that variability in times, I don't
really 100% know whether this trend of slow-down with increasing
shared memory is genuine or not.
I've also tried just in case shared_buffers=1G, and it is still very slow
(50 sec).
After that I changed the shared buffers back to 10G and the timings got
back to 25 sec.
Weird...
I still wonder whether there is problem with the way the locking is done
(as referenced in the recent "droping tables slowiness" thread).
Cheers, S
*****************************************************
Sergey E. Koposov, PhD, Research Associate
Institute of Astronomy, University of Cambridge
Madingley road, CB3 0HA, Cambridge, UK
Tel: +44-1223-337-551 Web: http://www.ast.cam.ac.uk/~koposov/