Databases Reference
In-Depth Information
When Benchmarking Is Good
There are exceptions to our advice not to benchmark. We sometimes do advise people
to run some iterative benchmarks, although usually in a different context than “server
tuning.” Here are some examples:
• If you're approaching a large investment, such as purchasing a number of new
servers, you can run benchmarks to understand your hardware needs. (The context
here is capacity planning, not server tuning.) In particular, we like to run bench-
marks with different amounts of memory allocated to the InnoDB buffer pool,
which helps us draw a “memory curve” that shows how much memory is really
needed and how it impacts the demands on the storage systems.
• If you want to understand how long it will take InnoDB to recover from a crash,
you can repeatedly set up a replica, crash it intentionally, and “benchmark” how
long InnoDB takes to recover after restarting. The context here is for high availa-
bility planning.
• For read-mostly applications, it can be a great idea to capture all queries with the
slow query log (or from TCP traffic with
pt-query-digest
), use
pt-log-player
to replay
it against the server with full slow query logging enabled, and then analyze the
resulting log with
pt-query-digest
. This lets you see how various types of queries
perform with different hardware, software, and server settings. For example, we
once helped a customer assess the performance changes of a migration to a server
with much more memory, but with slower hard drives. Most queries became faster,
but some analytical queries slowed down because they remained I/O-bound. The
context of this exercise was workload comparison.
What Not to Do
Before we get started with server configuration, we want to encourage you to avoid a
few common practices that we've found to be risky or harmful. Warning: rants ahead!
First, you should not “tune by ratio.” The classic “tuning ratio” is the rule of thumb
that your key cache hit ratio should be higher than some percentage, and you should
increase the cache size if the hit rate is too low. This is very wrong advice. Regardless
of what anyone tells you,
the cache hit ratio has nothing to do with whether the cache is
too large or too small
. To begin with, the hit ratio depends on the workload—some
workloads simply aren't cacheable no matter how big the cache is—and secondly,
cache hits are meaningless, for reasons we'll explain later. It sometimes happens that
when the cache is too small, the hit rate is low, and increasing the cache size increases
the hit rate. However, this is an accidental correlation and does not indicate anything
about performance or proper sizing of the cache.
