PostgreSQL データベースメンテナンス | パフォーマンス最適化とトラブルシューティング

はじめに

この実験 (Lab) では、PostgreSQL データベースの必須のメンテナンス作業について学習します。データベースオブジェクトのサイズを監視する方法、パフォーマンスを最適化するための定期的なメンテナンスの実行方法、そして接続やログを確認して潜在的な問題をトラブルシューティングする方法を理解することを目的とします。

まず、PostgreSQL データベースに接続し、テーブルとインデックスのサイズをクエリすることから始めます。次に、データベース統計情報を更新するための ANALYZE の実行方法と、ストレージスペースを再利用するための VACUUM の実行方法を学びます。最後に、アクティブなクライアント接続を一覧表示する方法と、エラーがないかサーバーログを確認してデータベースの問題を診断および解決する方法について説明します。

データベースオブジェクトサイズのクエリ

このステップでは、PostgreSQL データベースに接続し、テーブルとそのインデックスのサイズをクエリします。データベースオブジェクトのサイズを理解することは、パフォーマンスチューニングとキャパシティプランニングにとって非常に重要です。

まず、ターミナルを開きます。postgres ユーザーとして PostgreSQL サーバーに接続し、mydatabase データベースにアクセスするには、次のコマンドを実行します。

sudo -u postgres psql mydatabase

psql プロンプト (mydatabase=#) が表示され、mydatabase データベースに接続されていることが示されます。この実験 (Lab) での以降のすべての SQL コマンドは、特に指定がない限り、この psql シェルで実行してください。

次に、mytable のサイズを調べます。pg_size_pretty 関数と pg_relation_size 関数を使用します。pg_relation_size はテーブルのサイズをバイト単位で返し、pg_size_pretty はそれを人間が読める形式（例：KB、MB）にフォーマットします。

mytable のサイズを取得するには、次の SQL クエリを実行します。

SELECT pg_size_pretty(pg_relation_size('mytable'));

テーブルのデータが使用しているディスクスペースを示す、次のような出力が表示されるはずです。

 pg_size_pretty
----------------
 56 kB
(1 row)

次に、name 列に作成されたインデックス idx_mytable_name のサイズを確認します。

SELECT pg_size_pretty(pg_relation_size('idx_mytable_name'));

出力は、インデックスが消費しているスペースを示します。

 pg_size_pretty
----------------
 48 kB
(1 row)

最後に、すべてのインデックスを含むテーブルの合計サイズを取得するには、pg_total_relation_size 関数を使用します。

SELECT pg_size_pretty(pg_total_relation_size('mytable'));

この出力は、テーブルとそのインデックスの合計サイズを示します。

 pg_size_pretty
----------------
 176 kB
(1 row)

ANALYZE による最適化

このステップでは、良好なクエリパフォーマンスを維持するために不可欠な ANALYZE コマンドについて学習します。

ANALYZE の理解

ANALYZE コマンドは、データベース内のテーブルの内容に関する統計情報を収集します。PostgreSQL のクエリプランナーは、これらの統計情報を使用して、クエリの最も効率的な実行プランを選択します。正確な統計情報がないと、プランナーは不適切な選択をしてしまい、クエリパフォーマンスの低下につながる可能性があります。特にテーブルデータに大きな変更があった後は、定期的に ANALYZE を実行することが良い習慣です。

まだ psql シェル内にいる状態で、mytable テーブルに対して ANALYZE を実行します。

ANALYZE mytable;

このコマンドは mytable を分析し、その統計情報を更新します。コマンドが成功したことを確認する次の出力が表示されます。

ANALYZE

コマンドは単純に ANALYZE を返しますが、バックグラウンドで mytable の内部統計情報を更新しています。

VACUUM によるストレージの解放

このステップでは、もう一つの重要なメンテナンスタスクであるストレージの回収を行うために VACUUM コマンドを使用します。

VACUUM の理解

PostgreSQL では、行が更新または削除された場合、行の古いバージョン（「デッドタプル」）はディスクから直ちに削除されません。VACUUM は、これらのデッドタプルが占有しているストレージを回収し、そのスペースを再利用可能にします。また、データの可視性情報も更新し、クエリパフォーマンスの向上に役立ちます。

psql シェル内で、mytable テーブルに対して VACUUM を実行しましょう。

VACUUM mytable;

このコマンドはテーブルを処理し、確認メッセージを返します。

VACUUM

VACUUM と ANALYZE を単一の効率的なコマンドに組み合わせることもできます。これは一般的なメンテナンスプラクティスです。

VACUUM ANALYZE mytable;

このコマンドは、まずストレージを回収し、次にテーブルの統計情報を更新します。完了すると VACUUM を返します。

注意： VACUUM FULL というコマンドもあります。これはより積極的にスペースを回収し、ディスク上のファイルを縮小します。しかし、テーブル全体をロックするため、操作中は読み書きができなくなります。そのため、注意して、必要な場合にのみ使用する必要があります。

接続とログの監視

この最終ステップでは、アクティブなデータベース接続を監視する方法と、エラーがないかサーバーログを確認する方法を学習します。これらはトラブルシューティングの鍵となるスキルです。

まず、psql シェル内にいる間に、pg_stat_activity ビューをクエリすることで、サーバーへのすべてのアクティブな接続を確認できます。

以下の SQL クエリを実行します。

SELECT datname, usename, client_addr, state FROM pg_stat_activity;

このクエリは、データベース名 (datname)、ユーザー名 (usename)、クライアントの IP アドレス (client_addr)、および接続の現在の状態 (state) を表示します。出力には、以下のような自身の接続が表示されます。

  datname  | usename  | client_addr | state
-----------+----------+-------------+--------
 mydatabase| postgres |             | active
 postgres  | postgres |             |
           | labex    |             |
(3 rows)

次に、サーバーログを確認します。これを行うには、まず psql シェルを終了する必要があります。\q と入力して Enter キーを押します。

\q

これで、標準の Linux ターミナルプロンプトに戻りました。PostgreSQL のログは /var/log/postgresql/ ディレクトリにあります。このシステムでは、ログファイルは postgresql-14-main.log です。

grep コマンドを使用して、ログファイル内の "ERROR" を含む行を検索します。

grep ERROR /var/log/postgresql/postgresql-14-main.log

エラーがない場合、このコマンドは何も出力しません。この実験では何もエラーを引き起こすような操作を行っていないため、これは期待される結果です。サーバーログの確認は、データベースの問題を診断するための基本的なステップです。

まとめ

この実験では、PostgreSQL データベースの基本的なメンテナンスタスクを実行する方法を学びました。テーブルとインデックスのサイズを監視する方法、クエリパフォーマンスを最適化するために ANALYZE を実行する方法、そしてストレージスペースを回収するために VACUUM を使用する方法を理解しました。また、アクティブなクライアント接続を一覧表示し、エラーがないかサーバーログを確認する練習もしました。これらのスキルは、PostgreSQL データベースの健全性、パフォーマンス、および信頼性を確保するために不可欠です。