Pick up a few pointers on optimizing your MySQL install.
The last few “Tech Support” columns have shown how
to improve the performance of your PHP
applications. You can now speed up PHP performance with
memcached and APC,
configure and compile PHP to fine-tune it for your application and
system, and load balance among several servers with
PHP, however, is only one part of the stack. This month,
let’s look at how to tweak MySQL.
Since MySQL optimization is a significant topic and often specific
to your application, let’s instead focus on obtaining some
quick results that can lead to more in-depth investigation and
First, you should also deploy MySQL on the right hardware. MySQL
is memory hungry, so install as much RAM as you can afford. Once
installed, tune MySQL to use memory for its various buffers. Fast
disks and sufficient CPU are next, respectively.
Next, the official MySQL binaries, unlike many applications,
come extremely well-tuned. If you’re not an expert user, you
are almost certainly better off using the packages pre-built for
On the topic of packages, many distributions ship with MySQL
pre-installed. This is convenient, but may not allow you to stay
current with the latest stable version of MySQL. You’ll need
to make a decision on whether it’s best for your organization
to forgo distribution support for MySQL (say, if you’re using
an enterprise distribution) and benefit from the enhancements and
bug fixes provided by MySQL AB. If you’re not using a
distribution that comes with a support contract, this may be less
of a concern.
Regardless of which option you choose, you may find that you
lack a MySQL configuration file present, which results in general,
default settings. By default, MySQL is extremely resource-friendly,
but that can be a performance killer. If you don’t have an
/etc/my.cnf file, creating one should be
your first step in tuning.
What settings to change and the values of those parameters are
specific to your hardware and workload, so you’ll need to do
some research to find your own optimal settings. How do you know if
your settings are appropriate? Two tools can help you deduce an
answer: mytop and
"i">mysqlreport. The former is described online at
mysqlreport transforms the values from the
SHOW STATUS command into an easy-to-read
report that provides an in-depth understanding of how MySQL is
running. You can download mysqlreport from
"story_link">http://hackmysql.com/mysqlreport. Spend a little
time experimenting and reading the MySQL documentation and
benchmark your changes with mytop and
Once you know MySQL is running like a well-oiled machine, there
are other things you can do to improve performance.
*Be sure to enable the query
cache — but also be sure that you’re code takes
advantage of that cache. The cache for a table is invalidated
every time the table is written to. Hence,
if you write to a table to update a timestamp with every page view,
the cache is almost of no use. One way to avoid “cache
misses” is to create a HEAP table,
write the timestamps to that table, and then regularly batch the
updates to your main table.
*Remember that MySQL
optimization goes beyond just tuning mysqld.
You should also look closely at query optimization and making sure
you have the proper indexes on your tables. An errant JOIN, even on
relatively small tables, can result in a huge dataset being
returned and significant table locking. Using
"c">EXPLAIN will help you better understand how MySQL will
process the query you are looking into.
*If you’re not sure which
queries to optimize, enable the slow query log, which logs queries
that take more than a specified amount of time. By focusing on slow
queries in your real world environment, you can hasten overall,
*Mix MySQL storage engines on a
per-table basis. You can use MyISAM tables
for heavily read tables or for tables that require
"c">FULLTEXT search. Use InnoDB
tables if you need row level locking and transactions.
*Tune the underlying system.
Previous “Tech Support” columns have demonstrated
iostat and vmstat,
and both are very applicable to MySQL resource usage. You should
also ensure you have tuned your filesystem and virtual memory
*If you reach the maximum
capacity of a single machine, replicate your databases to multiple
machines. Send all writes to the single master and distribute the
reads to as many slaves as you need to keep up with demand.
Even simple, gross optimization efforts like the ones described
here can make a remarkable difference. With appropriate hardware
and a little work, MySQL scales much further than it did just a few
years ago, and is now powering some of the busiest sites on the
Jeremy Garcia is the founder and administrator of
LinuxQuestions.org, a free, friendly, and active Linux community
that uses MySQL on the backend. Please send questions and feedback