Re: LWLock Queue Jumping

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