Robert Haas <robertmh...@gmail.com> wrote: > Kevin Grittner <kevin.gritt...@wicourts.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
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
