On 8/24/2007 1:17 AM, Greg Smith wrote:
On Thu, 23 Aug 2007, Tom Lane wrote:
It is doubtless true in a lightly loaded system, but once the kernel is
under any kind of memory pressure I think it's completely wrong.
The fact that so many tests I've done or seen get maximum throughput in
terms of straight TPS with the background writer turned completely off is
why I stated that so explicitly. I understand what you're saying in terms
of memory pressure, all I'm suggesting is that the empirical tests suggest
the current background writer even with moderate improvements doesn't
necessarily help when you get there. If writes are blocking, whether the
background writer does them slightly ahead of time or whether the backend
does them itself doesn't seem to matter very much. On a heavily loaded
system, your throughput is bottlenecked at the disk either way--and
therefore it's all the more important in those cases to never do a write
until you absolutely have to, lest it be wasted.
Have you used something that like a properly implemented TPC benchmark
simulates users that go through cycles of think times instead of
hammering SUT interactions at the maximum possible rate allowed by the
network latency? And do your tests consider any completed transaction a
good transaction, or are they like TPC benchmarks, which require the
majority of transactions to complete in a certain maximum response time?
Those tests will show you that inflicting an IO storm at checkpoint time
will delay processing enough to get a significant increase in the number
of concurrent transactions by giving the "users" time enough to come out
of their thinking time. That spike in active transactions increases
pressure on CPU, memory and IO ... and eventually leads to the situation
where users submit new transactions at a higher rate than you currently
can commit ... which is where you enter the spiral of death.
Observing that very symptom during my TPC-W tests several years ago was
what lead to developing the background writer in the first place. Can
your tests demonstrate improvements for this kind of (typical web
application) load profile?
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== [EMAIL PROTECTED] #
---------------------------(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