Re: LWLOCK_STATS

On Fri, Jan 6, 2012 at 9:29 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Fri, Jan 6, 2012 at 2:24 PM, Robert Haas <robertmhaas(at)gmail(dot)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