📑

DB の負荷を計測する方法

2023/11/20に公開

tech

はじめに

業務でテーブル設計をしているときに DB の負荷についても考える機会があったので少しまとめてみました。

今回のテーブル設計の要件

RDB でサイドバーのメニューを管理して動的に実装する予定で以下のような要件があった。なお、実際の業務で要件とは少し変えています。

無限に階層構造を作ることができる
階層関係なくサイドバーを自由に並び替えることができる
１階層につき 15のサイドメニューを置くことができる
バックエンド側では無限に階層を作れるような設計をする。しかし、実際に使われるのは親ノードと子ノードの2階層の予定
移動させたら都度更新処理をするのではなく、編集モードのようなものを作り並び替えを一括更新をする

イメージはこんな感じです。

テーブル設計

上記の要件をもとにテーブル設計を行いました。なお、業務で設計したテーブルとは一部異なります。

今回はサイドバーを階層構造にしたかったのでテーブル設計の木構造を表現するときに有名なモデルのうち、閉包テーブルモデルというものを採用しました。なぜこのテーブルを採用したかは別の記事でまとめます。

こちらにテーブル設計の木構造について簡単にまとまっているので一読お願いします。

DB の負荷を計測

データ数

今回確認したいことは「並び替えを一括更新したときにどの程度負荷がかかるか」である。

以下を考慮してまず、データを用意する

要件にもある通り１階層に 15 のサイドメニューを置くことができる
ユーザーは 2階層まで使うことを想定している

よって、用意するデータは

15 × 15 = 225

DBのスペック

今回は負荷を確かめる方法を確認したいだけなのでローカルの DB で行います。一般的には本番環境と同じスペックの環境を用意して負荷を確かめます。なるべく同じような環境になるようにDocker環境のスペックを揃えました。

cloud sql のスペック

docker のスペック

実行するクエリ

今回 225 件を一括で更新したときの負荷を確認できればいいので、クエリ自体は単純です。

EXPLAIN ANALYZE
UPDATE sidemenu_nodes
SET "order" = "order" + 1

EXPLAIN ANALYZE とは実行計画を表示したいときに最初につけるものです。実行計画についてはのとほど説明します。

データベースの仕組み

負荷について説明するのに DB の内部の仕組みを簡単に理解しておく必要があるので簡単にまとめました。(達人に学ぶDB設計徹底指南書より)

パーサ
- 実行した SQL が文法的に正しいかを確認する場所
カタログマネージャ
- 統計情報を管理している場所
オプティマイザ
- 実行計画を決める場所、実行計画を立てるときにカタログマ- ネージャにアクセスして統計情報を取得し数多の経路の中から最適な経路を選択し、テーブルにアクセスする。いわば頭脳のようなものである。

改めて手順を整理すると、
① 実行されたSQL が文法的に正しいかをまずパーサでチェックする
② SQL の情報をオプティマイザに送る
③ オプティマイザがカタログマネージャーから統計情報を取得する
④ その統計情報をもとにテーブルにアクセスする最適な経路を導き出す
⑤ テーブルにアクセスする

実行計画

先程のクエリを実行すると以下の表のような結果がえられる。

計画時間: 0.083ミリ秒
実行時間: 0.885ミリ秒

処理内容	見積もりコスト	処理行数	行のサイズ	実行時間の開始	実行時間の終了	ループ回数
Update on sidemenu_nodes	0.00..11.81	0	0	0.852	0.852	1
-> Seq Scan on sidemenu_nodes	0.00..11.81	225	10	0.008	0.064	1

実行計画とは ① ~ ④ の時間 (クエリプランと言ったりもする)
実行時間とは ① ~ ⑤ の全体にかかる時間、この結果で一括更新するまでにかかる全体の時間がわかる、重要！！！！

CPU の負荷

上記のクエリを実行したときの結果である。

一番左の CPU の使用率を見ると更新のピーク時で 3％である。負荷は問題なさそう。

以上のことから

cpu の負荷がピーク時 3％
実行時間が 0.885ミリ秒

であることがわかり、DB の負荷的に問題ないことがわかる。

最後に

なんとなく流れは理解してもらえましたか？DB の内部の仕組みなどを知るきっかけになったので勉強になりました。みなさんも実行計画をするときの参考にしてみてください。