HOME PostgreSQL  
Prev INDEX  

パフォーマンスチューニング

移転しました

独自ドメインサイトへ移行しました。5秒後に

https://straypenguin.winfield-net.com/

へジャンプします。

数十万レコードのデータを持つ大規模なテーブルを扱うようになると、クエリによっては回答が得られるまでに数秒かかるケースも出てくる。これは、より多くのメモリやディスクの使用を PostgreSQL に許すことで改善される可能性が高い。ただし、扱っているデータベースが小さい時には大した効果は望めない。また、そもそもの実装メモリが 256M とか 128M という貧弱な状態では、調整の余地さえなく、単なる悪あがきだ。以下は搭載メモリ 1 ギガを目安に書いている。更に、テーブルの素性とクエリパターンによっては、テーブル自体のクラスタ化が加速を上乗せしてくれるかもしれない -- クラスタリングや適切なインデックスの作成は、メモリの少ないマシンにもできる、手軽ながらあなどれないチューニングだ。

メモリ割り当ての最適化

メモリ関係パラメータは扱っているデータベースの大きさやクエリの性格と頻度、実装メモリの量などによって最適値が全く異なるので一概には言えないが、最適値を割り出すための黄金律はある。どうも世の中のドキュメントは断片的で、この作業を包括的に扱ったドキュメントがないようだったので、苦労してこれをまとめるに至った。

役に立ったドキュメント：

max_connections

postgresql.conf の中の設定項目のひとつで、読んで字の如し。支障のない範囲でできるだけ小さく抑えるのがセオリーだ。コネクション数は、以下に述べる他のメモリ設定パラメータの掛け算の元になるわけで、欲張りすぎると最終的なメモリ必要量が膨らむ。ただし、実は、 max_connections を 64 本から 128 本に増やしたところで、必要な共有メモリ量は思ったほど増えない。 shared_buffers の計算実例を見ればそれが分かるはずだ。

work_mem

これも postgresql.conf の項目のひとつで、PostgreSQL 7.x までは sort_mem と呼ばれていた。昔の名前が示すとおり、データのソート処理に使用できるメモリ量 (KB) を制御するパラメータだ。ソートは明示的な ORDER BY の時に限らず、テーブル結合や DISTINCT, 集約関数の中でも、裏で、しかも複数回行われることが多い (行われているかどうかは EXPLAIN を行えば見える)。 PostgreSQL 7.3 及び 8.0 でのデフォルト値は 1024 (1MB) で、数万件のデータを扱うにはやや控えめすぎる。2048 (2MB) から 4096 (4MB) くらい取ってもいいのではないだろうか。ただし、複数のセッションが平行して行われるのが普通なので、瞬間的に work_mem x max_connections x 数倍 のメモリが使用される可能性を覚悟しておかなくてはならない。

shared_buffers

パフォーマンスを最も大きく左右し、しかも計算が厄介なのがこの設定項目。これは、バックエンド (データベースサーバ) がカーネルの共有メモリをどれだけ使えるかをバッファブロック数で指定したもの。 PostgreSQL を特殊な configure パラメータでコンパイルした場合は別として、 1 バッファブロックは通常 8192 バイトだ。確認したければ以下の SQL コマンドで調べられる (リコンパイルしない限り変更は不可能)；

SHOW block_size;

または、

SELECT current_setting('block_size');

shared_buffers は「最大値」といった類のものではなく、 postmaster が起動時に「これオレのだかんね」といって抱え込む量で、極端な話、フロントエンドから全く問い合わせが来なくて鼻チョウチンをふくらましていても、 postmaster はこの共有メモリを他に譲ったりはしない。ここは、いわば postmaster の「作業机」で、必要なテーブルはここに展開 (バッファ) して使う。データが既にバッファ上にあればすぐに作業に取りかかれるが、机のサイズが小さいと、要らなくなった図面 (リレーションまたはその一部) を机上から片付けてスペースを空けてからヒキダシ (ディスク) をまさぐってデータを引っ張り出し机に広げるという、非常にコストのかかる処理が繰り返されるので、処理速度に顕著な違いが現れる。

システム搭載メモリが数ギガという贅沢なマシンなら、運用しているデータベースが丸ごと収まる量にしたいといころだが、そうもいかないのが世の常。そこで、運用の中で頻繁に使われる特にコストの高いクエリがあるなら、せめて、それに使われるテーブルとインデックスだけでも納まるくらいに shared_buffers を拡大したい。

計算や設定の行程は、

の 5 段階として捉えることができる。計算は、電卓の桁数をはみ出るような値が頻出するのと、ヒストリー機能が効いて再計算が楽になるため、 psql や UNIX シェルの計算能力を使うのがお勧めだ。ここからの説明で出てくる計算結果の値は、あくまでも例であり 「推奨値」なんかではないと言う点を、重々ご承知おき頂きたい。そのまま書き写してサーバがどうなっても私は知らない。

第1段階： 運用テーブルサイズの割り出し

最適な値を割り出すための第一段階としてまず、テーブルのサイズを知ることから始めなくてはならない。合計には、インデックスリレーションのサイズも含める必要がある。また、例には登場させていないが、ラージオブジェクトを格納している場合は Toast テーブルも考慮に入れなければならない。例として、ユーザ hoge の所有するデータベース hoge の概算手順を示そう。スーパーユーザ postgres になって psql 上でシステムカタログに以下のような問い合わせをする。 PostgreSQL 8.0.4 文書 - 第24章ディスク使用量の監視 のほぼ受け売りだ。；

postgres=# \c hoge
hoge=# SELECT usesysid FROM pg_user WHERE usename='hoge';
<-- hoge のユーザid ナンバーがわかる。100 だったとする-->
hoge=# SELECT relname,relfilenode,relpages FROM pg_class WHERE relowner=100;
  relname  | relfilenode | relpages
-------------------------------------
t_hoge     |   111111111 |    25000
t_hoge2    |   111111113 |     5000
t_hoge_pkey|   111111112 |      200

サイズはページ数で表現される。 1 ページは通常 8K バイトなので、これら 3 つのリレーションの合計サイズは (PostgreSQL を電卓代わりにして)；

SELECT 25000 + 5000 + 200;
30200 (pages)
SELECT 30200 * 8;
241600 (KBytes)

だと分かる。

第2段階： shared_buffers の計算

そうしたら今度は、先ほどのテーブルサイズから、それらを収めるのに必要な shared_buffers 数を求める。目安は；

shared_buffers = (テーブルサイズ + 512K) / バッファブロックサイズ

であり、前述したようにバッファ 1 ブロックのサイズは通常 8 Kバイトなので、

SELECT 241600 + 512;
242112 (KBytes)
SELECT 242112 / 8;
30264 (pages)

となる。当計算例では少し余裕を持たせて shared_buffers = 31000 と仮決めする。おっと、ここで早合点して PostgreSQL を再起動しないように！ 今再起動するとおそらく `FATAL: could not create shared memory segment: Invalid argument' というエラーメッセージとともに postmaster が exit してしまうだろう。

第3段階： カーネルの shmmax の計算

shared_buffers の値は一応は出た、が、まだ序盤に過ぎない。それだけのメモリをシステム様が使わせてくれるかどうか確認し、必要であればカーネルを設定してやらなければならないのだ。カーネルに設定してやる値は shared_buffers の値そのままではない。ここがお算数の山場となる。

カーネルの共有メモリ量を決める主なパラメータは shmmax と shmall で、それぞれ /proc/sys/kernel/shmmax と /proc/sys/kernel/shmall を cat してやれば現状の値が分かるし、そこに新たな値を放り込むだけで、マシンの再起動さえなしに変更することができる。 PostgreSQL に直接関係するのは shmmax の方で、これは、共有メモリ 1 セグメントとしてカーネルが割り当てできる最大サイズ (バイト)。つまり、プログラムが「共有メモリをこれだけ貸せ」と言ってきた時に、カーネルが一度に差し出せるメモリ量の上限だ。今実際に貸し出している共有メモリセグメント各々の大きさを見てみたいなら、

 root# ipcs -m

でリストを得ることができる (全ユーザのセグメントを見るために、必ず root になってから行う)。

※ ちなみに、私は門外漢であるが FreeBSD では、「SHMMAX は SHMMAXPGS から自動計算されるだけなので SHMMAXPGS の方を変えなさい」というドキュメントを目にしたことがある。ご注意。

ではまず、現在の shmmax の値を見てみよう；

user$ cat /proc/sys/kernel/shmmax
33554432

上記は Fedora Core 4 のデフォルトを見た例で、メガバイト換算するとたったの 32M だ。第2段階の計算から、 PostgreSQL の欲しがっている量は ごく単純に考えても 230M バイトは下らないはずなので、これではとても足りない。ただし、物理メモリのほとんどをこれに当ててしまったとしたら、他のプログラムの実行メモリも逼迫するし、かえってスワップが発生して使い物にならなくなる。前述の max_connections の設定や、サーバ上で他にどんなサービスやアプリケーションを運用するかによって事情は全く違ってくるが、搭載メモリ 1 ギガのマシンで実際にやってみると、shmmax は全搭載メモリの 1/4 以下、または、搭載モリ全体から shmmax を差し引いて最低 750M 前後は残らないと帳尻が合わない --つまりスワップの足音に怯えながら暮らす羽目になる。

では、必要な shmmax の計算に入ろう。 PostgreSQL 7.3.4 管理者用ガイド - Chapter 3. サーバ実行時環境 によると、最低限必要な shmmax 値を得る計算は (ceil は繰り上げ)；

ceil( 250 K + (8.2 K * shared_buffers) + (14.2 K * max_connections) )

とあるから、仮に max_connections を 128 本に設定したいとすると shmmax は；

SELECT ceil(250 + 8.2 * 31000 + 14.2 * 128);
256268 (KBytes)
SELECT ceil(256268 / 1024);
250 (MBytes)

かそれ以上でなければならない。ちなみに max_connections を 64 本で我慢してみようと思っても；

SELECT ceil(250 + 8.2 * 31000 + 14.2 * 64);
255359 (KBytes)
SELECT ceil(255359 / 1024);
249 (MBytes)

となって、ほとんど差がないことが分かる。 64 と 128 で迷うなら、 shmmax だけで考えれば 128 のほうが得な気がする (実際には動的な work_mem による総消費量も増えるので、単純に多い方を選んでいいとは限らない)。以降の計算では前者の 256268 (KB) を使う。切りよく丸めてから単位換算をして proc 仮想ファイルシステムに書き込む。ここからは BASH の計算能力を活用してみよう。；

root# echo $((256268 / 1024))
250 (MBytes)
<-- 切りよく 16M の倍数に丸めて 256M にすることにする。偶然にも想定搭載メモリ 1G のきっかり 1/4 となった -->
root# echo $((256 * 1024 * 1024))
268435456 (Bytes)
root# echo 268435456 >/proc/sys/kernel/shmmax

shmmax はこれにて一応の解決を見た。ただし、まだこれは仮決めの状態。最終段階で shared_buffers を上下させることを考慮して、現段階では少し大きめに採っておくのがコツだ。例では 16M の倍数に丸めたが、そうしなければならない確たる根拠はない。

第4段階： カーネルの shmall の計算

カーネルの shmall 値は、システムの許す共有メモリの総量で、先ほどの shmmax と同じかそれ以上の量になっていなくてはいけない。ところがこれまた困ったことに、この shmall はバイト数で書いてあるとは限らず、ページ数表記かもしれないのだ。 Fedora Core 4 のものをはじめ、多くのカーネルではページ数指定となっているらしい。まずは兎も角 /proc/sys/kernel/shmall を cat してみよう。 Fedora Core 4 では下のようになった；

user$ cat /proc/sys/kernel/shmall
2097152

shmall は共有メモリの総量なのだから、デフォルトの shmmax より少ないはずはない。同じかそれ以上ならばバイト表記なので比較は単純だ。しかし上の結果は、 shmmax にデフォルトで設定されていた 33554432 (32M) より明らかに小さいので、間違いなくページ単位だ。

現状の shmall バイトを割り出す最も簡単な方法は ipcs コマンドを使う方法だ；

user$ ipcs -l

出力されるリストの `max total shared memory (kbytes)' が問題の値のキロバイト表示だ。

もうひとつの、もっと疑り深い人用のやり方は、カーネルメモリのページサイズを実際に割り出してから計算する方法。カーネルのページサイズは Tuning and Optimizing Red Hat Linux Advanced Server 2.1 for Oracle9i Databases (RedHat, 9i) 受け売りの、以下のスクリプトをターミナル上に貼り付けて実行することで確認できる (テンポラリな C プログラムを生成して実行するという強烈なワザにおみそれした)；

cat << EOF |
#include <stdio.h>
main() { printf ("%d bytes\n",getpagesize()); }
EOF
gcc -xc - -o /tmp/getpagesize
/tmp/getpagesize; rm -f /tmp/getpagesize

多くの場合 4 KB だが確認しておくに越したことはない。ここでは結果が 4096 (4K) だったと仮定して話を進める。現在の shmall の値と掛け合わせればバイト単位の共有メモリ総計サイズが出る；

root# echo $((2097152 * 4096))
8589934592 (Bytes)
root# echo $((8589934592 / 1024 / 1024))
8192 (MBytes)

ということで 8 ギガも確保されていた。充分であることが保証されたので shmall はお咎め無し。やれやれ、お疲れ様でした。

なお、 調整の必要のある shmmax は再起動すればデフォルトに戻ってしまうので、起動の度に設定されるように /etc/sysctl.conf に書いておく。以下の要領だ。もちろん、上で shmall も要調整という結果に転がったならば、同様に kernel.shmall = xxxx も書いておけば良い。；

kernel.shmmax = 268435456
最終段階： キャッシュ効果の確認と値の調整

shared_buffers の値が適切かどうかは、PostgreSQL の備える統計情報収集機構を使えば調べられる。 ps で見た時にデフォルトで postmaster と一緒に立ち上がる 'stats collector process' というプロセスがそれだ。もしも立ち上がっていなければ postgresql.conf の中の stats_start_collector を true にすれば立ち上がるようになる。そして、ここで話題にしている情報を実際に収集させるには、デフォルトではオフになっている以下の 2 項目も真にする；

stats_block_level = true
stats_row_level = true

このふたつがデフォルトでオフになっているのは用もないのに有効にしておくとオーバーヘッドになるからであり、確認作業が終わったら偽に戻しておくのを忘れないでいだだきたい。さて、設定ファイルの書き換えが終わったら PostgreSQL を再起動する。

再起動しなくても、セッション内でスーパーユーザ postgres になれば `SET stats_block_level=1;' といったSQLコマンドでも即時にオンにできるのだが、 SET コマンドや set_config() 関数は当の psql セッションにしか影響を与えないので、別のセッションの情報は収集させられない。ここからやるテストは、リアリティのあるクエリを数回から数十回発してみないと意味がない。 psql ターミナルで直接叩けるような単純なクエリばかりならそれでもいいが、大抵の運用では CGI やプログラムを経由して複雑なクエリ文を生成しているだろうから、やはりサービスの再起動か、少なくともリロードが必要だ。

しかるべきステータスコレクターを伴った postmaster が起動したところで、実際のクエリを 10～20 回ほど発行してやろう。それが終わったら、収集された統計情報を PostgreSQL 作り付けのビューを通じて確認する。詳細は PostgreSQL 8.0.4 文書 - 第23章データベース活動状況の監視 で述べられているが、今問題にしているキャッシュ効果に関しては以下の4つで大体見当がつく。スーパーユーザでも運用ユーザでも構わないがとにかく目的のデータベースにコネクトして；

SELECT * FROM pg_statio_user_tables;    <= 普通のテーブルに関する統計
SELECT * FROM pg_statio_user_indexes;   <= インデックスリレーションに関する統計
SELECT * FROM pg_statio_user_sequences; <= シーケンスに関する統計
SELECT * FROM pg_stat_database;         <= データベース毎の概要統計

*_blks_read はディスクから読まなければならなかったブロック数、それと対になった *_blks_hit がキャッシュからのリードですんだブロック数を表す。つまり *_blks_hit の値が *_blks_read よりも高く、その差が大きいほど、バッファキャッシュが効いていることになる。運用クエリを更に何回か発してやると、キャッシュが充分に効いていれば *_blks_hit の数が増える一方で、 *_blks_read はあまり変わって行かないはずだ。

あとは postgresql.conf の shared_buffers の値を変えては postmaster を再起動し、テスト、の繰り返し。統計カウンタの値は postgresql.conf の stats_reset_on_server_start が true になっていれば (デフォルト)、 postmaster の再起動の度にゼロリセットされる。 shared_buffers の上下幅は 1000 ずつで構わないだろう。なお、テストの過程では、 shared_buffers を減少させる場合や、増やすとしても shmmax にまだ余裕があれば、カーネルの shmmax をいちいち変える必要はない。

そうしていよいよ最適な shared_buffers が突き止められたところで、第3段階：カーネルの shmmax の計算 へ戻って最終的な shmmax を再計算して proc と sysctl.conf に放り込めば -- 遂に完了だ！

WAL処理の最適化

PostgreSQL は WAL (Write Ahead Log) という機構を持ち、データの更新などを一旦、バッファの役目をするファイルに貯め、内容が一定量に達するか checkpoint_timeout の設定秒数 (デフォルト 300秒) 経過する毎に、ディスク上の実データへ出力/反映するという処理を繰り返している (このバッファのことを `WAL ログ' と呼ぶが、普段口にする「ログ」とはかなり異質なもの。紛らわしい)。この頻度を下げてやれば「余分」な書き出し作業が減ることになるのでパフォーマンスが上がることがある。どれだけ溜まったら書き出すのかを制御するのが、 postgresql.conf のパラメータ；

 checkpoint_segments = 3

だ。パフォーマンス以前の問題として、ログに `checkpoints are occurring too frequently' という警告が出るようであれば、早速この値を増加させなくてはいけない。単位はセグメント数で、 PostgreSQL 8.0.4 のマニュアルによると WAL ログの 1 セグメントは通常 16MB。デフォルトは 3 つまり 48MB で、たいてい低すぎる。最適値は、 google してみると 16 セグメント程度らしく、8 か 16 あたりが妥当だと思われる。

注意点としては、チェックポイントセグメント数を極端に高くすると、マシンがクラッシュした際の自動復旧 (REDO) に時間がかかることと、最大で (checkpoint_segments * 2 + 1) * 16MB のディスク領域が必要となる点。 WAL ログの実体は PostgreSQL データディレクトリの下の pg_xlog サブディレクトリに 000000010000000000000000 で始まる長ったらしい名前を持ったセグメント毎のファイルとして置かれる。データディレクトリの容量が危なくなってきたら、 WAL ログ用ディレクトリを別のファイルシステム上に作り、本来の pg_xlog をそのシンボリックリンクに置き換えれば凌ぐこともできる。別のスピンドル上に WAL ログを置くことはまた、ひとつのパフォーマンスアップにもなるようだ。

適切なインデックスの作成

PostgreSQL のマニュアルなどを見ていると、「無駄なインデックスを減らせ」という方向ばかり連呼されているが、インデックスの作成に臆病になりすぎてはいけない。要は、検索効率のアップとテーブル物理サイズ肥大のバランスの問題だ。確かに、数十万レコードのテーブルからインデックスを作ると、インデックスサイズだけで数千ページつまり数十メガバイトに達するケースもあり、運用中に共有メモリ shared_buffers がそれだけ食いつぶされることになる。しかし、問い合わせにかかる時間やコストがそれで 1/100 になるとすれば、犠牲を差し引いてもたっぷりお釣りが来る。 100 倍のスピードアップというのは決して大袈裟ではなく、実際に 100 倍以上のスピードを獲得した体験がある。総当たり検索 (Sequencial Scan) よりもインデックススキャンの方が速いのは、考えてみれば当たり前だ。以下に、インデックスについて気づいた点をリストアップする；

インデックスが実際に活用されているかどうかは `EXPLAIN SQL_command ' でも見られるし、キャッシュ効果の確認と値の調整 で触れた pg_statio_user_indexes ビューでも確認できる。メモリにまだ余裕がある場合には、インデックス作成後に shared_buffers もその分だけ増やしてやった方が良い。

テーブルのクラスタリング (CLUSTER)

複数のデータベースサーバで並列分散処理をする...みたいな雰囲気の名前だが、そう思った人はハズレ。この SQL コマンドは、そのテーブルのレコードを、インデックスに基づいてディスク上で物理的に並び替え/再配置してくれる。コマンドの最も基本的な書式はこうだ；

CLUSTER index_name ON table_name;

クラスタリングが効果を発揮するのは、ごく限られた種類のテーブルだけらしい。それは、複数列を束ねたインデックスを持つテーブル、つまり `PRIMARY KEY(column1, column2, column3)' や `CREATE INDEX index_name ON table_name(column1, column2);' といった定義を持つテーブルのことだ。こうした類の或る巨大テーブルでは、ビフォー/アフターで EXPLAIN 結果を比べてみたところ、 ORDER や OUTER JOIN, WHERE を伴うクエリでおよそ 20 ％ 以上もコストが下がった。

憶えておかなければならないのは、CLUSTER を行ったテーブルには即座に VACUUM ANALYZE も掛けてテーブルの素性を PostgreSQL に勉強しなおさせてやること。そしてもうひとつ、 CLUSTER は「持続的」なコマンドではないと言う点。 CLUSTER 処理をどのテーブルにどのインデックスを基に実施したかはデータベースに記録されるものの、一度クラスタリングしたからといって、データ書き換えの度に勝手に整頓され続けるわけではない。書き換えの頻繁なテーブルでは定期的に CLUSTER をしないと遅かれ早かれ効果は失われてしまう。一方、運用上リードオンリーな性格のテーブルならば、大した手間もなくメリットが享受できるわけだ。

クエリの効率化

複雑なクエリになればなるほど、同じ処理を行うのにもたくさんのやり方が存在するものだ。クエリによってオプティマイザに検索手順を「強制」したり、 PostgreSQL にとって重い処理をより軽い処理に置き換えたりすることで、検索時間を短縮できることがある。ケースバイケースだが、いろいろな SQL 文を試してみる価値はある。

DISTINCT を GROUP BY に置き換えてみる

結果から値の重複するレコードを取り除きたい時、直感的に DISTINCT を使いがちだが、 DISTINCT 処理は暗黙にソート処理を伴うので重くなる場合がある。同じ効果の得られる GROUP BY に置き換えることによって、スピードが上がる可能性があり、重複するレコードが多いほど効果が見込める。その際 HAVING は必ずしも必要ではない。例えば；

SELECT DISTINCT id FROM t_names WHERE name = 'foo';

というクエリは、[案 1]

SELECT id FROM t_names GROUP BY id HAVING name = 'foo';

でも、 [案 2]

SELECT id FROM t_names WHERE name = 'foo' GROUP BY id;

でも同じ出力が得られるが [案 2] にした時に最も速く約 25% の時間短縮が達成できた経験がある。

POSIX正規表現マッチをLIKEに置き換える

`~' によるマッチングは PCRE (Perl 互換正規表現) に近い多機能な一致検索ができるが、その分、オーバーヘッドがある。 LIKE でカバーできる単純なマッチングならば LIKE を使った方が多少軽い。例えば WHERE name ~ '^foo.*' なんていうものは即刻、 WHERE name LIKE 'foo%' に置き換えるべきだ。運用のTips - パターンマッチング も参照のこと。

ただし、POSIX 正規表現を片っ端からやめればいいというわけではない。例を挙げると、 WHERE name ~ '^[abc]$' を WHERE name IN ('a','b','c') に置き換えてみて、かえって遅くなったことがある。

WHERE .. OR を WHERE IN (..) に置き換える

おしなべて、

SELECT id FROM t_names WHERE id = 120 OR id = 121 OR id = 122;

といったクエリは、

SELECT id FROM t_names WHERE id IN (120, 121, 122);

とした方が処理時間が短い。運用のTips - 副問い合わせ評価式 も参照のこと。

Prev INDEX  
運用のTips [その他] HOME