わーくあうと！

日々の作業でためになったことをアウトプットすることで自分の成長につながればなと。

トップ > 5分で出来るMySQLのお手軽チューニング

5分で出来るMySQLのお手軽チューニング

こちらのエントリーで書いているのですが、開発用のVPS鯖がメモリ使い過ぎで重くなっていて、mysqlに問題がありそうだったのでサクっと調整しました。
その手順を残しておきます。

MySQLのチューニング項目について説明

MySQLのメモリに関する設定項目はたくさんあります。
それぞれの項目に関しての詳しい情報は下記が参考になります。
http://trackback.blogsys.jp/livedoor/klab_gijutsu2/50860867


ただ、これらを全て把握するのは大変だと思うので、簡単に分類すると以下の2種類があります。

  • グローバルバッファ
  • スレッドバッファ

で、MySQLが使うメモリ量は下記になるわけです。

グローバルバッファ + ( スレッドバッファ * コネクション数 )


それらを踏まえた上で具体的なチューニングに入ります。

MySQLで使っているメモリ量を調べる

こんな便利なものを作ってくれた方がいました。
http://blog.mogmet.com/calculate-mysql-total-memory-sql/trackback/
【MySQL】総メモリー使用量を算出するSQL作ってみた




SQL部分を引用させてもらうと

select
@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH as GLOBAL_BUFFER_SIZE,
@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE as THREAD_BUFFER_SIZE,
@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH
 + (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS AS TOTAL_MEMORY_SIZE,
(@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH
 + (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS)/1024 AS TOTAL_MEMORY_SIZE_kb,
(@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH
 + (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS)/1024/1024 AS TOTAL_MEMORY_SIZE_mb,
(@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH
 + (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS)/1024/1024/1024 AS TOTAL_MEMORY_SIZE_gb
\G

となっており、これを実行すると以下のようになります。

 *************************** 1. row ***************************
  GLOBAL_BUFFER_SIZE: 159399936
  THREAD_BUFFER_SIZE: 9306112
   TOTAL_MEMORY_SIZE: 1564622848
TOTAL_MEMORY_SIZE_kb: 1527952.0000
TOTAL_MEMORY_SIZE_mb: 1492.14062500
TOTAL_MEMORY_SIZE_gb: 1.457168579102
1 row in set (0.38 sec)

この場合、一見GLOBAL_BUFFER_SIZEが大きいように見えますが、問題なのはスレッドバッファです。
何故ならスレッドバッファが使うメモリサイズは 【スレッドバッファ * コネクション数】となるためです。


ちなみにコネクション数を確認するにはmysqlのコンソールで下記を実行します。

mysql> select @@GLOBAL.MAX_CONNECTIONS;
 +--------------------------+
 | @@GLOBAL.MAX_CONNECTIONS |
 +--------------------------+
 |                      151 |
 +--------------------------+
1 row in set (0.00 sec)

151...
9306112 * 151 = 1405222912 byte = 1340.125MB
で、スレッドバッファに1340.125MBも使ってます。

MySQLの設定を修正

何が問題なのかわかったので実際にMySQLの設定を修正していきます。
『/etc/my.cnf』に下記の設定を書きます。

[mysqld]
max_connections = 31

今回はスレッド数が多すぎたので max_connections を 151ー＞31 と設定しました。
31という値については各環境で適当に設定してください。


グローバルバッファも設定したい場合は

[mysqld]
max_connections = 31
innodb_buffer_pool_size = 256MB

のように innodb_buffer_pool_size を設定します。
こちらの256MBという値についても各環境で適当に設定してください。


本当はグローバルバッファの設定には他の設定項目もありますが、大部分を占めるのが innodb_buffer_pool_size なので、シンプルに設定するためにこの項目だけにしています。


これだけでチューニングは完了です。
あとはmysqlをリスタートすれば設定が反映されます。

service mysql restart

設定後のメモリ使用量を確認

上のほうで紹介した長い長いSQLを実行して設定後のメモリ使用量を確認した結果、以下のようになりました。

 *************************** 1. row ***************************
  GLOBAL_BUFFER_SIZE: 293617664
  THREAD_BUFFER_SIZE: 9306112
   TOTAL_MEMORY_SIZE: 582107136
TOTAL_MEMORY_SIZE_kb: 568464.0000
TOTAL_MEMORY_SIZE_mb: 555.14062500
TOTAL_MEMORY_SIZE_gb: 0.542129516602
1 row in set (0.00 sec)

トータルメモリ使用量が0.5GBほどにまで下がってます。めでたい。

※メモリは使う量が少なければ少ないほどいいわけではなく、（swapしない範囲で）割り当てが多ければ多いほどパフォーマンスは向上します。今回はApache等も同時に動かしている鯖なので、mysqlのメモリ使用量を抑えたい目的で設定しています。そこは各自の環境に合わせて設定してください。

まとめ

  • max_connections
  • innodb_buffer_pool_size

を調整すればそこそこいい感じになる！


もっと調整していきたい！という方はここから他の細かい設定項目を調整していけばいいんじゃないでしょうか。