On Tue, Oct 7, 2014 at 2:40 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
> Robert Haas <robertmhaas@gmail.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. We can tell them
that they have to do it anyway, of course.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company