bekkou68 の日記

Gogengo! や IT 技術など。

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

Amazon RDS MySQL

(最終更新日: 2017/9/25)

はじめに

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 のパラメタ一覧]を参照すると、詳しい説明やエンジンデフォルトの値を見られる。
  • グローバルパラメータは SHOW GLOBAL VARIABLES LIKE "%hoge%"; で確認できる
    • GLOBAL を抜くとスレッドごとのパラメータが取得できる

パラメータの選定プロセス

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

なお、MySQL と RDS がデフォルトで用意している設定値はそれぞれ engine-default と RDS-default と表記しています。

変更すると決めたパラメータ

文字コード

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 (秒) にする。指定した秒数以上かかるクエリを、スロークエリと判定する。
  • MySQL 5.1 から float を指定できるようになった。2013 年頃は Amazon RDS 上では整数でしか指定できなかったが 2015 年頃から小数を指定できるようになった。

サイズ系

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 を ON にする。ヒット率が 6 割以下なら見直したほうがよい。

キャッシュウォーミング

innodb_buffer_pool_dump_at_shutdown / innodb_buffer_pool_load_at_startup

その他

thread_cache_size

  • engine-default=8 + (max_connections / 100) -> 32 にする。engine-default の実質の値はだいたい 10 前後くらいになっていることが多い。
  • キャッシュするスレッドの数。キャッシュしないと毎回スレッドをつくるので遅くなるおそれがある。

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%'; でスレッドの様子を確認できる。

変更しないと決めたパラメータ

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

max_allowed_packet

  • engine-default=4MB。このサイズを超すパケットは送らない想定。

max_connections

  • engine-default=296。十分な数字。
  • EC2 インスタンスを少し増やすときに都度気にするのは面倒なので、しばらく変えなくていいと思う数値にしておくと安心感がある。

table_open_cache

  • engine-default=2000。ひとまずこのままで。すべてのスレッドで使われるオープンテーブルの総合計。
  • 参考: 8.4.3.1 How MySQL Opens and Closes Tables
  • テーブルはスレッドごとにオープンされる。
  • テーブル数やパーティション数が多い場合は要検討。
  • 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 であれば十分大丈夫そうという判断ができます。

おわりに

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

参考文献