http://mysqltuner.com/ basta scaricarlo e lanciarlo da root con un semplice
perl mysql.plPer prima cosa vi chiederà nome utente e password per accede al database, una volta inseriti i dati analizzerà lo stato del vostro DB restituendovi tutta una serie di informazioni di questo tipo:
——– General Statistics ————————————————– [–] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.0.95 [!!] Switch to 64-bit OS – MySQL cannot currently use all of your RAM ——– Storage Engine Statistics ——————————————- [–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [–] Data in MyISAM tables: 165M (Tables: 203) [–] Data in InnoDB tables: 64K (Tables: 1) [!!] Total fragmented tables: 33 ——– Security Recommendations ——————————————- [!!] User ‘@localhost’ has no password set. [!!] User ‘@mail.etechs.it’ has no password set. ——– Performance Metrics ————————————————- [–] Up for: 4d 22h 36m 25s (4M q [10.970 qps], 146K conn, TX: 2B, RX: 1B) [–] Reads / Writes: 83% / 17% [–] Total buffers: 522.0M global + 15.1M per thread (100 max threads) [OK] Maximum possible memory usage: 2.0G (51% of installed RAM) [OK] Slow queries: 0% (21/4M) [OK] Highest usage of available connections: 22% (22/100) [OK] Key buffer size / total MyISAM indexes: 16.0K/40.5M [OK] Key buffer hit rate: 97.4% (2B cached / 72M reads) [OK] Query cache efficiency: 72.8% (2M cached / 3M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 4% (8K temp sorts / 205K sorts) [!!] Temporary tables created on disk: 46% (147K on disk / 315K total) [OK] Thread cache hit rate: 98% (2K created / 146K connections) [!!] Table cache hit rate: 9% (469 open / 4K opened) [OK] Open file limit used: 16% (681/4K) [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks) [OK] InnoDB data size / buffer pool: 64.0K/8.0M ——– Recommendations —————————————————– General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries Temporary table size is already large – reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Variables to adjust: table_cache (> 2048)in questo caso mi chiede di aumentare il valore di table_cache. Una volta cambiato il file my.cnf con i parametri richiesti mi si deve riavviare il demone. Mi raccomando di lanciare più volte lo script in quanto ad ogni aggiustamento va ricontrollato il database. Fate passare qualche tempo tra una parametrizzazione e l’altra. Enjoy it!]]>
