グロースエクスパートナーズ Advent Calendar 2021 18日目！
こんにちは！GxPの肥後です。
PostgreSQL Conference Japan 2021に参加しそこで一番興味を持った「商用運用ができる実践的PostgreSQL技術者の育成」について書こうと思います。
講演概要のリンクは以下
商用運用ができる実践的PostgreSQL技術者の育成
PostgreSQL Conference Japanとは？
日本PostgreSQLユーザ会(JPUG) が開催する PostgreSQLのConferenceです。
PostgreSQLに関する内外の導入事例、および技術情報を提供するプレゼンテーションが実施されていました。
PostgreSQL Conference Japan 2021
商用運用でデータベースに望むこと
こちらはプレゼンテーションで以下2点のことが大事と発表されていました。
・SQLの応答時間が一定であること
・SQLが遅くなったときに原因が特定できること
SQLの応答時間が一定であること
読者の皆さんはデータベースの商用運用中に絶対避けたいことはなんだと思いますか？
私はデータが入らないことかな？と考えてました。
発表の中では「最も避けたいのはデータベースが運用中に遅くなること」ということでした。
ただし、一部運用の変化に伴う場合は許容する、とも発言がありました。
どういうことなのか、整理したいと思います。
ここで言う運用の変化とは、データ量の増加や同時接続数の増加だったりのことです。
こういった予測できるものは対策ができるので結果として許容することが多いとも言われていました
ざっと表にすると以下の感じです。
| 運用の変化 | SQLの応答劣化 |
|:-:|:-:|:-:|
| データ量の増加 | 許容できる=遅くなることを理解している |
| 同時接続数の増加 | 同上 |
| 上記以外 | 許容できない |
上記以外は許容できないと表では記載されていますが、
運用の変化に対してSQL応答劣化の原因を理解できているのであれば許容できると判断して良いかと思います。
SQLが遅くなったときに原因が特定できること
異常時にはサーバーログを確認するかと思いますが、正常時と何が違うか比較するとのこと
その他にPostgreSQLの標準機能から取得できる情報としては統計情報コレクタだったり、
pg_stat_statements等の追加モジュールがあるとも言われていました。
統計情報コレクタやpg_stat_statementsは現時点の情報や累積情報を取得する際に便利であり
過去の時系列的な情報はサーバーログを確認するしかありません。
| 情報 | 手段 |
|:-:|:-:|:-:|
| 現時点の情報や累積情報 | ・システムカタログ
・統計情報コレクタ
・pg_stat_statements等の追加モジュール|
| 過去の時系列な情報 | サーバーログ
（postgresql.conf で設定） |
バッチ処理で遅いSQLをPostgreSQLの標準機能で対応する例が発表されていました。
自分なりに整理しますと、SQLの実行が遅いなといったときにはまずサーバーログを確認します。
その際に設定でlog_min_duration_statementに秒数を設定しておくことで、設定した秒数以上の実行に時間がかかればそのSQLがログに出力されます。
そうすることで、実行速度の遅いSQL文を確認できます。
発表でも言われていたのですが、この場合だと遅いSQLは分かるが、なぜ遅いかまでは不明です。
※log_min_duration_statementとはSQL文の実行が設定した秒数以上だったらログに記録してねというもの
また、遅いSQLの実行計画を知りたい場合ですと発表ではauto_explainを使用して実行計画をログから確認していました。
auto_explain句は公式ドキュメントで以下の説明がなされています。
手動でEXPLAINの実行を必要とせず、自動的に遅い文の実行計画をログ記録する手段を提供します。
自動で出力する場合はこれを設定しておけばよいですが、手動で行う場合はどうするんだろう？ということで整理してみました！
手動で行う場合は、EXPLAIN句、EXPLAIN ANALIZE句のどちらかをSELECT文の前につけて実行するとよいです！
2つのクエリ文の違いは以下です
・EXPLAIN句 推定された実行計画を表示する
・EXPLAIN ANALIZE句 推定ではなく、一度実行してみた結果を表示する（SQL文が実際に実行されるので注意）
log_min_duration_statementとauto_explainを設定をしておくことでSQLが遅い場所に勘所をつけることができ、遅くなった際に原因が特定しやすくなりそうです。
また、auto_explainを設定するとログが肥大化する、見づらくなる。といった場合ですとこの設定を無効にしておいてlog_min_duration_statementの秒数以上のSQL文に対してExplain句を実行してみるという運用も良さそうですね。
おまけ 実行計画とは？
Explain句のドキュメントで実行計画について以下の説明がありました。
実行計画は、問い合わせ文が参照するテーブル（複数の場合もある）をスキャンする方法（単純なシーケンシャ>ルスキャン、インデックススキャンなど）、複数のテーブルを参照する場合に、各テーブルから取り出した行を>結合するために使用する結合アルゴリズムを示すものです。
実行計画のなじみが薄い人向けにイメージしやすいように説明しますと「SQLを実行するための計画」ですね。
この説明を見たそこのあなた。ふーんと思いましたね？私も最初はそうでした！！
具体的に言いますと、どうしたらより短い時間でSQLを実行できるかを、計算して導き出した方法を「計画」としてまとめたものの事です。
SQLの実行手順書と言えばよりイメージがしやすいのではないでしょうか？
最後に
今年始めてPostgreSQL Conference Japanに参加したのですが、PostgreSQLについて理解を深めたい方はぜひ参加するのをおすすめします。
全ての講演が終わった後に有志の方たちのショートセッションがあり、そんなことができるのか！という驚きもありました。
参考資料