空気を読まずにPostgreSQLのを高速化する10のポイント - 象と戯れ - postgresqlグループ.の元エントリを読んで思うところがあったのだが、
だよねｗ。
まあ正直、上記元ネタのほうには色々突っ込みどころ満載なのだが、それは置いておくとしてL.starなりの高速化ポイントを一度書いておかないと、と思ったので記す。ただ、L.starはもうPostgreSQL界隈から離れて久しいので、必ずしも最新の内容を網羅していないことに注意されたし。また、出来るだけPostgreSQLだけでなく、一般的なDBに活用できるように記述している。
紙面と気力の都合もあるので、外部ツールとの切り分けとかの部分は記さなかった。LAP(P)ソリューションを本当に高速化したい人のための・・・がいるかもしれない。
追記：外部ツールに関するエントリを書いた。下ピンバック参照
RDBMSは汎用なので、実にさまざまな特色を持ちうる。読み込み中心、書き込み中心、OLTP、OLAP... それにより、いろんなものが異なってくる。そのため、L.starの経験では、どういうタイプかの特定なしに、意味のあるチューニングは出来ない。驚くべきことに、元エントリの16の方法では、この「特性」について一切ふれられていない。上げられている項目から見て、たぶんWebのバックエンドに多い、OLTP型。更新はちょっとで検索多めと言うことだろう。一方umitanuki氏も何も書いてないが、元々internalな部分に造詣が深い氏の場合は個別SQL向けチューニングポイントだろう。あなたのDBがこういった部分から外れていると、アドバイスはあまり役に立たなかったりする。
具体的にどの程度違うかと言う例だが、ものすごく参考になる記事として、pgsql氏の以下のエントリから表を引用する。氏には是非これを8.5に入れるべく頑張っていただきたいが。
このように、まったく同じテーブル定義だというのに、大きく差が出てくる。このタイプ分けにより、どのようなチューニングを施すべきかというのは決まってくるのである。以下に、どのようなタイプがあるか述べよう。
これはPostgreSQLに限らずの話。RDBMSの設計の最も初歩であるはずでありながら、ほとんど知られていないことがある。RDBMSは検索するツールであるから、そのスキーマはどのように検索するか？で決定される。何を格納するかではない。格納したいだけなら、インデックスなど要らないから、たんなるログファイルで良いのだ。
スキーマを決め、そのあとSQL設計をするのは、行き先を決めずに旅行に行くぐらい愚かだと心しなさい。（それはそれで楽しいであろうことは認めるが）
ちゃんと検索を考えてスキーマを決めた人、あるいはそれで問題があるのは分かっているが、戦略的な負けを認めた上で戦術的な最善を尽くしたい人は次に進んでよろしい:)
パレートの法則の変形であるが、問題のあるSQLはごく一部である。なので、それを修正することに集中し、ボトルネックとなっているSQLを潰そう。pgfoundryには、ログから（実行回数＊平均実行時間）の統計を取得してくれるツールがあったはずである。
この種のSQL解析をおこなったあと、次々と実際に時間の掛かるSQLを潰していくことが本来のチューニングの第一歩である。個別のSQLの何が問題かは、explain文でプランを読み解くことによって明らかに出来る。
L.starの知る限り、RDBMSを導入しているシステムというのは、このようなSQL（あるいはストアドプロシージャ）がボトルネックになっている。パラメータがどうこうというより、まずSQLを見直すことが重要であると心得なさい。それをそぎ落としたところで、ディスクI/Oがボトルネックとかそう言う話が出来るのである。
これは書き込み中心のアプリケーションなら基本中の基本である。上記表の(3)にもあるとおり、データ書き込みが発生する場合のボトルネックはxlogへの書き込みである。これはWALの意図からして同期書き込みでないといけないため、発生するI/O waitなども含めつつ考えないといけない。書き込みががつんと遅くなるRAID5などは、この種のDBにとっては最悪の選択肢である。
同期書き込みを良くするためにできること：
autovacuumがあるから大丈夫、と言われることも多い。いわゆる主キー持ちupdate中心のテーブルにはだいたい当てはまるが、油断してはいけない。まず、ちゃんとmax_fsm_pagesを設定しているか？というのは重要なポイントである。これが足りなければいくらvacuumしても無駄である。
また、本当の敵はTableのvacuumではなく、Indexのvacuumである。実は8.3のHOTが有効なケース（index関連行以外が更新される）は、7.4以降のindex vacuumが効率よく働くケースと酷似している。問題は、これに該当しない、大量投入大量削除ケースである。ログテーブルとか、SNSでメッセージを格納するようなのは、単調増加型のIDを持つのが普通だろうが、こういう場合にはHOTも効かないしindexは肥大する。
あと、vacuumは、deleteしてからすぐ効くのではなく、次のinsert時に再利用されると言う点は無視できない。すでに劣化した状態で、compactionによる性能向上を図りたいならvacuum fullやそれに類するもの(cluster,データが空ならtruncate。ただし、vacuumとは特性が異なるので注意)は必須である。
ところで、pg_reorgってどうなんですかね？一見良さそうなんですが。
これは正直、バージョンによって大きく違うのだが、8.3系の場合はもはやshared_bufferは取れるだけ取って良いようだ。かつて古いバージョンではロック管理が芳しくなく、多くするとロック待ちのために遅くなると言うことがあった。
しかし、限りあるメモリをPostgreSQLの二大検索用メモリパラメータである、work_memとshared_buffersにどう割り振るか？である。これはOLTP系（インデックスを使った少数件数取り出し中心）かOLAP系（シーケンシャルスキャン中心になる、大量件数取り出し＆多テーブルJOIN＆集約）なのかによって決まる。シーケンシャルスキャンが多くなると、キャッシュヒットは見込めなくなる。そのため
と言う配分が決まる。
RDBMSは、多数のプロセス/スレッドが走る。これらは競合しない限り並列実行による利益が享受できるが、ロック競合を起こすとそこはアムダールの法則の「マルチスレッドで実行できない状況」に従って、マルチスレッドでの性能向上を妨げる。マルチプロセッサシステムでの性能向上とは、すなわちこのロックを出来るだけ少なくすることにある。
だから、ユーザ側でもロックが発生する状況を熟知し、できるだけ競合が発生しないことを心がけないといけない。安直に「テーブルロックをかければいい」などというのは、性能面では御法度である。
例えば？ユニークIDの投入方法を考えてみよう。性能が良いのは以下の順である。
たしかに3は、ロールバックが発生したときにIDが飛ぶケースを防ぐことが可能だ。だが、性能低下は実際には2.の10倍以上になる。
ここらへんから、大きな部分は押さえ終わったのでTIPS系に。
Indexは検索を高速化するというのは周知の事実だが、更新を遅くするというのも理解しておいた方が良い。これの応用として、COPYで大量データ投入するときには、indexを全て外すというのがある。
もちろん、SQLはチューニングして、必要なだけindexを使うようにすべきである。そのとき、闇雲に増やすのではなく、考えて増やし、要らないindexは全てあとで消してしまうことを忘れないようにしよう。
PostgreSQLはコスト式Plannerしか持っていない。正しい統計情報があるかどうかは、SQLが高速に実行されるかどうかの重要なポイントである。コスト関連のパラメータをいじるなり、analyzeの機会を増やすなりして、できるだけその鮮度を保っておこう。
また、取得した統計が正しいかどうかもポイントである。IDのような連続したデータであればそれほど問題がない。しかし、これがトリッキーなデータになると、とたんに不正確になる。不正確なデータは不正確なプランを招き、性能低下の原因になる。8.4ではdefault_statistics_targetは10->100と大幅に増える
あとは一時テーブルの場合analyzeどうするか、とかそういう話もあるけど、PostgreSQLで一時テーブルは、そもそも使ってはいけない。
ソース嫁とかマニュアル嫁とかは、他の2つにも書いてあるので飛ばしておこう。しかし、本当に重要なことは眺めても分からない。ソースを読み込んだり、マニュアルの行間に書かれているのを発掘しないといけない。それがノウハウである。ここに書いてあるのはノウハウとしてポイントを得ているように出来るだけ書いたつもりだ。しかし、ここに書いてあるよりもずっと重要なことは、実際に動いているものからしか知ることが出来ない。
そもそも、あなたのシステムは1.で示した分類のどれか一つに当てはまるだろうか？たぶん複数の条件を備えているだろう。そうすると、相反するパラメータをどう設定するかとか、そう言う問題に引っかかる。正解は、残念ながらソースやマニュアルから導けない。だから、試して触って、どうなるか確かめるしかないのだ。
だから、ここに書いてある９つのポイントを参考にしたりしなかったりしながら自分自身で「これは使える」「これはダメ」と言うのを確かめ、それをノウハウに昇華しよう。そうすれば、継続して最適な性能を維持できる真のDBエンジニアになれるはずだ。道のりは遠いが頑張って欲しい。
だからそんなせまっくるしいところでトンチンカンにdisる暇あるんだったら自分のブログでお好みの議論を書くかさもなきゃ/dev/nullにでも吐けとやんわりと言ってるんだよハゲ。
というわけでｗ。
だよねｗ。
まあ正直、上記元ネタのほうには色々突っ込みどころ満載なのだが、それは置いておくとしてL.starなりの高速化ポイントを一度書いておかないと、と思ったので記す。ただ、L.starはもうPostgreSQL界隈から離れて久しいので、必ずしも最新の内容を網羅していないことに注意されたし。また、出来るだけPostgreSQLだけでなく、一般的なDBに活用できるように記述している。
紙面と気力の都合もあるので、外部ツールとの切り分けとかの部分は記さなかった。LAP(P)ソリューションを本当に高速化したい人のための・・・がいるかもしれない。
追記：外部ツールに関するエントリを書いた。下ピンバック参照
1. そのデータベースの傾向を知らないことには、何も始まらない。
RDBMSは汎用なので、実にさまざまな特色を持ちうる。読み込み中心、書き込み中心、OLTP、OLAP... それにより、いろんなものが異なってくる。そのため、L.starの経験では、どういうタイプかの特定なしに、意味のあるチューニングは出来ない。驚くべきことに、元エントリの16の方法では、この「特性」について一切ふれられていない。上げられている項目から見て、たぶんWebのバックエンドに多い、OLTP型。更新はちょっとで検索多めと言うことだろう。一方umitanuki氏も何も書いてないが、元々internalな部分に造詣が深い氏の場合は個別SQL向けチューニングポイントだろう。あなたのDBがこういった部分から外れていると、アドバイスはあまり役に立たなかったりする。
具体的にどの程度違うかと言う例だが、ものすごく参考になる記事として、pgsql氏の以下のエントリから表を引用する。氏には是非これを8.5に入れるべく頑張っていただきたいが。
2009-02-22サンプリング・プロファイラ (仮)
ID
1
2
3
4
pgbench
-S
-S
-N
(TPC-B)
shared_buffers
32MB
4MB
32MB
32MB
tps
12894
11245
1586
896
IDLE_IN_TRANSACTION
0.0%
0.0%
46.2%
44.0%
CPU
99.2%
93.7%
13.4%
12.8%
DATA_READ
0.1%
3.8%
0.0%
0.0%
DATA_WRITE
0.0%
0.0%
0.0%
0.0%
XLOG_WRITE
0.0%
0.0%
18.1%
5.6%
XLOG_FLUSH
0.0%
0.0%
0.0%
0.0%
LWLOCK_WAIT
0.7%
2.4%
22.3%
0.6%
LOCK_WAIT
0.0%
0.0%
0.0%
37.1%
このように、まったく同じテーブル定義だというのに、大きく差が出てくる。このタイプ分けにより、どのようなチューニングを施すべきかというのは決まってくるのである。以下に、どのようなタイプがあるか述べよう。
- 読み込み中心だよ
- キャッシュヒット率高いよ型（小規模DB型、CPUボトルネック）
- キャッシュヒット率低いよ（OTLP読み込み中心型）
- 検索で取ってくる件数少ないよ型（インデックス多用、OLTP系）
- 全件検索や集約演算多いよ型（シーケンシャル中心、OLAP系）
- 書き込み中心だよ
- 1件ごとの更新が多いよ
- update中心だよ派（OLTP書き込み中心）
- insert中心だよ派（ログ書き込み系）
- 一度に大量にやってくるよ派（バッチ書き込み系）
- 1件ごとの更新が多いよ
2. そもそも、スキーマ設計の段階で検索を考慮したか
これはPostgreSQLに限らずの話。RDBMSの設計の最も初歩であるはずでありながら、ほとんど知られていないことがある。RDBMSは検索するツールであるから、そのスキーマはどのように検索するか？で決定される。何を格納するかではない。格納したいだけなら、インデックスなど要らないから、たんなるログファイルで良いのだ。
スキーマを決め、そのあとSQL設計をするのは、行き先を決めずに旅行に行くぐらい愚かだと心しなさい。（それはそれで楽しいであろうことは認めるが）
ちゃんと検索を考えてスキーマを決めた人、あるいはそれで問題があるのは分かっているが、戦略的な負けを認めた上で戦術的な最善を尽くしたい人は次に進んでよろしい:)
3. 時間の掛かっているSQLから順にチューニングしろ
パレートの法則の変形であるが、問題のあるSQLはごく一部である。なので、それを修正することに集中し、ボトルネックとなっているSQLを潰そう。pgfoundryには、ログから（実行回数＊平均実行時間）の統計を取得してくれるツールがあったはずである。
この種のSQL解析をおこなったあと、次々と実際に時間の掛かるSQLを潰していくことが本来のチューニングの第一歩である。個別のSQLの何が問題かは、explain文でプランを読み解くことによって明らかに出来る。
L.starの知る限り、RDBMSを導入しているシステムというのは、このようなSQL（あるいはストアドプロシージャ）がボトルネックになっている。パラメータがどうこうというより、まずSQLを見直すことが重要であると心得なさい。それをそぎ落としたところで、ディスクI/Oがボトルネックとかそう言う話が出来るのである。
4. 書き込み中心のアプリケーションなら、ディスクの同期書き込みを制しろ
これは書き込み中心のアプリケーションなら基本中の基本である。上記表の(3)にもあるとおり、データ書き込みが発生する場合のボトルネックはxlogへの書き込みである。これはWALの意図からして同期書き込みでないといけないため、発生するI/O waitなども含めつつ考えないといけない。書き込みががつんと遅くなるRAID5などは、この種のDBにとっては最悪の選択肢である。
同期書き込みを良くするためにできること：
- WAL専用ディスク（SSDならなお良いだろうが、正直未知数）
- RAID0,1,1+10+1によるディスクI/Oの高速化
- バッテリーバックアップ付き書き込みキャッシュRAIDカード
- wal_sync_methodの適正な設定（現在はたいてい勘か全数調査）
- synchronus_commit=false(8.3)
5. 真のメンテナンスフリーのDBなどない。データの健全性のため、再構築には気を遣え
autovacuumがあるから大丈夫、と言われることも多い。いわゆる主キー持ちupdate中心のテーブルにはだいたい当てはまるが、油断してはいけない。まず、ちゃんとmax_fsm_pagesを設定しているか？というのは重要なポイントである。これが足りなければいくらvacuumしても無駄である。
また、本当の敵はTableのvacuumではなく、Indexのvacuumである。実は8.3のHOTが有効なケース（index関連行以外が更新される）は、7.4以降のindex vacuumが効率よく働くケースと酷似している。問題は、これに該当しない、大量投入大量削除ケースである。ログテーブルとか、SNSでメッセージを格納するようなのは、単調増加型のIDを持つのが普通だろうが、こういう場合にはHOTも効かないしindexは肥大する。
あと、vacuumは、deleteしてからすぐ効くのではなく、次のinsert時に再利用されると言う点は無視できない。すでに劣化した状態で、compactionによる性能向上を図りたいならvacuum fullやそれに類するもの(cluster,データが空ならtruncate。ただし、vacuumとは特性が異なるので注意)は必須である。
ところで、pg_reorgってどうなんですかね？一見良さそうなんですが。
6. メモリはwork_mem（ソート用メモリ）、shared_buffers（キャッシュ）のどっちに使うべきかは、インデックスを頻繁に使うかどうかを目安にしろ
これは正直、バージョンによって大きく違うのだが、8.3系の場合はもはやshared_bufferは取れるだけ取って良いようだ。かつて古いバージョンではロック管理が芳しくなく、多くするとロック待ちのために遅くなると言うことがあった。
しかし、限りあるメモリをPostgreSQLの二大検索用メモリパラメータである、work_memとshared_buffersにどう割り振るか？である。これはOLTP系（インデックスを使った少数件数取り出し中心）かOLAP系（シーケンシャルスキャン中心になる、大量件数取り出し＆多テーブルJOIN＆集約）なのかによって決まる。シーケンシャルスキャンが多くなると、キャッシュヒットは見込めなくなる。そのため
- OLTP系ならshared_buffers中心
- OLAP系ならwork_mem多め
と言う配分が決まる。
7. ロック競合は悪。絶対必要なのを除いてとにかく避けろ。
RDBMSは、多数のプロセス/スレッドが走る。これらは競合しない限り並列実行による利益が享受できるが、ロック競合を起こすとそこはアムダールの法則の「マルチスレッドで実行できない状況」に従って、マルチスレッドでの性能向上を妨げる。マルチプロセッサシステムでの性能向上とは、すなわちこのロックを出来るだけ少なくすることにある。
だから、ユーザ側でもロックが発生する状況を熟知し、できるだけ競合が発生しないことを心がけないといけない。安直に「テーブルロックをかければいい」などというのは、性能面では御法度である。
例えば？ユニークIDの投入方法を考えてみよう。性能が良いのは以下の順である。
- 絶対競合しないID生成法(timestamp+pid+セッション固有数値とか）
- シーケンス
（越えられない壁） - lockしてからselect max()でID生成
たしかに3は、ロールバックが発生したときにIDが飛ぶケースを防ぐことが可能だ。だが、性能低下は実際には2.の10倍以上になる。
8. Indexは多すぎてもダメ、少なすぎてもダメ
ここらへんから、大きな部分は押さえ終わったのでTIPS系に。
Indexは検索を高速化するというのは周知の事実だが、更新を遅くするというのも理解しておいた方が良い。これの応用として、COPYで大量データ投入するときには、indexを全て外すというのがある。
もちろん、SQLはチューニングして、必要なだけindexを使うようにすべきである。そのとき、闇雲に増やすのではなく、考えて増やし、要らないindexは全てあとで消してしまうことを忘れないようにしよう。
9. 統計情報に注意しろ
PostgreSQLはコスト式Plannerしか持っていない。正しい統計情報があるかどうかは、SQLが高速に実行されるかどうかの重要なポイントである。コスト関連のパラメータをいじるなり、analyzeの機会を増やすなりして、できるだけその鮮度を保っておこう。
また、取得した統計が正しいかどうかもポイントである。IDのような連続したデータであればそれほど問題がない。しかし、これがトリッキーなデータになると、とたんに不正確になる。不正確なデータは不正確なプランを招き、性能低下の原因になる。8.4ではdefault_statistics_targetは10->100と大幅に増える
あとは一時テーブルの場合analyzeどうするか、とかそういう話もあるけど、PostgreSQLで一時テーブルは、そもそも使ってはいけない。
10. とにかく触って、マニュアルにもソースにも書いてない「ノウハウ」をつかめ
ソース嫁とかマニュアル嫁とかは、他の2つにも書いてあるので飛ばしておこう。しかし、本当に重要なことは眺めても分からない。ソースを読み込んだり、マニュアルの行間に書かれているのを発掘しないといけない。それがノウハウである。ここに書いてあるのはノウハウとしてポイントを得ているように出来るだけ書いたつもりだ。しかし、ここに書いてあるよりもずっと重要なことは、実際に動いているものからしか知ることが出来ない。
そもそも、あなたのシステムは1.で示した分類のどれか一つに当てはまるだろうか？たぶん複数の条件を備えているだろう。そうすると、相反するパラメータをどう設定するかとか、そう言う問題に引っかかる。正解は、残念ながらソースやマニュアルから導けない。だから、試して触って、どうなるか確かめるしかないのだ。
だから、ここに書いてある９つのポイントを参考にしたりしなかったりしながら自分自身で「これは使える」「これはダメ」と言うのを確かめ、それをノウハウに昇華しよう。そうすれば、継続して最適な性能を維持できる真のDBエンジニアになれるはずだ。道のりは遠いが頑張って欲しい。
コメント
コメント一覧 (1)
いや本当にその通りだと思います。何故かPostgreSQLが「機嫌を損ねる」ときとかもあって、正直経験しかないですよね。