MySQLをチューニング

毎年このシーズンはiconDecotterの負荷がえらいことになるのですが、概ね原因はクリスマス→正月の切替です。
だいたいみなさんこぞって0時でデコレーション解除したり、別のものをデコレーションしたりするので、なかなかのアクセス集中が発生します。

GoogleAnalytics-時間別-20141221-20141226

26日0時のスパイクがなかなかです。
そしてこの時のリソースグラフがこちら。

sakuravps_20141226_1650

さくらVPSのCPUグラフの見方は、縦軸の1k=1コアあたりの100%らしいので、
現在の契約の仮想3コアだと3kで振り切る計算ですね。
はい、0時帯振り切ってます。
DISK I/Oもそのおかげで異常増加してますね。

画像データを送受信・処理するシステムなので、CPUパワーがある程度必要ではあるものの、
前々からDBの方で重い処理があることや、そもそもの設定値について
あまり深く考えてなかったので、ちょっと色々調べてチューニングしてみることにしました。
本当はPHP側のボトルネックもちゃんと調べるべきですけど
色々勉強しているうちにDB設計もちょっとアレなことが後々わかってきたので、
いずれにせよどうにかしなきゃならないな、ということで今回DBに手を入れます。

スポンサーリンク

参考情報

Yakst – MySQLをインストールしたら、必ず確認すべき10の設定

MySQLを高速化したいときのチューニング方法

チューニング方法は色々な記事がありましたが、
MySQLでテーブルはだいたいinnoDBなので、上記の2記事を主に参考にさせていただきました。

変更したもの

max_connections=200
thread_cache_size=200

max_connectionsはデフォルトで150だったものを若干引き上げました。
thread_cache_sizeは0だったのですが、これはmax_connectionsと同じ値にしておく方がよい、とのことなので同じ数字に。
200が妥当かどうかはまだわからないのですが、少なくとも、デフォルト150ではどうやらどこかのタイミングでオーバーしてたらしいので、ちょっとだけ上げてみることに。

wait_timeout=600

thread_cache_sizeを設定したので、コネクションのタイムアウト時間もデフォルトの8時間から変更。
そもそもPHPのセッションがあるので、一旦は10分程度ということで。

read_buffer_size=1M

ちょっとインデックスを使えない…というか使わない集計も必要な設計になってしまっていたので、ここの数字を上げておくことに。

innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=2800M
innodb_log_file_size=256M

概ねinnoDBで動いているので、実はこのあたりの設定はとても大事だったそうですが、未着手でした。
innodb_flush_log_at_trx_commitはメモリに保存する2に。
innodb_buffer_pool_sizeはサーバーのメモリの70~80%くらいの数字を入れていいらしいですので、2800M=2.8Gに。(全体は4Gなので)
innodb_log_file_sizeは若干適当な数字を入れました。本当は1Gくらい割り当ててもいいのかな。
なお、innodb_log_file_sizeの数字を変更→MySQL再起動だとエラーになるので、下記手順は必須の模様。

  1. コンソールでmysqlにログインし、下記を入力。
    mysql> SET GLOBAL innodb_fast_shutdown=0;
  2. MySQLをシャットダウン
    service mysqld stop
  3. ログファイルを退避
    mv /var/lib/mysql/ib_logfile* /tmp
  4. my.cnf を編集
    vi /etc/my.cnf
    [mysqld]
    #以下を追記
    innodb_log_file_size=256M
  5. MySQLを起動
    service mysqld start

これでちゃんと起動するようです。

こんな感じで、まずは様子を見ようと思います。
いまのところ、GoogleAnalyticsでのアクセス状況から比較すると、同等のアクセスがある同時間帯の前日・前々日のCPUグラフよりは30%程度は下がっているような?気がします。
まだまだ調整しないとならないところは多そうです。

スポンサーリンク
スポンサーリンク
iconDecotter-blog-contents-2




iconDecotter-blog-contents-2




シェアする

フォローする

スポンサーリンク
iconDecotter-blog-contents-2