There is a general principle at
work here that humans who optimize
know: when everyone is happy except
for you, make sure your local stuff is in
order before you go messing around
with the global stuff that affects everyone else, too.
This principle is why I flinch whenever someone proposes to change a
system’s Oracle SQL*Net packet size
when the problem is really a couple
of poorly written Java programs that
make unnecessarily many database
calls (and, hence, unnecessarily many
network I/O calls as well). If everybody
is getting along fine except for the user
of one or two programs, then the saf-est solution to the problem is a change
whose scope is localized to just those
one or two programs.
Efficiency. Even if everyone on the
entire system is suffering, you should
still focus first on the program that the
business needs fixed. The way to begin
is to ensure the program is working as
efficiently as it can. Efficiency is the inverse of how much of a task execution’s
total service time can be eliminated
without adding capacity and without
sacrificing required business function.
In other words, efficiency is an inverse measure of waste. Here are some
examples of waste that commonly occur in the database application world:
˲ ˲ A middle-tier program creates a
distinct SQL statement for every row it
inserts into the database. It executes
10,000 database prepare calls (and
thus 10,000 network I/O calls) when it
could have accomplished the job with
one prepare call (and thus 9,999 fewer
network I/O calls).
˲ ˲ A middle-tier program makes 100
database fetch calls (and thus 100 network I/O calls) to fetch 994 rows. It could
have fetched 994 rows in 10 fetch calls
(and thus 90 fewer network I/O calls).
˲ ˲ A SQL statement (my choice of article adjective here is a dead giveaway
that I was introduced to SQL within the
Oracle community) touches the database buffer cache 7,428,322 times to return a 698-row result set. An extra filter
predicate could have returned the seven
rows that the end user really wanted to
see, with only 52 touches upon the database buffer cache.
Certainly, if a system has some glob-
al problem that creates inefficiency for
broad groups of tasks across the sys-
tem (for example, ill-conceived index,
badly set parameter, poorly configured
hardware), then you should fix it. Do
not tune a system to accommodate pro-
grams that are inefficient, however. (Ad-
mittedly, sometimes you need a tour-
niquet to keep from bleeding to death,
but do not use a stopgap measure as a
permanent solution. Address the ineffi-
ciency.) There is a great deal more lever-
age in curing the program inefficiencies
themselves. Even if the programs are
commercial off-the-shelf applications,
it will benefit you more in the long run
to work with your software vendor to
make your programs efficient than it
will to try to optimize your system to run
with an inherently inefficient workload.
figure 1. this curve relates response time as a function of utilization for an m/m/m system
with m = 8 service channels.
m/m/8 System
5
Response time (R)
4
3
2
1
0
0.0
0.2
0.6 0.4
utilization (ρ)
0.8