Обсуждение: Optimizing Read-Only Scalability

Поиск
Список
Период
Сортировка

Optimizing Read-Only Scalability

От
Simon Riggs
Дата:
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



Re: Optimizing Read-Only Scalability

От
Tom Lane
Дата:
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


Re: Optimizing Read-Only Scalability

От
Robert Haas
Дата:
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


Re: Optimizing Read-Only Scalability

От
Tom Lane
Дата:
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


Re: Optimizing Read-Only Scalability

От
Simon Riggs
Дата:
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



Re: Optimizing Read-Only Scalability

От
Robert Haas
Дата:
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


Re: Optimizing Read-Only Scalability

От
Simon Riggs
Дата:
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



Re: Optimizing Read-Only Scalability

От
Josh Berkus
Дата:
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


Re: Optimizing Read-Only Scalability

От
Simon Riggs
Дата:
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



Re: Optimizing Read-Only Scalability

От
"Jignesh K. Shah"
Дата:

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



Re: Optimizing Read-Only Scalability

От
Simon Riggs
Дата:
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

Вложения