Re: lwlock contention with SSI

On October 7, 2014 10:06:25 PM CEST, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Tue, Oct 7, 2014 at 2:40 PM, Kevin Grittner <kgrittn(at)ymail(dot)com>
>wrote:
>>> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>>> About a month ago, I told Kevin Grittner in an off-list
>conversation
>>>> that I'd work on providing him with some statistics about lwlock
>>>> contention under SSI. I then ran a benchmark on a 16-core,
>>>> 64-hardware thread IBM server, testing read-only pgbench
>performance
>>>> at scale factor 300 with 1, 8, and 32 clients (and an equal number
>of
>>>> client threads).
>>>
>>> I hate to say this when I know how much work benchmarking is, but I
>>> don't think any benchmark of serializable transactions has very
>>> much value unless you set any transactions which don't write to
>>> READ ONLY. I guess it shows how a naive conversion by someone who
>>> doesn't read the docs or chooses to ignore the advice on how to get
>>> good performance will perform, but how interesting is that?
>>>
>>> It might be worth getting TPS numbers from the worst-looking test
>>> from this run, but with the read-only run done after changing
>>> default_transaction_read_only = on. Some shops using serializable
>>> transactions set that in the postgresql.conf file, and require that
>>> any transaction which will be modifying data override it.
>>
>> Well, we could do that. But I'm not sure it's very realistic. The
>> pgbench workload is either 100% write or 100% read, but most real
>> work-loads are mixed; say, 95% read, 5% write. If the client
>software
>> has to be responsible for flipping default_transaction_read_only for
>> every write transaction, or just doing BEGIN TRANSACTION READ WRITE
>> and COMMIT around each otherwise-single-statement write transaction,
>> that's a whole bunch of extra server round trips and complexity that
>> most people are not going to want to bother with.
>
>Well, people using serializable transactions have generally opted
>to deal with that rather than using SELECT ... FOR UPDATE, LOCK
>TABLE, etc. There's no free lunch, and changing BEGIN to BEGIN
>TRANSACTION READ WRITE for those transactions which are expected to
>write data is generally a lot less bother than the other.

Then it really shouldn't have supplanted the old pseudo serializable (aka repeatable read).

There's software where something like this is easy. But I think it's not that largely overlapping with the set of devs where serializable is the easier way.

Andres

---
Please excuse brevity and formatting - I am writing this on my mobile phone.