【今更】MySQLのInnoDB利用時のウォームアップ方法
Webサービスを運用していると、メンテナンス時にDBサーバの再起動が必要になるケースがいつか出てきます。
その際、再起動後そのままの状態でDBサーバをサービスインしてしまうと障害が起きてしまうことがあるので、
その対処法をメモしておきます。（InnoDB利用時編）
■InnoDB buffer pool
MySQLのストレージエンジンであるInnoDBはinnodb buffer poolに書込/読込データを一時的に保管していて、
定期的にibdataに書き込みを行うことでI/O性能を向上させています。
my.cnfのinnodb_buffer_pool_sizeでバッファサイズの指定ができるので、搭載メモリの50%〜70%ぐらいを指定します。
■ウォームアップ対象の洗い出し
buffer poolのサイズは有限なので、できるだけ多い回数呼び出されてるテーブルやINDEXをバッファに乗せておくと良いです。
洗い出す方法はなんでもいいのですが、今回はslow logを使って一定時間に呼ばれたクエリから集計します。
※クエリ記録中は負荷が増大するので、DBへの参照頻度を下げたりするなどしましょう。
また記録が終わったら忘れずにすぐ元の設定に戻しましょう。
全クエリの記録（MySQL5.1以上のみ）
my.cnfでslow logが有効になっているか確認
slow_query_log = 1 slow_query_log_file = mysql-slow.log long_query_time = 1
閾値を0秒にする（全クエリ記録）
mysql > set global long_query_time = 0;
適当な時間記録し続ける（1時間とか）
記録が終わったら戻す
mysql > set global long_query_time = 1;
mysqldumpslowを使って、呼び出し回数が多い順にソートする
mysqldumpslow -s c /var/lib/mysql/mysql-slow.log | less Count: 3034 Time=2.25s (6839s) Lock=0.00s (0s) Rows=70.2 (213050), hoge_user[hoge_user]@[10.*.*.*] select sum_c, total_sum_c from contest where c_id = N and user_id = N order by contest_id desc Count: 1338 Time=2.46s (3287s) Lock=0.00s (0s) Rows=0.8 (1016), hoge_user[hoge_user]@[10.*.*.*] select gift_datetime from gift_history where user_id = N and type = N and i_id = N and a_type = N
■ウォームアップ用クエリの作成
呼び出し回数が多くてデータ件数が多いものを優先的にバッファの載せるようにしましょう。
テーブルデータ確認
show table status LIKE 'i_hogeuser' \G; rowsとかを確認する。
テーブル構成確認
SHOW CREATE TABLE i_hogeuser \G; →PRIMARY KEYとINDEX確認 PRIMARY KEY (`user_id`,`p_type_id`), KEY `Index_i_hogeuser_request1` (`p_id`,`f_type`)
PRIMARY KEYつかって全データ読み込み
select count('user_id'),count('p_type_id') from i_hogeuser;設定されたINDEX使って全読み込み
select count(p_id),count(f_type) from (select p_id,f_type from i_hogeuser order by p_id,f_type) as t1;
EXPLAINでINDEXが利用されているかチェック
mysql> explain select count(p_id),count(f_type) from (select p_id,f_type from i_hogeuser order by p_id,f_type) as t1; +----+-------------+---------------------+-------+---------------+----------------------------+---------+------+-------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+-------+---------------+----------------------------+---------+------+-------+------------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | | 2 | DERIVED | i_hogeuser | index | NULL | Index_i_hogeuser_request1 | 16 | NULL | | 37527| Using index +----+-------------+---------------------+-------+---------------+----------------------------+---------+------+-------+------------------------------+ 2 rows in set (0.02 sec)
といった手順でどんどんウォームアップクエリを作っていきます。
でも本当はめんどうくさいので、
PRIMARY KEYだけでクエリ生成してぶん投げます （ゝω・）
select concat('select ','SUM(',column_name,') from ', table_name,';') from information_schema.statistics where TABLE_SCHEMA = 'スキーマ名';
+------------------------------------------------------------------------------------------------+
| select SUM(collect_id) from t_user; |
| select SUM(history_id) from t_user_history; |
| select SUM(vcomment_id) from t_comment; |
| select SUM(v_id) from t_visit; |
| select SUM(touch_id) from touch; |
+------------------------------------------------------------------------------------------------+
