Databases Reference
In-Depth Information
Workload-Based Configuration
One goal of configuring your server is to customize it for your specific workload. This
requires intimate knowledge of the number, type, and frequency of all kinds of server
activities—not just queries, but other activities too, such as connecting to the server
and flushing tables.
The first thing you should do, if you haven't done it already, is become familiar with
your server. Know what kinds of queries run on it. Monitor it with tools such as
inno-
top
, and use
pt-query-digest
to create a query report. It's helpful to know not only what
your server is doing overall, but what each MySQL query spends a lot of time doing.
Chapter 3
explains how to find this out.
Try to log all queries when your server is running at full capacity, because that's the
best way to see what kinds of queries suffer most. At the same time, capture snapshots
of the process list and aggregate them by their state or command (
innotop
can do this
for you, or you can use the scripts shown in
Chapter 3
). For example, are there a lot of
queries copying results to temporary tables, or sorting results? If so, you might need to
optimize the queries, and potentially look at the configuration settings for temporary
tables and sort buffers.
Optimizing for BLOB and TEXT Workloads
BLOB
and
TEXT
columns are a special type of workload for MySQL. (We'll refer to all of
the
BLOB
and
TEXT
types as
BLOB
here for simplicity, because they belong to the same
class of data types.) There are several restrictions on
BLOB
values that make the server
treat them differently from other types. One of the most important considerations is
that the server cannot use in-memory temporary tables for
BLOB
values.
14
Thus, if a
query involving
BLOB
values requires a temporary table—no matter how small—it will
go to disk immediately. This is very inefficient, especially for otherwise small and fast
queries. The temporary table could be most of the query's cost.
There are two ways to ease this penalty: convert the values to
VARCHAR
with the
SUB
STRING()
function (see
Chapter 4
for more on this), or make temporary tables faster.
The best way to make temporary tables faster is to place them on a memory-based
filesystem (
tmpfs
on GNU/Linux). This removes some overhead, although it's still
much slower than using in-memory tables. Using a memory-based filesystem is helpful
because the operating system tries to avoid writing data to disk.
15
Normal filesystems
are cached in memory too, but the operating system might flush normal filesystem data
every few seconds. A
tmpfs
filesystem never gets flushed. The
tmpfs
filesystem is also
14. Recent versions of Percona Server lift this restriction in some cases.
15. Data can still go to disk if the operating system swaps it.
