今回は,PostgreSQLのパフォーマンス・チューニングとクラスタリングについて解説する。

データベースにおけるパフォーマンス・チューニングとは

 データベースにおけるパフォーマンス・チューニング(以後チューニング)とは,データベース・システムに問合わせを投げて返って来るまでの時間(応答性)や,平均的な処理スピード(スループット)を改善する作業を指す。厳密には応答性を改善するのか,とスループットを改善するのかではチューニング内容が異なることがあるが,ここではあまり細かいことにはこだらないことにする。

 チューニング・テクニックはPostgreSQLのバージョンによっても変化するが,本稿ではPostgreSQL 8.4を前提とする。

 チューニングのポイントは主に以下である。

1)PostgreSQL設定ファイルの調整
2)データベース物理設計

 これらを順に解説する。

PostgreSQL設定ファイルの調整

 PostgreSQLの設定ファイルはいくつかあるが,性能に関する調整を行うのはデータベース・クラスタ下に作成されるpostgresql.confである。

メモリー関連のパラメータ

 まずメモリー関連のパラメータを見ていこう。

o shared_buffers

 これは,PostgreSQLのデータベース・エンジン(postgresプロセス)が共同でバッファ用に使うメモリー(共有メモリー)の大きさを指定する。バッファなので大きい方がよいが,大きくし過ぎてページングが始まってしまっては元も子もない。最初は実メモリーの1/2から1/4の大きさを指定して様子を見よう。

 OSによっては,カーネル・パラメータの調整を行わないと,大きな共有メモリーを取れないものもある。詳細はPostgreSQLのマニュアルを参照のこと。

o temp_buffers
o work_mem
o maintenance_work_mem

 これらはいずれも,postgresがプロセス内で使うメモリーの指定に関係する。可能な範囲で大きくするのがよいが,postgresプロセスの数は同時にPostgreSQLに接続するセッション数と同じになるため,多数の同時セッションを使うような用途では,これらのサイズをあまり大きくすると,システムのメモリーが不足して,返って性能が低下することになる。セッション単位で変更も可能なので,きめ細かい調整を行うことも検討しよう。

 temp_buffersは,一時テーブル用のメモリーを指定する。大きな一時テーブルを多用する際には増やすことを検討する。

 work_memは,ソートやハッシュなどの問合わせの内部処理を行う際に使用するバッファのサイズを指定する。このバッファ・サイズを越えるとワーク・ファイルを使って処理を行うようになるため,性能が低下する。メモリー内でのソートになっているかどうかは,「EXPLAINコ ANALYZE」コマンドで確認できる。

 最初の例では,「Sort Method: top-N heapsort Memory: 18kB」とあるので,18Kバイトのメモリーを使ってメモリー内でソートできていることが分かる。

test=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts ORDER BY abalance LIMIT 10;
途中省略
Sort Method: top-N heapsort Memory: 18kB
-> Seq Scan on pgbench_accounts (cost=0.00..2588.00 rows=100000 width=97) (actual time=0.064..246.712 rows=100000 loops=1)
Total runtime: 511.386 ms
(6 rows)

test=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts ORDER BY abalance;
途中省略
Sort Method: external sort Disk: 10456kB
-> Seq Scan on pgbench_accounts (cost=0.00..2588.00 rows=100000 width=97) (actual time=0.077..254.435 rows=100000 loops=1)
Total runtime: 1197.840 ms
(5 rows)

maintenance_work_mem

 これは,インデックスの作成や更新の際などに使用するワークメモリーのサイズを指定する。大量のデータをロードする処理や,特に更新が多いシステムでは大きくすると効果がある。