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
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.com
PostgreSQL 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.com
PostgreSQL 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.com
PostgreSQL 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