ISUCON反省会 MySQL編

2016-10-31 · 1119 文字 · 3 分くらいで読めます

ISUCON

ISUCON · チューニング · MySQL

tamuraです。

今回はMySQLの反省会です。 MySQLはMySQLではなくてPerconaいれるといい感じになる場合もあります。

設定編

Slow Log

すべてのログを取得します。

slow_query_log = 1
slow_query_log_file = /var/log/slow.log
long_query_time = 0
log_queries_not_using_indexes = 1

Performance Schema

初期状態で有効になっている。はずです。

[mysqld]
performance_schema=on

mysq> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set, 1 warning (0.01 sec)

バッファサイズ

innodb_buffer_pool_size

全メモリの70%~80%と行きたいところですが、アプリとの兼ね合いで様子見します。

その他

innodb_bufer_pool_instances
innodb_write_io_threads=20
innodb_read_io_threads=20

innodb_io_capacity=8000
innodb_io_capacity_max=9500
innodb_lru_scan_depth=2000

# かなりでっかくしておく。
innodb_log_file_size=512M
innodb_log_files_in_group=14

# ホスト名の逆引きをしない
skip-resolve-name

# テーブルごとにファイルを作る
innodb_file_per_table

アプリ編

インデックス

とにかくインデックスなのですが、やたらめったらとインデックスを張っても意味がありません。 MySQLはBツリーインデックスを使っています。

カーディナリティ

英語だと cardinality (濃度)のようです。

カラム種類／全レコード数で算出する値です。たとえば男女やtrue/falseのように値が二つしかないものはカーディナリティが低いです。このような値はインデックスに使用するとかえって性能が悪くなります。

auto incrementみたいな一意となるIDであれば、

カラム種類／全レコード数 = 100%

となるので、このようなカラムはインデックスとして有効です。

カーディナリティが50%以下となる場合はインデックスへアクセスする分効率が悪くなるのでそのような項目はインデックスにしないほうがよいです。

isucon中にそこまで見極められるとよいのですが、私のチームはカンでやってました。。。

問い合わせ方法

カラムに対して演算を行うようなSQLではインデックスを使うことができません。

select
  column
from
  table
where
  character_length(keyword) = 6

keywordにインデックスが張ってあり、このようなSQLを実行したとしてもインデックスは使われず、全レコードを参照することになります。こういうときはcharacter_length(keyword)を格納するカラムを用意し、それを参照させるようにします。 (この例だとよくないのですが、これだと前述のカーディナリティが低いのでインデックスに向きません)

複合インデックス

複合インデックスをどうしても使いたい場合は、カーディナリティが高いものを先頭に使います。

order by

インデックス項目をorder byに使うと効率よくソートが可能になります。複合インデックスも使えます。

使っていないインデックス

インデックス更新処理はパフォーマンスを低下させるため、使っていないインデックスを削除します。

select * from sys.schema_unused_indexes

その他

実行に時間がかかっているSQL

select * from statement_analysis

インデックスが使えていないSQL

select * from statements_with_full_table_scans

一時表をもっとも使用しているSQL

select * from statements_with_temp_tables

まとめ

まだあると思いますので、随時追記していきたいと思います。

設定編