Auroraがそこそこ浸透してきたように感じなくもないですが、そのわりに情報がまだ少なめなのは、それだけ従来のMySQLと変わりなく扱え、性能も十分満足いくものだろう、という証なのでしょうか。
中の人も、パラメータチューニングは済んでいるので、基本的にはスケールアップで対応してください、と申しているように、かなり良い調整がされているようです。しかし、インフラエンジニアというかエセDBAたるもの、何がどう調整されているかを具体的に確認しなくては気がすまないため、整理してみたわけです。
デフォルトの設定
パラメータグループについて
Auroraのパラメータは従来と異なり、ノード毎の設定である『DB Parameter Group』と、クラスタ内共通の『DB Cluster Parameter Group』の２つに設定が分かれます。必要に応じてクラスタの方に、文字コードやレプリケーション周りなど共通すべきものが括りだされ、ノードの方にはメモリ関連など残り全てが入っているようなイメージです。
デフォルト設定とSHOW VARIABLES | STATUS
RDSのパラメータグループには、最初から Aurora5.6 用の DB Parameter Group と DB Cluster Parameter Group が用意されています。管理画面から見てもよいのですが、大半のパラメータの値が空になっていて、それだけでは到底満足できるものではないため、デフォルトのパラメータ一覧と、SHOW GLOBAL VARIABLES、SHOW GLOBAL STATUS の結果をシート別にコピペしました。情報量が多くてブログ上では表示できないので、スプレッドシートでどうぞ。
ファイルパスなどはウェブ版だと確認しづらいので、その場合はエクセル版を。この辺はちゃんと細かく確認していきたい人用で全然楽しくないので、次いきましょう。
設定変数 DBInstanceClassMemory
該当パラメータの確認
ノード毎の DB Parameter Group では、DBInstanceClassMemory という変数が扱われており、以下４つのパラメータに使われています。| innodb_buffer_pool_size | {DBInstanceClassMemory*3/4} |
| max_connections | {log(DBInstanceClassMemory/8187281408)*1000} |
| query_cache_size | {DBInstanceClassMemory/24} |
| thread_cache_size | {DBInstanceClassMemory/1005785088} |
逆算してDBInstanceClassMemoryを求める
全てのインスタンスタイプの、スペックやSHOWを整理して、DBInstanceClassMemoryがどのように決められているかを確認していきます。| r3.large | r3.xlarge | r3.2xlarge | r3.4xlarge | r3.8xlarge |
|---|---|---|---|---|
| 総メモリ（GiB/Bytes） | ||||
| 15.0 GiB | 30.5 GiB | 61.0 GiB | 122.0 GiB | 244.0 GiB |
| 16,106,127,360 | 32,749,125,632 | 65,498,251,264 | 130,996,502,528 | 261,993,005,056 |
| innodb_buffer_pool_size = {DBInstanceClassMemory*3/4} | ||||
| 7.5 GiB | 18.7 GiB | 41.2 GiB | 86.3 GiB | 176.4 GiB |
| 8,007,974,912 | 20,103,299,072 | 44,296,044,544 | 92,676,292,608 | 189,442,031,616 |
| DBInstanceClassMemory = innodb_buffer_pool_size * (4/3) | ||||
| 9.9 GiB | 24.9 GiB | 55.0 GiB | 115.0 GiB | 235.2 GiB |
| 10,677,299,883 | 26,804,398,763 | 59,061,392,725 | 123,568,390,144 | 252,589,375,488 |
| 総メモリに対するDBInstanceClassMemoryの割合 = DBInstanceClassMemory / 総メモリ | ||||
| 66.3% | 81.8% | 90.2% | 94.3% | 96.4% |
| OS用に確保されるメモリ容量 = 総メモリ – DBInstanceClassMemory | ||||
| 5.1 GiB | 5.5 GiB | 6.0 GiB | 6.9 GiB | 8.8 GiB |
Auroraの不満点に、小さなインスタンスタイプを扱えないというものがありますが、Auroraを動かすのに必要なメモリ容量の最低条件があって、それでやらないのかなという推測もしてみたり。r3.large だと単体でも月額３万円弱になるので、テスト環境用だけRDS for MySQLにすることになったりして、ちょっとなーという問題を抱えております。
インスタンスタイプごとの値
SHOW VARIABLES で確認できる、実際の設定値を、全インスタンスタイプごとに異なる項目だけを確認していきます。DBInstanceClassMemory を扱っているパラメータや、管理画面上で値が空のパラメータに注目です。| r3.large | r3.xlarge | r3.2xlarge | r3.4xlarge | r3.8xlarge | |
|---|---|---|---|---|---|
| back_log | 250 | 450 | 650 | 850 | 900 |
| hostname | – | – | – | – | – |
| innodb_buffer_pool_size | 8,007,974,912 | 20,103,299,072 | 44,296,044,544 | 92,676,292,608 | 189,442,031,616 |
| innodb_open_files | 6000 | 12000 | 24000 | 48000 | 48000 |
| innodb_purge_batch_size | 300 | 300 | 900 | 900 | 1800 |
| innodb_purge_threads | 1 | 1 | 3 | 3 | 6 |
| innodb_read_io_threads | 4 | 8 | 16 | 32 | 64 |
| max_connections | 1000 | 2000 | 3000 | 4000 | 5000 |
| query_cache_size | 444,890,112 | 1,116,894,208 | 2,460,900,352 | 5,148,687,360 | 10,524,614,656 |
| server_id | – | – | – | – | – |
| server_uuid | – | – | – | – | – |
| table_open_cache | 6000 | 12000 | 24000 | 48000 | 48000 |
| table_open_cache_instances | 4 | 8 | 16 | 32 | 32 |
| thread_cache_size | 10 | 26 | 58 | 122 | 251 |
データベースをしっかり診る役割のエンジニアの方は、ちゃんと１つ１つ意味を調べるべきではありますが、この辺の値の差異についてはあまり気にしなくても多くの環境・状況において、ほぼ不都合なく稼働すると思われるため、☆おまかせコース☆を貫くスタイルも十分に通用しそうです。
独自設定すべきパラメータ
ここまでを踏まえて、独自に上書き設定すべきパラメータがどれで、どのような値を検討すべきか、について軽く考察していきます。特に、設定値が空で自動入力される項目で、固定値になっているものが要注意です。それと、DiskI/Oが従来と全く別物になっただけに、全体的に読み書きの上限やタイミングに関わるパラメータが、軒並み編集不可になっているのは、まぁそうだよねといったところでしょうか。
| 基本環境 | |
|
character_set_*** （Cluster Parameter） |
環境次第ですが、多くの場合、全て utf8 に。character-set-client-handshake は boolean ですが、ノーコメントで:-) |
|
collation_*** （Cluster Parameter） |
同上で utf8_general_ci に。 |
|
time_zone （Cluster Parameter） |
アプリケーション次第ですが、フレームワークが自動変換してくれる場合は、設定なしの空＝UTC でよいです。 |
| Connection | |
| max_connections | デフォの1000ずつ増加では、足りない場面が出そうなので、その場合は アプリケーションサーバー台数 * 最大スレッド数 を見て調整します。ただし、上限が 16000 です。 |
|
wait_timeout thread_cache_size |
接続維持にする場合、デフォの 28800 と 自動設定 で大丈夫です。毎回切断の場合は、要調整です。 |
| InnoDB | |
| innodb_buffer_pool_size | デフォの {DBInstanceClassMemory*3/4} でよいですが、変更する可能性アリという意味で書いておきます。 |
| innodb_file_format | innodb_file_per_table が有効だと、Barracuda にすることはあるのですが、innodb_file_per_table は変更可とはいえデフォの無効がよさげなので、こちらもデフォの Antelope となります。 |
|
innodb_flush_log_at_trx_commit （Cluster Parameter） |
デフォ 1 は毎回のログ出力のために遅い処理ですが、IOPSはそれなりに上限が大きいので、データ保全優先でいいと思います。2 にするのは、インスタンスタイプを上げたくないけどIOPSが厳しそうな状況とか、になってからでよさげ。 |
| innodb_stats_on_metadata | デフォが OFF で、ON だとI/OやCPUが重くなったりする恐れがあるけど、必要があれば ON にします。 |
| innodb_lock_wait_timeout | デフォが 50（秒）固定とちょっと長いので、5 or 10 あたりに変更します。 |
| innodb_thread_concurrency | 編集不可。0 固定なので最大限並列化してくれます。 |
|
innodb_read_io_threads innodb_write_io_threads |
編集不可。read はスペックで変化し、write は 4 固定の、おまかせコースです。 |
|
innodb_io_capacity innodb_io_capacity_max |
デフォが 200 / 2000 の固定で編集不可。バックグラウンドのことは俺に任せろーッ！ﾊﾞﾘﾊﾞﾘ！！ってことなんでしょう。 |
| innodb_log_buffer_size | パラメータグループから削除。デフォの 8388608 固定となりました。 |
| Global Buffer | |
|
tmp_table_size mas_heap_table_size |
デフォが 16MB 固定と少なめなので、ほどほどに設定されるよう {DBInstanceClassMemory/24} などに。 |
| Cache | |
|
query_cache_type query_cache_size |
有効な機能なので（検証記事）、デフォの 1 & {DBInstanceClassMemory/24} でいき、のちのち dynamic に調整します。 |
| Log | |
| general_log | 当然ですが、デフォは 0 です。 |
|
slow_query_log long_query_time |
1 , 0.5(s) くらいにしておきます。 |
| log_output | TABLEだと mysql.slow_log などに積もるので、好みやリスク回避で FILE にします。FILEにすると、OS上は /rdsdbdata/log/ 配下に様々なログが置かれますが、いざ取得する際はそれ以下のパスを指定して取得します。FILEの場合、ログは1時間ローテートとなり、1日以上経過で削除となります。 |
| sync_binlog | 編集不可。デフォ 1 で固定です。IOPS上限は十分だし、binlogの保全を前提としたツクリにした──というかディスク共有だから使ってなさそう。 |
Terraform の DB Cluster Parameter Group
従来の DB Parameter Group は Terraform の aws_db_parameter_group で作成できるのですが、DB Cluster Parameter Group にはまだ対応していません。それについては、CHANGELOG にあるとおり、v0.7 で aws_rds_cluster_parameter_group が追加されるので、しばし見守っていればよさそうです。
──以上で、およそ基本は抑えられたと思います。多くの設定がデフォで適切になってくれたり、Aurora仕様の固定設定になったりで、レシピに記述するパラメータ数がグッと減って、いい感じです。
HDD時代にカリッカリにチューニングしていた時代と比べると、物足りない感がなくもないですが、秘伝のタレが不要になるのは間違いなく良いことなので、ありがたや～でございます:-)
