Re: patch: improve SLRU replacement algorithm - Mailing list pgsql-hackers

On Thu, Apr 5, 2012 at 12:30 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>> I'm not sure I find those numbers all that helpful, but there they
>> are.  There are a couple of outliers beyond 12 s on the patched run,
>> but I wouldn't read anything into that; the absolute worst values
>> bounce around a lot from test to test.  However, note that every
>> bucket between 2s and 8s improves, sometimes dramatically.
>
> However, if it "improved" a bucket by pushing the things out of it
> into a higher bucket, that is not really an improvement.  At 8 seconds
> *or higher*, for example, it goes from 88 things in master to 90
> things in the patch.
>
> Maybe something like a Kaplan-Meier survival curve analysis would be
> the way to go (where a long transaction "survival" is bad).  But
> probably overkill.

I think we are well into the realm of overkill here.  At ten thousand
feet, this patch is preventing us from having every backend wait on an
I/O that only one of those backends actually cares about.  While it
possible that, in some set of circumstances, a bizarre behavior of
that type could happen to work out well, we are not in the business of
coding stupid algorithms and hoping that a meteor hits us and makes
them work out to be wins.  We should not be too afraid of changing
behavior that is obviously wrong, and this is an example of that.
I've presented extensive data showing that, in fact, this patch
eliminates exactly the behavior that it is intended to eliminate
without causing regressions elsewhere.

The fact that there is some criteria that you can apply to the results
of those tests where the patch doesn't win is unsurprising.  If you
slice data that contains a large amount of random noise in enough
different ways, one of them will almost certainly show a regression;
that's what "noise" is.  Yes, it's true that if you look at the number
of transactions that stall for >= 8s, the patch appears to be a very
slight loss.  But first, this is only one trial.  A second run would
produce different numbers that might be better or worse.  And second,
if you picked >= 7s, then the run with the patch would win by about as
much as it loses in the >= 8 s test.   If you pick >= 11 s, it wins by
a lot.

> What were full_page_writes and wal_buffers set to for these runs?

on, 16MB.

>> It's worth
>> keeping in mind here that the system is under extreme I/O strain on
>> this test, and the kernel responds by forcing user processes to sleep
>> when they try to do I/O.
>
> Should the tests be dialed back a bit so that the I/O strain is less
> extreme?  Analysis is probably best done right after where the
> scalability knee is, not long after that point where the server has
> already collapsed to a quivering mass.

I think that would be missing the point, which IMV is to avoid having
a server collapse into a quivering mass just because we apply a heavy
write workload.  Actually, I think we're doing pretty well with that
up to about 16-24 concurrent writers, and even going somewhat beyond
that we're not really slowing down so much as just not scaling.  For
many people that's not a problem because they don't have that much
write workload, but if someone does have that much write workload, we
can hardly tell them: well, you know, if you don't apply so much load
to the database, it will perform better.  No kidding.  Anybody can
write a database that performs well when it's totally unloaded.  The
trick is to write one that scales as far as possible and degrades as
gracefully as possible when it can no longer scale.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company