PostgreSQLのドキュメントコンパイル
きっかけ
postgresql_fdwという、外部のPostgreSQLのデータにアクセスするための外部データラッパを作成しているのですが、併せて説明ドキュメントも本体のドキュメントの一部として書いています。
PostgreSQLのドキュメントは、SGMLベースのドキュメント記述言語であるDocBookで記述されていて、必要に応じてそこからHTMLやPDFにフォーマットできるようになっています。ドキュメントの付録にも説明がありますね。
最近MacBook AirにPostgreSQL関連の開発環境を移したのですが、ドキュメントコンパイル環境がまだ揃ってなかったのでWebの情報をもとに整えました。
Range Typeでパーティショニング
きっかけ
6/23に日本PostgreSQLユーザ会の夏セミナーで PostgreSQL 9.2 新機能の話をしてきました。会場から「範囲型を使うとレンジパーティションの定義や管理が楽に出来ますか？」という質問があり「できますよ〜」と簡単に答えてしまったのですが、本当に出来るか不安になったので検証してみました。
ANALYZEでのサンプルデータ抽出ロジック
ANALYZE のサンプルデータ抽出ロジックを少し調べたので備忘録として。
PostgreSQL では、ANALYZE を実行したときにテーブルからサンプルデータを抽出し、それらの値に基づいて統計情報を作成しますが、サンプルデータの量（タプル数）は対象テーブルの各列の STATISTICS 設定(未設定ならばdefalut_statistics_target)にもとづいて決定されます。
コード中のコメントを見ると、サンプルデータの抽出アルゴリズムは Vitter 氏考案のサンプリングアルゴリズム(PDF)とか Knuth 氏のアルゴリズム S とかのハイブリッドになっている模様。
なお、ANALYZE 対象テーブルごとに一回のスキャンでサンプリングを完了させるため、ANALYZE対象の列のうち最も多くサンプルを必要とする列の設定に従います。各列の目標サンプル数は STATISTICS 設定の300倍ですが、Vitter アルゴリズムの制限(?)により最低目標サンプル数は100だそうです。
サンプル抽出のアルゴリズムを擬似コード化するとこんな感じです。
総ブロック数取得 if (呼び出し元が総ブロック数を要求) 引数経由で返却 現在アクティブなトランザクションの開始時にアクティブだった最古のXIDを取得 while (まだサンプルが必要) { 対象ブロック番号決定 VACUUM遅延チェック 対象ブロックをバッファに読込 バッファを共有ロック foreach(ページ内タプル) { if (DEADタプル) DEADタプルカウントアップ if (!Normalタプル) continue switch (タプル状態) { case 有効タプル: サンプル採用、LIVEタプルカウントアップ case DEADタプル: DEADタプルカウントアップ case INSERT中タプル: if (自TXがINSERTしたもの) サンプル採用、LIVEタプルカウントアップ case DELETE中タプル: if (自TXがDELETEしたもの) DEADタプルカウントアップ else LIVEタプルカウントアップ default: エラー } if (サンプル採用) { if (サンプル数が目標数未満) { 結果バッファ末尾にコピー サンプル数カウントアップ } else { if (残りスキップ数 < 0) スキップ数決定 if (残りスキップ数 > 0) 入れ替え位置を決定 結果バッファの入れ替え位置にコピー スキップ数カウントアップ } } } バッファロック解放 } if (サンプル数が目標数に届いた) { タプル位置(TID)の昇順でソート } 総タプル数を算出 DEADタプル数を算出 サンプル数を戻り値で返却
目標サンプル数を超えた後は取得した新しい(後ろのほうにある)タプルを既存サンプルと入れ替えていくのですが、そのときの入れ替え位置は単純なランダムです。取得したレコードがすべてLIVEタプルでDEADタプルなし、ということに単純化できれば、外部データラッパでANALYZE用にサンプリングするのに使えそうな気がします。
JPUG 第22回 しくみ＋アプリケーション勉強会
1/21(土)に開催された勉強会の備忘録。
PHPを使ったSQLインジェクション対策（前半）
- 第四企画の坂井さんの発表。
- '\t' などの意味を活かしたい場合は、standard_conforming_string = on のまま E'〜'と書くのがいいのでは。
- リテラルに B'〜' や X'〜' なんて表記があったのか(遅
- エンコーディングに気をつけよう。特にShift-JISは5C問題（「表」や「ソ」など2バイト目が'\'のもの）があるので要注意。
- クライアント〜アプリ〜DBまでを自由に決められるのであれば、現時点ではUTF-8に統一するのがよいのでは。
- 数値もクォートしたほうがよいの？
- PHPで大きい値を比較するときはint->floatなどの型昇格に注意。
- DBアクセスライブラリのMDB2やPDOのquoteメソッドを使うのが便利。
- 質疑
- あるサイトがSQLインジェクション対策がなされているか知る方法はある？→「' OR '1' = '1」とかを実際にインジェクションしてみるとかｗ
- ストアドプロシージャを使うとSQLインジェクションに強くなる？→SPに渡す値が確認済みならばOKでは。
xml_fdw XMLファイルをPostgreSQLの情報源として扱う外部データラッパの試作
- NTTソフトの原田さんの発表。ブログにエントリあり。
- xml_fdwを作ってみた。
- 外部サーバ：XMLファイル
- 外部テーブル：ノード種別
- カラム：子ノード種別
- 列値：子ノードのテキストデータ
- 列に関する情報は、9.2から追加された列単位FDWオプションですっきりしそう。
- 既存のドキュメントだけでは、API仕様などがわかりづらいとのこと。ドキュメント直すべきか？
- ExplainForeignScanとPlanForeignScanの区別
- スキャン終了でIterateが呼ばれなくなる条件
- プランナとエグゼキュータとの相互作用など、データフローの図がわかりやすい！カンファレンスでの発表に
パクらせて参考にさせてもらおう。 - ハマリどころは、コールバック関数間でのデータ引継ぎと仕事の振り分け、あとはスキャンの終了の仕方（最初は無限ループになっちゃった）。
なんちゃってEarly Lock Release --実装と評価--
- NECの堀川さんの発表。
- Early Lock Release を実装してみたけど、あまり効果はなかったとのこと。
- DBT-1の「pxgc用」は「pgxc用」のこと？
- rdtcsって何だろう？プロファイラ関連らしいので後で調べる。
- 負荷を上げたときにレスポンスがサチるのはなぜ？→ボトルネックがDBT-1のキューに移るから。
- 測定環境はメモリ数GB、DBサイズ4GB
- ロック競合が多いモデルのほうが効果が見えるかも。
- 非同期コミットとELRではリスクは同じ？ELRではクライアント目線ではトランザクションを失うことはないが、別トランザクション目線でも大丈夫？（@fujii_masaoさんのtweetより）確かに。
まとまってないまとめ
- パラメータのサニタイズとかクォート大変そう…プレースホルダ方式だけでは…多分だめなんですよね。Let's Postgresちゃんと読もう。
- FDWのドキュメントがんばる。
- 各発表50分だったけど、スライドは30枚くらいか……二月のPostgreSQL Conferenceの30分枠での発表は20枚くらいにすべき？
Synchronized Snapshot お試し:PostgreSQL Advent Calendar #7
このエントリはPostgreSQL Advent Calendarの12/7分です。
現在開発中のPostgreSQL 9.2でSynchronized Snapshot(同期スナップショット？)なる機能が導入されます。開発の動機はpg_dumpallで複数のデータベースのバックアップを一貫性のある内容で取りたいということのようで、あまり派手な機能ではないのですが、開発中の pgsql_fdw と組み合わせて面白いことできないかな、と思って調べたので内容を整理してみます。
続きを読むしくみ分科会+アプリケーション分科会勉強会
2011/10/29(土)に開催された勉強会に行ってきたので、聴講中のメモを備忘録として書いておきます。
Explaining EXPLAIN 第3回
前々回、前回からの連載記事。SQL チューニングについての Tips 的な話。Oracle ではチューニングを色々やったけど、やはりコストベースのプランナーは 80点くらいのプランしか出せない、という前提なのだろうか。懇親会でも話題に出てたけど「速くならなくていいから遅くならないで欲しい」という要望にどう応えたらいいのか…。
- SSD を使う場合はデフォルトのコスト値 index_scan_costs = 4.0 (seq_scan_costs の4倍)は大きすぎるかも。
- 結合条件の列名が同じ場合は USING (列名) でいける。外部テーブルの JOIN push-down でもサポートしているか不安になった。
- バージョン 7.4 までは、ANALYZE していないテーブルの見積もり件数は 1000 件になる。これを聞いた翌日、「pgsql_fdw が大量のメモリを消費してバックエンドが落ちる場合がある」という指摘を受けた。
- VACUUM 後でも、削除されたタプルを含めてスキャンするとのこと。インデックススキャンの話だったかな？9.2 からは index only scans が採用されたので、少し事情が変わりそう。
- 聴講者から「VACUUM FULL でディスクフルになった場合の挙動は？」という質問が出ました。板垣氏によると「VACUUM FULL に限らずデータファイルに関するディスクフルならば個別処理のエラー終了のみでクラッシュやデータ損失はない。ただし、WAL はディスクフルに弱いのでパフォーマンス以外の観点でもパーティションを分割しておいたほうが良い」とのこと。
- 「EXPLAIN 結果を可視化するツール/サービスはないか」という質問がでた。勉強会後にも調べたがコストまで含めて分かりやすい表示のものは意外とないようだ。
- pgAdmin3:実行計画をグラフィカルに表示してくれる。細かいところだけど、上位ノードに返されるデータ量が矢印の太さに反映されているっぽい。
- explain.depesz.com:実行計画を収集しているサイト。history ページでは実行計画のノードごとの情報を色分けして表示してくれる。
- Visual Explain:pgAdmin3と似たような表示。
データベースの古くて新しいボトルネック：ロック(WAL）
久しぶりに「しくみ分科会」という感じのネタでした。かなり駆け足だったので、若干消化不良かも。
データベース組み込みの全文検索を使うには(後編)
前回に続いて、全文検索。社内システムで使ってみようかと思ったけど、LIKE も以外と速いと聞いたので再考中。
- N-gram の説明の「N文字のデータをインデックシングするのではなく、1文字のデータに最大(N-1)までの付加情報をつけてインデックシングする」と聞いてなんとなく納得。
- pg_trgm を日本語で使うにはリビルドが必要とのこと。ドキュメントに書かないときっとみんなはまる。ソースコードからのビルドを許さないサイトでは採用できないということか。
- Prepared Statement でもインデックスを使ってくれる。
- 文中に改行やスペースがあると説が切れるのでデータ投入前にクレンジングすべし。
- pg_trgm は 8.3や8.4でも動くかも。textsearch_sennaは8.2以降で動く。
- 質疑応答では、サジェストや暗号化したテキストの検索、ランキング処理などアプリケーション側の仕様と関連した質問が多かった感じ。どこまで DB でやってどこからアプリケーションでやったらよいのか、中々きれいな答えは出ないみたい。
懇親会
色々な方と話せて毎回楽しいです。こっちがメインといってもよい？後で聞いたら二次会もあったらしい。惜しいことをした…。
