MySQLTuner es una herramienta que realiza comprobaciones al MySQL para dar una serie de recomendaciones sobre su optimización.


Su uso es extremadamente sencillo:
Primer descarregar-ho
 wget http://mysqltuner.com/mysqltuner.pl
--2009-11-17 08:02:38--  http://mysqltuner.com/mysqltuner.pl
Resolving mysqltuner.com... 174.143.240.31
Connecting to mysqltuner.com|174.143.240.31|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 39054 (38K) [text/plain]
Saving to: `mysqltuner.pl'

100%[======================================>] 39,054      70.1K/s   in 0.5s

2009-11-17 08:02:39 (70.1 KB/s) - `mysqltuner.pl' saved [39054/39054]


Després executar-ho

ies-sabadell:~# perl mysqltuner.pl

 >>  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
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.51a-24+lenny2
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 3532)
[--] Data in InnoDB tables: 83M (Tables: 225)
[--] Data in MEMORY tables: 372K (Tables: 5)
[!!] Total fragmented tables: 179

-------- Performance Metrics -------------------------------------------------
[--] Up for: 20h 46m 23s (1M q [16.859 qps], 38K conn, TX: 3B, RX: 236M)
[--] Reads / Writes: 72% / 28%
[--] Total buffers: 58.0M global + 2.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 320.5M (10% of installed RAM)
[OK] Slow queries: 0% (2/1M)
[!!] Highest connection usage: 100%  (101/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/268.1M
[OK] Key buffer hit rate: 99.5% (6M cached / 35K reads)
[OK] Query cache efficiency: 69.7% (639K cached / 916K selects)
[!!] Query cache prunes per day: 51031
[OK] Sorts requiring temporary tables: 0% (179 temp sorts / 37K sorts)
[!!] Temporary tables created on disk: 42% (72K on disk / 172K total)
[OK] Thread cache hit rate: 97% (791 created / 38K connections)
[!!] Table cache hit rate: 0% (64 open / 29K opened)
[OK] Open file limit used: 11% (122/1K)
[OK] Table locks acquired immediately: 99% (461K immediate / 461K locks)
[!!] InnoDB data size / buffer pool: 83.9M/8.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
    Reduce or eliminate persistent connections to reduce connection usage
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    max_connections (> 100)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (> 16M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 83M)
 
 links
http://dev.mysql.com/doc/refman/5.0/es/innodb-start.html