- 参考
- DBチューニングで大事なこと
- テストデータ作成でエラー
- 解答のクエリ
- インデックスとは?
- なぜか遅くなったのでMySQLのパフォーマンスを見る
- 複合インデックスとは?
- Visual Explainの変遷
- RDBをHDDからSSDにすると性能向上
- まとめ
参考
下記サイトを参考にDBチューニングをやってみましたので、メモ書きを残しておきます。
DBチューニングで大事なこと
インデックス化とバッファプールの2つ。
とりあえずこれだけ覚えておこう。
テストデータ作成でエラー
いきなりテストデータの作成でつまずく。
Insert時に下記のエラー。
Error Code: 2013. Lost connection to MySQL server during query
下記サイトを参考にエラー解決。ありがとうございます。
MySQL Workbench 「Error Code: 2013. Lost connection to MySQL server during query」が発生した場合の対処法 | mebee
どうやら、Insertに時間かかりすぎてタイムアウトしていた模様。
解答のクエリ
まずは、課題に対する解答のクエリを2つ考えました。
・クエリ1
select sum(case when todo.STATUS = 1 then 1 else 0 end) / count(*) * 100 as'TODO', sum(case when todo.STATUS = 2 then 1 else 0 end) / count(*) * 100 as 'Doing', sum(case when todo.STATUS = 3 then 1 else 0 end) / count(*) * 100 as 'Done' from user join todo on user.USER_ID = todo.USER_ID where user.CREATED_AT >= DATE_ADD(NOW(), interval -2 month);
結果1 19.906秒
Todo | Doing | Done |
---|---|---|
33.3137 | 33.3160 | 33.3704 |
・クエリ2
SELECT todo.STATUS, count(*) / (select count(*) from user join todo on user.USER_ID = todo.USER_ID where user.CREATED_AT >= DATE_ADD(NOW(), interval -2 month)) * 100 as '割合' from user join todo on user.USER_ID = todo.USER_ID where user.CREATED_AT >= DATE_ADD(NOW(), interval -2 month) group by todo.STATUS;
結果2 40.453秒
STATUS | 割合 |
---|---|
1 | 33.3137 |
2 | 33.3160 |
3 | 33.3704 |
インデックスとは?
データベースにおけるインデックスとは、目的のレコードを効率よく取得するための「索引」のこと。
インデックス化するとソートされたテーブルがもう1枚できるようなイメージ。
カラムがソートされているので、検索は早い。
行追加時にはテーブル本体とインデックスの両方に書き込むので、追加は遅くなる。
・参考
インデックス化しよう
インデックスの候補はwhere, order by. group by で指定されるカラムになるそうです。
候補
- user テーブル: user_id, created_at
- todo テーブル: user_id, status
user.user_id は PRIMARYのため、元々インデックスが効いており、インデックスを貼り直さなくてOK。
参考サイトと同じSQLです。
alter table user add index idx_created_at(created_at) alter table todo add index idx_status(status) alter table todo add index idx_user_id(user_id)
インデックス作成後に再びクエリ1・2を実行。
- クエリ1 19.906秒→156.860秒
- クエリ2 40.453秒→187.719秒
んん???
めっちゃ遅くなってますやん!なんで!?
なぜか遅くなったのでMySQLのパフォーマンスを見る
インデックス作ったのに遅くなったので、とりあえずパフォーマンスを見てみます。
Server→Dashboardで見れます。
画像取り忘れたのですが、InnoDB Buffer PoolのUsageが90%を超えていました。
なのでPoolのサイズを増やします。※やり方は後述。
バッファプール増加後にクエリ1・2を実行。
- クエリ1 19.906秒→156.860秒→2.031秒
- クエリ2 40.453秒→187.719秒→2.984秒
劇的高速化!!!
インデックス化により、バッファプールの使用量が増えたことで遅くなっていたようです。
Buffer Poolとは?
テーブルの本体データとインデックスの保存領域。
一度読み込んだデータをここに保存して、DBとのI/Oを減らし高速化する。
バッファプールの容量があまりにも大きすぎると、逆にバッファー検索に時間がかかってしまうこともある。
下記サイトがわかりやすい。
チューニング ~データベースチューニング~|PostgreSQLインサイド : 富士通
Amazon RDS for MySQL のパラメータ設定 パート 1: パフォーマンス関連のパラメータ | Amazon Web Services ブログ
・バッファプールサイズの確認
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'
Vriable_name | Value |
---|---|
innodb_buffer_pool_size | 8388608 |
約8MB。
・バッファプールの必要量確認
SELECT table_name, engine, table_rows AS tbl_rows, avg_row_length AS rlen, floor((data_length+index_length)/1024/1024) AS all_mb, #総容量 floor((data_length)/1024/1024) AS data_mb, #データ容量 floor((index_length)/1024/1024) AS idx_mb #インデックス容量 FROM information_schema.tables WHERE table_schema=database() ORDER BY (data_length+index_length) DESC;
all_mbの合計値が必要量なので、330MB。
全然足りないので増やそう。
・バッファプールサイズの変更方法
350MBに増量。
SET GLOBAL innodb_buffer_pool_size = 350000000;
ちゃんと増えているか確認↓
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'
Vriable_name | Value |
---|---|
innodb_buffer_pool_size | 352321536 |
OK!!!
Table Opened Cacheとは?
同時に開けるテーブル数のこと。
・参考
MySQLのテーブルキャッシュについて - Qiita
MariaDB - table_open_cache の最適化 - table_open_cacheは、パフォーマンスを改善するために調整するのに役立つ変数です。 同じテーブルにアクセスする並行セッションは、それぞれ独立してアク - 日本語
パフォーマンス画面では下記のように表示されていて気になったので調べました。Efficiency(効率)97%なので良さげ。
・Table Opened Cacheの確認
select @@table_open_cache;
・現在開いているテーブル数の確認
show global status like 'opened_tables';
キャッシュ領域2000に対して、開いているテーブル数は179なので問題なし。
複合インデックスとは?
複数列のインデックスのこと。
・参考
複合インデックス化しよう
複合インデックス対象は、user テーブルは created_at + user_id 、 todo テーブルは user_id + status 。順番も変えて全部登録する。
alter table user add index idx_created_at_user_id(created_at, user_id) alter table user add index idx_user_id_created_at(user_id, created_at) alter table todo add index idx_status_user_id(status, user_id) alter table todo add index idx_user_id_status( user_id, status)
クエリ1・2を実行すると。
- クエリ1 2.031→0.453
- クエリ2 2.984→0.797
1秒切った!
Visual Explainの変遷
SQL入力欄の上部の虫眼鏡アイコンで表示される。
赤色がFullTableScanで一番時間がかかり、青色がSingle row: constantで最も早い。
クエリ2
インデックス前 40.453秒
todoテーブルがフルスキャンになっている。Rowsは合計で約8M。
インデックス化&バッファプールサイズの増量後 2.984秒
todoテーブルが茶色のNon-Unique Key Lookupに改善、idx_user_idインデックスが使われている。userテーブルはIndexRangeScanに改悪?
Rows合計が875.55Kと大幅に改善。
複合インデックス化後 0.797秒
秒数以外はあんまり変わってない。todoテーブルで複合インデックスidx_user_id_statusが使われている。
参考サイトによると複合インデックス化後はテキストのExplainを見たほうが良いらしいが、ちょっとよくわからない。
とりあえずExtraの列にUsing Indexと表示されていれば良さそう。
クエリ1
インデックス前の画像撮り忘れ。
インデックス化後&バッファプール増量後 2.031秒
複合インデックス化後 0.453秒
Explainの見方
参考サイトに書いてないものの補足。曖昧な部分あり。
・nested loop
テーブル結合の処理をしている箇所っぽい。
userテーブルの1行に対して、マッチする行をtodoテーブルから1行ずつ検索している。
普通のExplainについては下記の参考サイトに書いてあるので割愛。
・VisualExplainについて↓
https://engineering.linecorp.com/ja/blog/mysql-workbench-index-behavior-with-visual-explain/
・普通のExplainについて↓
最低限押さえておきたい EXPLAIN の見かた - Qiita
RDBをHDDからSSDにすると性能向上
約2.4倍になったらしい↓
HDDをSSDにしたらデータベースはどれだけ速くなるか? オラクルと富士通が実験 - Publickey
クライアントからの検索範囲がバッファプール以下のサイズだとSSDに代えても性能向上しないのもポイント。
まとめ
クエリ1
- インデックス前:19.906秒
- インックス後:156.860
- バッファプール増量後:2.031
- 複合インデックス後:0.453
クエリ2
- インデックス前:40.453
- インックス後:187.719
- バッファプール増量後:2.984
- 複合インデックス後:0.797
きっちり早くなってよかった。
何をインデックス化してどのように検索すると早くなるのか、はもっと学習が必要そう。