Databases Reference
In-Depth Information
application changes for a while. Before we get deep into the details of scaling MySQL,
here are some things you might be able to do now, before you make major scaling
efforts:
Optimize performance
You can often get significant performance improvements from relatively simple
changes, such as indexing tables correctly or switching from MyISAM to the
InnoDB storage engine. If you're facing performance limitations now, one of the
first things you should do is enable and analyze the slow query log. See
Chap-
ter 3
for more on this topic.
There is a point of diminishing returns. After you've fixed most of the major prob-
lems, it gets harder and harder to improve performance. Each new optimization
makes less of a difference and requires more effort, and they often make your ap-
plication much more complicated.
Buy more powerful hardware
Upgrading your servers, or adding more of them, can sometimes work well. Espe-
cially for an application that's early in its lifecycle, it's often a good idea to buy a
few more servers or get some more memory. The alternative might be to try to keep
the application running on a single server. It can be more practical just to buy some
more hardware than to change your application's design, especially if time is critical
and developers are scarce.
Buying more hardware works well if your application is either small or designed so it
can use more hardware well. This is common for new applications, which are usually
very small or reasonably well designed. For larger, older applications, buying more
hardware might not work, or might be too expensive. For example, going from 1 to 3
servers isn't a big deal, but going from 100 to 300 is a different story—it's very
expensive. At that point, it's worth putting in a lot of time and effort to get as much
performance as possible out of your existing systems.
Scaling Up
Scaling up means buying more powerful hardware, and for many applications this is
all you need to do. There are many advantages to this strategy. A single server is so
much easier to maintain and develop against than multiple servers that it offers signif-
icant cost savings, for example. Backing up and restoring your application on a single
server is also simpler because there's never any question about consistency or which
dataset is the authoritative one. The reasons go on. Cost is complexity, and scaling up
is simpler than scaling out.
You can scale up quite far. Commodity servers are readily available today with half a
terabyte of memory, 32 or more CPU cores, and more I/O power than you can even
use for MySQL (flash storage on PCIe cards, for example). With intelligent application
