Thread: 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.comPostgreSQL Training, Services and Support
Simon Riggs <simon@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
On Thu, May 14, 2009 at 1:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Simon Riggs <simon@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
Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, May 14, 2009 at 1:28 PM, Tom Lane <tgl@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
On Thu, 2009-05-14 at 13:28 -0400, Tom Lane wrote: > Simon Riggs <simon@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.comPostgreSQL Training, Services and Support
On Thu, May 14, 2009 at 1:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Thu, May 14, 2009 at 1:28 PM, Tom Lane <tgl@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
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.comPostgreSQL Training, Services and Support
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
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.comPostgreSQL Training, Services and Support
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
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