On Wed, Oct 20, 2010 at 8:24 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Robert Haas <robertmh...@gmail.com> writes: >> On Wed, Oct 20, 2010 at 10:07 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >>> I'm less than convinced by the hypothesis that most transactions would >>> avoid taking snapshots in this regime, anyway. It would only hold up >>> if there's little locality of reference in terms of which tuples are >>> getting examined/modified by concurrent transactions, and that's a >>> theory that seems much more likely to be wrong than right. > >> There will certainly be workloads where most transactions acquire a >> snapshot, but just to take one obvious example, suppose we have a data >> warehouse where every night we bulk load the day's data, and then we >> run reporting queries all day. Except during the overnight bulk >> loads, there's no concurrent write activity at all, and thus no need >> for snapshots. > > Well, yeah, but in this scenario there's also no contention involved in > taking snapshots --- there are only readers of ProcArray and (IIRC) they > only need shared locks on the array. If you want to make any meaningful > improvement in this area, you need something that solves the ProcArray > access contention caused by a heavy mixed read/write transaction load.
There is certainly some improvement to be had even in eliminating shared ProcArrayLocks, and the work that must be done while holding them. For example, you have to hold an exclusive lock to end a transaction, so that's going to compete with any shared lockers. But suppose we do have a heavy mixed read/write transaction load. Who is to say that requires a snapshot? It will require a snapshot if one transactions reads data that has been recently written by another transaction, but I'm not convinced that'll necessarily happen that often. For example, suppose you have a table with a million records and you have 200 concurrent database connections. Each connection repeatedly starts a transaction where it reads 10 records and then writes 10 records. When a new transaction starts, it overlaps 199 other transactions; if all of those transactions have done all of their reads and writes already, there are 3,980 records in the table for which we'll need a snapshot to determine tuple visibility; assuming we look at no other tuples (all the accesses using index scans rather than sequential scans), the chances that we'll need to take a snapshot are only 1-((1-(3980/1000000))^20) = ~7.7%. That's pretty good, and of course if you have fewer overlapping transactions or fewer operations per transaction it gets better very quickly. Now, of course, if you have a lot of locality of reference, things aren't going to be nearly so good. If we assume that the accesses are spread across only 100,000 records instead of 1,000,000, then each transaction has a better-than-even chance of needing a snapshot. However, in that situation, you're going to have other contention problems, too: there's very significant probability that two backends will actually try to update the same tuple, and one will sleep until the other commits. So maybe the cost of taking snapshots won't be the biggest problem in that case anyway (he said hopefully). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
