各々のエンジニアが開発に使用するDockerやVagrant等内のデータベースって、あまりチューニングされていなかったりしますよね。
- チューニングされているサーバーは、カーネルパラメータ含め多くのパラメータが調整されているので何が主要なポイントか分からない。
- 耐障害性を捨ててでも開発環境は速く。
そんな方のための開発環境用簡易チューニング記事です。
MariaDBは10.1(10.x系)を想定しています。
※ちなみに筆者は価格の事を棚に上げればSQLServer(MSSQL)や
PostgreSQLも9.4以降(特に9.5)なら好きですし、MariaDBはGalera Clusterがあるから好きです。
仕事ではAWSを扱う機会が非常に多いのでAmazon Auroraは外せません。
設定例
メモリ1Gほどデータベースに割り当てる例です。
[mysqld]
skip-name-resolve
lower_case_table_names = 1
character_set_client = utf8mb4
character_set_database = utf8mb4
character_set_filesystem = binary
character_set_server = utf8mb4
character_set_system = utf8mb4
collation-server = utf8mb4_general_ci
max_connections = 50
thread_cache_size = 50
innodb_file_per_table = 1
innodb_file_format = Barracuda
innodb_large_prefix = 1
innodb_flush_log_at_trx_commit = 2
# グローバルバッファ
key_buffer_size = 128M # 調整
innodb_buffer_pool_size = 512M # まず調整
innodb_log_buffer_size = 16M
max_heap_table_size = 32M
tmp_table_size = 32M
query_cache_size = 64M # 調整
# スレッドバッファ
read_rnd_buffer_size = 1M
sort_buffer_size = 2M
read_buffer_size = 512K
join_buffer_size = 512K
# その他
expire_logs_days = 1
各パラメータについて
skip-name-resolve
skip-name-resolve
開発環境の場合、大体ループバックやプライベートアドレスなのでDNSルックアップを無効化します。
DNSのルックアップを無効化出来ない場合はhost_cache_sizeの値を増やしてホストキャッシュを大きくすることでパフォーマンスを向上させます。
default-character-set, character_set_xxxx, collation-server
character_set_client = utf8mb4
character_set_database = utf8mb4
character_set_filesystem = binary
character_set_server = utf8mb4
character_set_system = utf8mb4
collation-server = utf8mb4_general_ci
デフォルトではlatin1なのでutf8mb4等なんらかの文字コードに統一しておいたほうが良いです。
lower_case_table_names
lower_case_table_names = 1
こちらはアプリケーションの仕様次第にはなりますが、
全て小文字に統一してしまうのが良いでしょう。
max_connections
max_connections = 50
許可される最大のクライアントの同時接続数です。
デフォルトで151(Mysql 5.6, 5.7)で、特に変更しなくても良いのですが、
ローカルの開発用途であれば減らしてしまってよいですし
到達することのない最大接続数を考慮してチューニングしても仕方ないので減らしておきます。
thread_cache_size
thread_cache_size = 50
クライアントが接続を切断したときにスレッドをキャッシュしておき、スレッドのキャッシュが空の場合のみ新しいスレッドが作成されます。
開発用途であれば、max_connections = thread_cache_sizeでとりあえず良いと思います。
※ほとんどの新しい接続でキャッシュされたスレッドを使用できているのであれば減らしても良いですが、調整する場合はThreads_created等の値を見てスレッドキャッシュの効率性を確認してから変更します。
※max_connections / 3 と目安値はいくつかありますが、適切なチューニングを行いたいのであれば、そもそもスレッドキャッシュがどれだけ効いているのか 計測して 調整するべきです。
innodb_file_per_table, innodb_file_format, innodb_large_prefix
innodb_file_per_table = 1
innodb_file_format = Barracuda
innodb_large_prefix = 1
innodb_file_formatをBarracudaへ変更します。
詳しくはDYNAMIC および COMPRESSED 行フォーマットにありますが
テーブルが ROW_FORMAT=DYNAMIC または ROW_FORMAT=COMPRESSED で作成された場合、長いカラム値は完全にオフページに格納され、クラスタ化されたインデックスレコードにはオーバーフローページへの 20 バイトのポインタのみが含まれます。
- DYNAMIC … データ圧縮は行わない行フォーマット
- COMPRESSED … データ圧縮（データサイズ縮小）を行う行フォーマット
が使用可能になります。
innodb_file_per_table はBarracudaを使うために必要です。が、Barracudaでなくても行削除時等にディスクサイズが減らない等の問題もあるため分からなければ1にしておくのが良いと思います。
innodb_large_prefix は utf8mb4を使う場合、767byte問題によりvarchar(255)といったカラムにインデックスが貼れないため。
※utf8の場合 767 / 3 > 255.66…ですが、utfmb48の場合 767 / 4 = 191.75となり、767byteの中で格納出来る文字数の限界が異なります。
innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit = 2
innodb_flush_log_at_trx_commit に関しては、アプリケーションの開発用の仮想環境等であれば1秒間のデータロスが許されないシーン等はありませんし切って良いと思います。
※これは個人の仮想マシン等による開発環境用の設定の場合です。
※本番環境等ではクラッシュした場合に、トランザクションが1秒間失われる可能性がありますのでご注意下さい。
key_buffer_size
key_buffer_size = 128M
MyISAMを使うシーンが少ない場合はそんなに割り当てても意味がないので、
そこまで割かなくて良いかな、と思います。(※アプリケーション用のスキーマはInnoDBでも、mysqlスキーマ等はMyISAMだったりします)
SHOW STATUS して、ヒット率（Key_reads / Key_read_requests）を見て高い値（0.01〜0.05以上等）を出すようであれば上げるくらいで。
innodb_buffer_pool_size
innodb_buffer_pool_size = 512M
テーブルおよびインデックスのデータをキャッシュするメモリー領域です。
この値を大きく設定するほど、ディスクIOが少なくなります。（読取も書込も）
開発用に仮想環境でデータベースサーバーを用意するならば、メモリの６〜８割くらいこのパラメータに割いて良いと思います。
ただし「テーブルおよびインデックスのデータをキャッシュするメモリー領域」ですので、ある一定以上は増やしても全てメモリに載ってしまえば効果が薄くなります。
innodb_log_buffer_size
innodb_log_buffer_size = 16M
ログバッファーを大きくすると、トランザクションがコミットする前にディスクにログを書き込まなくても、大規模なトランザクションを実行できます。
max_heap_table_size
max_heap_table_size = 32M
この変数を設定しても、既存の MEMORY テーブルに影響しませんが、CREATE TABLE などのステートメントでテーブルを再作成したり、ALTER TABLE または TRUNCATE TABLE でテーブルを変更したりした場合は影響します。サーバーを再起動しても、既存の MEMORY テーブルの最大サイズがグローバルの max_heap_table_size 値に設定されます。
tmp_table_size
tmp_table_size = 32M
内部インメモリーの一時テーブルの最大サイズ。(実際の制限値は tmp_table_size と max_heap_table_size の最小値として決定されます。)インメモリーの一時テーブルが制限値を超えると、MySQL はこれを自動的にディスク上の MyISAM テーブルにします。多数の高度な GROUP BY クエリーを実行する場合にメモリーが多くあるときは、tmp_table_size (さらに必要に応じて max_heap_table_size) の値を増やします。この変数はユーザーが作成した MEMORY テーブルには適用されません。
UNIONやDISTINCT ORDER BY、複数テーブルのUPDATE等で内部一時テーブルが作成されることがあるので、tmp_table_sizeの値を増やします。
tmp_table_sizeを増やす場合はmax_heap_table_sizeも合わせて増やしmax_heap_table_size >= tmp_table_size となるようにします。
query_cache_size
query_cache_size = 64M
クエリー結果をキャッシュするためのパラメータです。
innodb_buffer_pool_sizeを割くかquery_cache_sizeを割くか悩む所ですが、
一旦設定例では512Mと64Mとしてみました。（ちょっと64Mは手厚めですが…）
こちらは基本的にinnodb_buffer_pool_sizeを増やしても速度差が出ない場合にquery_cache_sizeを増やしてみる、更新系が重いならquery_cache_sizeを0にするのも有りです。
参照クエリが複雑になればクエリキャッシュは有用ですし、逆にシンプルなクエリの連続で更新も多い場合は逆効果ですしケース・バイ・ケースとしか言いようがないですが、開発環境等は一旦ある程度割いておくのがベターかなと思います。
read_rnd_buffer_size, sort_buffer_size
read_rnd_buffer_size = 1M
sort_buffer_size = 2M
ソート処理で利用されるバッファです。
スレッドバッファなので余り大きい値を割り当てるべきではありません。
read_buffer_size, join_buffer_size
read_buffer_size = 512K
join_buffer_size = 512K
index無しの場合に発揮されるパラメータです。
そもそもここの値を大きくしないとパフォーマンスが出ないのは、インデックスの設計や発行されるクエリが良くない状態ですので、基本的に増やしません。
expire_logs_days
expire_logs_days = 1
バイナリログの保存期限です。
開発環境であれば特に不要ですので、1日たったら削除で良いと思います。
その他
開発環境やとりあえずチューニング、レベルでは不要であると思いますが、次点としては
- table_open_cache
- query_cache_limit
- innodb_io_capacity
- innodb_read_io_threads
- innodb_write_io_threads
このあたりのパラメータも調整していくと、パフォーマンスが上がったり下がったりします。
参考：
https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html
https://dev.mysql.com/doc/refman/5.6/ja/innodb-parameters.html
https://dev.mysql.com/doc/refman/5.6/ja/optimizing-innodb-diskio.html