www.hbirds.net

MySQL のチューニングをしてみました。

$ sudo /usr/bin/mysqltuner

>> MySQLTuner 1.0.1 – 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: *****
Please enter your MySQL administrative password: ********

——– General Statistics ————————————————–
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.41-3ubuntu12.10-log
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 76M (Tables: 170)
[!!] Total fragmented tables: 9

——– Performance Metrics ————————————————-
[--] Up for: 8d 3h 25m 53s (1M q [2.389 qps], 57K conn, TX: 23B, RX: 665M)
[--] Reads / Writes: 67% / 33%
[--] Total buffers: 560.0M global + 160.4M per thread (151 max threads)
[!!] Maximum possible memory usage: 24.2G (498% of installed RAM)
[OK] Slow queries: 0% (19/1M)
[OK] Highest usage of available connections: 8% (13/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/24.0M
[OK] Key buffer hit rate: 100.0% (127M cached / 30K reads)
[OK] Query cache efficiency: 71.9% (943K cached / 1M selects)
[!!] Query cache prunes per day: 17560
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 176K sorts)
[!!] Temporary tables created on disk: 33% (105K on disk / 311K total)
[OK] Thread cache hit rate: 99% (20 created / 57K connections)
[!!] Table cache hit rate: 11% (256 open / 2K opened)
[OK] Open file limit used: 47% (486/1K)
[OK] Table locks acquired immediately: 99% (743K immediate / 744K locks)

——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Temporary table size is already large – reduce result set size
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 (> 32M)
table_cache (> 256)

「—- Recommendations —–」以降におすすめの 設定値が出力されます。(文中の[!!]箇所は改善が必要な箇所です)
query_cache_size (> 32M)とtable_cache (> 256)を大きくしたほうがいいよということなので、

$ sudo nano my.cnf で　
query_cache_size 64M
table_cache 512
に修正しました。

mysql のリスタート
$ sudo /etc/init.d/mysql restart

別途、WordPressのプラグインでデータベースのOptimizeもしてから、再度 mysqltuner を実行
——– General Statistics ————————————————–
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.41-3ubuntu12.10-log
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 76M (Tables: 170)
[!!] Total fragmented tables: 5

——– Performance Metrics ————————————————-
[--] Up for: 24m 13s (5K q [3.621 qps], 490 conn, TX: 62M, RX: 1M)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 592.0M global + 160.4M per thread (151 max threads)
[!!] Maximum possible memory usage: 24.2G (499% of installed RAM)
[OK] Slow queries: 0% (0/5K)
[OK] Highest usage of available connections: 2% (4/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/23.8M
[OK] Key buffer hit rate: 95.8% (407K cached / 17K reads)
[OK] Query cache efficiency: 43.7% (1K cached / 3K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 608 sorts)
[!!] Temporary tables created on disk: 35% (415 on disk / 1K total)
[OK] Thread cache hit rate: 99% (4 created / 490 connections)
[OK] Table cache hit rate: 22% (304 open / 1K opened)
[OK] Open file limit used: 51% (609/1K)
[OK] Table locks acquired immediately: 100% (3K immediate / 3K locks)

——– 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
Temporary table size is already large – reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses

ちゃんとチューニングできたようです。

翌日、再度チェックしてみると、
——– 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
Temporary table size is already large – reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 64M)

64Mでも足りないと言っているので
64M→128Mにしてみました。
どこまで行くのか・・・・

参考サイト
・MySQLを診断してチューニングポイントを教えてくれるツール MySQL Tuner の紹介