Tuning MySQL Performance with a neat little perl script
Searching randomly through Google for my next post topic, I came across a little MySQL Tuner perl script written by a RackSpace Senior Systems Engineer named Major Haden. Two things caught my eye.
1. It's always great to optimize things, especially MySQL.
2. Major is a seriously cool name.
Nothing about this script was lacking in coolness, so I decided to run it on my crappy little home FreeBSD development box.
fetch http://mysqltuner.com/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl
The result?
>> MySQLTuner 1.0.0 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login:
Please enter your MySQL administrative password:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.35
[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: 756M (Tables: 26)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 2
-------- Performance Metrics -------------------------------------------------
[--] Up for: 6d 9h 42m 52s (5K q [0.010 qps], 23 conn, TX: 482K, RX: 5M)
[--] Reads / Writes: 58% / 42%
[--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 439.8M (43% of installed RAM)
[OK] Slow queries: 0% (0/5K)
[OK] Highest usage of available connections: 1% (2/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/395.4M
[OK] Key buffer hit rate: 98.5% (32K cached / 472 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3 sorts)
[OK] Temporary tables created on disk: 7% (3 on disk / 38 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 87% (49 open / 56 opened)
[OK] Open file limit used: 0% (98/11K)
[OK] Table locks acquired immediately: 100% (5K immediate / 5K locks)
[!!] Connections aborted: 13%
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)
Clearly I haven't gone anywhere near my MySQL config.
Recent Posts
- Tuning MySQL Performance with a neat little perl script
- 10 Simple, Elegant, and Free Wordpress Themes
- FreeBSD 8.0 – Code Freeze – Whats cooking? Why FreeBSD? Why are you using linux?
- How to install Lighttpd, PHP 5, and MySQL 5.1 on FreeBSD 7.2
- How to install MySQL 5.1 on FreeBSD 7.2
Categories
Archives
Meta
Powered By
