Databases Reference
In-Depth Information
We do sometimes run sets of benchmarks to examine or stress particular parts of the
server so we can understand their behavior better. A good example is the many bench-
marks we've run over the years to understand InnoDB's flushing behavior, in our quest
to develop better flushing algorithms for various workloads and types of hardware. It
often happens that we benchmark extensively with different settings to understand
their effects and how to optimize them. But this is not a small undertaking—it can take
many days or weeks—and it is also not beneficial for most people to do, because such
tunnel vision about a specific part of the server often obscures other concerns. For
example, sometimes we find that specific combinations of settings enable better per-
formance in edge cases, but the configuration options are not really practical for pro-
duction usage, due to factors such as wasting a huge amount of memory or optimizing
for throughput while ignoring the impact on crash recovery altogether.
If you must do this, we suggest that you develop a custom benchmark suite before you
begin configuring your server. You need something that represents your overall work-
load and includes edge cases such as very large and complex queries. Replaying your
actual workload against your actual data is usually a good approach. If you have iden-
tified a particular problem spot—such as a single query that runs slowly—you can also
try to optimize for that case, but you risk impacting other queries negatively without
knowing it.
The best way to proceed is to change one or two variables, a little at a time, and run
the benchmarks after each change, being sure to run them long enough to observe the
steady-state behavior. Sometimes the results will surprise you; you might increase a
variable a little and see an improvement, then increase it a little more and see a sharp
drop in performance. If performance suffers after a change, you might be asking for too
much of some resource, such as too much memory for a buffer that's frequently allo-
cated and deallocated. You might also have created a mismatch between MySQL and
your operating system or hardware. For example, we've found that the optimal
sort_buffer_size
might be affected by how the CPU cache works, and the
read_
buffer_size
needs to be matched to the server's read-ahead and general I/O subsystem
configuration. Larger is not always better, and can be much worse. Some variables are
also dependent on others, which is something you learn with experience and by un-
derstanding the system's architecture.
