Re: LWLOCK_STATS - Mailing list pgsql-hackers

On Fri, Jan 6, 2012 at 9:29 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Fri, Jan 6, 2012 at 2:24 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> It's been a while since I did any testing with LWLOCK_STATS defined,
>> so I thought it might be about time to do that again and see how
>> things look.  Here's how they looked back in July:
>>
>> http://archives.postgresql.org/pgsql-hackers/2011-07/msg01373.php
>>
>> Here are the results from a test I ran today on latest sources, again
>> on Nate Boley's machine.  Five-minute pgbench run, scale factor 100,
>> permanent tables, my usual config settings.
>
> What was the tps/or and number of transactions?
>
> I assume this was -c80 -j40?

Sorry.  -c 32 -j 32.  tps was 9-10k, but I don't take it too seriously
with LWLOCK_STATS defined, because it has some performance impact.

> Also, do you know what percent of CPU time was spend idle during the test?

Sorry, no.

> If the very little time is spend sleeping on lwlocks (i.e. CPU time
> near 100%), it doesn't matter much how that waiting is distributed.

Well, clearly, there is clearly a pretty big impact, because unlogged
tables are much faster than regular tables.  See for example:

http://archives.postgresql.org/pgsql-hackers/2011-12/msg00095.php

...where the comparable result on slightly older sources are:

8 CLOG buffers, permanent tables: tps = 10025.079556 (including
connections establishing)
32 CLOG buffers, permanent tables: tps = 11247.358762 (including
connections establishing)
8 CLOG buffers, unlogged tables: tps = 16999.301828 (including
connections establishing)
32 CLOG buffers, permanent tables: tps = 19653.023856 (including
connections establishing)

As of today, you get 32 CLOG buffers without patching the source code.That test was also done before commits
d573e239f03506920938bf0be56c868d9c3416da and
0d76b60db4684d3487223b003833828fe9655fe2, which further optimized
ProcArrayLock.

> Also, was there a big difference in tps between LWLOCK_STATS defined
> and not defined (i.e. the overhead of doing the accounting)?

Yes, see notes above.

>> Somewhat depressingly,
>> virtually all of the interesting activity still centers around the
>> same three locks that were problematic back then, which means that -
>> although overall performance has improved quite a bit - we've not
>> really delivered any knockout punches.  Here are the perpetrators:
>
> I don't think that is depressing at all.  Certain locks needs to exist
> to protect certain things, and a benchmark which tests those things is
> going to take those locks rather than some other set of locks.  X
> times faster is still X times faster, even if the bottleneck hasn't
> move to some other part of the code.

True.  What I don't like is: I think we've really only pushed the
bottleneck out a few cores.  Throw a 64-core machine at it and we're
going to have all these same problems over again.  I'd like to find
solutions that change the dynamic in a more fundamental way, so that
we buy a little more.  But I'm not going to complain too much; the
performance gains we've gotten with these techniques are obviously
quite substantial, even though they're not a total solution.

>> ....but we haven't done anything about WALInsertLock and it's
>> therefore grown from just over a third of the blocks to almost half.
>
> But not all blocks are for the same length of time.  Do we know how
> much time is spent blocking?  I've seen some code around that tries to
> instrument that, but on my machine of the time it added a lot of
> overhead so it couldn't be used effectively.  I can try to dig it up
> and update it to git-head if you want to try it and aren't already
> aware of it.  (My code was based on something I found somewhere in
> this list.)

I haven't tried it for reasons of overhead, but I'm aware of the problem.

> Also, I assume this is without the recent "Moving more work outside
> WALInsertLock" applied?

Right.  If we can get that done for 9.2, we'll be cooking with gas -
on my tests that was a big improvement.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company