Horton

Re: 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

Sergey Koposov <koposov@ast.cam.ac.uk>

View thread
From: Sergey Koposov <koposov@ast.cam.ac.uk>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>, Robert Haas <robertmhaas@gmail.com>, pgsql-hackers@postgresql.org, Stephen Frost <sfrost@snowman.net>
Date: 2012-05-30T20:35:44Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Improve bulk-insert performance by keeping the current target buffer pinned

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/