Thread: clog_buffers to 64 in 8.3?
All, Through the User Concurrency Thread on -performance [1], Tom and Jignesh found that our proximate bottleneck on SMP multi-user scaling is clog_buffers. Increasing clog_buffers to 64 improved this scaling by 30% per Jignesh: =================== 8.3+ HOT = did not defer more from 8.2 Numbers and hit the CLOG problem at 1100 users (instead of 1000 for 8.2) 8.3 + HOT + CLOG = Got a 1350 users peak of 137364 txn and it held steady till 1450 before it started dropping.. The Best 8.2 +CLOG patch is at 1250user at 128638 txn.. which at the same users in 8.3 did 131265.. So per user transactions also seems to have improved.. Good but roughly 2% at same user count.. But peak value in terms of scalability the improvement is 6.7% Pristine 8.2 could do about 950 users at 100828 txn: So at same user transactions 8.3+HOT+CLOG gives about 102058 txn = 1.2% while in terms of scalability throughput we get a huge boost of 36.2% So if we get the CLOG patch integrated in 8.3+HOT+CLOG release then overall the gain from our pristine 8.2.4 release will be about 36% out of the box .... =================== So: 1) Is there any potential negative impact to increasing the number of CLOG buffers? 2) Is this a small enough change that we can make it during beta? ---Josh [1] http://archives.postgresql.org/pgsql-performance/2007-07/msg00237.php -- Josh Berkus PostgreSQL @ Sun San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> Through the User Concurrency Thread on -performance [1], Tom and
> Jignesh found that our proximate bottleneck on SMP multi-user scaling
> is clog_buffers.
I don't actually think that what Jignesh is testing is a particularly
realistic scenario, and so I object to making performance decisions on
the strength of that one measurement.
regards, tom lane
Tom, > I don't actually think that what Jignesh is testing is a particularly > realistic scenario, and so I object to making performance decisions on > the strength of that one measurement. What do you mean by "not realistic"? What would be a realistic scenario? -- Josh Berkus PostgreSQL @ Sun San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> Tom,
>> I don't actually think that what Jignesh is testing is a particularly
>> realistic scenario, and so I object to making performance decisions on
>> the strength of that one measurement.
> What do you mean by "not realistic"? What would be a realistic scenario?
The difference between maxing out at 1200 sessions and 1300 sessions
doesn't excite me a lot --- in most environments you'd be well advised
to use many fewer backends and a connection pooler. But in any case
the main point is that this is *one* benchmark on *one* platform. Does
anyone outside Sun even know what the benchmark is, beyond the fact that
it's running a whole lot of sessions?
Also, you should not imagine that boosting NUM_CLOG_BUFFERS has zero
cost. The linear searches used in slru.c start to look pretty
questionable if we want more than a couple dozen buffers. I find it
entirely likely that simply changing the constant would be a net loss
on many workloads.
regards, tom lane
On Thu, 2 Aug 2007, Tom Lane wrote: > I find it entirely likely that simply changing the [NUM_CLOG_BUFFERS] > constant would be a net loss on many workloads. Would it be reasonable to consider changing it to a compile-time option before the 8.3 beta? From how you describe the potential downsides, it sounds to me like something that specific distributors might want to adjust based on their target customer workloads and server scale. That would make it available as a tunable to those aiming at larger systems with enough CPU/memory throughput that the additional overhead of more linear searches is trumped by the reduced potential for locking contention, as appears to be the case in Sun's situation here. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Thu, 2007-08-02 at 12:50 -0400, Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> writes: > > Tom, > >> I don't actually think that what Jignesh is testing is a particularly > >> realistic scenario, and so I object to making performance decisions on > >> the strength of that one measurement. > > > What do you mean by "not realistic"? What would be a realistic scenario? > > The difference between maxing out at 1200 sessions and 1300 sessions > doesn't excite me a lot --- in most environments you'd be well advised > to use many fewer backends and a connection pooler. But in any case > the main point is that this is *one* benchmark on *one* platform. Does > anyone outside Sun even know what the benchmark is, beyond the fact that > it's running a whole lot of sessions? I like Greg Smith's idea to add a parameter, at least for testing. transaction_buffers? > Also, you should not imagine that boosting NUM_CLOG_BUFFERS has zero > cost. The linear searches used in slru.c start to look pretty > questionable if we want more than a couple dozen buffers. I find it > entirely likely that simply changing the constant would be a net loss > on many workloads. Doesn't that just beg the question: why do we have linear searches in slru? The majority of access is going to be to the first 1-3 pages, so adding an array that keeps track of the LRU would be much faster anyhow. We can still scan the whole LRU before doing an I/O. That way we would be able to vary the size of the caches. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
"Simon Riggs" <simon@2ndquadrant.com> writes:
> On Thu, 2007-08-02 at 12:50 -0400, Tom Lane wrote:
>> Also, you should not imagine that boosting NUM_CLOG_BUFFERS has zero
>> cost. The linear searches used in slru.c start to look pretty
>> questionable if we want more than a couple dozen buffers.
> Doesn't that just beg the question: why do we have linear searches in
> slru?
Because with the designed number of buffers, that was much cheaper than
anything smarter would be. If we increase the number of buffers, we can
change to some different algorithm that is less sensitive to the number
of buffers, but it will still be slower to look up a page than it is
now. The reason I'm resisting a stampede to change this parameter is
that no one has done any work to quantify the penalty that will be paid
by scenarios other than Jignesh's one test case.
> The majority of access is going to be to the first 1-3 pages, so
> adding an array that keeps track of the LRU would be much faster anyhow.
Not sure how well that'll play with the desire for lookup operations not
to need exclusive lock (see the comments for SlruRecentlyUsed()).
In any case this is getting pretty darn far away from a one-liner patch.
I think it needs more thought and more testing than we can spare now.
regards, tom lane
Tom, > In any case this is getting pretty darn far away from a one-liner patch. > I think it needs more thought and more testing than we can spare now. I'm still hoping that we can show that a moderate increase (say 24 or 32) has no noticeable effect on other workloads. Haven't had time to run the other workloads yet, though ... -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
On Fri, 2007-08-03 at 18:16 -0400, Tom Lane wrote: > In any case this is getting pretty darn far away from a one-liner patch. > I think it needs more thought and more testing than we can spare now. Agreed. We're much further behind with this release than ever before, so we need to put the cut somewhere. Once the major features are in, we do need a period of secondary/final tuning. For the *next* release, I would like to plan for a Tuning Month somewhere in the release process. That would allow some formal way of handling this kind of thing, 'cos we seem to hit the "its too late for this kind of change" fairly regularly. Also, the more we tune, the more performance regressions occur, so we'll need to start formally checking for those too. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --------------------------------------------------------------------------- Greg Smith wrote: > On Thu, 2 Aug 2007, Tom Lane wrote: > > > I find it entirely likely that simply changing the [NUM_CLOG_BUFFERS] > > constant would be a net loss on many workloads. > > Would it be reasonable to consider changing it to a compile-time option > before the 8.3 beta? From how you describe the potential downsides, it > sounds to me like something that specific distributors might want to > adjust based on their target customer workloads and server scale. That > would make it available as a tunable to those aiming at larger systems > with enough CPU/memory throughput that the additional overhead of more > linear searches is trumped by the reduced potential for locking > contention, as appears to be the case in Sun's situation here. > > -- > * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +