Here's results from a batch of test runs with LDC. This patch only
spreads out the writes, fsyncs work as before. This patch also includes
the optimization that we don't write buffers that were dirtied after
starting the checkpoint.

http://community.enterprisedb.com/ldc/

See tests 276-280. 280 is the baseline with no patch attached, the
others are with load distributed checkpoints with different values for
checkpoint_write_percent. But after running the tests I noticed that the
spreading was actually controlled by checkpoint_write_rate, which sets
the minimum rate for the writes, so all those tests with the patch
applied are effectively the same; the writes were spread over a period
of 1 minute. I'll fix that setting and run more tests.

The response time graphs show that the patch reduces the max (new-order)
response times during checkpoints from ~40-60 s to ~15-20 s. The change
in minute by minute average is even more significant.

The change in overall average response times is also very significant.
1.5s without patch, and ~0.3-0.4s with the patch for new-order
transactions. That also means that we pass the TPC-C requirement that
90th percentile of response times must be < average.


All that said, there's still significant checkpoint spikes present, even
though they're much less severe than without the patch. I'm willing to
settle with this for 8.3. Does anyone want to push for more testing and
thinking of spreading the fsyncs as well, and/or adding a delay between
writes and fsyncs?

Attached is the patch used in the tests. It still needs some love..

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"Heikki Linnakangas" <heikki@enterprisedb.com> writes:

> The response time graphs show that the patch reduces the max (new-order)
> response times during checkpoints from ~40-60 s to ~15-20 s.

I think that's the headline number here. The worst-case response time is
reduced from about 60s to about 17s. That's pretty impressive on its own. It
would be worth knowing if that benefit goes away if we push the machine again
to the edge of its i/o bandwidth.

> The change in overall average response times is also very significant. 1.5s
> without patch, and ~0.3-0.4s with the patch for new-order transactions. That
> also means that we pass the TPC-C requirement that 90th percentile of response
> times must be < average.

Incidentally this is backwards. the 90th percentile response time must be
greater than the average response time for that transaction.

This isn't actually a very stringent test given that most of the data points
in the 90th percentile are actually substantially below the maximum. It's
quite possible to achieve it even with maximum response times above 60s.

However TPC-E has even more stringent requirements:

During Steady State the throughput of the SUT must be sustainable for the
remainder of a Business Day started at the beginning of the Steady State.

Some aspects of the benchmark implementation can result in rather
insignificant but frequent variations in throughput when computed over
somewhat shorter periods of time. To meet the sustainable throughput
requirement, the cumulative effect of these variations over one Business
Day must not exceed 2% of the Reported Throughput.

Comment 1: This requirement is met when the throughput computed over any
period of one hour, sliding over the Steady State by increments of ten
minutes, varies from the Reported Throughput by no more than 2%.

Some aspects of the benchmark implementation can result in rather
significant but sporadic variations in throughput when computed over some
much shorter periods of time. To meet the sustainable throughput
requirement, the cumulative effect of these variations over one Business
Day must not exceed 20% of the Reported Throughput.

Comment 2: This requirement is met when the throughput level computed over
any period of ten minutes, sliding over the Steady State by increments one
minute, varies from the Reported Throughput by no more than 20%.


--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Greg,

> However TPC-E has even more stringent requirements:

I'll see if I can get our TPCE people to test this, but I'd say that the
existing patch is already good enough to be worth accepting based on the TPCC
results.

However, I would like to see some community testing on oddball workloads (like
huge ELT operations and read-only workloads) to see if the patch imposes any
extra overhead on non-OLTP databases.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Heikki Linnakangas <heikki@enterprisedb.com> wrote:

> Here's results from a batch of test runs with LDC. This patch only
> spreads out the writes, fsyncs work as before.

I saw similar results in my tests. Spreading only writes are enough
for OLTP at least on Linux with middle-or-high-grade storage system.
It also works well on desktop-grade Widnows machine.

However, I don't know how it works on other OSes, including Solaris
and FreeBSD, that have different I/O policies. Would anyone test it
in those environment?

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center



---------------------------(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

Heikki Linnakangas wrote:
> Here's results from a batch of test runs with LDC. This patch only
> spreads out the writes, fsyncs work as before. This patch also includes
> the optimization that we don't write buffers that were dirtied after
> starting the checkpoint.
>
> http://community.enterprisedb.com/ldc/
>
> See tests 276-280. 280 is the baseline with no patch attached, the
> others are with load distributed checkpoints with different values for
> checkpoint_write_percent. But after running the tests I noticed that the
> spreading was actually controlled by checkpoint_write_rate, which sets
> the minimum rate for the writes, so all those tests with the patch
> applied are effectively the same; the writes were spread over a period
> of 1 minute. I'll fix that setting and run more tests.

I ran another series of tests, with a less aggressive bgwriter_delay
setting, which also affects the minimum rate of the writes in the WIP
patch I used.

Now that the checkpoints are spread out more, the response times are
very smooth.

With the 40% checkpoint_write_percent setting, the checkpoints last ~3
minutes. About 85% of the buffer cache is dirty at the beginning of
checkpoints, and thanks to the optimization of not writing pages dirtied
after checkpoint start, only ~47% of those are actually written by the
checkpoint. That explains why the checkpoints only last ~3 minutes, and
not checkpoint_timeout*checkpoint_write_percent, which would be 6
minutes. The estimation of how much progress has been done and how much
is left doesn't take the gain from that optimization into account.

The sync phase only takes ~5 seconds. I'm very happy with these results.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"Heikki Linnakangas" <heikki@enterprisedb.com> writes:

> I ran another series of tests, with a less aggressive bgwriter_delay setting,
> which also affects the minimum rate of the writes in the WIP patch I used.
>
> Now that the checkpoints are spread out more, the response times are very
> smooth.

So obviously the reason the results are so dramatic is that the checkpoints
used to push the i/o bandwidth demand up over 100%. By spreading it out you
can see in the io charts that even during the checkpoint the i/o busy rate
stays just under 100% except for a few data points.

If I understand it right Greg Smith's concern is that in a busier system where
even *with* the load distributed checkpoint the i/o bandwidth demand during t
he checkpoint was *still* being pushed over 100% then spreading out the load
would only exacerbate the problem by extending the outage.

To that end it seems like what would be useful is a pair of tests with and
without the patch with about 10% larger warehouse size (~ 115) which would
push the i/o bandwidth demand up to about that level.

It might even make sense to run a test with an outright overloaded to see if
the patch doesn't exacerbate the condition. Something with a warehouse size of
maybe 150. I would expect it to fail the TPCC constraints either way but what
would be interesting to know is whether it fails by a larger margin with the
LDC behaviour or a smaller margin.

Even just the fact that we're passing at 105 warehouses -- and apparently with
quite a bit of headroom too -- whereas previously we were failing at that
level on this hardware is a positive result as far as the TPCC benchmark
methodology.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Fri, 15 Jun 2007, Gregory Stark wrote:

> If I understand it right Greg Smith's concern is that in a busier system
> where even *with* the load distributed checkpoint the i/o bandwidth
> demand during t he checkpoint was *still* being pushed over 100% then
> spreading out the load would only exacerbate the problem by extending
> the outage.

Thank you for that very concise summary; that's exactly what I've run
into. DBT2 creates a heavy write load, but it's not testing a real burst
behavior where something is writing as fast as it's possible to.

I've been involved in applications that are more like a data logging
situation, where periodically you get some data source tossing
transactions in as fast as it will hit disk--the upstream source
temporarily becomes faster at generating data during these periods than
the database itself can be. Under normal conditions, the LDC smoothing
would be a win, as it would lower the number of times the entire flow of
operations got stuck. But at these peaks it will, as you say, extend the
outage.

> It might even make sense to run a test with an outright overloaded to
> see if the patch doesn't exacerbate the condition.

Exactly. I expect that it will make things worse, but I'd like to keep an
eye on making sure the knobs are available so that it's only slightly
worse.

I think it's important to at least recognize that someone who wants LDC
normally might occasionally have a period where they're completely
overloaded, and that this new feature doesn't have an unexpected breakdown
when that happens. I'm still stuggling with creating a simple test case
to demonstrate what I'm concerned about. I'm not familiar enough with the
TPC testing to say whether your suggestions for adjusting warehouse size
would accomplish that (because the flow is so different I had to abandon
working with that a while ago as not being representative of what I was
doing), but I'm glad you're thinking about it.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Gregory Stark wrote:
> "Heikki Linnakangas" <heikki@enterprisedb.com> writes:
>> Now that the checkpoints are spread out more, the response times are very
>> smooth.
>
> So obviously the reason the results are so dramatic is that the checkpoints
> used to push the i/o bandwidth demand up over 100%. By spreading it out you
> can see in the io charts that even during the checkpoint the i/o busy rate
> stays just under 100% except for a few data points.
>
> If I understand it right Greg Smith's concern is that in a busier system where
> even *with* the load distributed checkpoint the i/o bandwidth demand during t
> he checkpoint was *still* being pushed over 100% then spreading out the load
> would only exacerbate the problem by extending the outage.
>
> To that end it seems like what would be useful is a pair of tests with and
> without the patch with about 10% larger warehouse size (~ 115) which would
> push the i/o bandwidth demand up to about that level.

I still don't see how spreading the writes could make things worse, but
running more tests is easy. I'll schedule tests with more warehouses
over the weekend.

> It might even make sense to run a test with an outright overloaded to see if
> the patch doesn't exacerbate the condition. Something with a warehouse size of
> maybe 150. I would expect it to fail the TPCC constraints either way but what
> would be interesting to know is whether it fails by a larger margin with the
> LDC behaviour or a smaller margin.

I'll do that as well, though experiences with tests like that in the
past have been that it's hard to get repeatable results that way.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(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

"Greg Smith" <gsmith@gregsmith.com> writes:

> On Fri, 15 Jun 2007, Gregory Stark wrote:
>
>> If I understand it right Greg Smith's concern is that in a busier system
>> where even *with* the load distributed checkpoint the i/o bandwidth demand
>> during t he checkpoint was *still* being pushed over 100% then spreading out
>> the load would only exacerbate the problem by extending the outage.
>
> Thank you for that very concise summary; that's exactly what I've run into.
> DBT2 creates a heavy write load, but it's not testing a real burst behavior
> where something is writing as fast as it's possible to.

Ah, thanks, that's precisely the distinction that I was missing. It's funny,
something that was so counter-intuitive initially has become so ingrained in
my thinking that I didn't even notice I was assuming it any more.

DBT2 has "think times" which it uses to limit the flow of transactions. This
is critical to ensuring that you're forced to increase the scale of the
database if you want to report larger transaction rates which of course is
what everyone wants to brag about.

Essentially this is what makes it an OLTP benchmark. You're measuring how well
you can keep up with a flow of transactions which arrive at a fixed speed
independent of the database.

But what you're concerned about is not OLTP performance at all. It's a kind of
DSS system -- perhaps there's another TLA that's more precise. But the point
is you're concerned with total throughput and not response time. You don't
have a fixed rate imposed by outside circumstances with which you have to keep
up all the time. You just want to be have the highest throughput overall.

The good news is that this should be pretty easy to test though. The main
competitor for DBT2 is BenchmarkSQL whose main deficiency is precisely the
lack of support for the think times. We can run BenchmarkSQL runs to see if
the patch impacts performance when it's set to run as fast as possible with no
think times.

While in theory spreading out the writes could have a detrimental effect I
think we should wait until we see actual numbers. I have a pretty strong
suspicion that the effect would be pretty minimal. We're still doing the same
amount of i/o total, just with a slightly less chance for the elevator
algorithm to optimize the pattern.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On 6/15/07, Gregory Stark <stark@enterprisedb.com> wrote:
> While in theory spreading out the writes could have a detrimental effect I
> think we should wait until we see actual numbers. I have a pretty strong
> suspicion that the effect would be pretty minimal. We're still doing the same
> amount of i/o total, just with a slightly less chance for the elevator
> algorithm to optimize the pattern.

...and the sort patching suggests that the OS's elevator isn't doing a
great job for large flushes in any case. I wouldn't be shocked to see
load distributed checkpoints cause an unconditional improvement since
they may do better at avoiding the huge burst behavior that is
overrunning the OS elevator in any case.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq