Amazon Auroraを始めるためのパラメータ資料

Auroraがそこそこ浸透してきたように感じなくもないですが、そのわりに情報がまだ少なめなのは、それだけ従来のMySQLと変わりなく扱え、性能も十分満足いくものだろう、という証なのでしょうか。

中の人も、パラメータチューニングは済んでいるので、基本的にはスケールアップで対応してください、と申しているように、かなり良い調整がされているようです。しかし、インフラエンジニアというかエセDBAたるもの、何がどう調整されているかを具体的に確認しなくては気がすまないため、整理してみたわけです。




デフォルトの設定

パラメータグループについて

Auroraのパラメータは従来と異なり、ノード毎の設定である『DB Parameter Group』と、クラスタ内共通の『DB Cluster Parameter Group』の2つに設定が分かれます。

必要に応じてクラスタの方に、文字コードやレプリケーション周りなど共通すべきものが括りだされ、ノードの方にはメモリ関連など残り全てが入っているようなイメージです。

デフォルト設定とSHOW VARIABLES | STATUS

RDSのパラメータグループには、最初から Aurora5.6 用の DB Parameter Group と DB Cluster Parameter Group が用意されています。管理画面から見てもよいのですが、大半のパラメータの値が空になっていて、それだけでは到底満足できるものではないため、デフォルトのパラメータ一覧と、SHOW GLOBAL VARIABLES、SHOW GLOBAL STATUS の結果をシート別にコピペしました。

情報量が多くてブログ上では表示できないので、スプレッドシートでどうぞ。
  • Aurora5.6 Parameters – Google スプレッドシート(ウェブ版)
  • エクセル版

  • ファイルパスなどはウェブ版だと確認しづらいので、その場合はエクセル版を。この辺はちゃんと細かく確認していきたい人用で全然楽しくないので、次いきましょう。


    設定変数 DBInstanceClassMemory

    該当パラメータの確認

    ノード毎の DB Parameter Group では、DBInstanceClassMemory という変数が扱われており、以下4つのパラメータに使われています。

    innodb_buffer_pool_size{DBInstanceClassMemory*3/4}
    max_connections{log(DBInstanceClassMemory/8187281408)*1000}
    query_cache_size{DBInstanceClassMemory/24}
    thread_cache_size{DBInstanceClassMemory/1005785088}
    どれも重要なパラメータですが、DBInstanceClassMemory が何になるのかを具体的に知っておくと、他のパラメータにも自分で計算して扱いやすくなるので、規則を確認しておきましょう。

    逆算して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
    OSにおけるプロセス・ネットワーク・ファイルキャッシュなどのために確保する容量が、だいたい 5 ~ 9 GiB ということで、スペックの比率に対してあまり増加しない条件となっています。それゆえに、最も低い r3.large で扱えるDBメモリの割合がちょっと少ないかな、という印象はあるものの、だいたいこんなもんだろうと言える数値です。

    Auroraの不満点に、小さなインスタンスタイプを扱えないというものがありますが、Auroraを動かすのに必要なメモリ容量の最低条件があって、それでやらないのかなという推測もしてみたり。r3.large だと単体でも月額3万円弱になるので、テスト環境用だけ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
    文字色を変えてるのは DBInstanceClassMemory を扱っているパラメータです。スペックに比例したりしなかったりですが、概ねえぇんちゃうん、という感想です(何様)。

    データベースをしっかり診る役割のエンジニアの方は、ちゃんと1つ1つ意味を調べるべきではありますが、この辺の値の差異についてはあまり気にしなくても多くの環境・状況において、ほぼ不都合なく稼働すると思われるため、☆おまかせコース☆を貫くスタイルも十分に通用しそうです。


    独自設定すべきパラメータ

    ここまでを踏まえて、独自に上書き設定すべきパラメータがどれで、どのような値を検討すべきか、について軽く考察していきます。

    特に、設定値が空で自動入力される項目で、固定値になっているものが要注意です。それと、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の保全を前提としたツクリにした──というかディスク共有だから使ってなさそう。
    他には Thread Buffer である ***_buffer_size とかありますが、それらも余程でない限り適切な値になっています。アプリケーションの都合で増やす場合は、いつもどおり max_connections に気をつけつつ、SWAPに足が出ないように計算しましょう。


    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時代にカリッカリにチューニングしていた時代と比べると、物足りない感がなくもないですが、秘伝のタレが不要になるのは間違いなく良いことなので、ありがたや~でございます:-)