はじめに
今まで試行錯誤で MySQL のチューニングを手探りで行ってきました。my.cnf をいじってみましたが、成果は、いまいちです。そんなとき、サーバーの規模に応じてイカした設定をすでに施した my.cnf が実は MySQL のインストールとともに用意されている、という情報を耳にしました！ 早速試してみました♪
落ち着いて！ね？試す前に、イカした my.cnf のサンプルの情報を整理です♪
my.cnf のサンプルファイルは /usr/share/mysql/ にあります。 サーバーのメモリ量に応じてどれを使用するかを考えることになります。
| No | ファイル名 | 備考 |
| 1 | my-small.cnf | メモリが 64 M 以下の MySQL サーバー向け |
| 2 | my-medium.cnf | メモリが 32 〜 64 M の MySQL が重要な位置を占めているサーバーや、128 M 以下で他のプログラム（ウェブサーバーなど）と共に MySQL が使われているサーバー向け |
| 3 | my-large.cnf | メモリが 512 M の MySQL がメインのサーバー向け |
| 4 | my-huge.cnf | メモリが 1 〜 2 G の MySQL がメインのサーバー向け |
| 5 | my-innodb-heavy-4G.cnf | メモリが 4 G で InnoDB のみで構成し、 ACID で、コネクションが少ない、重たいクエリの MySQL サーバー向け |
なお、文字コードの設定はされておりませんので、以前次の投稿で行ったチューニング以外の設定をこれらのサンプルに忘れずに施してから比較しようと思います。
比較項目は、メモリ使用量、Apache Bench の結果です。これらの測定前には、何ページか本ブログを適当に表示させてみてメモリ使用量が安定してから Apache Bench を行うようにします。 また、Apache Bench 実行時のコマンドは「ab -n 100 -c 10 https://oki2a24.com/」とします。
変更前
/etc/my.cnf の設定値には今までトライアル・アンド・エラーで設定した項目を挙げています。あらためて見ますと、たったの 2 つです。
- /etc/my.cnf の設定値
- query_cache_size = 64M
- thread_cache_size=151
- free -mt によるメモリ使用量
- total 1024
- used 278
- free 745
- Apache Bench の結果
- Failed requests: 5
- Requests per second: 0.94 [#/sec] (mean)
メモリ使用量が合計 1024 M のサーバーですので、試せるのは my-small.cnf、my-medium.cnf、my-large.cnf、の 3 つです。これらを試していこうと思います。
my-small.cnf を適用した場合
- /etc/my.cnf の設定値
- key_buffer_size = 16K
- max_allowed_packet = 1M
- table_open_cache = 4
- sort_buffer_size = 64K
- read_buffer_size = 256K
- read_rnd_buffer_size = 256K
- net_buffer_length = 2K
- thread_stack = 128K
- free -mt によるメモリ使用量
- total 1024
- used 200
- free 823
- Apache Bench の結果
- Failed requests: 1
- Requests per second: 0.88 [#/sec] (mean)
メモリ使用量が減り、Failed requests も減った代わりに Requests per second の値も低くなりました。つまり、性能は落ちました。my-small.cnf は使わないほうがよさそうです。
my-medium.cnf を適用した場合
- /etc/my.cnf の設定値
- skip-external-locking
- key_buffer_size = 16M
- max_allowed_packet = 1M
- table_open_cache = 64
- sort_buffer_size = 512K
- net_buffer_length = 8K
- read_buffer_size = 256K
- read_rnd_buffer_size = 512K
- myisam_sort_buffer_size = 8M
- free -mt によるメモリ使用量
- total 1024
- used 218
- free 805
- Apache Bench の結果
- Failed requests: 91
- Requests per second: 0.86 [#/sec] (mean)
メモリ使用量が増えましたが、Failed requests もものすごく大幅に増え、一方で Requests per second は増えましたがわずかです。これも、性能は落ちていると考えて良さそうです。my-medium.cnf は使わないほうがよさそうです。
my-large.cnf を適用した場合
- /etc/my.cnf の設定値
- skip-external-locking
- key_buffer_size = 256M
- max_allowed_packet = 1M
- table_open_cache = 256
- sort_buffer_size = 1M
- read_buffer_size = 1M
- read_rnd_buffer_size = 4M
- myisam_sort_buffer_size = 64M
- thread_cache_size = 8
- query_cache_size= 16M
- thread_concurrency = 8
- free -mt によるメモリ使用量
- total 1024
- used 472
- free 551
- Apache Bench の結果
- Failed requests: 3
- Requests per second: 0.86 [#/sec] (mean)
メモリを大きく使用するようになりました。キャッシュが効いて性能が上がるかと思ったのですけれども、Requests per second はほとんど増えていません。Failed requests は悪くないのですけれども。 サーバースペックの問題でしょうか？全然わかりません＞＜。
サンプルファイルには「thread_concurrency は CPU 数の 2 倍を設定するとよい」とあったため、「thread_concurrency = 2」に変更して測定してみたのですけれども、メモリ使用量が数 M 減り、Requests per second はわずか 0.05 増加した一方で Failed requests が 57 と大幅に増えてしまいました。 やっぱりわかりません。
本ブログへはアクセス可能ですので、最後のこの状態のまま、少しのあいだ放置して sar コマンドで何か変化がないか見てみようと思います。
おわりに
本ブログのサーバーは VPS です。次の Entry プランのサービスを利用させていただいております。
MySQL のチューニングは結局、うまくいきませんでした＞＜。あ、MySQLTuner、試していませんでした。少し休憩してから見てみようと思います。 それはともかく、本投稿については、次のページが参考になりました。結果は芳しくありませんでしたが、御礼申し上げます。ありがとうございます！
ちなみに、変更前の my.cnf があの形になるまで次のようなことを試してきていました。 以上です。