Databases Reference
In-Depth Information
Summary
Choosing and configuring hardware for MySQL, and configuring MySQL for the hard-
ware, is not a mystical art. In general, you need the same skills and knowledge that you
need for most other purposes. However, there are some MySQL-specific things you
should know.
What we commonly suggest for most people is to find a good balance between perfor-
mance and cost. First, we like to use commodity servers, for many reasons. For example,
if you're having trouble with a server and you need to take it out of service while you
try to diagnose it, or if you simply want to try swapping it with another server as a form
of diagnosis, this is a lot easier to do with a $5,000 server than one that costs $50,000
or more. MySQL is also typically a better fit—both in terms of the software itself and
in terms of the typical workloads it runs—for commodity hardware.
The four fundamental resources MySQL needs are CPU, memory, disk, and network
resources. The network doesn't tend to show up as a serious bottleneck very often, but
CPUs, memory, and disks certainly do. You generally want many fast CPUs for MySQL,
although if you must choose between many and fast, choose fast instead of many (all
other things being equal).
The relationship between CPUs, memory, and disks is intricate, with problems in one
area often showing up elsewhere. Before you throw resources at a problem, ask yourself
whether you should be throwing resources at a different problem instead. If you're disk-
bound, do you need more I/O capacity, or just more memory? The answer hinges on
the working set size, which is the set of data that's needed most frequently over a given
duration.
At the time of writing, we think it makes sense to proceed as follows. First, it's generally
good not to exceed two sockets. Even a two-socket system can offer a lot of CPU cores
and hardware threads, and the CPUs available for four sockets are dramatically more
expensive. In addition, they are less widely used (and thus less tested and less reliable),
and they come with lower clock frequencies. Finally, four-socket systems appear to
suffer from the increased cost of cross-socket synchronization. On the memory front,
we like to fill our servers with economically priced server-class memory. Many com-
modity servers currently have 18 DIMM slots, and 8 GB DIMMs are a good size—their
price is the same per gigabyte as smaller DIMMs, but much less than 16 GB DIMMs.
That's why we see a lot of servers with 144 GB of memory these days. This equation
will change over time—the sweet spot will eventually be 16 GB DIMMs, and there
might be a different number of slots in common server form factors—but the general
principle will probably remain.
