![SQLの解析
? slow_queryを設置する
## /etc/my.cnfに以下を記述
[mysqld]
long_query_time=1
log-slow-queries=/var/log/slow_query.log
## 以下を記述するとインデックスを使用しないSQLも
拾える
log-queries-not-using-indexes](https://image.slidesharecdn.com/dbturning-121227110029-phpapp01/85/DB-13-320.jpg)
![SQLの解析
? mysqlslowdumpコマンドで解析
$ mysqldumpslow [option] [log_?le]
## よく使うオプションは-s(並び替え)
-s c → 総クエリ数の多い順
-s l → 総ロックタイムの長い順
-s r → 総行数の多い順
-s t → 総実行時間の長い順
-s a(l ? r ? c) → 各平均の長い順](https://image.slidesharecdn.com/dbturning-121227110029-phpapp01/85/DB-14-320.jpg)
![## 文字列 → S、 数字 → N と置換してくれる
## Count:総クエリ数 Time=平均実行時間(総実行時間)
## Lock=平均ロックタイム(総ロックタイム) Rows=平均行数(総行数)
$ mysqldumpslow -s c slow_query.log ? less
Count: 49 Time=1.49s (73s) Lock=0.00s (0s) Rows=1.0 (49),
user[user]@2hosts
SELECT * FROM tbl1 WHERE name = 'S' ORDER BY id LIMIT N
Count: 36 Time=1.47s (52s) Lock=0.00s (0s) Rows=1.0 (36),
user[user]@2hosts
SELECT * FROM tbl2 WHERE id = 'S' AND deleted = 'S' ORDER BY id LIMIT N
.
.
.](https://image.slidesharecdn.com/dbturning-121227110029-phpapp01/85/DB-15-320.jpg)
More Related Content
What's hot (20)
Similar to お金をかけない顿叠チューニング (20)
![【ウェブ セミナー】AI 時代のクラウド データ ウェアハウス Azure SQL Data Warehouse [実践編]](https://cdn.slidesharecdn.com/ss_thumbnails/webinarsqldw20170726-180220004900-thumbnail.jpg?width=560&fit=bounds)
Recently uploaded (11)
お金をかけない顿叠チューニング
- 2. DBサーバが重たい場合の対応 ? サーバ側 ? 台数増やす、スペックアップ ? DB側 ? レプリケーション 台数増やす ? 尘测.肠苍蹿チューニング ? アプリ側 ? 厂蚕尝チューニング
- 3. DBサーバが重たい場合の対応 ? サーバ側 ? 台数増やす、スペックアップ ? DB側 ? レプリケーション 台数増やす ? 尘测.肠苍蹿チューニング ? アプリ側 ? 厂蚕尝チューニング
- 4. DBサーバが重たい場合の対応 ? サーバ側 ? 台数増やす、スペックアップ ? DB側 ? レプリケーション 台数増やす ? 尘测.肠苍蹿チューニング コストがかかる！ ?監視対象が増える！ アプリ側 重たいSQLが残ったまま！ ?厂蚕尝チューニング ?運用の手間がかかる ?サーバが重たい原因が残ったまま
- 5. DBサーバが重たい場合の対応 ? サーバ側 ? 台数増やす、スペックアップ ? 今回チューニングするのはこれ DB側 ? レプリケーション 台数増やす ? 尘测.肠苍蹿チューニング ? アプリ側 ? 厂蚕尝チューニング
- 6. 尘测.肠苍蹿チューニング
- 7. my.cnfの設定方法 ## 雛形ファイルがあるのでそれをコピーする $ pwd /usr/share/doc/MySQL-server-5.5.28 $ ls -1 my-* my-huge.cnf my-innodb-heavy-4G.cnf my-large.cnf my-medium.cnf my-small.cnf $ cp my-huge.cnf /etc/my.cnf
- 8. my.cnfの雛形ファイルの種類 ファイル名 適したサーバ my-small.cnf 64MB以下のメモリを搭載したサーバ my-medium.cnf 128MB以下のメモリを搭載したサーバ my-large.cnf 512MB以下のメモリを搭載したサーバ 1GB～2GB以下のメモリを搭載した my-huge.cnf サーバ my-innodb- 4GBのメモリとInnoDBで作成したDB heavy-4G.cnf で構築したサーバ
- 9. my.cnfのチューニング my.cnfのチューニング メモリ割当の最適化 以下の点に留意しながら最適化を行う ?サーバーのリソース ?サーバー上で稼働しているミドルウェア ? key_bu?er_size ? key_bu?er_size = 256M ? インデックスを保存するバッファサイズ ? メモリの余裕がある場合は多く振る
- 10. my.cnfのチューニング ? sort_bu?er_size ? sort_bu?er_size = 2M ? ソートを行う場合のバッファサイズ ? ORDER ? GROUP BYの実行速度があがる ? record_bu?er_size ? record_bu?er_size = 2M ? インデックスを含まないクエリの実行速度 があがる
- 11. my.cnfのチューニング 最適なメモリサイズの割り振り key_bu?er_size +(sort_bu?er_size + record_bu?er_size) max_connections 実メモリサイズ + スワップメモリサイズ ※MySQL以外のミドルウェアがある場合は そちらに振るメモリも考慮して割り振る
- 12. 厂蚕尝チューニング
- 13. SQLの解析 ? slow_queryを設置する ## /etc/my.cnfに以下を記述 [mysqld] long_query_time=1 log-slow-queries=/var/log/slow_query.log ## 以下を記述するとインデックスを使用しないSQLも 拾える log-queries-not-using-indexes
- 14. SQLの解析 ? mysqlslowdumpコマンドで解析 $ mysqldumpslow [option] [log_?le] ## よく使うオプションは-s(並び替え) -s c → 総クエリ数の多い順 -s l → 総ロックタイムの長い順 -s r → 総行数の多い順 -s t → 総実行時間の長い順 -s a(l ? r ? c) → 各平均の長い順
- 15. ## 文字列 → S、 数字 → N と置換してくれる ## Count:総クエリ数 Time=平均実行時間(総実行時間) ## Lock=平均ロックタイム(総ロックタイム) Rows=平均行数(総行数) $ mysqldumpslow -s c slow_query.log ? less Count: 49 Time=1.49s (73s) Lock=0.00s (0s) Rows=1.0 (49), user[user]@2hosts SELECT * FROM tbl1 WHERE name = 'S' ORDER BY id LIMIT N Count: 36 Time=1.47s (52s) Lock=0.00s (0s) Rows=1.0 (36), user[user]@2hosts SELECT * FROM tbl2 WHERE id = 'S' AND deleted = 'S' ORDER BY id LIMIT N . . .
- 16. 重たいSQLの傾向 ? 複数テーブルをまたいだORDER BY SELCT * FROM tbl1 INNER JOIN tbl2 ON ... INNER JOIN tbl3 ON ... ORDER BY tbl1.created DESC, tbl2.created.DESC LIMIT 0, 20; ? ページングでよく使われるSQL ? Using temporary; Using ?lesort がどうして も発生してしまう
- 17. 重たいSQL対応その1 ? SQLを分割する(ID取得) SELCT tbl1.id FROM tbl1 INNER JOIN tbl2 ON ... INNER JOIN tbl3 On ... ORDER BY tbl1.created DESC, tbl2.created DESC LIMIT 0, 20; ? レコードをIDのみにする ? レコードを減らすだけでもクエリ実行時間は 結構減る
- 18. 重たいSQL対応その1 ? SQLを分割する(表示データ取得) SELCT * FROM tbl1 INNER JOIN tbl2 ON ... INNER JOIN tbl3 On ... WHERE tbl1.id = 1 ? 取得したID毎に表示用のSQLを実行する ? SQL本数は増えるが、IDをキーに取得してい るので実行時間は0.1secとかで収まる
- 19. 重たいSQL対応その2 ? 無駄なJOIN、ORDER BYを減らす SELCT tbl1.id FROM tbl1 INNER JOIN tbl2 ON ... // INNER JOIN tbl3を削除 ORDER BY tbl1.created DESC LIMIT 0, 20; ? JOIN先のテーブルの件数次第では、JOINを減 らすだけでXsec下げれたりする ? ORDER BYも同様 ? ある程度の不整合に目をつぶるかも。。
- 20. 重たいSQLの対応まとめ ? 最低限のレコードを取得するようにする ? なるべくJOINの数を減らす ? テーブルをまたぐORDER BYはなるべく避け る 基本的な事をやるだけでも数sec落とす事は可能 ※10万件越えたあたりから効果は現れる？
- 21. それでもダメなら
- 23. おわり