Databases Reference
In-Depth Information
• Running identical queries in a loop. Real queries aren't identical, so they cause
cache misses. Identical queries will be fully or partially cached at some level.
• Failing to check for errors. If a benchmark's results don't make sense—e.g., if a
slow operation suddenly completes very quickly—check for errors. You might just
be benchmarking how quickly MySQL can detect a syntax error in the SQL query!
Always check error logs after benchmarks, as a matter of principle.
• Ignoring how the system performs when it's not warmed up, such as right after a
restart. Sometimes you need to know how long it'll take your server to reach ca-
pacity after a restart, so you'll want to look specifically at the warmup period.
Conversely, if you intend to study normal performance, you'll need to be aware
that if you benchmark just after a restart many caches will be cold, and the bench-
mark results won't reflect the results you'll get under load when the caches are
warmed up.
• Using default server settings. There's more on optimizing server settings in later
chapters.
• Benchmarking too quickly. Your benchmark needs to last a while. We'll say more
about this later.
Merely avoiding these mistakes will take you a long way toward improving the quality
of your results.
All other things being equal, you should typically strive to make the tests as realistic as
you can. Sometimes, though, it makes sense to use a slightly unrealistic benchmark.
For example, say your application is on a different host from the database server. It
would be more realistic to run the benchmarks in the same configuration, but doing
so would add more variables, such as how fast and how heavily loaded the network is.
Benchmarking on a single node is usually easier, and, in some cases, it's accurate
enough. You'll have to use your judgment as to when this is appropriate.
Designing and Planning a Benchmark
The first step in planning a benchmark is to identify the problem and the goal. Next,
decide whether to use a standard benchmark or design your own.
If you use a standard benchmark, be sure to choose one that matches your needs. For
example, don't use TPC-H to benchmark an ecommerce system. In TPC's own words,
“TPC-H is an ad-hoc, decision support benchmark.” Therefore, it's not an appropriate
benchmark for an OLTP system.
Designing your own benchmark is a complicated and iterative process. To get started,
take a snapshot of your production dataset. Make sure you can restore this dataset for
subsequent runs.
Next, you need queries to run against the data. You can make a unit test suite into a
rudimentary benchmark just by running it many times, but that's unlikely to match
