PostgreSQLの基本構造とチューニング

PostgreSQLの 基本構造とチューニング 対応バージョン 11 Mariko Nakai

進行について • PostgreSQLの基本について 約30分通しで説明 • 15分の質問タイム • SQLのチューニングについて 約30分通しで説明 • 15分の質問タイム 全体を通し、トータル1時間半を予定しています。 途中でのご質問は控えていただき、質問タイムにまとめてご質問ください。 その際、該当のページ番号をお知らせください。 ページ番号は各ページ右下にあります。 2

わかるようになる事 PostgreSQLデータベースの基本構造 遅いSQLの見つけ方 SQLの実行計画、実行時間の取得と見方 SQLのチューニング方法 3

PostgreSQLの基本構造

PostgreSQL？ それ何？ 何がいいの？ いつもこれを使うの？ データベースです。 用途を問わず無料で利用可能。 いいえ。 ポストグレエスキューエル、ポストグ レス、ポスグレ、などと呼ばれます。 標準的なSQL文が利用可能。 良さはありますが、他のRDBMSに も他の良さがあります。 世の中に何個かある、リレーショナ ルデータベース管理システム＝ RDBMSの一つです。 それらを比較し、そのシステムに最 も合うと判断されたものを使用す るのが通常です。 同じようなRDBMSには、Oracle、 MySql、SQLserverなどがありま す。 5

" 少し考えてみましょう。 アプリからデータベースに対し、データを登録しました。 このデータはデータベースの中にあり、取り出すこともできます。 では、データベースの中にデータがあるってどういうことなんで しょうか？ 6

7

データベースはファイル群 PostgreSQLのデータベースの集合体をデータベースクラスタと呼び、実体はファイル群 $PGDATA base 16384 16386 pg_wal Postgresql.conf 12345 12345_vm データベースクラスタ ＄PGDATAはデータベースクラスタの最上位ディレクトリでインストール時に用意される変数、特定のパスが設定されている この配下にデータベースのデータがすべて存在しているよという場所 8

スキーマ、テーブル、インデックスの保存先 $PGDATA base 16384 16386 12345 12345_vm pg_wal Postgresql.conf TABLEname: company OID: 12345 DBname: admin DBname: east_india_company OID: 16384 OID: 16386 9

データベースクラスタ内の各ディレクトリやファイルの用途 名称 用途 $PGDATA データベースクラスタの最上位ディレクトリ base/数字 テーブルなどのオブジェクトを格納するディレク トリ base/数字/数字 テーブルなどのオブジェクトのファイル global データベース内の定義情報（ユーザー、オブ ジェクトなど）を格納するディレクトリ pg_hba.conf 認証設定ファイル pg_wal WALファイル（変更履歴記録ファイル）を 格納するディレクトリ postgres.conf postgres.auto.conf データベースクラスタ起動時に読み込まれる 設定ファイル postmaster.pid 起動に作成されるPIDファイル 実際にサーバ内でディレクトリやファイルを確認 することができる。 10

PostgreSQLのプロセス PostgreSQLを起動すると複数のプロセスが連携して動作し、RDBMSとしての機能が使える状態となります。ここで はそれらのプロセスを役割毎に3つに分類して紹介します。 プロセス＝メモリ内で実行されるプログラム postmaster プロセス PostgreSQLのメインプログラムのそのもので、全体を管理するためのプロセス。コントローラーのような役割で、接続要 求をうけて別のプロセスに引き継いだり、バックグランドプロセスを管理したりする。 バックエンド プロセス クライアントからの接続要求を受け、SQL処理を行うプロセス。複数のクライアントから接続がある場合、その数のプロセ スが実行される。 バックグラウンド プロセス SQL処理とは別に、データベースの内部動作を実行するプロセス。メモリ上にあるデータをファイルに書き込むプロセス、 ログファイルの書き出しプロセス、VACUUMの処理の実行プロセスなどいくつかプロセスがある。 11

PostgreSQLのメモリ領域 PostgreSQLが使用するメモリも役割によって、領域を分けて使用されます。 メモリ＝プログラムを実行する作業スペース バッファ＝メモリの一部で一時保存場所 共有バッファ データベースクラスタで 共有 SQL実行に必要なデータをディスクから読み込み保管する領域。共有バッファにすでにデータがあればディス クアクセス不要となるためその分高速になる。SQL実行後もバッファ上にデータは残される。 WALバッファ UPDATE/INSERT/DELETEなどの変更履歴を保管する領域。蓄積された変更履歴は主にSQL実行 のコミット時にWALファイルに永続化される。 ワークメモリ 一つのバックエンドプロセス毎に割り当てられ、ソート処理やハッシュ処理に使用される。 同じようにバックグラウンドプロセス毎にメンテナンスワークメモリ、自動VACUUMワークメモリも割り当てられる。 12

接続毎に実行されるバックエンドプロセス PostgreSQL インストールサーバ postmaster プロセス ① 共有バッファ WALバッファ ② 接続開始 ③ バックエンド-1 プロセス バックグラウンド プロセス ワークメモリ ディスク内 バックグラウンド プロセス $PGDATA base pg_wal ※このページの解説は次ページにあり。 13

接続毎に実行されるバックエンドプロセス 解説 ①クライアントが何等かの方法でPostgreSQLへの接続要求をし、メインプロセスがそれ受ける。 ②メインプロセスはその接続要求に対して、一つバックエンドプロセスが実行する。 ③以降はこの接続は専用のバックエンドプロセスによってSQL実行等の操作が可能になる。 14

SQL実行 必要データをディスクから共有バッファに読み込み PostgreSQL インストールサーバ 共有バッファ postmaster プロセス WALバッファ ④ SELECT実行 ① バックエンド-1 プロセス ワークメモリ バックグラウンド プロセス ③ ディスク内 ② バックグラウンド プロセス $PGDATA base pg_wal ※このページの解説は次ページにあり。 15

SQL実行 必要データをディスクから共有バッファに読み込み 解説 ①クライアントはSQLの実行を行う。 ②必要なデータをディスクにあるファイルから取得。 ③共有バッファに一時読み込み。 ④以降は、すでに共有バッファにあるデータを参照するようになり、ディスクへのアクセスが不必要になる分早く処理できるようになる。 バックエンドプロセス毎に、ワークメモリという作業スペースが用意され、共有バッファから取得したデータにソートなどのさらなる加工が 必要な場合はこのメモリスペースを使用する。 16

SQL実行 変更履歴をWALバッファへ→コミット→WALファイルとして永続化 PostgreSQL インストールサーバ 共有バッファ postmaster プロセス WALバッファ ③ ② INSERT実行 ① バックエンド-1 プロセス バックグラウンド プロセス ワークメモリ ディスク内 $PGDATA base バックグラウンド プロセス ④ pg_wal ※このページの解説は次ページにあり。 17

SQL実行 変更履歴をWALバッファへ→コミット→WALファイルとして永続化 解説 ①クライアントがSQLの実行を行う。 ②このSQL実行により、データの変更が行われた。 ③この時行われた変更についてはWALバッファに変更履歴を一時保管する。 ④クライアントからSQL実行に対してのコミットがあった段階で、専用のバックグランドプロセスによりWALバッファの変更履歴はディスク のWALファイルに永続化され、WALバッファで一時保管されていたデータはなくなる。 18

接続毎に実行されるバックエンドプロセス 共有バッファのデータは共有 PostgreSQL インストールサーバ 共有バッファ postmaster プロセス バックエンド-1 プロセス ワークメモリ SELECT実行 バックグラウンド プロセス ② ディスク内 ① バックエンド-2 プロセス ワークメモリ WALバッファ バックグラウンド プロセス $PGDATA base pg_wal ※このページの解説は次ページにあり。 19

接続毎に実行されるバックエンドプロセス 共有バッファのデータは共有 解説 新たなクライアントから接続要求があり、この接続に対しても新しくバックエンドプロセスと専用のワークメモリが用意される。 ①新たなクライアントがSQL実行を行う。 ②すでに必要なデータが共有バッファにあればディスクへのアクセスは必要なく共有バッファのデータを使用できる。 20

SQL実行 不足があれば必要データをディスクから共有バッファに読み込み PostgreSQL インストールサーバ 共有バッファ postmaster プロセス バックエンド-1 プロセス ワークメモリ バックグラウンド プロセス ④ SELECT実行 ディスク内 ① バックエンド-2 プロセス WALバッファ $PGDATA base ワークメモリ バックグラウンド プロセス ③ pg_wal ② ※このページの解説は次ページにあり。 21

SQL実行 不足があれば必要データをディスクから共有バッファに読み込み 解説 ①クライアントがSQL実行を行う。 ②SQL実行で共有バッファにない別のデータが必要な場合、同じようにディスクから必要なデータを取得。 ③共有バッファに読み込む。 ④以降は共有バッファのデータを使用する。 22

接続終了するとその接続用のバックエンドプロセスは実行終了 PostgreSQL インストールサーバ postmaster プロセス 共有バッファ WALバッファ 接続終了 バックグラウンド プロセス ディスク内 バックエンド-2 プロセス ワークメモリ バックグラウンド プロセス $PGDATA base pg_wal ※このページの解説は次ページにあり。 23

接続終了するとその接続用のバックエンドプロセスは実行終了 解説 ①クライアントが、接続を終了とする。 ②クローズされた接続のためにあったバックエンドプロセスと、ワークメモリはなくなる。 24

接続が何もなければバックエンドプロセスとワークメモリは一つもない状態となる PostgreSQL インストールサーバ postmaster プロセス 共有バッファ WALバッファ バックグラウンド プロセス 接続終了 ディスク内 バックグラウンド プロセス $PGDATA base pg_wal ※このページの解説は次ページにあり。 25

接続が何もなければバックエンドプロセスとワークメモリは一つもない状態となる 解説 ①クライアントが、接続を終了とする。 ②クローズされた接続のためにあったバックエンドプロセスと、ワークメモリはなくなる。 ③クライアントがいなければバックエンドプロセスとワークメモリは一つもない状態となる。 26

共有バッファのデータはバックグラウンドプロセスによってディスクに永続化される PostgreSQL インストールサーバ postmaster プロセス 共有バッファ WALバッファ バックグラウンド プロセス ディスク内 バックグラウンド プロセス $PGDATA base pg_wal ※このページの解説は次ページにあり。 27

共有バッファのデータはバックグラウンドプロセスによってディスクに永続化される 共有バッファに一時保管されたデータは、変更のあったデータは定期的に、または、共有バッファを空けるために削除になったタイミング で専用のバックグランドプロセスによりディスクのファイルに永続化される。 28

共有バッファのデータは利用頻度が低いものから削除されていく PostgreSQL インストールサーバ postmaster プロセス 共有バッファ WALバッファ バックグラウンド プロセス ディスク内 バックグラウンド プロセス $PGDATA base pg_wal ※このページの解説は次ページにあり。 29

共有バッファのデータは利用頻度が低いものから削除されていく 解説 共有バッファに一時保管されたデータは利用頻度が低いものから削除されていき、その分スペースが空けられる。 30

追記型アーキテクチャ PostgreSQLでは追記型アーキテクチャという特徴的な仕組みが採用されています。 削除・更新の動作を不要フラグによるマーク付けによって行っており、ロールバックやトランザクション分離を実現するため に利用されています。 削除 の場合 削除対象の行に対し、不要フラグを立てる。実際の行削除は行わない。 更新 の場合 更新対象の行に不要フラグを立て、更新された値を持つ新しい行を挿入する。実際の行削除は行わな い。 31

追記型アーキテクチャにはいくつか特徴があり、以下の特徴はいずれもデメリットのようなものです。 カラムの更新でも新規行が挿入されていく 特定カラムのみの更新をする場合でも、前頁の更新の場合の動作が実施され、新規行が挿入される。 更新後の検索結果の並び順が変わる ソート順を指定しないSELECTを実行した場合、基本的には物理的なデータの並び順で表示される。 更新した行は新規行になるため、検索結果の一番下となり、並び順が変わったような印象を受ける。 オブジェクトファイルが肥大化していく 不要フラグでマーク付けされた行は実際には削除されていないため、ディスク領域も使用したまま。 更新を繰り返すと、新規行の挿入によってオブジェクトファイルがどんどん大きくなっていく。 結果的に、ディスクの圧迫やデータアクセスパフォーマンスの低下といった問題の原因となる。 32

追記型アーキテクチャのイメージ 削除 不要フラグ 不要フラグ ✔ ID 支社名 所在地 101 イギリス本社 イギリス ロンドン 102 ダッカ支社 インド ダッカ 103 ムンバイ支社 インド ムンバイ 104 ボストン支社 アメリカ ボストン ID 支社名 DELETE FROM 支社 WHERE ID = 104; 所在地 101 イギリス本社 イギリス ロンドン 102 ダッカ支社 インド ダッカ 103 ムンバイ支社 インド ムンバイ 104 ボストン支社 アメリカ ボストン 33

追記型アーキテクチャのイメージ 削除 ロールバック 不要フラグ ✔ 不要フラグ ID 支社名 所在地 101 イギリス本社 イギリス ロンドン 102 ダッカ支社 インド ダッカ 103 ムンバイ支社 インド ムンバイ 104 ボストン支社 アメリカ ボストン ID 支社名 ROLLBACK; 所在地 101 イギリス本社 イギリス ロンドン 102 ダッカ支社 インド ダッカ 103 ムンバイ支社 インド ムンバイ 104 ボストン支社 アメリカ ボストン 34

追記型アーキテクチャのイメージ 更新 不要フラグ 不要フラグ ✔ ID 支社名 所在地 101 イギリス本社 イギリス ロンドン 102 ダッカ支社 インド ダッカ 103 ムンバイ支社 インド ムンバイ 104 ボストン支社 アメリカ ボストン ID 支社名 UPDATE 支社 SET 所在地 = ‘バングラディシュ ダッカ’ WHERE ID = 102; 所在地 101 イギリス本社 イギリス ロンドン 102 ダッカ支社 インド ダッカ 103 ムンバイ支社 インド ムンバイ 104 ボストン支社 アメリカ ボストン 102 ダッカ支社 バングラディシュ ダッカ 35

追記型アーキテクチャのイメージ 削除・更新 多発すると 不要フラグ ID 支社名 所在地 UPDATE 支社 SET 所在地 ={国名入れるのやめ よ}; ✔ 101 イギリス本社 イギリス ロンドン ✔ 102 ダッカ支社 インド ダッカ ✔ 103 ムンバイ支社 インド ムンバイ ✔ 104 ボストン支社 アメリカ ボストン ✔ 101 イギリス本社 ロンドン ✔ 102 ダッカ支社 ダッカ ✔ 103 ムンバイ支社 ムンバイ ✔ 104 ボストン支社 枠外は全部ただのごみとなった ボストン 101 イギリス本社 イギリス ロンドン DELETE FROM 支社 WHERE ID = 104; 102 ダッカ支社 インド ダッカ COMMIT; 103 ムンバイ支社 インド ムンバイ 104 ボストン支社 アメリカ ボストン ✔ COMMIT; UPDATE 支社 SET 所在地 ={やっぱり国名入れ ることにしよ}; 36

VACUUM VACUUMは不要になった領域を再利用可能な領域にする処理のことで、テーブル毎に実行されます。 追記型アーキテクチャのデメリットをクリアするための処理です。 行が再利用可能にはなりますが、スペースはそのまま存在するためファイルサイズもそのままとなります。 VACUUM処理の動作は以下の「Visibillty Map」の内容を元に行われ、再利用可能になった行は 「Free Space Map」に記載されます。 Visibillity Map (可視化マップファイル) 不要フラグの付いた行がどれかという情報を記録しており、VACCUM処理時はこのファイルを使用する。 Free Space Map (空き領域マップファイル) VACCUM処理によって再利用可能になった行の情報が格納されている。 挿入や更新時に、このファイルがチェックされ、使用可能な行があれば再利用される。 37

VACUUM処理のイメージ VACUUM 支社; 不要フラグ ✔ ✔ 不要フラグ ID 支社名 所在地 101 イギリス本社 イギリス ロンドン 102 ダッカ支社 インド ダッカ 103 ムンバイ支社 インド ムンバイ 104 ボストン支社 アメリカ ボストン 102 ダッカ支社 バングラディシュ ダッカ ID 101 支社名 イギリス本社 ①不要フラグの付いた行を確認し Visibillity Map 実体： $PGDATA/base/数字/数字_vm 所在地 イギリス ロンドン ②再利用可能な行として記録する Free Space Map 103 102 ムンバイ支社 ダッカ支社 インド ムンバイ バングラディシュ ダッカ 実体： $PGDATA/base/数字/数字_fsm 38

VACUUM処理後 行の再利用 不要フラグ 不要フラグ ID 支社名 所在地 101 イギリス本社 イギリス ロンドン 103 ムンバイ支社 インド ムンバイ 102 ダッカ支社 バングラディシュ ダッカ ID 支社名 所在地 101 イギリス本社 イギリス ロンドン 105 チェンナイ支社 インド チェンナイ 103 ムンバイ支社 インド ムンバイ 102 ダッカ支社 INSERT INTO 支社 (ID,支社名,所在地) VALUES (105, ’チェンナイ支社’, ’’); バングラディシュ ダッカ 再利用可能な行があればそこに挿入 Free Space Map 実体： $PGDATA/base/数字/数字_fsm 39

自動VACUUM VACUUMは特定のテーブルに対し、任意のタイミングでコマンドを実行し行うこともできますが、実施目的 は不要になった領域の再利用なので、頻繁に更新や削除のあるテーブルであればあるほど、頻繁に行う 必要があります。 自動的にVACUUMを実行する機能があり、よほどの理由がない場合は、この自動CACUUMを利用す るのがよいとされています。 自動VACUUMの使用 自動VACUUMの設定が有効の場合に、各表毎に設定された閾値を超えた場合に自動的にVACUUM が行われるようになる。 一緒にANALYZEも行われる 自動VACUUMではVACUUMのほかANALYZEが行われる。 ANALYZE＝統計情報の再取得。 統計情報＝データベース内の表や索引、シーケンスなどのオブジェクトに関する詳細情報データの集合体 40

SQLチューニング

速度改善チューニング 一般的にチューニングを行う場合、SQLチューニング、それでだめならデータベースチューニングの順番で実 施します。 SQLチューニング 主にこちらの説明 スロークエリとなっているSQLの書き方を変更したり、INDEXを付与するなどしてよりよい実行計画を得ら れるよう、SQL、または統計情報を変更されるようにする。 データベースチューニング メモリ領域、VACCUM処理、ファイルアクセスなどに関連にした設定パラメータの調整を行い、速度改善を 行う。 42

" 「おかしい！この画面の表示にすごく時間がかかる。」 そのような報告があり、調べてみると、サーバからのレスポンス に大幅に時間がかかっていることがわかりました。 こういった場合に実行に時間のかかるSQLがあるのではない かと疑います。 さて、そのSQLどうやってみつけたらいいでしょう？ 43

44

遅いSQLの見つけ方 まず、レスポンスが遅いのか？レンダリングが遅いのか？ 画面の表示が遅いという場合にまず気にするべき箇所で各ブラウザの開発者ツールなどで確認可能。以下で切り分け をする。 レスポンスが遅い場合は、SQLの実行を含むアプリのサーバサイドの処理が遅い。 レンダリングが遅い場合は、サーバからのレスポンスを得てからフロントサイドの処理が遅い。SQLが原因でない可能性が 高く別視点からレンダリングの遅延原因を探った方がよい。 PostgreSQLのスロークエリログ上にて、実行に時間のかかっているSQLはあるか？ 実行に指定時間以上かかったSQLがあればその情報をログファイルに記録する設定が可能。ログファイルの作成場所も 設定によって指定されている。ログファイルに特定のSQLの記録がないかを確認する。 pg_stat_statementsビューにて、実行に時間のかかっているSQLはあるか？ 実行されたSQL情報を内部的に記録し、pg_stat_statementsビューを通して確認できるようにする設定が可能。 ビューのカラムから実行時間や、ディスク読み込みが多発するSQLがないかを確認する。 45

サーバからのレスポンスを受けるまでの時間の確認方法 この値が多ければ多いほど、サー バサイドの処理で何かしら時間が かかっているという意味。 例、 ・SQL以外のロジックを通る時間 ・SQLの実行時間 ・ファイルへのアクセス ・sleep 可能性はサーバサイドで行う処理 のすべてにあるが、これまで問題が なかったのに遅くなったということで あればSQLの実行時間が原因で 遅くなった可能性が高い。 レスポンスを受けるまでの時間 46

スロークエリログの確認方法 スロークエリが書き出されるログファイルを確認し、特定のSQLの記録がないかを確認する。 ログファイルの有無、場所、内容、どれだけの実行時間の場合にスロークエリと判断するかは設定により異なる。 実行にかかった時間 そのときのSQL 47

ログに関する設定パラメータ 名称 用途 設定例 反映タイミング logging_collector ログを収集するか否か on postmaster log_directory ログ出力先ディレクトリ名 log sighup log_filename ログファイル名 postgresql-%Y-%m%d_%H%M%S.log sighup log_line_prefix ログの各行に付与するプレフィック ス %m [%p][%e]DB_name:%d sighup log_min_messages ログに記録するメッセージレベル warning superuser log_min_duration_stat ement ログに出力対象とするSQL実行 時間の閾値 3000 superuser postmaster 再起動要 sighup リロード要 superuser SQLで動的変更が可能 48

設定の確認 方法1：設定されているパラメータの値を個別に確認 show {パラメータ名称}; 例、 show logging_collector; 方法2：設定パラメータ一覧をpg_settingsビューにて確認 SELECT * FROM pg_settings; 例、 SELECT name, setting, unit, context FROM pg_settings WHERE name LIKE '%log%'; 49

設定の変更 方法1：設定ファイルを編集する $PGDATA/postgres.conf を開き設定パラメータの値を編集する 例、 logging_collector = on 方法2：ALTER SYSTEM文を適用する ALTER SYSTEM SET {パラメータ名} TO {値}; 例、 ALTER SYSTEM SET logging_collector TO 'on'; ※パラメータ毎の反映タイミングにより、再起動やリロードが必要となる。 ※基本の設定がpostgres.conf、それに上書きするのがALTER SYSTEM適用のイメージ。実際に設定値として扱われる値は前 頁の確認方法で表示されるものとなる。 50

pg_stat_statementsビューの確認方法 pg_stat_statementsビューから実行時間に時間のかかっているSQL情報を取得する SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 1; 例、 SELECT query, calls,rows,total_time,shared_blks_hit,shared_blks_read FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5; 実行回数 SQLテキスト 実行時間(ｍｓ) 影響行数 ディスクからの読み込み数 メモリ上のヒット数 51

pg_stat_statementsビューの使用設定方法 pg_stat_statementsビューを使用するにはデータベース毎に以下の使用設定が必要、未設定の場合ビューが表示されない １、 pg_stat_statementsモジュールを読み込むように設定パラメータに追加する $PGDATA/postgres.conf を開き 設定パラメータの値を編集する、再起動要 shared_preload_libraries = 'pg_stat_statements' ２、対象のデータベースに対しエクステンションの作成を行う CREATE EXTENSION pg_stat_statements; 対象のデータベースですでに使用可能な エクステンションは以下で確認可能 \dx 52

遅いSQLが見つからない 確かにサーバからのレスポンスが遅い。 だからログもみた。pg_stat_statementsビューもみた。だけど遅いSQLが見つからない。そんな時は以 下を疑い、アプリコードを順に読み解き調査するとよい。 アプリのロジックで特定の条件にはまると執拗に繰り返し実行されるSQLはあるか？ 一つのSQLの実行時間は短いが、そのSQLを繰り返し実行してしまっていないか？例えばユーザーに紐づ く別情報の取得を紐づくレコード数分実行してしまうなど。ユーザーに紐づく特定の別情報が2万件＊一 回の実行時間が0.2秒の場合、4000秒＝66分かかる。 このようなケースはそのSQLをそんなに実行する以外の方法がないのか？を検討する SQL以外のコードで遅い処理があるか？ SQLとの関連はなくロジック自体に時間がかかるものがあるのかもしれない。できるなら、各要所の通過時 の時間を記録するようなログを出すようにし、「どこが？」の特定をする。 53

SQLの処理ステップ １、構文解析 パーサ 適用されたSQLが正しい構文かチェックする。 ２、書き換え リライタ オブジェクトの存在を確認し、オブジェクト名を内部の識別子に書き換える。 ３、実行計画生成 プランナ SQLと統計情報を元に実行計画を作成する。この時、あらゆる組み合わせを考慮し最もコストの低いものを選択する。 統計情報＝データベース内の表や索引、シーケンスなどのオブジェクトに関する詳細情報データの集合体 ４、実行 エグゼキュート 実行計画に従い、処理を実行する。 54

SQLの実行計画の取得 生成された実行計画を確認することができ、 所要時間や計画を詳しく見ることができる ３、実行計画生成 プランナ SQLと統計情報を元に実行計画を作成する。この時、あらゆる組み合わせを考慮し最もコストの低いものを選択する。 統計情報＝データベース内の表や索引、シーケンスなどのオブジェクトに関する詳細情報データの集合体 55

実行計画の取得方法 実行時に何から順にデータを取得し、どの作業でどれくらい対象があって、どれくらい時間がかかる。その計 画書みたいなものが実行計画です。 実行計画は、統計情報を利用して作成されます。自動VACUUMが有効であれば定期的にANALYZE が行われているのでさほど気にする必要はありませんが、無効にしているような場合はANALYZEを行って から実行計画の取得を行うようにするとよいです。 EXPLAIN を実行する SQLを実行することなく、生成された実行計画を確認できる。実行計画だけを確認したい場合に使うとよ い。しかしこちらは実行をしないため、実行時間の計測は不可。 EXPLAIN ANALYZE を実行する 実際にSQLを実行して得られた結果も表示する。実行計画のほか、処理時間と処理行数が確認できる。 スロークエリの特定や、チューニング後の改善に対し効果測定でもよく使用する。 56

EXPLAIN を実行する 実行計画を取得する EXPLAIN {実行SQL}; 例、 EXPLAIN SELECT * FROM company.department WHERE department.closed = false; 取得結果 実行計画 57

EXPLAIN ANALYZE を実行する 実行計画と実行時間を取得する EXPLAIN ANALYZE {実行SQL}; 例、 EXPLAIN ANALYZE SELECT * FROM company.department WHERE department.closed = false; 取得結果 実行計画 実行にかかった時間 58

実行計画の見方 実行計画を見たらSQLの実行にかかる時間がわかる、どこで時間がかかっているのかわかるらしい！ そうなのですが、、、実行計画は初見殺し。見た感想は「で、どこから見たらいいですか？」となります。 59

参考：実行SQL EXPLAIN ANALYZE SELECT employee.id, employee.name, department.name, branch.name FROM employee.employee INNER JOIN employee.history_employee_x_department ON employee.id = history_employee_x_department.employee_id INNER JOIN employee.latest_employee_x_department ON history_employee_x_department.id = latest_employee_x_department.employee_x_department_id INNER JOIN company.department ON history_employee_x_department.department_id = department.id INNER JOIN company.branch ON department.branch_id = branch.id; 60

どうやってみたらいいですか？ 実行にかかっている時間を知りたい 実行にかかった時間 61

どうやってみたらいいですか？ どこに時間がかかっているのか知りたい インデントが下がっているものが先に実行された計画タイプ 矢印一つが一つの計画タイプ actual time= {処理開始時間}..{終了時間} {終了時間} - {処理開始時間}が所要時間となり、所要時間が多い箇所の処理に時間がかかって いると判断する。 loop={繰り返した回数} 1以外の場合は、所要時間＊loopの値が実際にかかった時間となる。 62

どうやってみたらいいですか？ どうして時間がかかっているのか知りたい 結合の方法 結合の条件 スキャンの方法と対象テーブル 結合の方法、スキャンの方法が、対象にするデータに合わずに時間がかかることがある。 どのような計画にしたいか、直接指定することはできないため、データに合った結合方法や、スキャン方 法になるようにSQLを変更しながら、現状のデータ取得にあった計画にさせる必要がある。 63

代表的な計画タイプ 主なスキャン方法 実行計画上の記載 有効な使用シーン 内容 Seq Scan ・表のほとんどにアクセスする必要がある ・小さい表にアクセスする場合 ・テーブルを最初から最後までアクセスする Index Scan ・表の一部のデータにアクセスする場合 ・インデックスを利用して必要データにピンポイ ントでアクセスする 主な結合方法 実行計画上の記載 有効な使用シーン 内容 Hash Join ・結合する対象が大量 ・行数の少ない表と多い表の結合 ・行数の少ない表の結合条件列に重複値がない ・結合キーでハッシュを作成し、ハッシュと結合 先を突き合わせて結合する Merge Join ・結合する対象が対象 ・結合する表の行数が同じくらい ・あらかじめデータがソートされている ・結合するテーブルを結合キーでソート後、順 番に突き合わせて結合する Nested Loop ・結合する対象が少量 ・行数の少ない表と多い表の結合 ・行数が多い表の結合条件列にINDEXがある ・主となるテーブルに結合する方のテーブルを 全スキャンして結合する 有効な使用シーンに該当しないのに、その計画タイプとなっている場合が要注意！ 64

どうやってみたらいいですか？ ほかに何が書いてある？ 計画の推定情報 Hash Joinのための結合準備 計画実行時の実際の情報 取得される行数 繰り返し回数 取得される行数 ＊ 繰り返し回数が実際の行数 計画を立てるのにかかった時間 残念ながらここまでに出てきたもので全種類ではありません。代表的なものでもソートや、フィルターがあります。 ほとんどのものは実行計画を見て、「なるほど、ここのことか」と気づきを得ながら解読していくことになると思います。 65

" 「 コンビニに行って極上肉まん一個買ってきて!」 「この机に使ってるねじと同じもの買ってきて。近所に2つあるホーム センターのうちのどちらかで買ったはず。ねじの名前はちょっと憶えて いないけど、同じもの！一つ！」 さて、どちらのおつかいの方が時間がかかるでしょう？ 66

67

SQLにしてみると コンビニの極上肉まん ホームセンターのねじ SELECT ( SELECT 商品.名前, 商品.値段, 商品.実物 商品.名前, 商品.値段, package(商品.実 物) FROM ロイヤルホームセンター.棚 INNER JOIN ロイヤルホームセンター.商品 全部の棚を持ってきたねじと 同じのあるか見て回る ON 商品.ID = 棚.商品ID FROM WHERE 目視scan(商品. 見た目) ＝目視scan(持ってきたねじの見た目) コンビニ・棚 INNER JOIN コンビニ・商品 ON 商品.ID = 棚.商品ID WHERE 棚.名称 ＝ ‘レジ横ホットフード棚’ AND 商品.名前 ＝ ‘極上肉まん’ LIMIT 1 ; LIMIT 1 ) UNION ( SELECT 商品.名前, 商品.値段, 商品.実物 FROM コーナン.棚 INNER JOIN コーナン.商品 ON 商品.ID = 棚.商品ID WHERE 目視scan(商品. 見た目) ＝目視scan(持ってきたねじの見た目) LIMIT 1 ); 68

高負荷になりやすいSQL 命令が多い、まわりくどい命令がある そもそも命令が多い、命令の中でもまわりくどい命令が多いとそれが高負荷になりやすい 対象が膨大であてなく全部見る必要がある いろんなテーブルを接続し、接続し、接続し、大きな表になればその分高負荷になりやすく、さらに特定の 一つを見つけるのに、全量を読み込む必要ある場合、高負荷になりやすい NOT EQUAL演算子を使用している それ以外を探すという動作になる場合、設定されているINDEXがうまく動作しないため高負荷となりやす い ディスクやバッファの読み取りブロック数が多い 対象が大きくなると、ディスクからの読み取り量が増え、バッファやワークメモリの領域が不足する可能性が あり、そうなると高負荷となりやすい 69

SQLの改善の事例 INDEXを付与することでピンポイントのアクセスとなり改善 絞り込んで特定のデータを取得する場合や、特定のデータをキーにして結合する場合、そこにINDEXが 付与されていれば、ピンポイントでスキャンをしたり、結合したりできるのに、INDEXがないことで、全スキャ ンや全結合の計画タイプが選択されてしまい無駄に時間がかかってしまっていた。 キーになるカラムに対しINDEXを作成することで改善した。 ちなみに主キーの場合は自動的にINDEXが作成されている。 結合時に必要な分だけしか取得しないSQLにすることで改善 たくさんのテーブル結合を行うSQLでサブクエリを実行し、その結果同士を結合するSQL。サブクエリでは行 数の多いテーブルから全スキャンで取得、十万行近く全量スキャン。しかし、実際には外側のクエリで絞り 込まれた数十件分のみが必要なだけだった。 サブクエリを使用するのをやめ、外側のクエリで絞りこまれた結果と結合することで全量スキャンをやめ、必 要分だけ取得するようになった。 70

データベース設定の改善 SQLチューニングで改善が見られない場合は、データベースクラスタ自体の設定の調整で対応を検討する 必要がある。これに関しては方法手順ではなく、検討材料とすべきものを紹介のみとします。 メモリ関連 共有バッファ、WALバッファ、ワークメモリのサイズが足りているのか？足りていない場合に、ディスクへのアク セスが高頻度で発生し、それが負荷の原因となることがある。それぞれサイズの増加が可能。 バックグラウンド動作関連 基本的に低負荷で高頻度でバックグラウンドプロセスを実行させる状態が望ましい。一時的に、急激に、 負荷がかかるようなバックグラウンドプロセスの動きがないように調整をする。 テーブル設計 テーブル毎にどれだけファイルにデータを書き込むか、どれだけ空きが出たらVACUUMをさせるか設定が可 能。サイズや更新の頻度もテーブル毎にあったものに調整をする。 71

万能ではない 遅いSQLを見つける、直す、繰り返す 時間のかかるSQLを直しても、またしばらくするとデータの状態が変わり、改変したSQLでまた遅くなる。と いうことが度々発生する。その対応を繰り返すことが普通のこと。 どこかのデータベースにとっては有用でも、どこかのデータベースには有害かもしれない いくつかのデータベースに同じSQLを適用する場合、データベースによって持っているデータの状況が異なる ため、一方は早くなっても、もう一方は遅くなるといったこも発生する。そのSQLの適用されるすべてのデー タベースでの影響を確認する必要がある。 72

まとめ

わかるようになった事 PostgreSQLデータベースの基本構造 データベースクラスタ、プロセス、メモリ領域、VACUUM 遅いSQLの見つけ方 スロークエリログ、pg_stat_statementsビュー SQLの実行計画の取得と見方 EXPLAIN ANALYZE、実行計画の見方、スキャンの種類、結合の種類 SQLのチューニング方法 の一部 高負荷になりやすいSQL、無駄な取得をしないSQL、コツはあるがトライエラーで対応を続ける必要があ る 74

おすすめサイト Postgres基本構造について解説あり https://www.fujitsu.com/jp /products /software/resources /featurestories/postgres /article-index/architecture-overview/ 実行計画のスキャンや結合についてわかりやすく解説あり https://www.fujitsu.com/jp /products /software/resources /featurestories/postgres /article-index/implement-sqltuning / 取得SQLでのチューニングについて https://www.fujitsu.com/jp /products /software/resources /featurestories/postgres /article-index/tuningrule9-search/ データベースチューニングについて https://www.fujitsu.com/jp /products /software/resources /featurestories/postgres /article-index/tuningrule9-base/ 75