MySQL Startup Options

MySQL AB provides a wealth of information regarding the tuning of server parameters, much of which the average user will never need to use. So as not to completely overwhelm you with information, this section contains only a few of the common startup options for a finely tuned MySQL server.

By the Way

You can read more in the MySQL Manual, at

When you start MySQL, a configuration file called my.cnf is loaded. This file contains information ranging from port number to buffer sizes but can be overruled by command-line startup options.

In the support-files subdirectory of your MySQL installation directory (or in the installation directory itself on Windows), you'll find sample configuration files, each tuned for a specific range of installed memory:

  • my-small.cnfFor systems with less than 64MB of RAM, where MySQL is used occasionally.

  • my-medium.cnfFor systems with less than 64MB of RAM, where MySQL is the primary activity on the system, or for systems with up to 128MB of RAM, where MySQL shares the box with other processes. This is the most common configuration, where MySQL is installed on the same box as a web server and receives a moderate amount of traffic.

  • my-large.cnfFor a system with 128MB to 512MB of RAM, where MySQL is the primary activity.

  • my-huge.cnfFor a system with 1GB to 2GB of RAM, where MySQL is the primary activity.

To use any of these as the base configuration file, simply copy the file of your choice to /etc/my.cnf (or wherever my.cnf is on your system) and change any system-specific information, such as port or file locations.

Key Startup Parameters

There are two primary startup parameters that will affect your system the most: key_buffer_size and table_cache. If you get only two server parameters correctly tuned, make sure they're these two!

The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. Try to find the fine line between finely tuned and over-optimized; you might have a key_buffer_size of 256MB on a system with 512MB of RAM, but any more than 256MB could cause degraded server performance.

A simple way to check the actual performance of the buffer is to examine four additional variables: key_read_requests, key_reads, key_write_requests, and key_writes. You can find the values of these variables by issuing the SHOW STATUS command:


A long list of variables and values will be returned, listed in alphabetical order. Find the rows that look something like this (your values will differ):

| Key_read_requests        | 10182771   | | Key_reads                | 9326       | | Key_write_requests       | 48487      | | Key_writes               | 2287       |

If you divide the value of key_reads by the value of key_read_requests, the result should be less than 0.01. Also, if you divide the value of key_writes by the value of key_write_requests, the result should be less than 1. Using the previous values, we have results of 0.000915861721998 and 0.047167281951863, respectively, well within the acceptable parameters. You could try to get these numbers even smaller by increasing the value of key_buffer_size, but these numbers are fine as they are.

The other important server parameter is table_cache, which is the number of open tables for all threads. The default is 64, but you might need to adjust this number. Using the SHOW STATUS command, look for a variable called open_tables in the output. If this number is large, the value of table_cache should be increased.

The sample configuration files included with your MySQL installation use various combinations of key_buffer_size and table_cache. You can use these combinations as a baseline for any modifications you need to make. Whenever you modify your configuration, you have to restart your server for changes to take effectsometimes with no knowledge of the consequences of your changes. In this case, be sure to try your modifications in a development environment before rolling the changes into production.

Sams Teach Yourself PHP, MySQL And Apache All in One
Sams Teach Yourself PHP, MySQL and Apache All in One (3rd Edition)
ISBN: 0672328739
EAN: 2147483647
Year: 2004
Pages: 327

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: