mysqltunerIf you own MySQL-based database servers, I recommend downloading and periodically running the mysqltuner script, which analyzes the current state and logs of the server and gives you some recommendations for optimizing its performance.
This script gives hints on what settings or actions can be used to improve the performance of your MySQL server and how to optimize the configuration files.
You can download the script at this link: link.
Or directly from within the operating system:
Linux (Debian/CentOS):
$ wget http://mysqltuner.com/mysqltuner.pl
FreeBSD:
$ fetch http://mysqltuner.com/mysqltuner.pl
Run it. It will ask you for the superuser login and password (by default, the root user).
$ ./mysqltuner.pl
>> 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
Please enter your MySQL administrative login: reagent
Please enter your MySQL administrative password:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.58-1~dotdeb.1
[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: 607K (Tables: 29)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 6
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 7d 3h 38m 26s (101K q [0.165 qps], 9K conn, TX: 57M, RX: 16M)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[!!] Maximum possible memory usage: 463.8M (183% of installed RAM)
[OK] Slow queries: 0% (0/101K)
[OK] Highest usage of available connections: 2% (4/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/191.0K
[OK] Key buffer hit rate: 100.0% (3M cached / 171 reads)
[OK] Query cache efficiency: 65.1% (36K cached / 56K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
[OK] Temporary tables created on disk: 19% (68 on disk / 354 total)
[OK] Thread cache hit rate: 99% (4 created / 9K connections)
[!!] Table cache hit rate: 2% (64 open / 2K opened)
[OK] Open file limit used: 8% (82/1K)
[OK] Table locks acquired immediately: 100% (61K immediate / 61K locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
table_cache (> 64)
As you can see, the script immediately dumped out the result along with recommendations for optimizing and improving the performance of our MySQL server.
Comments