MySQL Tuner

这个小小的脚本能发现 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 上有问题。

有空再研究。