温州大学草样论坛稳定的运行了几天,但是却发现在访问量增大的时候会出现访问缓慢和502错误，所以开始优化下服务器，nginx和php可以做的真的不多，所以把目标放在了mysql上面。
这里推荐一个软件给大家，挺好用的。
用于MySql调优的诊断脚本
MySqlTuner 是一个Perl脚本，简单好用。首先是下载
wget mysqltuner.pl
然后就是修改权限，然后运行
chmod +x mysqltuner.pl
./mysqltuner.pl
然后他会输出你的数据库情况，并给出优化意见
我运行后的结果
——– General Statistics ————————————————–
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.39-1
[OK] Operating on 64-bit architecture——– Storage Engine Statistics ——————————————-
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 5M (Tables: 152)
[–] Data in MEMORY tables: 124K (Tables: 1)
[!!] InnoDB is enabled but isn’t being used
[!!] Total fragmented tables: 7——– Performance Metrics ————————————————-
[–] Up for: 3d 16h 46m 54s (1M q [3.349 qps], 53K conn, TX: 763M, RX: 107M)
[–] Reads / Writes: 89% / 11%
[–] Total buffers: 58.0M global + 2.6M per thread (151 max threads)
[!!] Maximum possible memory usage: 454.4M (177% of installed RAM)
[OK] Slow queries: 0% (2/1M)
[OK] Highest usage of available connections: 3% (6/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/1.2M
[OK] Key buffer hit rate: 100.0% (3M cached / 1K reads)
[OK] Query cache efficiency: 82.4% (516K cached / 627K selects)
[!!] Query cache prunes per day: 17862
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 24K sorts)
[!!] Temporary tables created on disk: 34% (122K on disk / 352K total)
[OK] Thread cache hit rate: 99% (6 created / 53K connections)
[!!] Table cache hit rate: 0% (63 open / 8K opened)
[OK] Open file limit used: 11% (117/1K)
[OK] Table locks acquired immediately: 99% (145K immediate / 145K locks)——– Recommendations —————————————————–
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
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 is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 16M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_cache (> 64)
为了保证能获得比较好的优化结果，最好是等1-3天看看网站的访问情况再进行修改，效果会更好
