MySQL 5.5.10 ～パフォーマンスチューニング

WordPressのパフォーマンス向上の目的で、パフォーマンスチューニングを始めました。

httpd層では、別記事に記載したように、mod_chche、mod_disk_cacheを使用して、最低レベルの対策を実施しています。もちろん、まだ上のレベルがありますが、ここでは、DB層のパフォーマンスチューニングを実施します。

WordPressですので、DB層はMySQLです。現時点で最新のバージョン5.5.10を使用しています。

さて、まずは、現状を知ることから。

そこで、MySQLTuner を使用してみます。MySQLTuner は稼働中のMySQLの設定情報やログ情報からセキュリティ、パフォーマンスに関する診断結果と推奨情報を提供してくれるperlスクリプトです。

実際の利用には、まずは MySQLTuner をダウンロードします。

[root ~]# wget mysqltuner.pl
--2011-03-30 12:51:21--  http://mysqltuner.pl/
mysqltuner.pl をDNSに問いあわせています... 50.56.84.181
mysqltuner.pl|50.56.84.181|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Found
場所: http://mysqltuner.pl/mysqltuner.pl [続く]
--2011-03-30 12:51:22--  http://mysqltuner.pl/mysqltuner.pl
mysqltuner.pl|50.56.84.181|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 41393 (40K) [text/plain]
`mysqltuner.pl' に保存中

100%[======================================>] 41,393      76.2K/s 時間 0.5s

2011-03-30 12:51:23 (76.2 KB/s) - `mysqltuner.pl' へ保存完了 [41393/41393]

1．現状把握

mysqltuner.pl を実行します。この際に念のため、httpdサービスを停止しておきます。現在の状態を維持したままで、チューニングを実施するためにMySQLへのアクセスを遮断する意味合いがあります。

[root ~]# perl mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.10-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 21M (Tables: 15)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 15

-------- Security Recommendations  -------------------------------------------
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User 'root@localhost' has no password set.
[!!] User 'root@' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 14h 14m 4s (101K q [0.327 qps], 2K conn, TX: 359M, RX: 33M)
[--] Reads / Writes: 86% / 14%
[--] Total buffers: 210.0M global + 6.3M per thread (180 max threads)
[!!] Maximum possible memory usage: 1.3G (87% of installed RAM)
[OK] Slow queries: 0% (53/101K)
[OK] Highest usage of available connections: 5% (10/180)
[OK] Key buffer size / total MyISAM indexes: 32.0M/102.0K
[OK] Key buffer hit rate: 96.4% (558 cached / 20 reads)
[!!] Query cache efficiency: 0.0% (0 cached / 76K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 16K sorts)
[!!] Temporary tables created on disk: 37% (7K on disk / 19K total)
[OK] Thread cache hit rate: 99% (10 created / 2K connections)
[!!] Table cache hit rate: 8% (59 open / 705 opened)
[OK] Open file limit used: 4% (48/1K)
[OK] Table locks acquired immediately: 100% (114K immediate / 114K locks)
[OK] InnoDB data size / buffer pool: 21.1M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    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:
    query_cache_limit (> 1M, or use smaller result sets)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 256)

この結果から次のようなことが分かります。

2．チューニング

ここからがチューニングです。

(1) パスワード

これについては、承知のことで、わざとこうしているので問題視していません。よって、このままで良しとします。

(2) Query cache(クエリキャッシュ)

これについては、「Recommendations」に出力されているように、 query_cache_limitの設定値を1MB超にすべしとのこと。
query_cache_limitとは、「この値を超える大きさのクエリ(結果)はキャッシュしない」という閾値のようです。
現在のmy.cnfには設定していません。
そこで次のようにして、実際の設定値を確認してみます。

[root ~]# mysqladmin -u root variables | grep query_cache
| have_query_cache                                  | YES            |
| query_cache_limit                                 | 1048576        |
| query_cache_min_res_unit                          | 4096           |
| query_cache_size                                  | 16777216       |
| query_cache_type                                  | DEMAND         |
| query_cache_wlock_invalidate                      | OFF            |

query_cache_limit はデフォルトで 1048576Byte(1MB余)になっているようです。これをもっと大きくすべしということです。大きなクエリ結果があるということなのでしょう。my.cnf にひとまず 2M として追加します。

query_cache_limit = 2M

ここで、query_cache_type が DEMAND になっているのを発見しました。query_cache_type の設定値は次のように3つあります。

query_cache_type =

キャッシュが利いていない直接の原因はこの設定のようです。ここでは 1 に変更します。

query_cache_type = 1

(3) tmp_table_size

tmp_table_size も my.cnf に設定していません。
デフォルトの設定を確認してみます。

[root ~]# mysqladmin -u root variables | grep tmp_table_size
| tmp_table_size                                    | 16777216       |

16MB余りであるようです。これをもっと大きくすべしとのことですので、my.cnf に次の設定を追加します。

tmp_table_size = 32M

(4) max_heap_table_size

max_heap_table_size も my.cnf に設定していません。
デフォルトの設定を確認してみます。

[root ~]# mysqladmin -u root variables | grep max_heap_table_size
| max_heap_table_size                               | 16777216       |

16MB余りであるようです。
ただし、これについては、「Recommendations」の General recommendations に次のような記載があります。
When making adjustments, make tmp_table_size/max_heap_table_size equal
よって、上記(3)の tmp_table_size と同じ値で、次の設定を my.cnf に追加します。

max_heap_table_size = 32M

(5) table_cache

実は、table_cache はバージョン5.5にはありません。table_open_cache に変更になっています。
table_open_cache は my.cnf で 256 を指定していましたが、次のように変更します。

table_open_cache = 1024

MySQLTunerの実行結果に基づいたチューニングとしての設定変更はこれで終わりです。

3．MySQLの再起動

ここで、MySQLを再起動します。

[root ~]# service mysqld restart
mysqld を停止中:                                           [  OK  ]
mysqld を起動中:                                           [  OK  ]

4．チューニング結果検証

この状態で、再度 MySQLTuner を実行します。

[root ~]# perl mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.10-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 21M (Tables: 15)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 15

-------- Security Recommendations  -------------------------------------------
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User 'root@localhost' has no password set.
[!!] User 'root@naboo' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 10s (9 q [0.900 qps], 6 conn, TX: 10K, RX: 559)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 226.0M global + 6.3M per thread (180 max threads)
[!!] Maximum possible memory usage: 1.3G (88% of installed RAM)
[OK] Slow queries: 0% (0/9)
[OK] Highest usage of available connections: 0% (1/180)
[OK] Key buffer size / total MyISAM indexes: 32.0M/102.0K
[!!] Key buffer hit rate: 50.0% (6 cached / 3 reads)
[!!] Query cache efficiency: 0.0% (0 cached / 4 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[OK] Thread cache hit rate: 83% (1 created / 6 connections)
[OK] Table cache hit rate: 78% (26 open / 33 opened)
[OK] Open file limit used: 0% (22/2K)
[OK] Table locks acquired immediately: 100% (36 immediate / 36 locks)
[!!] Connections aborted: 16%
[OK] InnoDB data size / buffer pool: 21.3M/128.0M

-------- 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
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_limit (> 2M, or use smaller result sets)

Query cache(クエリキャッシュ)はチューニングを始めた状態で利いていなかったわけですから、このままではいつまでもゼロのままです。
そこで、httpdを起動して、WordPressを少し使用してみます。
その後、改めて MySQLTuner を実行すると次のようになりました。

[root ~]# perl mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.10-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 21M (Tables: 15)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 15

-------- Security Recommendations  -------------------------------------------
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User 'root@localhost' has no password set.
[!!] User 'root@naboo' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4m 43s (437 q [1.544 qps], 37 conn, TX: 1M, RX: 50K)
[--] Reads / Writes: 87% / 13%
[--] Total buffers: 226.0M global + 6.3M per thread (180 max threads)
[!!] Maximum possible memory usage: 1.3G (88% of installed RAM)
[OK] Slow queries: 0% (2/437)
[OK] Highest usage of available connections: 1% (2/180)
[OK] Key buffer size / total MyISAM indexes: 32.0M/102.0K
[!!] Key buffer hit rate: 50.0% (6 cached / 3 reads)
[OK] Query cache efficiency: 74.1% (254 cached / 343 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 9 sorts)
[OK] Temporary tables created on disk: 14% (4 on disk / 27 total)
[OK] Thread cache hit rate: 94% (2 created / 37 connections)
[OK] Table cache hit rate: 88% (56 open / 63 opened)
[OK] Open file limit used: 2% (52/2K)
[OK] Table locks acquired immediately: 100% (95 immediate / 95 locks)
[OK] InnoDB data size / buffer pool: 21.3M/128.0M

-------- 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

ひとまず、これでチューニングを完了とします。
～～～～～～～～

実は、この後同様のチューニングを繰り返した結果、my.cnf のキャパシティに関する設定は次のようになりました。

[mysqld]
max_connections = 180
key_buffer_size = 32M
max_allowed_packet = 1M
table_open_cache = 1024
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 16M
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 4M
thread_cache_size = 180
wait_timeout = 60
thread_concurrency = 4

～～～～～～～～

以下、「Recommendations」の「General recommendations」の出力に関して補足しておきます。

グーフー WordPressのためのLinuxノート