mysql 优化工具
mysqlard 是一个连接到 MySQL 服务器上的守护程序，负责每 5 分钟搜集一次数据，并将它们存储到后台的一个 Round Robin Database 中。有一个 Web 页面会显示这些数据，例如表缓存的使用情况、关键字效率、连接上的客户机以及临时表的使用情况。尽管 mytop 提供了服务器健康信息的快照，但是 mysqlard 则提供了长期的健康信息。作为奖励，mysqlard 使用自己搜集到的一些信息针对如何对服务器进行调优给出一些建议。
搜集 SHOW STATUS 信息的另外一个工具是 mysqlreport。其报告要远比 mysqlard 更加复杂，因为需要对服务器的每个方面都进行分析。这是对服务器进行调优的一个非常好的工具，因为它对状态变量进行适当计算来帮助确定需要修正哪些问题。
MySQL 优化工具 MySQLTuner
MySQLTuner 下载地址：http://github.com/rackerhacker/MySQLTuner-perl
MySQLTuner 使用方法：
Downloading and using MySQLTuner is actually a very simple process:
perl mysqltuner.pl
If you’d rather not invoke perl every time, just make it executable:
./mysqltuner.pl
» Latest development version
If you want to get the bleeding edge commits, you can check out the latest code with git:
看看我的运行结果
>> MySQLTuner 1.1.1 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.43-log
[OK] Operating on 64-bit architecture-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 889M (Tables: 479)
[--] Data in MEMORY tables: 1M (Tables: 4)
[!!] Total fragmented tables: 29-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned-------- Performance Metrics -------------------------------------------------
[--] Up for: 12h 55m 27s (4M q [105.230 qps], 295K conn, TX: 9B, RX: 546M)
[--] Reads / Writes: 82% / 18%
[--] Total buffers: 432.0M global + 6.3M per thread (500 max threads)
[!!] Maximum possible memory usage: 3.5G (175% of installed RAM)
[OK] Slow queries: 0% (48K/4M)
[OK] Highest usage of available connections: 3% (16/500)
[OK] Key buffer size / total MyISAM indexes: 384.0M/332.6M
[OK] Key buffer hit rate: 100.0% (2B cached / 205K reads)
[OK] Query cache efficiency: 49.3% (1M cached / 3M selects)
[!!] Query cache prunes per day: 268042
[OK] Sorts requiring temporary tables: 0% (10 temp sorts / 122K sorts)
[OK] Temporary tables created on disk: 19% (4K on disk / 22K total)
[OK] Thread cache hit rate: 99% (16 created / 295K connections)
[!!] Table cache hit rate: 18% (256 open / 1K opened)
[OK] Open file limit used: 19% (497/2K)
[!!] Table locks acquired immediately: 88%-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Increase table_cache gradually to avoid file descriptor limits
Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 32M)
table_cache (> 256)
版权所有：《太阳花工作室》 => 《MySQL 优化工具》
本文地址：http://bg.artuion.com/linux/192.html
除非注明，文章均为 《太阳花工作室》 原创，欢迎转载！转载请注明本文地址，谢谢。