Re: [HACKERS] Optimizing Read-Only Scalability
On Sat, 2009-05-16 at 23:36 -0400, Jignesh K. Shah wrote:
Simon Riggs wrote:
So we can optimize away the scan through the procarray by doing two if
tests, one outside of the lock, one inside. In normal running, both will
be optimized away, though in read-only periods we would avoid much work.
How much work would it be to work up a test patch?
Not much. The most important thing is a place to test it and access to
detailed feedback. Let's see if Dimitri does this.
There are some other tuning aspects to be got right first also, but
those are already known.
I would be interested in testing it out.. I have been collecting some
sysbench read-scalability numbers and some other numbers that I can cook
up with dbt3 , igen.. So I have a frame of reference on those numbers ..
I am sure we can always use some extra performance.
I've added
shared_buffer_partitions = 16..256
plus the GetSnapshotData optimization discussed. I expect the buffer
locks to be the dominant problem.
Together, they should improve RO scalability at high end.
Note this renumbers LWlocks from current value of FirstLockMgrLock
onwards, which may skew the Dtrace reports.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
Index: src/backend/storage/ipc/procarray.c
===
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/storage/ipc/procarray.c,v
retrieving revision 1.49
diff -c -r1.49 procarray.c
*** src/backend/storage/ipc/procarray.c 4 Apr 2009 17:40:36 - 1.49
--- src/backend/storage/ipc/procarray.c 17 May 2009 19:46:05 -
***
*** 729,804
/* initialize xmin calculation with xmax */
globalxmin = xmin = xmax;
! /*
! * Spin over procArray checking xid, xmin, and subxids. The goal is to
! * gather all active xids, find the lowest xmin, and try to record
! * subxids.
! */
! for (index = 0; index arrayP-numProcs; index++)
{
- volatile PGPROC *proc = arrayP-procs[index];
- TransactionId xid;
-
- /* Ignore procs running LAZY VACUUM */
- if (proc-vacuumFlags PROC_IN_VACUUM)
- continue;
-
- /* Update globalxmin to be the smallest valid xmin */
- xid = proc-xmin; /* fetch just once */
- if (TransactionIdIsNormal(xid)
- TransactionIdPrecedes(xid, globalxmin))
- globalxmin = xid;
-
- /* Fetch xid just once - see GetNewTransactionId */
- xid = proc-xid;
-
/*
! * If the transaction has been assigned an xid xmax we add it to the
! * snapshot, and update xmin if necessary. There's no need to store
! * XIDs = xmax, since we'll treat them as running anyway. We don't
! * bother to examine their subxids either.
! *
! * We don't include our own XID (if any) in the snapshot, but we must
! * include it into xmin.
*/
! if (TransactionIdIsNormal(xid))
{
! if (TransactionIdFollowsOrEquals(xid, xmax))
continue;
- if (proc != MyProc)
- snapshot-xip[count++] = xid;
- if (TransactionIdPrecedes(xid, xmin))
- xmin = xid;
- }
! /*
! * Save subtransaction XIDs if possible (if we've already overflowed,
! * there's no point). Note that the subxact XIDs must be later than
! * their parent, so no need to check them against xmin. We could
! * filter against xmax, but it seems better not to do that much work
! * while holding the ProcArrayLock.
! *
! * The other backend can add more subxids concurrently, but cannot
! * remove any. Hence it's important to fetch nxids just once. Should
! * be safe to use memcpy, though. (We needn't worry about missing any
! * xids added concurrently, because they must postdate xmax.)
! *
! * Again, our own XIDs are not included in the snapshot.
! */
! if (subcount = 0 proc != MyProc)
! {
! if (proc-subxids.overflowed)
! subcount = -1; /* overflowed */
! else
{
! int nxids = proc-subxids.nxids;
! if (nxids 0)
{
! memcpy(snapshot-subxip + subcount,
! (void *) proc-subxids.xids,
! nxids * sizeof(TransactionId));
! subcount += nxids;
}
}
}
--- 729,808
/* initialize xmin calculation with xmax */
globalxmin = xmin = xmax;
! /* If our snapshot is new, or snapshot has changed, re-calculate */
! if (snapshot-xmax != xmax || snapshot-xmin != snapshot-xmax)
{
/*
! * Spin over procArray checking xid, xmin, and subxids. The goal is to
! * gather all active xids, find the lowest xmin, and try to record
! * subxids.
*/
! for (index = 0; index arrayP-numProcs; index++)
{
! volatile PGPROC *proc = arrayP-procs[index];
! TransactionId xid;
!
! /* Ignore procs running LAZY VACUUM */
! if (proc-vacuumFlags PROC_IN_VACUUM)
continue;
! /* Update globalxmin to be the smallest valid xmin */
! xid = proc-xmin; /* fetch just once */
! if
Re: [HACKERS] Optimizing Read-Only Scalability
Simon Riggs wrote: On Thu, 2009-05-14 at 16:21 -0700, Josh Berkus wrote: So we can optimize away the scan through the procarray by doing two if tests, one outside of the lock, one inside. In normal running, both will be optimized away, though in read-only periods we would avoid much work. How much work would it be to work up a test patch? Not much. The most important thing is a place to test it and access to detailed feedback. Let's see if Dimitri does this. There are some other tuning aspects to be got right first also, but those are already known. I would be interested in testing it out.. I have been collecting some sysbench read-scalability numbers and some other numbers that I can cook up with dbt3 , igen.. So I have a frame of reference on those numbers .. I am sure we can always use some extra performance. Regards, Jignesh -- Jignesh Shah http://blogs.sun.com/jkshah The New Sun Microsystems,Inc http://sun.com/postgresql -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing Read-Only Scalability
On Thu, 2009-05-14 at 16:21 -0700, Josh Berkus wrote: So we can optimize away the scan through the procarray by doing two if tests, one outside of the lock, one inside. In normal running, both will be optimized away, though in read-only periods we would avoid much work. How much work would it be to work up a test patch? Not much. The most important thing is a place to test it and access to detailed feedback. Let's see if Dimitri does this. There are some other tuning aspects to be got right first also, but those are already known. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Optimizing Read-Only Scalability
In a thread on -perform it has been observed that our Read-Only scalability is not as good as it could be. One problem being that we need to scan the whole of the ProcArray to derive a snapshot, which becomes the dominant task with many users. If we think about a situation where write transactions happen infrequently, then the likelihood is that we end up with xmin==xmax most of the time. If our last snapshot had xmin=xmax and the xmax hasn't changed since our last snapshot then we don't need to scan the procarray at all, just look at the header. So we can optimize away the scan through the procarray by doing two if tests, one outside of the lock, one inside. In normal running, both will be optimized away, though in read-only periods we would avoid much work. We don't need to change the API to GetSnapshotData since the snapshot is statically allocated and unless newly created will contain the last snapshot's data. Interesting? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing Read-Only Scalability
Simon Riggs si...@2ndquadrant.com writes: In a thread on -perform it has been observed that our Read-Only scalability is not as good as it could be. One problem being that we need to scan the whole of the ProcArray to derive a snapshot, which becomes the dominant task with many users. GetSnapshotData doesn't take an exclusive lock. Neither does start or end of a read-only transaction. AFAIK there is no reason, and certainly no shred of experimental evidence, to think that ProcArrayLock contention is the bottleneck for read-only scenarios. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing Read-Only Scalability
On Thu, May 14, 2009 at 1:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: In a thread on -perform it has been observed that our Read-Only scalability is not as good as it could be. One problem being that we need to scan the whole of the ProcArray to derive a snapshot, which becomes the dominant task with many users. GetSnapshotData doesn't take an exclusive lock. Neither does start or end of a read-only transaction. AFAIK there is no reason, and certainly no shred of experimental evidence, to think that ProcArrayLock contention is the bottleneck for read-only scenarios. I think Simon's point was that it is O(n) rather than O(1), not that it took an exclusive lock. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing Read-Only Scalability
Robert Haas robertmh...@gmail.com writes: On Thu, May 14, 2009 at 1:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: GetSnapshotData doesn't take an exclusive lock. Neither does start or end of a read-only transaction. AFAIK there is no reason, and certainly no shred of experimental evidence, to think that ProcArrayLock contention is the bottleneck for read-only scenarios. I think Simon's point was that it is O(n) rather than O(1), not that it took an exclusive lock. I think my point was that there's no evidence that GetSnapshotData is where the scalability issue is. Without some evidence there's no point in kluging it up. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing Read-Only Scalability
On Thu, 2009-05-14 at 13:28 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: In a thread on -perform it has been observed that our Read-Only scalability is not as good as it could be. One problem being that we need to scan the whole of the ProcArray to derive a snapshot, which becomes the dominant task with many users. GetSnapshotData doesn't take an exclusive lock. Neither does start or end of a read-only transaction. AFAIK there is no reason, and certainly no shred of experimental evidence, to think that ProcArrayLock contention is the bottleneck for read-only scenarios. I agree completely; I didn't mention the ProcArrayLock at all... I did mention scanning the procarray itself, which is likely too big to fit in on-chip cache and so must be re-read from main RAM each time. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing Read-Only Scalability
On Thu, May 14, 2009 at 1:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, May 14, 2009 at 1:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: GetSnapshotData doesn't take an exclusive lock. Neither does start or end of a read-only transaction. AFAIK there is no reason, and certainly no shred of experimental evidence, to think that ProcArrayLock contention is the bottleneck for read-only scenarios. I think Simon's point was that it is O(n) rather than O(1), not that it took an exclusive lock. I think my point was that there's no evidence that GetSnapshotData is where the scalability issue is. Without some evidence there's no point in kluging it up. Sure. I don't think anyone was proposing to commit something without first testing it. Supposing that the patch can be shown to improve performance for all-read-only workloads, and supposing further that the patch can be shown to have no material negative impact on write-heavy workloads, it would also be interesting to throw in a bit of scattered write traffic and see whether that completely negates the benefit or not. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing Read-Only Scalability
On Thu, 2009-05-14 at 14:06 -0400, Robert Haas wrote: Supposing that the patch can be shown to improve performance for all-read-only workloads, and supposing further that the patch can be shown to have no material negative impact on write-heavy workloads, it would also be interesting to throw in a bit of scattered write traffic and see whether that completely negates the benefit or not. If you have a workload consisting of high volume single/few row lookups (OLRP), then ISTM that the majority of data cache line accesses will be on the procarray, especially so when we have many sessions. More to the point, MySQL would not need to access an equivalent data structure and so Postgres would access much more memory. The way I understand it, typically 4 CPUs at a time will be able to access that memory at the same time. If they can skip that part entirely, then we will get better scalability. Anyway, I'd be indebted to anyone that can shed more light on the hardware technical details in my above paragraphs. We'll learn something either way. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing Read-Only Scalability
Simon, So we can optimize away the scan through the procarray by doing two if tests, one outside of the lock, one inside. In normal running, both will be optimized away, though in read-only periods we would avoid much work. How much work would it be to work up a test patch? -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
