MySQL: Database Optimization with MySQLTuner

At work, we ran into some MySQL max connections issues that were bringing down our entire site. One highly used table was corrupted which was making all the queries lock, idle or sleep. This would lead to max connections being hit and our whole site going down. We asked our provider to look into it and they turned us on a really cool MySQL optimizer script.

MySQLTuner is a Perl script that looks at all aspects of your database and suggests optimizations to help improve stability. Here is MySQLTuner output our provider sent us:

[pre lang=’text’]
>> MySQLTuner 1.0.1 – Major Hayden
>> 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.90-rs-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

——– Storage Engine Statistics ——————————————-
[–] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 1000M (Tables: 236)
[–] Data in InnoDB tables: 1M (Tables: 101)
[!!] Total fragmented tables: 15

——– Performance Metrics ————————————————-
[–] Up for: 16h 26m 5s (4M q [78.505 qps], 130K conn, TX: 4B, RX: 464M)
[–] Reads / Writes: 83% / 17%
[–] Total buffers: 247.0M global + 12.2M per thread (150 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.0G (102% of installed RAM) (EDIT: eeeek!!!!)
[OK] Slow queries: 0% (410/4M)
[!!] Highest connection usage: 100% (151/150)
[OK] Key buffer size / total MyISAM indexes: 115.0M/223.0M
[OK] Key buffer hit rate: 99.9% (103M cached / 67K reads)
[OK] Query cache efficiency: 67.9% (2M cached / 3M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 292K sorts)
[!!] Joins performed without indexes: 37363
[OK] Temporary tables created on disk: 18% (12K on disk / 67K total)
[OK] Thread cache hit rate: 99% (488 created / 130K connections)
[OK] Table cache hit rate: 59% (967 open / 1K opened)
[OK] Open file limit used: 5% (1K/20K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 1.9M/2.0M

——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours – recommendations may be inaccurate
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 600) interactive_timeout (< 600) join_buffer_size (> 2.0M, or always use indexes with joins)
[/pre]

This info basically sealed the deal that we needed more RAM for our server. We also need to repair a few tables, review some queries and create a ton of new indexes to speed up joins. We have a lot of legacy code =(

So our server is in for a tune up now and hopefully will come back nice and snappy. Check MySQLTuner out and let me know what you think. Does anyone know of any other good MySQL optimization resources?

Continue Reading