okinawa

IT勉強メモ

GMOのDBチューニング課題やってみた

参考

下記サイトを参考にDBチューニングをやってみましたので、メモ書きを残しておきます。

recruit.gmo.jp

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で見れます。

performance dashboard
performance 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;

data size
data size

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 open cache
table open cache

・Table Opened Cacheの確認

select @@table_open_cache;

show table open cache
show table open cache

・現在開いているテーブル数の確認

show global status like 'opened_tables';

opened table
opened table

キャッシュ領域2000に対して、開いているテーブル数は179なので問題なし。

複合インデックスとは?

複数列のインデックスのこと。

・参考

qiita.com

複合インデックス化しよう

複合インデックス対象は、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と表示されていれば良さそう。

text explain
text explain

クエリ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

きっちり早くなってよかった。

何をインデックス化してどのように検索すると早くなるのか、はもっと学習が必要そう。