Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
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.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.