PostgreSQL / データの読み取りとクエリ
PostgreSQL パフォーマンスの最適化入門
はじめに
データベースを利用したアプリケーションを扱う場合、パフォーマンス管理は継続的なタスクです。実行速度の遅いクエリは、タイムアウトを引き起こしたり、ユーザーエクスペリエンスを低下させたり、より多くのリソースを使用したり、データベースの支払い方法によっては予算に影響を与えたりする可能性があります。これらの問題により、問題のあるクエリを特定して修正できるように、データベースのパフォーマンス特性を理解することが重要になります。
このガイドでは、PostgreSQL データベースでパフォーマンスの低いクエリを特定するさまざまな方法について説明します。その後、PostgreSQL のパフォーマンスを維持するために遅いクエリを修正するために使用できるさまざまな手法について説明します。
Prisma ORM の PostgreSQL コネクタを使用すると、JavaScript および TypeScript アプリケーションから直接 PostgreSQL データベースを管理できます。
既存のプロジェクトに Prisma を追加する方法、またはゼロから開始する方法を学びましょう。
Prisma Postgres データベースは、本番環境に対応した Prisma Postgres データベースを CLI からすばやくセットアップできます。
次に、ガイドに従って、完全な TypeScript アプリケーションを構築します。
アクティブなクエリとプロセスの確認
パフォーマンスの低いクエリを追跡しようとする際に最初に確認すべき場所は、現在アクティブなクエリとプロセスのリストです。PostgreSQL は、pg_stat_activity ビューを使用してこのデータを利用できるようにします。
pg_stat_activity ビューは、PostgreSQL の累積統計システム内で利用可能なビューの 1 つです。サーバープロセスごとに 1 行が含まれており、各プロセスが現在何に取り組んでいるかを確認するのに役立ちます。
ビュー内のすべての情報を表示するには、次のように入力します。
注: 従来のセミコロン (;) の代わりに \gx 行終端シーケンスを使用すると、PostgreSQL に現在のクエリの拡張出力モードを使用するように指示します。これにより、各レコードの列と関連する値が水平方向ではなく垂直方向に表示されるため、場合によっては読みやすさが向上します。
遅いクエリを探す際に役立つ可能性のある出力には、多くのフィールドがあります。最も関連性の高いフィールドには、次のようなものがあります。
state: プロセスの現在の状態。activeとしてリストされている行は現在実行中です。その他の状態には、新しいクライアントコマンドを待機しているプロセスのidle、トランザクションコンテキスト内でコマンドを待機しているプロセスのidle in transaction、およびステートメントがエラーを引き起こしたトランザクションのidle in transaction (aborted)が含まれます。query: 最近実行されたクエリ。アクティブなプロセスの場合、これは現在実行中のクエリになります。usename: プロセスに関連付けられたユーザーの名前。application_name: プロセスに接続されているアプリケーションの名前。datname: ユーザーが接続しているデータベースの名前。wait_event: プロセスが待機しているイベントの名前 (存在する場合)。プロセスがactive状態であり、wait_eventが存在する場合、クエリは現在システムの他の部分によってブロックされていることを意味します。wait_event_type: プロセスが待機しているイベントのカテゴリ。pid: プロセスのプロセス ID。query_start: アクティブなクエリの場合、現在のクエリが開始されたタイムスタンプ。xact_start: プロセスがトランザクションを実行している場合、現在のトランザクションが開始されたタイムスタンプ。
現在のコンテキストに関連する列でクエリをフィルタリングできます。役立つパターンの 1 つは、age() 関数を使用してクエリの実行時間を計算することです。たとえば
これにより、アイドル状態ではないクエリの実行時間、ユーザー名、データベース、およびクエリテキストが表示されます。結果は、実行時間の長いクエリから短いクエリの順に並べ替え、この特定のクエリを結果から除外します。
同様に、アイドル状態ではなく、待機イベントが発生しているすべてのプロセスを確認できます。
これは、システムの他の部分 (たとえば、ロック競合) のために現在進行していないクエリを確認するのに役立ちます。
その他のシステム統計を確認する
pg_stat_activity ビューは、遅いクエリを特定するために必要な情報のほとんどを提供する可能性がありますが、最適化の追加ターゲットを特定するために、他のシステム統計も確認すると役立つ場合があります。
データベース統計の表示
pg_stat_database テーブルには、各データベースに関する統計が含まれています。
私たちの目的のために興味深い列には、次のようなものがあります。
blks_read: データベースで読み取られたディスクブロック数。blks_hit: ディスクブロックがバッファキャッシュで見つかった回数 (ディスクからの低速な読み取りを回避)。xact_commit: コミットされたトランザクション数。xact_rollback: ロールバックされたトランザクション数。
Data Egret チームがブログで示しているように、これらの生の値を使用して、キャッシュヒット率などの興味深い統計を計算できます。
これは、最も一般的なクエリを効果的にキャッシュできるように、データベースクラスターに RAM を追加することからメリットが得られるかどうかを評価するのに役立つ貴重な情報になる可能性があります。
テーブル統計の表示
もう 1 つの役立つビューのファミリは、pg_stat_all_tables、pg_stat_user_tables、および pg_stat_sys_tables です。 pg_stat_all_tables ビューはすべてのデータベースのアクセス統計を表示し、他の 2 つのビューは、テーブルがユーザーテーブルであるかシステムテーブルであるかに基づいてテーブルをフィルタリングします。
これらのビューの興味深い列には、次のようなものがあります。
seq_scan: テーブルで実行されたシーケンシャルスキャンの数。seq_tup_read: シーケンシャルスキャンから返された行数。idx_scan: テーブルに対して実行されたインデックススキャンの数。idx_tup_fetch: インデックスを介して取得された行数。
これらの列の数値は、インデックスのパフォーマンスと、実行しているクエリによって効果的に使用されているかどうかを評価するのに役立ちます。テーブルに多くのシーケンシャルスキャンがあることが判明した場合、最も一般的なクエリで使用できる追加のインデックスを作成することからメリットが得られる可能性があります。
インデックスヒットの表示
現在持っているインデックスに関する詳細情報が必要な場合は、pg_stat_all_indexes、pg_stat_user_indexes、および pg_stat_sys_indexes ビューを確認できます。
これらは、各インデックスの使用頻度に関する情報を提供します。 idx_scan 列は、インデックスがスキャンされた回数を示しています。 idx_tup_read 列は、スキャンによって返されたエントリ数を示し、idx_tup_fetch は、インデックススキャンによって返された行の総数を示します。
この情報は、クエリで使用されていないインデックスがある場合を理解するのに役立ちます。これらのインデックスを特定したら、インデックスを利用するようにクエリを書き換えるか、未使用のインデックスを削除して書き込みパフォーマンスを向上させることができます。
ロック情報の表示
遅いクエリについて収集した情報の一部は、ロックの問題を指摘している可能性があります。現在保持されているすべてのロックに関する詳細情報を確認するには、pg_locks ビューをクエリします。
出力は、PostgreSQL 内のすべてのロックに関する情報を提供します。これは、個別のプロセスが同じオブジェクトの制御を要求するときに発生する可能性のある競合の問題を診断するのに役立ちます。
問題のあるロックの調査に役立つ可能性のある列には、次のようなものがあります。
locktype: ロック可能なオブジェクトのタイプdatabase/relation/page/tuple: ロックされたアイテムのオブジェクト ID。データベースとリレーションの場合、これらはpg_databaseおよびpg_classで相互参照できます。mode: 実装または要求されたロックモード。granted: ロックが付与されたかどうかを表すブール値。
スロークエリログを有効にする
実行時間の長いクエリに関する情報をより簡単に見つける 1 つの方法は、スロークエリログを有効にすることです。スロークエリログを有効にすると、PostgreSQL は、指定された時間よりも長く実行されるクエリを自動的に記録できます。これにより、調査時に実行されていない遅いクエリに関する情報を収集できます。
PostgreSQL がすでにスロークエリをログに記録しているか確認する
最初にすべきことは、スロークエリログの現在の状態を確認することです。スロークエリログがすでに有効になっている場合は、何もする必要はありません。
スロークエリログが有効になっているかどうかを確認するには、次のように入力します。
short_desc および extra_desc 列の値を確認すると、現在ログが有効になっているかどうかを評価できる情報が見つかります。 setting 列が現在 -1 に設定されているため、スロークエリログが現在有効になっていないことがわかります。
現在の状態がわかったので、必要に応じて変更できます。
PostgreSQL を構成してスロークエリをログに記録する
先に進む前に、スロークエリログは非常に便利ですが、パフォーマンスに影響を与える可能性があることに注意することが重要です。PostgreSQL は、各クエリの時間を測定し、結果をログに記録するために追加の操作を実行する必要があります。これはパフォーマンスに影響を与え、ハードドライブの容量を予期せずいっぱいにする可能性があります。
常にスロークエリをログに記録することは良い考えではないかもしれません。代わりに、問題を積極的に調査している場合は機能を有効にし、完了したら無効にします。
スロークエリをグローバルにログに記録する
それを念頭に置いて、PostgreSQL サーバーの構成ファイルを変更することで、スロークエリログをグローバルに構成できます。これらの値をインタラクティブに変更することもできますが、構成に適切なデフォルトを設定すると、後でインタラクティブに調整するのが簡単になります。
PostgreSQL の構成ファイルを開きます。現在の構成ファイルの場所は、次のように入力することで確認できます。
ファイル内で、log_min_duration_statement 設定を検索します。上記の出力例の値が構成ファイルから読み取られた場合、機能が現在無効になっていることを示すために -1 に設定されます。ニーズに応じて調整できる関連設定も多数あります。
現在、log_min_duration_statement 設定はコメントアウトされており、現在の値はデフォルト値を表す -1 に設定されています。他の設定はファイル内で適切にコメントされており、すべてのステートメントをログに記録するのではなく、最小値を超えるステートメントをサンプリングできます。最後の設定では、トランザクション内で発生するステートメントのサンプリングも実行できます。
log_min_duration_statement のコメントを解除し、別の値を設定することで、長時間実行クエリのログ記録を有効にできます。たとえば、完了までに 5 秒以上かかるステートメントをログに記録するように 5 秒に設定できます。
ファイルを保存したら、PostgreSQL 内から PostgreSQL サーバーをリロードできます。
現在の値を再度確認することで、サーバーが新しい設定を使用していることを確認できます。
これで、setting フィールドが 5000 に設定され、unit フィールドが ms に設定され、5 秒の設定が 5000 ミリ秒に変換され、適用されたことが示されます。 sourcefile 行は、この値が変更した構成ファイルから読み取られていることも確認します。
データベースごとにスロークエリをログに記録する
遅いクエリを検出する別のオプションは、スロークエリログを特定のデータベースに制限することです。 log_min_duration_statement は、前のセクションで示したようにグローバルに設定できますが、データベースレベルでも構成できます。
単一のデータベースのスロークエリログを有効にするには、ALTER DATABASE コマンドを使用します。
グローバル設定とは異なり、ALTER DATABASE コマンドを使用する場合、値はミリ秒単位の時間を表す単位のない整数である必要があることに注意してください。
データベースごとのロール設定をクエリすることで、設定が適用されたことを確認できます。
これにより、以前に 5 秒のしきい値に設定したグローバル設定が妨げられていないことを確認できます。
スロークエリログのテスト
最小ログ記録時間を超えるステートメントを発行して、設定をテストします。
ログを確認すると、実行時間の長いクエリが発生したことを示すステートメントが見つかるはずです。
グローバル制限と特定のテーブルで異なるしきい値があるため、一方をトリガーするが他方をトリガーしないクエリ時間を使用して、それぞれが正しく適用されていることをテストできます。
たとえば、しきい値が低いデータベースに接続し、4 秒間スリープすると、ログ行がトリガーされるはずです。
ログには次のように表示されます。
次に、グローバル設定のみの影響を受けるはずの別のデータベースに切り替えることができます。同じスリープステートメントでは、ログ行はトリガーされません。
新しいログ行は記録されないはずです。
結論
この記事では、PostgreSQL が利用できるようにするパフォーマンス情報の一部を表示および理解する方法について説明しました。この情報を表示すると、システムリソース、クエリパターン、構成設定のさまざまなボトルネックを把握できます。パフォーマンスの低下が発生した場合は、PostgreSQL が提供する情報を確認して、問題のある動作の調査を開始できます。
また、スロークエリログを使用して、システムリソースを占有し、予想よりも長く実行されているクエリを正確に特定する方法についても説明しました。このデータを記録し、結果のログを評価することで、追加のインデックス、異なるクエリ構造、またはより効率的なクエリ設計が必要になる可能性のある場所を特定できます。これらのコストのかかる操作を特定する方法を知ることは、より機能的なデータベースを利用したアプリケーションを実行するための第一歩です。
PostgreSQL データベースコネクタは、JavaScript および TypeScript アプリケーションから PostgreSQL データベースを管理するのに役立ちます。既存のプロジェクトに Prisma を追加する方法、またはPrisma をゼロから開始する方法を学びましょう。
