MySQL - arosh/arosh.github.com GitHub Wiki

my.cnf

[mysqld]
character_set_server = utf8mb4
lower_case_table_names = 1
sql_mode = TRADITIONAL,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY

## slow query log
slow_query_log
long_query_time = 0.1
log_queries_not_using_indexes

## innodb
innodb_lock_wait_timeout = 5

character_set_server

絵文字で泣きたくなければutf8mb4

default_time_zone

AppEngineの中身はUTCなので，UTCに統一しておくと面倒がなくて済みそう。

innodb_file_per_table

MySQL 5.6.6からデフォルトで有効。

Cloud SQLでは5.5より後のバージョンではデフォルトで有効。

https://cloud.google.com/sql/docs/mysql-flags#tips-file-per-table

innodb_flush_log_at_trx_commit

0, 2, 1の順番で安全になり遅くなる。ISUCONでは0にしている。

デフォルトは1だが，性能が気になってきたら2にするのは良いかもしれない。

https://dev.mysql.com/doc/refman/5.6/ja/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

http://dba.stackexchange.com/questions/12611/is-it-safe-to-use-innodb-flush-log-at-trx-commit-2

innodb_large_prefix

5.5と5.6では有効にしないと問題があったらしい。5.7では設定不要。

http://qiita.com/seizans/items/b0e81c44e2b6909a2b32

innodb_lock_wait_timeout

デフォルト値が50秒と微妙な値なので，5くらいにしておけば良さそう。

https://dev.mysql.com/doc/refman/5.6/ja/innodb-parameters.html#sysvar_innodb_lock_wait_timeout

log_queries_not_using_indexes

ぜひ有効にしよう

https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_log_queries_not_using_indexes

long_query_time

デフォルト値が10秒と微妙な値なので，0.1くらいにしておけば良さそう。

https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_long_query_time

max_allowed_packet

5.6のデフォルト値は4MBである。BLOBをMySQLに保存したりしていたら問題になるかもしれない。

https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_max_allowed_packet

query_cache_type

MySQL 5.6.8でデフォルト値が0 (クエリキャッシュ不使用) に変わった。

クエリキャッシュを使うことによるオーバーヘッドが無視できないらしい。

https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_query_cache_type

http://downloads.mysql.com/presentations/20151208_02_MySQL_Tuning_for_Beginners.pdf

lower_case_table_names

1に設定すると，テーブル名を保存するときには小文字に変換するし，テーブル名を比較するときも大文字小文字を区別しない。

https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_lower_case_table_names

slow_query_log

ぜひ有効にしよう

https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_slow_query_log

sql_mode

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

5.7からのデフォルトは ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_traditional

TRADITIONALは以下のものと同じ

STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

TRADITIONAL,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY を指定すると，デフォルト値にSTRICT_ALL_TABLESとNO_AUTO_VALUE_ON_ZEROを足したことになる。

STRICT_ALL_TABLESやNO_AUTO_VALUE_ON_ZEROは付けないと不幸な呪いがかかる。

PIPES_AS_CONCATはMySQLでも文字列結合に||を使えるようにするオプションで，これを付ける人もいる。

http://www.songmu.jp/riji/archives/2013/05/mysqlsql-modetr.html

http://gihyo.jp/dev/serial/01/mysql-road-construction-news/0018

http://sakaik.hateblo.jp/entry/20100303/mysqlmode

ISUCONで使っていた設定 (Cloud SQLで使えないものも含む)

innodb_file_format=Barracuda

innodb_buffer_pool_size = 3G
innodb_log_file_size = 512MB
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT

loose_innodb_buffer_pool_dump_at_shutdown = 1
loose_innodb_buffer_pool_load_at_startup = 1

slow_query_log                = 1
slow_query_log_file           = /var/log/mysql/mysql-slow.log
long_query_time               = 0.1
log-queries-not-using-indexes = 1

MySQL5.7ではinnodb_file_formatのデフォルト値がBarracudaになっているなど，デフォルト値が良くなっている。

Cloud SQLで設定できない項目

  • innodb_file_format … 5.7からBarracudaがデフォルト
  • innodb_buffer_pool_size … なるべくたくさん
  • innodb_log_file_size … 512MBという魔法のパラメータがよく使われる
  • collation_server … utf8mb4_binにしても良いかもしれないけどスキーマ定義時に設定したほうが良いかも？ ISUCONではそうしてる
  • max_connections
  • skip_name_resolve
  • transaction_isolation … REPEATABLE-READが最も安全でデフォルト設定だが，MySQL以外のほとんどのRDBMSではREAD-COMMITTEDが使われている。MySQL 5.7でREAD-COMMITTEDがデフォルトになりそうになったが結局そうなっていない https://yoku0825.blogspot.jp/2015/01/mysql-57.html

文字コード周り

Python

http://methane.hatenablog.jp/entry/2014/10/27/mysqlclient_%E3%81%8C_Django_%E3%81%AE%E6%8E%A8%E5%A5%A8%E3%83%89%E3%83%A9%E3%82%A4%E3%83%90%E3%81%AB%E3%81%AA%E3%82%8A%E3%81%BE%E3%81%97%E3%81%9F

IO周りのパラメータ

innodb_log_file_size

チェックポイントを遅延させるには大きくする必要がある。

innodb_log_files_in_group の設定も重要。

innodb_io_capacity, innodb_io_capacity_max

大きすぎると IO wait が増加する。5.6で改善されたという情報もある。

innodb_flush_sync

チェックポイント時に innodb_io_capacity を無視して全力で書き込む。デフォルトはONだがマルチテナントを考慮するとOFFにすることも検討に値する。

innodb_max_dirty_pages_pct

ダーティページの割合がこのパーセンテージを超えると強制的に（更新処理をブロックして）チェックポイントを実行する。あまり変更しないほうがよさそう。

https://enterprisezine.jp/dbonline/detail/3829?p=2

メンテナンス前にオンラインでこの値を小さく変更して、シャットダウンを高速化するというテクニックもある。

https://www.mk-mode.com/octopress/2015/11/10/mariadb-speedup-shutdown/

シャットダウンプロセスについてのドキュメントは読んでおいたほうがよさそう

https://dev.mysql.com/doc/refman/5.7/en/server-shutdown.html

レプリケーション方式

asynchronous replication

スレーブのリレーログに記録されるのを待たずにマスターが ACK を返す。ACK を返してからリレーログに記録されるまでの間にマスターが壊れるとデータが失われる。

semi-sync replication

スレーブのリレーログに記録されるのを待ってから ACK を返す。マスターのバイナリログに書いて、マスターのストレージエンジンに書いてからスレーブのリレーログに記録する。

まだクライアントにACKは返していないが、別のクライアントからはストレージエンジンの内容が参照できるタイミングが存在する。そのため、ストレージエンジンに書いてからスレーブのリレーログに記録するまでの間にマスターが壊れるとデータがロストしたように見えることがある（ACID 的には違反していないはず…？）。

loss-less semi-sync replication

マスターのバイナリログに書いたあと、ストレージエンジンに書く前にスレーブのリレーログに記録する。安全。

Explain

https://speakerdeck.com/yoshiakiyamasaki/mysqlpahuomansutiyuningutips?slide=39

SHOW SLAVE STATUS

  • マスターのバイナリログのどこまでを読んだか
    • Master_Log_File
    • Read_Master_Log_Pos
  • 書き込み中のリレーログのポジション
    • Relay_Log_File
    • Relay_Log_Pos
  • SQL スレッドがどこまで実行したか（マスターのバイナリログ換算）
    • Relay_Master_Log_File
    • Exec_Master_Log_Pos

つまり Read_Master_Log_Pos = Exec_Master_Log_Pos なら SQL スレッドが追い付いている。Relay_Log_Pos ではないので注意

https://www.slideshare.net/matsunobu/automated-master-failover/14

トランザクション分離レベル

  • READ-UNCOMMITTED … commit していないデータが読める（ダーティリード）
  • READ-COMMITTED … 複数回のSELECTの間にUPDATEがcommitされると結果が違う（ファジィリード）
  • REPEATABLE-READ … 複数回のSELECTの間にINSERTがcommitされると結果が違う（ファントムリード）

ロックに関わる仕様は ANSI で標準化されていない領域であって、製品ごとに差異がある。結局のところ公式ドキュメントを読むのが一番よい。

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

  • REPEATABLE-READ
    • non-locking read、すなわち普通の SELECT は何回呼び出しても同じ結果を返す
    • SELECT with FOR UPDATE or FOR SHARE, UPDATE, DELETE はロックを取るが、条件によって挙動は変わる
      • UNIQUE インデックス + UNIQUE 検索条件 の場合には見つかったレコードのインデックスレコードにのみロックが取られてギャップロックなし（※注意：「見つかった」というところに注意。空打ちならギャップロックが取られる）
      • そうでない場合はギャップロックとネクストキーロックが取られる
  • SERIALIZABLE
    • すべての SELECT に FOR SHARE が付いたのと同じ状態になる

文字コード

  • character_set_client はクライアントが送信するステートメントの文字セットの宣言
  • character_set_connection, collation_connection はサーバーがステートメントを受信した後にサーバー内で利用されるために変換する文字セットと照合順序
  • character_set_results はサーバーからクライアントに返送する際にサーバーが変換する文字セット

MySQL 4.0 ではクライアントに文字セットを決めさせず、MySQL server の管理者が一括で決めるという不思議な設計だった。この挙動に戻すオプションが --skip-character-set-client-handshake である