Skip site navigation (1) Skip section navigation (2)

Re: LWLock Queue Jumping

From: Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: LWLock Queue Jumping
Date: 2009-08-30 18:01:36
Message-ID: 4A9ABE80.2070305@kaltenbrunner.cc (view raw or flat)
Thread:
Lists: pgsql-hackers
Jeff Janes wrote:
>     ---------- Forwarded message ----------
>     From: Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc>
>     To: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com
>     <mailto:heikki(dot)linnakangas(at)enterprisedb(dot)com>>
>     Date: Sun, 30 Aug 2009 11:48:47 +0200
>     Subject: Re: LWLock Queue Jumping
>     Heikki Linnakangas wrote:
> 
>         Greg Stark wrote:
> 
>             On Fri, Aug 28, 2009 at 8:07 PM, Simon
>             Riggs<simon(at)2ndquadrant(dot)com <mailto:simon(at)2ndquadrant(dot)com>>
>             wrote:
> 
>                 WALInsertLock is heavily contended and likely always
>                 will be even if we
>                 apply some of the planned fixes.
> 
>             I've lost any earlier messages, could you resend the raw
>             data on which
>             this is based?
> 
> 
>         I don't have any pointers right now, but WALInsertLock does
>         often show
>         up as a bottleneck in write-intensive benchmarks.
> 
> 
>     yeah I recently ran accross that issue with testing concurrent COPY
>     performance:
> 
>     http://www.kaltenbrunner.cc/blog/index.php?/archives/27-Benchmarking-8.4-Chapter-2bulk-loading.html
>     discussed here:
> 
>     http://archives.postgresql.org/pgsql-hackers/2009-06/msg01019.php
> 
> 
> 
> It looks like this is the bulk loading of data into unindexed tables.  
> How good is that as a target for optimization?  I can see several (quite 
> difficult to code and maintain) ways to make bulk loading into unindexed 
> tables faster, but they would not speed up the more general cases. 

well bulk loading into unindexed tables is quite a common workload - 
apart from dump/restore cycles (which we can now do in parallel) a lot 
of analytic workloads are that way.
Import tons of data from various sources every night/weeek/month, index, 
analyze & aggregate, drop again.


>  
> 
>     and (iirc) also here:
> 
>     http://archives.postgresql.org/pgsql-hackers/2009-06/msg01133.php
> 
> 
> 
> I played around a little with this, parallel bulk loads into a 
> unindexed, very skinny table.  If I hacked XLogInsert so that it did 
> nothing but take the WALInsertLock, release it, then return a fake 
> RecPtr, it scaled better but still not very well.  So giant leaps in 
> throughput would need to involve calling XLogInsert less often (or at 
> least taking the WALInsertLock less often).  You could nibble around the 
> edges by tweaking what happens under the WALInsertLock, but I don't 
> think that that will get you big wins by doing that for this case.  But 
> again, how important is this case?  Are bulk loads into skinny unindexed 
> tables the best test-bed for improving XLogInsert?

well you can get similiar looking profiles from other workloads (say 
pgbench) as well. Pretty sure the archives have examples for those as well..


Stefan

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2009-08-30 18:14:00
Subject: Re: clang's static checker report.
Previous:From: Grzegorz JaskiewiczDate: 2009-08-30 17:56:29
Subject: Re: clang's static checker report.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group