Improving Site Speeds by Tweaking MySQL

I’m always trying to get my sites to run faster, not only does it make the user experience better, it’s something that Google pays attention to in rankings.

If you’re working in a LAMP environment, one way to optimize your site that’s often overlooked it working with MySQL to try to get as much performance out of it as possible. Out of the box, it’s not tuned for optimum performance, so if you’re just started it up and starting throw content at it, chances are it’s not operating at peak efficiency.

Here are a few things I’ve found while tuning the MySQL installation that runs our campus CMS, which is WordPress.

First, using MySQL’s OPTIMIZE TABLES command often is a good thing. It will defragment your tables and do other clean-up tasks. I run it nightly when I do a scheduled database backup via CRON. I do it by running this:

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

If you run that via the command line, it will prompt you for your MySQL root user’s password. You can hard code it there if you’re adding it to a script.

Secondly, turn on and tune MySQL’s built-in caching options. Again, this may be something that isn’t turned on by default. You can see your current settings by running this query inside MySQL:

show variables like 'query%'

That will show something like this:

To set up caching, enter this into your my.cnf file. Make a backup first.

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

The trick is finding the right values for you and your code. Obviously, you may want to increase this if you are using MySQL for your CMS, less if your database gets a smaller amount of hits.

So how do you know the right value? Good question. There’s a great, free Python script called MySQLTuner that will take a look at your MySQL setup and make recommendations. The output it gives is very interesting.

First, to install it, log into your server. You’ll most likely need to have root access, so su into root and run this:

cd /usr/bin
wget http://mysqltuner.pl/mysqltuner.pl
chmod 755 mysqltuner.pl

Once it’s installed, you can run it this way:

./mysqltuner.pl

Here’s the output of the script:

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 100M (Tables: 915)
[!!] InnoDB is enabled but isn't being used
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 43

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 79d 3h 49m 36s (42M q [6.146 qps], 1M conn, TX: 1733B, RX: 5B)
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 178.0M global + 11.2M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.3G (32% of installed RAM)
[OK] Slow queries: 0% (13/42M)
[OK] Highest usage of available connections: 68% (68/100)
[OK] Key buffer size / total MyISAM indexes: 64.0M/7.5M
[OK] Key buffer hit rate: 99.3% (49M cached / 335K reads)
[OK] Query cache efficiency: 83.2% (30M cached / 36M selects)
[!!] Query cache prunes per day: 25038
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 1M sorts)
[!!] Temporary tables created on disk: 41% (618K on disk / 1M total)
[OK] Thread cache hit rate: 99% (1K created / 1M connections)
[!!] Table cache hit rate: 0% (1K open / 194K opened)
[OK] Open file limit used: 4% (2K/65K)
[OK] Table locks acquired immediately: 99% (7M immediate / 7M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Add skip-bdb to MySQL configuration to disable BDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 32M)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    table_cache (> 2048)

As you can see, it gives recommendations of things you can do to improve performance. In this case, it’s making sure to optimize tables and turn off some functions that aren’t being used. It also gives some recommended values, based on your server, for some variables you can control in my.cnf.

If you do make changes to my.cnf, don’t forget to restart mysqld or the changes won’t take effect and if you re-run MySQLTuner, you’ll see no difference. On many Linux systems, you can restart MySQL this way:

/etc/init.d/mysqld restart

If you are still seeing performance issues, it may be your code. The more specific you are with your queries, the better. It will take less time for MySQL to gather up and process what you’ve asked for. It doesn’t matter how tuned your MySQL is if every one of your queries starts with SELECT * from TABLE. If you’re interested in what queries are taking awhile to run, you can turn on MySQL’s slow query logging feature.

First, set up a file. Again, you’ll most likely need to be root.

touch /var/log/mysql_slow.log

And in your my.cnf file, add this:

slow-query-log = 1
slow-query-log-file = /var/log/mysql_slow_queries.log

Hopefully, some of these steps will improve your site’s performance. Don’t forget to keep checking on it, either as you add new code or your site grows in popularity.