LinuxQuestions.org
Go Job Hunting at the LQ Job Marketplace
Go Back   LinuxQuestions.org > Articles > Jeremy's Magazine Articles
User Name
Password

Notices

By jeremy at 2007-03-12 14:32
Quick Tips to Optimize MySQL
Thursday, March 8th, 2007
By Jeremy Garcia

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 Perlbal.

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 customization.

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 your platform.

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 mysqlreport. The former is described online at http://www.linux-mag.com/2001-12/mysql_01.html; 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 http://hackmysql.com/mysqlreport. Spend a little time experimenting and reading the MySQL documentation and benchmark your changes with mytop and mysqlreport.

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 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, actual performance.

*Mix MySQL storage engines on a per-table basis. You can use MyISAM tables for heavily read tables or for tables that require 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 properly.

*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 web.

Jeremy Garcia is the founder and administrator of LinuxQuestions.org, a free, friendly, and active Linux community that uses MySQL on the backend.


  



All times are GMT -5. The time now is 07:19 AM.

Main Menu
Advertisement

My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration