| MySQL was created with the express purpose of being a fast and reliable database. Still, there are ways to improve performance even within the existing framework of the software. The first way to improve the performance of MySQL is to upgrade the server itself. The connection from the client to the server (via the Internet, for example) is one potential bottleneck, and the processor speed, amount of RAM, and type of hard drive all affect MySQL's performance. Since, in the end, MySQL is constantly reading and writing from files on the hard drive, it's easy to see how outdated or slow hardware can inhibit performance. Ideally, if you have a dedicated server with a RAID array, that would be a great place to place the MySQL data directory. And, of course, installing more RAM is always a benefit. The second way to spruce things up is to manipulate how the MySQL server (mysqld) runs. You can do this by specifying different options, such as the following: A third way to improve performance is to optimize your tables. To do so, you would follow the analysis and repair techniques shown in the preceding section, but choose Optimize Tables instead. Similarly, you could run the OPTIMIZE command within the mysql client. I'll show you where to go within the MySQL Administrator to tune the server's performance. Unfortunately, there are no universally correct answers as to what exact values you might want to use. For more information on any of the options, as well as what indicators you should be looking for, see the MySQL manual or search the Web. Also remember that the Health pane of the MySQL Administrator will show you the current memory usage and performance of your server. This information will often indicate if you need to change how MySQL runs. To optimize MySQL: 1. | Open the MySQL Administrator and connect to the MySQL server.
You should connect as an administrative user (of course).
| 2. | Click Startup Variables (Windows) or Options (Mac).
This section of the application is where you control how the MySQL server runs.
| 3. | Increase the Key buffer size if using MyISAM tables (Figure 13.25).
Figure 13.25. Increasing the key buffer is one of the first optimization changes you should make, if you use a lot of MyISAM tables. 
The buffer has a default value of 8 MB, which should be increased if you have a lot of memory and your computer primarily runs MySQL. It only affects MyISAM tables, though.
As with every setting within this area of the MySQL Administrator, click the icon to the left of the item in order to enact the changes (you only need to do this if the icon has a red slash or X on it).
| | | 4. | Click MyISAM Parameters (Windows, Figure 13.26) or select MyISAM Parameters from the drop-down menu (Mac).
Figure 13.26. There are many adjustments you can make as to how MyISAM tables are used in MySQL. 
In this area you can establish the parameters for just the MyISAM tables. In terms of performance, look under Advanced Settings at the bottom. Generally speaking, increasing the values will improve the performance, but don't go randomly changing things unless you understand what they mean (read the descriptions, then see the manual for more) and know you have a need.
| 5. | Repeat Step 4 for the InnoDB Parameters (Figure 13.27).
Figure 13.27. The InnoDB storage engine (while it lasts in MySQL) offers another bevy of adjustments. 
The InnoDB storage engine has its own options you can set. There's no absolutely right answer for what settings you should use, so read the descriptions and pay attention to your server's health and performance, then adjust accordingly.
| | | 6. | Click Performance (Windows) or select Performance from the drop-down menu (Mac).
This is the last of the performance areas to peek into. At the time of this writing, there are only four options (Figure 13.28), each of which is easy to comprehend. These performance settings adjust how MySQL caches queries and results. When MySQL caches a query, the results of that query can be pulled from memory, without your having to requery the database. This is a major performance boost.
Figure 13.28. Increasing the cache size will improve the MySQL server's performance. 
|
 Tips
By using and analyzing slow query logs, you can find and fix particularly problematic queries. You can also use EXPLAIN to see how MySQL executes a query (see the MySQL manual or the Web for more). Your database's performance is also highly dependent upon the decisions you made when designing and implementing it. This includes how you define your columns and indexes. See Chapter 4, "Creating a MySQL Database," for more. As mentioned in the steps, the key buffer value is specific to the MyISAM table type. If you are not using this kind of table, you can improve MySQL's performance by setting this value as small as possible.
|
