« Google日本法人が自ら「有料リンク」の地雷踏んで祭り化してる件 | Main | pg_pconnectに気をつけろという話に気をつけるべき理由 »

PostgreSQLを高速化する16のポイント

漢(オトコ)のコンピュータ道: MySQLを高速化する10の方法という記事が人気だったらしい。早速便乗しよう。
キーワード： postgresql, パフォーマンス, 性能, チューニング, 高速化

「0.2秒のSQLを0.1秒に高速化する努力なんて無駄だ」というキリギリス根性をまず最初に改める

それPostgreSQLの話じゃないよというツッコミはお約束過ぎるのでスルー。何事もはじめの動機付けが肝要である。

Chromeはなぜ速いのか － ＠IT (2008/12)

「この結論に驚くかもしれない――、ユーザーが0.5秒の遅れに気付くかって？しかし、Amazon.comでもわれわれは同様の経験をしている。100ミリ秒単位でページ表示を遅らせるA/Bテスト（条件を変えて2つのサービスを同時に公開するテスト）で、非常に小さな遅延ですら、収入に大きく響いてくるということを発見した。速いというのは本当に大切なことだ。マリッサが言うように“ユーザーはスピードに敏感に反応する”のだ」

最新版を使う

  • ver 7.4でin句の高速化(2003)
  • ver 8.0でテーブルスペース対応(2005)
  • 8.1でメモリ管理の高速化、複数CPU（マルチコア）での高速化、ビットマップインデックス(2005)
  • 8.2でソート高速化、自動バキューム(2006)
  • 8.3でHeap Only TupleによるUPDATEの高速化や、不要領域の増加抑制つまりバキューム処理のための運用負荷の大幅な低減(2008)

ここ数年のPostgreSQLの進化はすさまじく、2009年現在においてその性能はOracleと同等かそれより速い。にもかかわらず、いつまでも古いバージョンを使い続けている人（システム）は多いだろう。面倒くさがらずに最新版に上げてそこをスタート地点にしたほうがいい。

実際に使うマシンでコンパイルする

OSのDVDイメージについてたPostgreSQLのrpmパッケージをそのまんま使ってます、というケースも多々あるだろう。性能を気にするのであればそんなものを使ってはいけない。自分で./configure, makeすると自分のCPUやOSに最適化されるので性能が少なくとも10%くらいは違ってくる。

メモリ関係の設定だけでもちゃんとやる

ちょっと信じがたいが、よくわからないからという理由でデフォルト設定のままで使っているケースを聞いたことがある。デフォルトのpostgresql.confには例えば 「shared_buffers = 32MB」 なんて書いてあったりするが、この数値は例えば2GBのメモリを積んでるマシンであれば300MBかそれ以上であってもおかしくない。メモリ関係の設定値だけでもきちんと調査してハードの能力に合わせてしかるべくセットすべきだ。なお、OS側の設定もいじる必要がでてくるケースもあるだろう。

遅いSQLを観測する

高速化したいというのだから何かが遅いのだろう。なのに具体的に何が遅いのかを調べずに闇雲に動くのは時間の無駄である。 例えば log_min_duration_statement = 5000 とすることで5秒以上かかったSQL文をログに記録することができる。

ログがどこにあるのかわからない？ログを取ってない？！それは末期的だ。 筆者のおすすめは手っ取り早くsyslogに出しておく方法。
log_destination = 'syslog'
syslog_facility = 'LOCAL3'
とするとsyslogのlocal3に出る。あとはsyslog.confに
local3.* -/var/log/postgresql.log
とでも書けばそこに出る。ログローテートされないようであればlogrotateの設定追加も忘れずに。

explain または explain analyze でインデックスの使われ具合を調べる

アプリのコードを書いているときでもいいし、運用状態で先ほどの設定でログに出た内容そのものでもいい。コピペしてその先頭に explain とつけ加えるだけで実行計画を見ることができる。analyzeオプションをつけると実際にSQLが実際に実行されて Total runtime（実行時間）まで出る。例えばindexをcreate/dropしながらやってみるとindexがいかに高速化に貢献しているかという基本からして身をもって実感できるだろう。教科書に書いてあったことは忘れても自分でコマンドを叩いてわかったことは忘れないものだ。

定期的にバキュームする（ver8.2.x以下）

特に、UPDATEやDELETEの多いシステムでのvacuumは必須である。最低でも1日1回だろう。あるいは数十分に1回やっているというシステムも聞いたことがある。 vacuumの必要性はpostgresqlの最大の弱点である、いや、「だった」。 8.3以降ではHeap Only Tuple(HOT)の実装によってvacuumの必要性そのものが薄れつつあり、あとはデフォルトでオンになった自動バキュームに任せておけばよい。

なお、バキュームが弱点だとはいっても、Oracleでもまた統計情報の取り直し（ANALYZE）を定期的にやるのが普通だと思うので、PostgreSQLと大差ないんじゃなかろうか。

式インデックス（関数インデックス）を使う

例えばdate型（つまりYYYY-MM-DDのような日付）のカラムを持つテーブルがあるとする。ちゃんとインデックスも張ろう。
create table test_table (id int4, dt date)
create index dt_index on test_table (dt)
このテーブルから「水曜日のレコードだけを抽出したい」とする場合、
select * from test_table where date_part('dow', dt) = 4
（注：dowとは曜日のことでゼロが日曜で6が土曜）
などと書くが、このときdt_indexインデックスは使用されずにSeq Scan（つまりフル走査）が走ってしまう。つまり遅い。なぜなら、dt_indexはYYYY-MM-DDのような日付の値のインデックスだから。

そこで関数インデックスの登場。
create index dt_dow_index on test_table (date_part('dow',dt))
とすると、date_part()という関数によって得られた0-6の値に対してインデックスを張ることができるので、先ほどのSELECTであればdt_dow_indexインデックスを使った高速な Index Scanをしてくれる。

部分インデックスを使う

注文テーブルには過去3年分で計100万件のレコードが入っている。景気がいいサイトのようだ（笑）。 だが主に見るのはこれから商品をピックアップして発送しなければならない注文だ。つまり発送済みフラグが立ってないレコード。発送済みの古いレコードを他のテーブルに移しつつDELETEすれば整理がつくのかもしれないが、そういう改造をしている暇が無い場合もある。

create index shipping_order_index on order_table (order_id) where shipped_flag=0
create indexなのにselect文のようなwhere句がついているのがポイント。これで、where句にshipped_flag=0が入っているSELECT文がこのインデックスを使うようになり、高速化される。対象レコード数が減ってインデックスを張りなおす(reindex)するスピードも速くなるので運用が楽になるというメリットもある。

幾何データ型とか配列データ型とかGINインデックスなんてのもある

PostgreSQLのデータ型とインデックスの種類はとても豊富だ。Oracleの倍くらいの種類があるだろう。それらをうまく組み合わせると、便利かつ高速なアプリを作ることができる。

全文検索はludiaを使う

where xxx like '%hoge%'
これはいわゆるキーワード検索のときに使われる典型的なwhere句だが、 知ってのとおり基本的にindexが利かないので遅い。こういう検索をどうするかというのがいわゆる全文検索技術であり、これはこれでものすごく奥が深い。

これまでcontrib扱い＝正式じゃないけど便利なのでソースに含んでおくから自分で追加コンパイルして使ってね的機能＝だった、tsearch2という全文検索機能が、8.3以降で初めから組み込み済みになった。だが、空白で単語を区切れるヨーロッパ系言語と違って、日本語を扱うのはまだちょっと面倒くさい。（こういうのもあるけど）

そもそも形態素解析方式だと、そのシステム(サイト)独特の固有名詞について辞書に登録しておくなどの運用が必要になる。だが、辞書のメンテなんてやってられないのが実際の現場というものだ。多少のノイズが増えてもN-GRAM方式でよいとする場合が多いのではなかろうか。

ludiaは形態素解析もN-Gram解析も両方対応している。mecabなんて使わない(--without-mecab)でも、高速なインデックス作成と高速な検索の両方が可能だ。たとえば
create index my_ludia_index on hoge_tbl using fulltextb(hoge)
というふうにludiaのインデックスを作っておいて、あとは
select * from hoge_tbl where xxx like '%hoge%'
を
select * from hoge_tbl where xxx %% 'hoge'
にするだけだ。これで数百倍のスピード差が出るだろうというのは決して言いすぎではない。 ちなみに「%%」はludiaインデックスを使わせるための検索演算子である。 なお、ludiaのインデックスはPostgreSQLのインデックスと論理的に同等なので、普通にレコードをINSERT/DELETE/UPDATEすればludiaのインデックスもそれにあわせて更新される。

ところで、PostgreSQLのような一般RDBMS上での全文検索をあきらめて、検索専門のエンジン＝OSSで言えばnamazuとかluceneとかHyperEstraierとか＝に頼ろうとするケースも多いようだ。

mixiとかもうそういう超超超巨大サイトならそうせざるを得ないだろうしそうするべきだろう。 だがそういうサービスじゃないところにおいて、RDBMSを捨てるわけにも行かない状況にあるのにもかかわらず、専門検索エンジン環境＝もうひとつの別のデータ層＝を安易に作ってしまうのは（あくまで個人的見解だが）開発/運用負荷が増えるばっかりで愚策だと思う。この辺の話はまた別の記事で改めてまとめたい。

テーブルスペースを分ける

おそろしいことに、2005年にリリースされたver8.0まで、PostgreSQLにはテーブルスペース(表領域）という概念が無かった。さらに、長いことpgsqlを使っているユーザーはその状況に慣れきってしまって「テーブルスペースってナニ？ウマいの？」状態になっているケースすらあるのではなかろうか。

例えば、全てのデータはファイルシステム的には $PGDATA (ex. /usr/local/pgsql/data) の配下に格納されるのがデフォルト。

だが、create tablespace hogehoge.... のようにすると、/disk1/hoge とか /disk2/fuga のように任意のディレクトリをTABLE SPACEとして確保し、そこを指定して自在にテーブルやインデックスの情報を格納できる。
create table hogehoge_table (id int4, ........) tablespace hoge_space;
という感じ。

ディスクI/Oネックを避けるのために、TABLESPACEの概念は非常に有効である。 たとえばHDDを二個積んでいるのであれば一方のパーティションををテーブル格納用、一方をインデックス格納用というふうに分けるというのはよくある常套手段である。

アプリ側でのコネクションプール機構を使おう

SQLを投げて結果をどうこうする以前にまず、Web/apサーバがDBサーバに接続/切断するコストがバカにならない。

PHPであればpg_pconnect、Java+TomcatであればTomcatのコネクションプール機構をそのまま、その他アプリケーションフレームワークのレベルで提供されるコネクションプール機構があるようならそれを、必ず使うべきだ。

DB検索結果をアプリ側でキャッシュする

たとえばECサイトで特定の商品のページを表示する場合、商品テーブルから商品IDでDBを検索して商品名などを取得している。だが、商品名や説明はそんなにバンバン変わるのだろうか？ そのページが毎秒0.1ページビューを得ているとすると 同じSQLを投げて同じ結果を得ることを1時間に360回繰り返していることになる。 無駄だ。軽いSQLであってもチリも積もれば十分に負荷である。

memcachedのようなキャッシュ専用サーバを使うのもいいがちょっと大げさなので、 PHPであればCache_Lite、JavaであればOScacheなどがいいだろう。

Cache_LiteもOSCacheも、ファイル上に情報をキャッシュできる。Oscacheはヒープメモリ上にもキャッシュ可能だがヒープメモリが肥大化することになりそうなので筆者はあまり使わない。ファイルでも十分速いし。

JavaであればSELECTの結果を単純なJavaオブジェクトのArrayListかなにかに入れるケースが多いだろう。そのオブジェクトをそのままOSCacheに預ければいい。

PHPであればSELECTの結果をなんらかの多次元連想配列にすることが多いだろう。Cache_Liteオブジェクトのautomaticserializationをオンにしておくことでそうした配列データもキャッシュすることができる。

どんなキャッシュ機構であれたいていは「キャッシュID→データ」という単純なペアで情報を管理している。 「DB検索の結果をキャッシュしておく」という目的においてキャッシュIDにユニーク性を与えたいのであれば発行するSQL文そのものをキャッシュIDにしてしまうのが手っ取り早い。 プリペアドステートメントを使っているのであればバインド前のSQL文とバインドしたい変数を単純に文字列連結してしまえばユニークなキャッシュIDにできうるという効果は同じ。

商品ページが毎秒0.1ページビューを得ている状態でキャッシュの有効期限を30分にするのであれば、商品検索SQLの発行数を180回から1回に減らすことができる。 これで空いたDBサーバのリソースは、本当に高速性が必要なのに重いSQL＝トランザクションを伴う注文処理とか＝に費やすことができる。注文が入ったら在庫数が1減る＝商品テーブルの「在庫数」のカラムを-1することになる。ならばそのときついでに商品のキャッシュもクリアすればいい。

マクロな負荷観測もやっておく

DBサーバ上でsarを取っておくとか、net-snmpとcactiでロードアベレージのグラフ化ぐらいやっておくとか。Webサーバ側でも、せめてapacheのログで処理時間を取得するぐらいはやっておいて損はない。webアプリにおいてSQLの実行が速くなれば最終的にはapacheのリクエスト毎処理時間にもその効果が現れるはずだ。

PostgreSQLの公式マニュアルを読む

上にいくつもリンクしたように、PostgreSQLのマニュアルはとても充実している。困ったらまずこれを読み返そう。

root権のある専用サーバーを使う

ここまで読んで気がついたと思うが、上の項目のほとんどが通常の共有ホスティング型レンタルサーバでは不可能なことばかりである。root権が必要だ。 当たり前だろとか思っている人はこの業界の現実を知らない。 意外なほど多くの弱小サイト（の中の人）が、root権の無い安価な共用サーバ環境を当然のものとして使い続けている。root権てナニ？ウマいの？ 知らないということは実に恐ろしいことだ。

以上、別にPostgreSQLに限らんでもRDBMS一般に言えることが多々混じっておりますが、まあそう気にスンナ。

追記：

はてなブックマーク - dgdgのブックマーク

HOTとautovacuumを過大評価しすぎな気が。indexが張ってあるカラムの更新処理が走った場合にHOTは機能しない。autovacuumでは回収できない不要なディスク領域がある。やはりVACUUM FULLは定期的に実行したい。REINDEXも

有名な人も過去同じことを言っていたが、ソーシャルブックマークサイトのコメント欄はちょっとおかしなものが多いので基本的にスルーすべきである。単なる勘違いであれ情報不足であれ悪意であれなんであれ、そもそも、字数制限のきびしいところでの「公開するひとこと」の書き方にはテレビニュースのテロップ並みの気の使い方をしないとその読み手も書き手も不幸にしかならない。そういう意味においても、筆者はよくこう言う。「自分のブログに書くかそれができなきゃ/dev/nullにでも吐け」。ついでに言うならソーシャルブックマークはプライベートモードにしておきましょう。

とにかく、「根拠も示されていないような情報であってもとにかく活字にするだけで意外に信頼性が高まってしまう」という初歩的なトリックによって他の読者を誤誘導しないために、今回は特別として追記しておこう。

PostgreSQL8.3で実装されたHOT=Heap Only Tuplesは、「本来書き直す必要のない情報まで書き直してしまうムダを避ける」ためのものである。したがって、 「indexが張ってあるカラムの更新処理が走った場合にHOTは機能しない。」のは当然である。 indexが張ってあるカラムに収まっている情報を更新したらそのtableとそのindexの両方を更新しなければならない。これどのRDBMSを使っても同じこと。 それを否定することはオムレツを作るためなのにタマゴを割ることを否定するようなものであり、要するにトンチンカンである。

なお、定期的なREINDEXの必要性についてはたしかにそうだ。しかしそれはOracleでも似たようなものである。Oracleで、あるINDEXが断片化してしまっているらしくて性能がうんぬんでREINDEXしないとね、、、みたいなのはよくある会話だ。

その他、vacuumの必要性がどうとかいう話は識者に譲ることにしよう。下記のとおり。

  • HOTの効果 (1) HOTとは？ — Let's Postgres (lets.postgresql.jp 2009/1)
  • HOTの効果 (2) 更新処理性能が大幅アップ！ - DBT-2 — Let's Postgres (lets.postgresql.jp 2009/1)
  • HOTの効果 (3) VACUUM不要！？ - DBT-1 — Let's Postgres (lets.postgresql.jp 2009/1)
    DBT-1でPostgreSQLの8.2.5と8.3.0で12時間の測定をします。この時、本来であれば定期的にガベージ掃除のためのVACUUMが必要なのですが、これを敢えてせずに実施します。なお、PostgreSQL8.3からは、autovacuumという自動で然るべきタイミングに VACUUMを実施してくれる機能がデフォルトで有効になりましたが、これも無効にしています。

    （途中省略）

    8.2.5では、最終的に25万レコード分のガベージが蓄積されましたが、8.3.0ではわずか450という結果でした。HOTの効果の凄さが分かります。HOTのガベージ自動回収のおかげで、VACUUM無しでも安定して運用できています。autovacuumと組み合わせれば、ほぼメンテナンスフリーな運用が可能となりますね。 ちなみに、8.2.5でもしっかりとVACUUMを実施していれば安定した性能を見せてくれますのでご安心下さい
  • HOTの効果 (4) 【コラム】　ガベージの監視 — Let's Postgres (lets.postgresql.jp 2009/1)



はてなブックマーク - ねこるりの中の人のブックマーク
普通に良いまとめなのに追記で台無し。HOTの効果が薄い場合もあるのは確かなんだから、disる暇があるなら、HOTを前提にインデックスの張り方やスキーマを見直そうとか議論しようぜ。あとpg_pconnectは接続数の合計に注意 2009/02/24

だからそんなせまっくるしいところでトンチンカンにdisる暇あるんだったら自分のブログでお好みの議論を書くかさもなきゃ/dev/nullにでも吐けとやんわりと言ってるんだよハゲ。

追記：

ソーシャルブックマークサイトのせまっくるしいコメント欄で（結果的に）トンチンカンを吐くハゲと対極にあるのが、以下のような記事である。random_page_costか、なるほどねー。

追記：

2009年2月23日 Comments [0] | Trackbacks [0]

トラックバックURL

このエントリーのトラックバックURL:
http://www.ywcafe.net/mt/mt-tb.cgi/949

コメントする

(初めてのコメントの時は、コメントが表示されるためにこのブログのオーナーの承認が必要になることがあります。承認されるまでコメントは表示されませんのでしばらくお待ちください)


画像の中に見える文字を入力してください。