apr 232011
MySQL Tuner è uno script in perl in grado di analizzare una installazione di MySQL e consigliare come migliorare le performance configurando oppurtunamente il demone in my.cf.
L’utilizzo è davvero semplice: basta scaricare il file, renderlo eseguibile e lanciarlo. Chiederà username e password di amminstratore e, dopo qualche secondo, arriverà l’analisi.
wget http://mysqltuner.com/mysqltuner.pl chmod +x mysqltuner.pl ./mysqltuner.pl
Ed ecco l’output una volta lanciato su questo server:
# ./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: root Please enter your MySQL administrative password: -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.0.77 [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: 48M (Tables: 316) [--] Data in InnoDB tables: 46M (Tables: 31) [!!] Total fragmented tables: 45 -------- Performance Metrics ------------------------------------------------- [--] Up for: 3m 47s (451 q [1.987 qps], 19 conn, TX: 2M, RX: 64K) [--] Reads / Writes: 92% / 8% [--] Total buffers: 106.0M global + 2.7M per thread (100 max threads) [OK] Maximum possible memory usage: 374.7M (18% of installed RAM) [OK] Slow queries: 0% (0/451) [OK] Highest usage of available connections: 5% (5/100) [OK] Key buffer size / total MyISAM indexes: 16.0M/11.1M [!!] Key buffer hit rate: 94.3% (3K cached / 193 reads) [OK] Query cache efficiency: 30.4% (117 cached / 385 selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 39 sorts) [!!] Temporary tables created on disk: 45% (19 on disk / 42 total) [OK] Thread cache hit rate: 73% (5 created / 19 connections) [OK] Table cache hit rate: 88% (64 open / 72 opened) [OK] Open file limit used: 10% (110/1K) [OK] Table locks acquired immediately: 100% (361 immediate / 361 locks) [OK] InnoDB data size / buffer pool: 46.6M/48.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate 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 Variables to adjust: tmp_table_size (> 32M) max_heap_table_size (> 32M)