Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> Did these transactions write anything? If not, were they
>> declared to be READ ONLY? If they were, in fact, only reading,
>> it would be interesting to see what the performance looks like if
>> the recommendation to use the READ ONLY attribute is followed.
>
> pgbench -S doesn't do any writes, or issue any transaction control
> statements. It just fires off SELECT statements against a single
> table as fast as it can, retrieving values from rows chosen at
> random. Each SELECT implicitly begins and ends a transaction.
So that test could be accomplished by setting
default_transaction_read_only to on. That's actually what we're
doing, because we have a lot more of them than of read-write
transactions. But, with the scripts I can confirm the performance
of that on this end. It should be indistinguishable from the
repeatable read line; if not, there's something to look at there.
> Possibly the system could gaze upon the SELECT statement and infer
> that the one-statement transaction induced thereby can't possibly
> write any tuples, and mark it read-only automatically, but I'm
> actually not that excited about that approach
I wasn't intending to suggest that. In fact I hadn't really thought
of it. It might be a fun optimization, although it would be well
down my list, and it wouldn't be trivial because you couldn't use if
for any statements with volatile functions -- so the statement would
need to be planned far enough to know whether that was the case
before making this decision. In fact, I'm not sure the community
would want to generate an error if a user marked a function other
than volatile and ran it in this way. Definitely not something to
even look at any time soon.
> trying to fix the lwlock contention that's causing the headache in
> the first place seems like a better use of time, assuming it's
> possible to make some headway there.
Absolutely. I just thought the timings with READ ONLY would make
for an interesting data point. For one thing, it might reassure
people that even this artificial use cases doesn't perform that
badly if the advice in the documentation is heeded. For another, a
result slower than repeatable read would be a surprise that might
point more directly to the problem.
> My general observation is that, on this machine, a lightweight
> lock that is taken in exclusive mode by a series of lockers in
> quick succession seems to max out around 16-20 clients, and the
> curve starts to bend well before that.
OK, I will keep that in mind.
Thanks,
-Kevin