A powerful tool:

apt-get install mysqltuner

You can use:

mysqltuner –user=root –pass=xxx

A report sample:

>> MySQLTuner 1.0.1 - Major Hayden major@mhtx.net

>> Bug reports, feature requests, and downloads at http://mysqltuner.com/

>> Run with ‘–help’ for additional options and output filtering

[OK] Logged in using credentials passed on the command line

——– General Statistics ————————————————–

[–] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.1.49-3

[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-

[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[–] Data in MyISAM tables: 91M (Tables: 1344)

[–] Data in InnoDB tables: 17M (Tables: 349)

[–] Data in MEMORY tables: 0B (Tables: 2)

[!!] Total fragmented tables: 411

——– Performance Metrics ————————————————-

[–] Up for: 42d 22h 39m 32s (10M q [2.924 qps], 361K conn, TX: 20B, RX: 15B)

[–] Reads / Writes: 87% / 13%

[–] Total buffers: 58.0M global + 2.6M per thread (151 max threads)

[OK] Maximum possible memory usage: 454.4M (44% of installed RAM)

[OK] Slow queries: 0% (3/10M)

[OK] Highest usage of available connections: 7% (12/151)

[OK] Key buffer size / total MyISAM indexes: 16.0M/40.6M

[OK] Key buffer hit rate: 99.9% (27M cached / 14K reads)

[OK] Query cache efficiency: 42.6% (2M cached / 6M selects)

[!!] Query cache prunes per day: 254

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 25K sorts)

[OK] Temporary tables created on disk: 13% (7K on disk / 53K total)

[OK] Thread cache hit rate: 99% (66 created / 361K connections)

[!!] Table cache hit rate: 0% (64 open / 125K opened)

[OK] Open file limit used: 4% (47/1K)

[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)

[!!] InnoDB data size / buffer pool: 17.0M/8.0M

——– Recommendations —————————————————–

General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size (> 16M) table_cache (> 64) innodb_buffer_pool_size (>= 17M)

Follow the recommendations ;-)

A very good Book about Mysql High Performance MySQL: Optimization, Backups, Replication, and More: Optimization, Backups, and Replication.

The Perl script code in GitHub.