Re: LWLock Queue Jumping - Mailing list pgsql-hackers

Jeff Janes wrote:
>     ---------- Forwarded message ----------
>     From: Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
>     To: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com
>     <mailto:heikki.linnakangas@enterprisedb.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@2ndquadrant.com <mailto:simon@2ndquadrant.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