Databases Reference
In-Depth Information
how you really use the database. A better approach is to log all queries on your pro-
duction system during a representative time frame, such as an hour during peak load
or an entire day. If you log queries during a small time frame, you might need to choose
several time frames. This will let you cover all system activities, such as weekly reporting
queries or batch jobs you schedule during off-peak times.
3
You can log queries at different levels. For example, you can log the HTTP requests on
a web server if you need a full-stack benchmark. You can also enable MySQL's query
log, but if you replay a query log, be sure to recreate the separate threads instead of just
replaying each query linearly. It's also important to create a separate thread for each
connection in the log, instead of shuffling queries among threads. The query log shows
which connection ran each query.
Even if you don't build your own benchmark, you should write down your bench-
marking plan. You're going to run the benchmark many times over, and you need to
be able to reproduce it exactly. Plan for the future, too. You might not be the one who
runs the benchmark the next time around, and even if you are, you probably will not
remember exactly how you ran it the first time. Your plan should include the test data,
the steps taken to set up the system, how you measured and analyzed the results, and
the warmup plan.
Design some method of documenting parameters and results, and document each run
carefully. Your documentation method might be as simple as a spreadsheet or note-
book, or as complex as a custom-designed database. Keep in mind that you'll probably
want to write some scripts to help analyze the results, so the easier it is to process the
results without opening spreadsheets and text files, the better.
How Long Should the Benchmark Last?
It's important to run the benchmark for a meaningful amount of time. If you're inter-
ested in the system's steady-state performance, which you probably should be, then
you need to observe the system in a steady state. This can take a surprisingly long time
to achieve, especially on servers with a lot of data and a lot of memory. Most systems
have some buffers that create burstable capacity—the ability to absorb spikes, defer
some work, and catch up later after the peak is over. But if you pressure these mecha-
nisms for a long time, they will fill up, and you will eventually see that the system can't
sustain its short-term peak performance.
Sometimes you don't know how long your benchmark needs to run. If this is the case,
you can just run the benchmark forever, and observe until you are satisfied that the
system is starting to become stable. Here's an example of how we did this on a system
we didn't know well.
Figure 2-1
shows a time-series plot of the system's disk read and
write throughput.
3. All this is provided that you want a perfect benchmark, of course. Real life usually gets in the way.
