MySQL設定メモ

インストール後、いつも変更するMySQLの設定をメモ。
特にinnodb_file_per_tableと文字コード周りは運用後に変更しにくいので、最初に設定しておくこと!

参考

sql_mode

STRICT_TRANS_TABLESを追加して、
列制約に違反する形でデータを書き込むとエラーが発生するように指示します。
サイズオーバーだったり、Not Null列にNullを挿入しようとした場合にエラーハンドリングできるようになります。

innodb_file_per_table

InnoDBの実データが格納される.ibdファイルをテーブル毎に作成します。
これを設定しておかないと、ibdataXに全テーブルのデータが詰め込まれてしまいます。
テーブルを削除してもibdataXは縮小されることは無く、空き領域として再利用される事となります。
ディスクの空き容量が少なくなった場合に困るため、innodb_file_per_tableは必ず有効にしています。

innodb_buffer_pool_size

InnoDBの割り当てメモリ。基本的な設定ですね。
けちらずどかんと割り当てましょう。

innodb_log_file_size

REDOログのファイルサイズ。
InnoDBのクラッシュ時のリカバリにも使われます。
大きなトランザクションのパフォーマンスが向上するので、
バックアップのリストアにかかる時間も改善します。
場合によりけりですが、最初は128MB〜256MBに設定しています。
なお、この値を変更する際、MySQLの停止後、ib_logfileXを削除してからMySQLを起動する必要があります。

innodb_file_format

Barracudaにしておくことでメモリ効率が良くなる・・らしい。
5.6以降はデフォルトのmy.cnfに記述されているかも。
※5.6以降デフォルトかもというのは勘違いでした

character_set_client, character_set_connection, character_set_database

文字コードの設定。
ちょっと前まではutf8だったんですが、最近はサロゲートペアを含むutf8mb4にしておいた方が無難です。

collation_database, collation_connection, collation-server

照合順序の設定。
utf8mb4の場合は色々あるのですが、個人的にはutf8mb4_binに設定したい。
デフォルトはutf8mb4_general_ciになるのですが、こいつはアルファベットの大文字と小文字を区別しません。
たとえばユニーク制約を設定した際、”aaa”と”AAA”は同じ値となり、同時に挿入する事はできません。
また、”aaa”という値を検索した際、”AAA”が検索にヒットします。
便利ではあるのですが、ユーザIDの検索や、重複チェックの際に誤爆してしまうリスクが怖いので、経験上collateは完全一致になるようにしたいです。

slow_query_log, slow_query_log_file, long_query_time

スロークエリの設定。
long_query_timeは1ぐらいにしています。

max_connections

最大コネクション数。これも基本的な設定です。
コネクションあたりのメモリを計算して、
innodb_buffer_pool_sizeと足した値が物理メモリサイズを越えないよう気をつけつつ、適切な値にしておきましょう。

tmp_table_size, max_heap_table_size

一時テーブルのサイズとheapテーブルの最大サイズを指定します。
一時テーブルの場合は、この2つの設定両方の最小値を見るため、同じ値に設定しておきましょう。
物理メモリと相談ですが、なるべく大きい値にしたいところ。

細かいチューニングをしていくとまだ色々とあるのですが、
簡易的な初期設定はこんなところでしょうか。
また、レプリケーションの設定をする場合はさらに色々とチューニングが可能です。