2008-10-20 23:04 
老徐 这个小小的脚本能发现 MySQL 配置文件的一些问题，很是有用。
输入用户名和密码后，它会给你提供一个诊断结果。
[root@freelamp ~]# mysqltuner.pl
>>? MySQLTuner 0.9.9 – 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-community-log
[!!] Switch to 64-bit OS – MySQL cannot currenty use all of your RAM——– Storage Engine Statistics ——————————————-
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3M (Tables: 4)
[--] Data in InnoDB tables: 235M (Tables: 190)
[--] Data in MEMORY tables: 39K (Tables: 1)
[!!] Total fragmented tables: 1——– Performance Metrics ————————————————-
[--] Up for: 12h 26m 26s (768K q [17.154 qps], 9K conn, TX: 960M, RX: 96M)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 1.8G global + 100.4M per thread (240 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 25.3G (662% of installed RAM)
[OK] Slow queries: 0% (0/768K)
[OK] Highest usage of available connections: 10% (25/240)
[OK] Key buffer size / total MyISAM indexes: 256.0M/12.7M
[OK] Key buffer hit rate: 99.8% (6M cached / 14K reads)
[OK] Query cache efficiency: 64.5% (467K cached / 726K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 21K sorts)
[!!] Joins performed without indexes: 1320
[!!] Temporary tables created on disk: 35% (4K on disk / 11K total)
[OK] Thread cache hit rate: 99% (25 created / 9K connections)
[!!] Table cache hit rate: 9% (235 open / 2K opened)
[OK] Open file limit used: 3% (43/1K)
[OK] Table locks acquired immediately: 100% (488K immediate / 488K locks)
[OK] InnoDB data size / buffer pool: 235.4M/1.0G——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours – recommendations may be inaccurate
Adjust your join queries to always utilize indexes
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:
*** MySQL’s maximum memory usage exceeds your installed memory ***
*** Add more RAM before increasing any MySQL buffer variables? ***
join_buffer_size (> 64.0M, or always use indexes with joins)
tmp_table_size (> 512M)
max_heap_table_size (> 128M)
table_cache (> 512)
我认为最重要的性能调整就是通过 tmp_table_size? 和 query_cache_size 来实现的。当然这个脚本还是有一定作用的。
可惜这个脚本在我的 Sun U20 的 AMD x64 上的 MySQl 64Bits 上有问题。
有空再研究。
发表于 
