mysqlクエリキャッシュとslow_query_logの設定
mysqlクエリキャッシュとslow_query_logの設定
サイトがどうにも重いので、クエリのキャッシュ設定と、
クエリ自体をみなおすことに。
slow_query_logはmysql自体の機能で、設定した秒数よりも
時間のかかったクエリを記録しておく機能。
１．mysqlクエリキャッシュ。
mysqlに発行するクエリに対してキャッシュを設定することができる。
/etc/my.cnf [mysqld] query_cache_limit = 1M query_cache_min_res_unit = 4k query_cache_size = 32M query_cache_type = 1
追記。mysql再起動。
２．mysqlのログ
http://labs.unoh.net/2010/08/how-to-collect-queries-for-tuning-mysql.html
ウノウのブログにあった遅いクエリを出力する方法を試してみるものの、
mysql5.0系と5.1系ではスロークエリログに関する仕様がちがう様子。
自分はtriton関連でどうしても5.0系を使いたいので、5.0と5.1の違いを明記。
表記の違い
5.0 log_slow_query 5.1 slow_query_log
機能の違い
5.0系までは１秒未満のクエリを補足できなかったが、5.1以降は
long_query_time=0.1というような設定ができるようになったそうな。
5.1の使い方
show variables like 'log%';
[mysqld] slow_query_log = 1 slow_query_log_file = /path/to/mysql-slow.log long_query_time = 1
5.0の使い方
[mysqld] log-slow-queries=/var/lib/mysql/mysql/mysql-slow.log long-query-time=5 log-queries-not-using-indexes log-slow-admin-statements
(参考）http://a-programmer.blog.so-net.ne.jp/2007-09-15
遅いクエリをソートして、同じクエリをgruopbyして見る↓
mysqldumpslow -s at /var/lib/mysql/mysql/mysql-slow.log
３．さっそくＳＱＬのチューニング
3-1さっそくログで怪しいのが見つかったのでexplainしてみる
+----+-------------+-------------------+--------+-----------------------------------------------+--------------------+---------+--------------------------------------------------+--------+--------------------
--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+-------------------+--------+-----------------------------------------------+--------------------+---------+--------------------------------------------------+--------+--------------------
--------------------------+
| 1 | SIMPLE | m_bookmark_genre | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using
temporary; Using filesort |
| 1 | SIMPLE | clip_bookmark_ext | index | clip_bookmark_code | clip_bookmark_code | 5 | NULL | 430021 | Using index
|
| 1 | SIMPLE | clip_bookmark | eq_ref | PRIMARY,bookmark_genre_code,data_acquire_date | PRIMARY | 4 | apparelclip.clip_bookmark_ext.clip_bookmark_code | 1 | Using where
|
+----+-------------+-------------------+--------+-----------------------------------------------+--------------------+---------+--------------------------------------------------+--------+--------------------
--------------------------+
3-2実行計画の見方
type 結合型 ここでallやrangeとなる場合は要確認 possible_keys 使用可能インデックス key possible_keysのうち実際に使用されるインデックス ref keyとともに使用されるカラム rows シークされるカラム（予測） extra 追加情報 ussing filesort,using temporaryとなるクエリは注意
3-3一般的に問題になるパターンと、良いパターン
問題大
typeがallとなっている場合、フルスキャンが行なわれている
問題少
typeがocnst primaryまたはuniquie_keyの検索で、かつ一致レコードが１件。最も高速な検索。
typeがrefとなる。＞primaryやuniquie_keyをもたないインデックスを使用して、レコード選択がされる場合。
typeがeq_refとなる＞テーブルをjoinしているＳＱＬで、primaryまたはuniquie_keyを使用して１件ずつデータが検索されることを示している。
extraがusing index ,typeがindexでない（rangeとかね）＞インデックス検索のみで処理が完結することを示す
試して実行時間をみてみるのが一番。
3-4インデックス張ったりなど。
+----+-------------+------------------+------+---------------+------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+------+---------------+------+---------+------+------+----------------------------------------------+ | 1 | SIMPLE | monthly_bookmark | ALL | NULL | NULL | NULL | NULL | 292 | Using where; Using temporary; Using filesort | +----+-------------+------------------+------+---------------+------+---------+------+------+----------------------------------------------+
http://f32.aaa.livedoor.jp/~azusa/?t=mysql&p=index#a_create_index
CREATE INDEX idx_monthly_b_genre ON monthly_bookmark(bookmark_genre_code);
CREATE INDEX idx_monthly_b_date ON monthly_bookmark(data_acquire_date);
SHOW INDEX FROM monthly_bookmark;
+----+-------------+------------------+------+----------------------------------------+---------------------+---------+-------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+------+----------------------------------------+---------------------+---------+-------+------+----------------------------------------------+ | 1 | SIMPLE | monthly_bookmark | ref | idx_monthly_b_genre,idx_monthly_b_date | idx_monthly_b_genre | 5 | const | 16 | Using where; Using temporary; Using filesort | +----+-------------+------------------+------+----------------------------------------+---------------------+---------+-------+------+----------------------------------------------+
