Mysql оптимизация при помощи mysqltuner

MySQLTuner (http://mysqltuner.com) представляет собой Perl скрипт,
анализирующий статистику работы MySQL сервера и на ее основе дающий
рекомендации по оптимизации настойки СУБД. И так, скачиваем сам скрипт:

wget http://mysqltuner.com/mysqltuner.pl
 chmod +x mysqltuner.pl

запускаем:

perl mysqltuner.pl

вас попросят ввести логин и пароль root-a. В итоге вы получите подобный результат:

-------- General Statistics --------------------------------------------------
 [--] Skipped version check for MySQLTuner script
 [OK] Currently running supported MySQL version 5.1.56
 [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: 104M (Tables: 339)
 [--] Data in InnoDB tables: 15M (Tables: 40)
 [!!] Total fragmented tables: 90

-------- Performance Metrics -------------------------------------------------
 [--] Up for: 16d 20h 48m 45s (2M q [1.535 qps], 84K conn, TX: 55B, RX: 429M)
 [--] Reads / Writes: 43% / 57%
 [--] Total buffers: 106.0M global + 9.2M per thread (500 max threads)
 [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
 [!!] Maximum possible memory usage: 4.6G (231% of installed RAM)
 [OK] Slow queries: 0% (3/2M)
 [OK] Highest usage of available connections: 6% (32/500)
 [OK] Key buffer size / total MyISAM indexes: 16.0M/13.9M
 [OK] Key buffer hit rate: 100.0% (65M cached / 3K reads)
 [OK] Query cache efficiency: 74.4% (1M cached / 1M selects)
 [!!] Query cache prunes per day: 626
 [OK] Sorts requiring temporary tables: 0% (289 temp sorts / 42K sorts)
 [!!] Joins performed without indexes: 52872
 [OK] Temporary tables created on disk: 24% (24K on disk / 99K total)
 [OK] Thread cache hit rate: 99% (32 created / 84K connections)
 [!!] Table cache hit rate: 9% (305 open / 3K opened)
 [OK] Open file limit used: 17% (459/2K)
 [OK] Table locks acquired immediately: 99% (947K immediate / 947K locks)
 [!!] InnoDB data size / buffer pool: 15.1M/8.0M

-------- Recommendations -----------------------------------------------------
 General recommendations:
 Run OPTIMIZE TABLE to defragment tables for better performance
 Enable the slow query log to troubleshoot bad queries
 Adjust your join queries to always utilize indexes
 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 ***
 query_cache_size (> 64M)
 join_buffer_size (> 2.0M, or always use indexes with joins)
 table_cache (> 1024)
 innodb_buffer_pool_size (>= 15M)

Обратите свое внимание на рекомендации, и для лучшей работы MySQL внести предложенные изменения в my.cnf

Оригинал: eddnet.org/?p=1784