Discuss this article on the forums. (48 posts)

Before you get started

            The very first thing to do is a full server backup.  Be especially sure to backup MySQL databases and my.cnf.  The first thing that you need to do is figure out how much RAM you have to use.  Now, by this, I don’t mean how much RAM is installed in your server.  I mean how much is free.  Obviously, the more you have, the better chance you’ll have more free (but it’s not always the case… I have a server with 2gb of RAM that has 2 megs free…).  The easiest way to see this, is to run the simple command “free -m”… That’ll display a bunch of useful information.  The one we are interested in is in the 2nd line (starts with “Mem:”) , and 4th column (the “free” column).  That’ll show you how many megs of RAM you have free.  Write this number down (it’ll be important later).  Next, you’ll want to run the following command “ top -d 1 -n 2 -u mysql ” (replace mysql with the user MySQL is running under).  Now, look at the column “VIRT” to see how much memory MySQL is currently using… Add that to the free memory from before, and that’s what you have to work with…  Check on these values from time to time.  The last thing you want to do is force something into swap.

Check out your my.cnf file

            IMPORTANT, BACKUP my.cnf NOW, before you change ANYTHING.  You can find your my.cnf (on linux) in the /etc directory.  Before we can tweak anything, we need to know what we’re starting with.  Here’s the default.  Yours may look different, but it’s the starting point. 

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/lib/mysql/mysql.sock

            Notice how there are no variables with numbers next to them (if you have some, that’s fine, just delete them so that it looks similar to this.  Don’t delete anything about connections, timeouts, or open_files_limit).  Now, remember that memory number?  Here’s where we are going to use it.  Add the following directives to the my.cnf under the [mysqld] heading (substituting the % of memory from before where you see a %).

Query_cache_limit=2M
Query_cache_size=25%
Query_cache_type=1
Thread_cache_size=128
Key_buffer = 25%
Join_buffer = 4M
Table_cache=25%
Sort_buffer=4M
Read_rnd_buffer_size=1%
Tmp_table_size=10% (or 32M, whichever is greater)

            Notice how we’re only using 86% of the free memory plus about 10 megs.  This is important as we don’t want to use up too much memory.  Now, restart MySQL to get a baseline and let it run for a few days under normal traffic conditions (or if you are on a development server, simulate EXPECTED traffic conditions).  Also, make sure to grab the running memory usage and free memory again after it has been running on the new setup.

Here’s the fun part.

            Now that we have some real life data, let’s figure out what we need to tweak.  Open up your phpMyAdmin installation in your browser.  Right on the front page is the most valuable link (but most people never even realize it’s there), “Show MySQL runtime information”.  Click on that link.  Now, the page it takes you to is loaded with all sorts of information.  Before I go further into determining what we need to do, let me quickly run down this page (depending on your version of phpMyAdmin).  The first “block” is the server traffic.  It details how many queries have been sent to the server (and connections).  The next block is query statistics.  It basically lists the number of each type of query.  After that block, comes the really stuff we are interested in.  From here out, there are 3 columns (Variable, Value, and Description).   Notice that all bad variable values will have the value in red.  This is important for later.

How to determine what needs tweaking. 

Since we are dealing with server settings here, we’re going to ignore the following variables (that deal with query efficiency): slow_queries, all the handler_ variables, and the select_ variables.  Lets start with the Key Cache.  Scroll down to the section about “Key Cache”.  You want Key_reads to be MUCH smaller (about 1000 times smaller) than Key_read_requests.  If it isn’t, that means that you need to increase the key buffer.  Start by increasing the key_buffer by about 25%, and see how that works.  You can tell if your key buffer is too big by comparing the key_blocks_unused with the key_blocks_used.  You’ll want some free space (about 10% free should do).  Remember, don’t make it too big or you risk swapping.  Don’t restart MySQL yet… There are a few more areas we need to look at.

The next big area is Tables.  Compare the Open_tables and the Opened_tables variable.  As long as they are close, you’re fine.  If opened_tables is much bigger than open_tables (say 10 times as big), then you’ll need to adjust the table_cache variable.  Remember we want to go up in increments, so only increase the size by 25%.  After this, let’s look at Query Cache.  Take a look at Qcache_free_memory.  You want this number to be fairly high (at least a few megs).  You also want the Qcache_lowmem_prunes to be 0 if possible.  If it’s not 0, increase the query_cache_size.  The last thing you want to check is created_tmp_files.  If this is not small, increase tmp_table_size. 

Check it again

Now, restart the server.  Let it run for a few days (checking with free –m to make sure we’re not using too much memory), and go through the cycle again.  Once you have everything listed here sitting nice (where you don’t need to adjust any of the variables), you’re done!  You’ll want to do this from time to time as your server load changes and DB changes.  You should notice a VAST difference (from a stock install) in MySQL performance.

Discuss this article on the forums. (48 posts)