読者です 読者をやめる 読者になる 読者になる

bekkou68の日記

開発しているサービス, IT技術, 英語など。

Amazon RDS における MySQL 5.6 のパラメータ設計例

Amazon RDS MySQL

はじめに (2014/09/19 更新)

production 環境で動かすための MySQL 5.6 のパラメータ設計例をまとめました。

サービスの状況に応じててきぎ読みかえてください。

カバーしている内容は以下のとおりです。

  • パラメータを設定するスクリプト
  • 各パラメータにおける変更するかどうかの判断基準
  • 想定されるメモリの消費サイズを算出してパラメータが妥当かどうか確認する方法

結論: パラメータグループ作成・パラメータ設定のスクリプト

aws-cliスクリプトです。もちろん Amazon AWS の Web Console から設定することもできます。

#!/bin/sh

# == パラメータグループ作成
aws rds create-db-parameter-group --db-parameter-group-name myproject-production --db-parameter-group-family mysql5.6 --description "for production"

# == パラメータ設定
# バックスラッシュ+改行でなんとなくグループ分けした
aws rds modify-db-parameter-group --db-parameter-group-name myproject-production --parameters \
  ParameterName=character_set_client,ParameterValue=utf8mb4,ApplyMethod=immediate \
  ParameterName=character_set_connection,ParameterValue=utf8mb4,ApplyMethod=immediate \
  ParameterName=character_set_database,ParameterValue=utf8mb4,ApplyMethod=immediate \
  ParameterName=character_set_filesystem,ParameterValue=utf8mb4,ApplyMethod=immediate \
  ParameterName=character_set_results,ParameterValue=utf8mb4,ApplyMethod=immediate \
  ParameterName=character_set_server,ParameterValue=utf8mb4,ApplyMethod=immediate \
  ParameterName=collation_server,ParameterValue=utf8mb4_general_ci,ApplyMethod=immediate \
  ParameterName=skip-character-set-client-handshake,ParameterValue=0,ApplyMethod=pending-reboot \
  \
  ParameterName=slow_query_log,ParameterValue=1,ApplyMethod=immediate \
  ParameterName=long_query_time,ParameterValue=0.5,ApplyMethod=immediate \
  \
  ParameterName=max_heap_table_size,ParameterValue=`expr 32 \* 1024 \* 1024`,ApplyMethod=immediate \
  ParameterName=tmp_table_size,ParameterValue=`expr 32 \* 1024 \* 1024`,ApplyMethod=immediate \
  ParameterName=query_cache_size,ParameterValue=0,ApplyMethod=immediate \
  \
  ParameterName=thread_cache_size,ParameterValue=32,ApplyMethod=immediate \
  ParameterName=innodb_lock_wait_timeout,ParameterValue=2,ApplyMethod=immediate \
  ParameterName=innodb_max_dirty_pages_pct,ParameterValue=95,ApplyMethod=immediate \
  ParameterName=slow_launch_time,ParameterValue=1,ApplyMethod=immediate \
  \
  ParameterName=innodb_buffer_pool_dump_at_shutdown,ParameterValue=1,ApplyMethod=immediate \
  ParameterName=innodb_buffer_pool_load_at_startup,ParameterValue=1,ApplyMethod=pending-reboot

あると理解しやすい前提知識

  • パラメータのグローバルバッファかスレッドバッファかを意識して設計すること
  • リファレンスとしてMySQL 5.6 のパラメタ一覧が使える
  • パラメータのデフォルト値は MySQL が Engine Default としてもっている。さらにその値が RDS から上書きされている場合がある
    • 以下 RDS のデフォルト値上書きは RDS-default と表記する
  • グローバルパラメータは SHOW GLOBAL VARIABLES LIKE "%hoge%"; で確認できる
    • GLOBAL を抜くとスレッドごとのパラメータが取得できる

各パラメータの考慮

上記のスクリプトで書いたようなパラメータ設計をした判断基準を書いていきます。

設計のプロセスとしては、まず変更候補のパラメータをあげ、検討し、変更するパラメータ、変更しないパラメータをそれぞれ分けました。

変更すると決めたパラメータ
文字コード
  • character_set_*
    • 絵文字対応ありなら utf8mb4 にする
    • 絵文字対応なしなら utf8
  • collation_server
    • 絵文字対応ありなら utf8mb4_general_ci にする
    • 絵文字対応なしなら utf8_general_ci
  • skip-character-set-client-handshake
    • OFF にする
    • 有効にしなくていい。charset をちゃんと指定していれば大丈夫
スローログ
  • slow_query_log
    • ON にする
    • スロークエリのログ出力をするかどうか
  • long_query_time
    • 0.5 (秒) にする
      • 秒数はサービスに依る
      • ユーザ向けサービスである程度のトラフィックが見込まれるなら 0.5 程度で見ておいたほうがいい。1 はかかりすぎ
    • 設定した秒数以上かかるクエリをスロークエリと判定する
    • MySQL 5.1 から float を指定できる
    • 2013年頃は Amazon RDS 上では int しか指定できなかったが現在は float を指定できる
サイズ系
  • max_heap_table_size
  • tmp_table_size
    • max_heap_table_size と同じ値にすべき
    • テンポラリテーブルの最大サイズ
    • テンポラリテーブルは複雑なJOIN や GROUP BY の処理中につくられる
    • テンポラリテーブルのほとんどはメモリベースの heapテーブルが占める
    • メモリ内のテンポラリテーブルのサイズが tmp_table_size を超えた場合、MySQL はディスクベースの MyISAM に切り替えわり遅くなるので十分なサイズを保持しておくこと
  • query_cache_size
    • engine-default=1048576 -> 0 にする
    • query_cache_type が OFF でクエリキャッシュを使わないので
キャッシュウォーミング
その他
  • thread_cache_size
    • engine-default=9 -> 32 にする
    • キャッシュするスレッドの数。キャッシュしないと毎回スレッドをつくるので遅くなる恐れ
  • innodb_lock_wait_timeout
    • engine-default=50s -> 2s にする
    • スレッドが lock をどれくらい待つか。50s は待ちすぎ
  • innodb_max_dirty_pages_pct
    • engine-default=75 -> 95 にする (単位は %)
    • redoファイルに innodb_max_dirty_pages_pct パーセントのログが溜まったらディスクに書き込む。なるべく書き込む回数を減らす
    • たとえば innodb_max_dirty_pages_pct を 10 に設定すると書き込みが頻発するのでせっかく innodb_buffer_pool_size を大きくしても生かせない
  • slow_launch_time
    • engine-default=2 -> 1 にする
    • 起動の遅いスレッドを何秒で判定するか
    • SHOW GLOBAL STATUS LIKE '%thread%'; でスレッドの様子を確認できる
変更しないと決めたパラメータ (2014/09/17 更新)

変更を検討したけれどデフォルトでよさそうだったものを列挙します。

  • max_allowed_packet
    • engine-default=4MB
    • これを超すパケットサイズは送らないだろう
  • max_connections
    • engine-default=296
    • 十分な数字。EC2インスタンスを少し増やすときに都度気にするのは面倒なので、しばらく変えなくていいと思う
  • table_open_cache
    • engine-default=2000
    • ひとまずこのままで
    • すべてのスレッドで使われるオープンテーブルの総合計
    • スレッドごとにテーブルをオープンする
    • テーブル数やパーティション数が多い場合は要検討
    • SHOW GLOBAL STATUS LIKE 'opened_tables'; でオープンされてるテーブル数がわかる。もしこの数が大きかったり急に増えたりしていたら table_open_cache の値を大きくすべき
    • mysqld が必要とするファイルディスクリプタの数にも影響する
  • table_definition_cache
    • engine-default=1400
    • ひとまずこのままで
    • table_open_cache の値に依存する。400 + (table_open_cache / 2) が適切らしい
  • query_cache_type
    • engine-default=OFF
    • クエリキャッシュは使わない
  • skip_name_resolve
  • innodb_stats_on_metadata
  • performance_schema
    • RDS-default=OFF engine-default=不明
    • ON にするとパフォーマンス情報をスキーマに書き出す。だがパフォーマンスが 5%くらい低下するらしいので運用時は OFF が無難
  • innodb_log_file_size
    • RDS-default=128MB
    • redo log なので大きいほど reboot時の復旧が遅くなる性質がある
  • innodb_buffer_pool_size
  • innodb_flush_method

想定される最大のメモリ使用量を算出

設定したプロパティがメモリサイズに対して適切かどうかを確認します。

以下のクエリで確認できます。

SELECT (
    /* グローバル */
    @@key_buffer_size
  + @@query_cache_size
  + @@innodb_buffer_pool_size
  + @@innodb_additional_mem_pool_size
  + @@innodb_log_buffer_size
  + @@max_heap_table_size /* 必ず割り当てられる訳ではないが、安全のため計算に含める */

  /* スレッドごと */
  + @@max_connections * (
      @@read_buffer_size
    + @@read_rnd_buffer_size
    + @@sort_buffer_size
    + @@join_buffer_size
    + @@binlog_cache_size
    + @@thread_stack
    + @@tmp_table_size
    + @@max_allowed_packet /* 必ず割り当てられる訳ではないが、安全のため計算に含める */
  )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;

8.11.4.1 How MySQL Uses Memory を参考にしました。

さて、確認します。

MySQL にログインし、@@max_connections を想定しているスレッド数で書き換えて実行しましょう。

たとえば db.m3.medium の RDS のメモリサイズは 3.75 GB です。 @@max_connections = 16 であれば十分大丈夫そうという判断ができます。

おわりに

他に考慮すべきパラメータや、間違いなどお気づきの点がありましたらコメントなどで教えてください。

参考文献