Optimizing MySQL on FreeBSD part 1

I have written a few other times at a few separate locations about tuning MySQL in the past, so I'm going to attempt and write a bit of updated material and keep it all in one place, this blog. I will be following up in the next few months concerning additional tuning steps that can be taken.

Recently while browsing the interweb, I came across a nifty little perl script written by Major Hayden of rackspace.com.

I put a copy of this perl script here for ease of downloading and use. To get it, simply download -> extract it -> make executable. Of course you need perl installed to use it...

Some examples of output that I received when I ran the script ./mysqltuner.pl on one of my higher transaction test servers:

General recommendations:
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
Variables to increase:
*** MySQL's maximum memory usage exceeds your installed memory ***
*** Add more RAM before increasing any MySQL buffer variables ***
max_connections (> 125)
key_buffer_size (> 11.1G)
query_cache_size (> 256M)
join_buffer_size (> 1024.0M, or always use indexes with joins)
Variables to decrease:
wait_timeout (<>

I modified most of the variables in question in my /etc/my.cnf and restarted mysqld and let it run for a few days. I then ran the script again and got the following output:

./mysqltuner.pl
MySQL High-Performance Tuner - Major Hayden
Bug reports, feature requests, and downloads at mysqltuner.com
Run with '--help' for additional options and output filtering
Please enter your MySQL login: root
Please enter your MySQL password:
[OK] Currently running supported MySQL version 5.0.41-log
-------- General Statistics --------------------------------------------------
[--] Up for: 6d 5h 5m 20s (8M q [16.393 qps], 139K conn, TX: 2G, RX: 4G)
[--] Reads / Writes: 65% / 35%
[!!] Maximum possible memory usage: 442.7G (1341% of installed RAM)
[OK] Slow queries: 0%
[OK] Highest usage of available connections: 49%
[OK] Key buffer size / total MyISAM indexes: 12.0G/11.1G
[OK] Key buffer hit rate: 99.8%
[OK] Query cache efficiency: 31.5%
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0%
[!!] Joins performed without indexes: 2838670
[OK] Temporary tables created on disk: 0%
[OK] Thread cache hit rate: 99%
[OK] Table cache hit rate: 78%
[OK] Open file limit used: 13%
[OK] Table locks acquired immediately: 99%
-------- Recommendations -----------------------------------------------------
General recommendations:
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Variables to increase:
*** MySQL's maximum memory usage exceeds your installed memory ***
*** Add more RAM before increasing any MySQL buffer variables ***
join_buffer_size (> 1.5G, or always use indexes with joins)

All in all, this is a highly useful script to get some quick stats and easy adjustment variables to help tune your MySQL server. I should also note that this is not specific to FreeBSD, but I happen to be a FreeBSD junkie and this this was all tested on a FreeBSD 6.2 Rel box.

Cheers,
JJC