Recommended
More Related Content
What's hot
What's hot (20)
Similar to PostgreSQL 9.0 in OSC@Tokyo,Okinawa
Similar to PostgreSQL 9.0 in OSC@Tokyo,Okinawa (20)
More from Takahiro Itagaki
Recently uploaded
Recently uploaded (9)
PostgreSQL 9.0 in OSC@Tokyo,Okinawa
- 1. 日本PostgreSQLユーザ会 / フォルシア株式会社 板垣 貴裕 2010.9.11
- 2. 板垣 貴裕 (いたがき たかひろ) 所属 ◦ 日本PostgreSQLユーザ会 ◦ PostgreSQL Committer ◦ フォルシア株式会社 PostgreSQLは7.4～8.0あたりから触り始めた 主にPostgreSQLを中核にしたシステムの開発 ◦ 必要に応じてPostgreSQL本体の改造や 拡張モジュールも作ってみたり。 2
- 3. PostgreSQL 9.0 の新機能 ◦ 記念すべきリリース ◦ 新機能の紹介 – レプリケーション以外にも盛りだくさん！ レプリケーションをもっと詳しく ◦ ホット・スタンバイを使う際の、お勧めのノード構成 ◦ レプリケーションで、できること / できないこと 3
- 4. 9.0は5年ぶりの「記念リリース」 9.0.0 ◦ 上一桁の更新は「適用領域の大幅な拡大」を表す メジャー マイナー ◦ メジャーバージョンは上2桁 / バグ修整は3桁目 ホット・スタンバイとレプリケーションをぜひ使って欲しいので！ Ingress PostgreSQL 8.1 1977 6.0 7.4 •パーティショニング 8.3 9.0 (2010/秋予定 秋予定) 秋予定 1986 1996 2000 •2相コミット •バッファ管理改良 •HOT: 更新性能向上 ホット・スタンバイ •ホット・スタンバイ •VACUUM自動化 2003 2004 •全文テキスト検索 レプリケーション •レプリケーション POSTGRES 2005 •列 / 条件付きトリガ 2006 7.3 •排他制約 2007 2008 2009 8.0 2010 対応 •Windows対応 8.2 •セーブポイント •CPUスケール •メディア故障対応(PITR) •オンライン索引作成 8.４ •テーブルスペース •GIN: 汎用転置索引 Window関数・再帰クエリ •VACUUM用メモリ自動管理 •他DBMS互換性向上 4
- 5. 紹介する新機能 ◦ 1. ホット・スタンバイとストリーミング・レプリケーション ◦ 2. 排他制約 (地理データ型の“重なり”を制限) ◦ 3. 高速なVACUUM FULL ◦ 4. アクセス制御の一括変更 / デフォルト設定 ◦ 5. EXPLAINとクエリ統計の強化 ◦ 6. Key=Valueデータの保存 ◦ 7. pgbenchで性能計測 / 負荷試験 その他の新機能 ◦ 条件付き 及び 列単位のトリガ ◦ LISTEN/NOTIFY の高速化 / メッセージ送信 ◦ DO文：PL/pgSQLの簡易実行 ◦ Windows 環境での 64-bit 版サポート その他にも多数の新機能あり。下記URLも参照 http://developer.postgresql.org/pgdocs/postgres/release-9-0.html http://lets.postgresql.jp/documents/technical/9.0/ 5
- 6. PostgreSQL 9.0のレプリケーション機能 ◦ ホット・スタンバイ (HS) スタンバイで参照を許可する ◦ ストリーミング・レプリケーション (SR) 2つの組合せ WALレコードをスタンバイへ流す これまでのウォーム・スタンバイの発展系 8.0 オンライン・バックアップ アーカイブ・リカバリ 8.3 ウォーム・スタンバイ 9.0 ホット・スタンバイ ストリーミング・レプリケーション 6
- 7. READ WRITE READ WRITE ウォーム スタンバイ側では スタンバイ 何も処理できない (～v8.4) プライマリ スタンバイ 8.4 WALファイル単位のため archive_command WAL pg_standby 遅延がある (～1分) READ WRITE READ WRITE スタンバイ側で ホット 参照処理ができる スタンバイ 9.0 プライマリ (v9.0) スタンバイ WALレコード単位のため 遅延が少ない (～数秒) wal sender WAL wal receiver 9.0のスタンバイは資源を有効活用できる 7
- 8. 凡例 非常に良い 良い レプリケーション Slony-I pgpool-II バージョン 9.0 2.0 2.3 シングルマスタ シングルマスタ 種別 プロキシサーバ マルチスレーブ マルチスレーブ 伝播方式 物理変更セット 論理変更セット クエリ複製 伝播遅延 少ない 中程度 無し 更新性能 そのまま 若干低下 低下 複製範囲 DB一括 選択 (手間もかかる) DB一括 SQL互換性 完全互換 PK必須, DDL不可※ 概ね互換 スレーブ台数☆ ～10 ～10 ～3 カスケード 不可 可 (意味が無い) 自動参照分散 － － ○ 自動切り替え － － ○ バージョン混在 － ○ － 用途に応じてサードパーティ製ツールの使い分けが必要 ☆台数は妥当な性能を得られる上限 ※PK=主キー, DDL=データ定義言語 8
- 9. 範囲や地理データ型の“重なり”を制限 ◦ 一意性制約 (UNIQUE) “点” の “一致” を禁止 ◦ 排他制約 (EXCLUDE) “広がり” を持つ型の “重なり” を禁止 地理データ管理やスケジュール管理で有用 地理データ型の重なり 予約時間の重なり 新しいタイプのアプリケーションでの利用 ◦ GPS情報を利用するシステム ◦ グループ・コミュニケーション 9
- 10. 空間を占めるオブジェクトの重なりを避ける CREATE TABLE placement ( &&は “重なり” を表す object text, 比較演算子 location box, -- 矩形 EXCLUDE USING gist (location WITH &&) ); 部屋の予約時間の重なりを避ける CREATE TABLE reservation ( room text, during period, -- {開始日時,終了日時}のペア EXCLUDE USING gist (room WITH =, during WITH &&) ); 複数列gistインデックスも利用可能 ◦ 注意 text型のgistインデックスサポートはcontrib/btree_gistが必要 period型は自作が必要 (次期9.1で計画中) 10
- 11. VACUUM FULLの扱いが正反対に ◦ 8.4までは “最悪” のテーブル再編成方法 ダンプ→リストアのほうが遥かに効率的 ◦ 9.0からは “最良” のテーブル再編成方法 内部的なダンプ→リストア相当処理のため、最高速 VACUUM FULLも利用価値UP。ただしあくまで補助 ほとんどのケースはVACUUM (無印) で十分 ◦ FULLは対象テーブルの排他ロックが必要 ◦ 大量削除後はFULLが適する場合もあるが、 その場合でも「表分割+TRUNCATE」がより好ましい 11
- 12. コマンド テーブル インデックス 競合 VACUUM ゴミ取り ゴミ取り なし VACUUM 8.4 行を移動 ゴミ増加 参照, 更新 FULL 9.0 作り直し 作り直し 参照, 更新 CLUSTER ソート+作り直し 作り直し 参照, 更新 REINDEX - 作り直し 参照, 更新 ≪それぞれのコマンドの取り扱い≫ 普段はVACUUMで「ゴミを取る」だけで十分 ◦ ゴミを取り除いた後の領域は、次回以降の更新で再利用 VACUUM FULL (8.4) は、インデックスのゴミが増える ◦ 完了後、REINDEXしなければならないケースも VACUUM FULL (9.0) と CLUSTERは、REINDEXを含む ◦ 追加のREINDEXは不要
- 13. セキュリティ管理機能が使いやすくなった ◦ ROLEを使い分けてアクセス制御しているユーザ向け 新構文 ◦ GRANT/REVOKE ON ALL … IN SCHEMA 指定したスキーマ内のすべてのオブジェクトの権限を 一括して譲渡する / 取り消す ◦ ALTER DEFAULT PRIVILEGES FOR ROLE / IN SCHEMA ROLEが今後作成する / SCHEMA内に今後作られる オブジェクトのデフォルトの権限を設定する 8.4以前では GRANT ON ALL の代わりに PL/pgSQLでガマン FOR tbl IN SELECT tablename FROM pg_tables LOOP EXECUTE 'GRANT SELECT ON ' || tbl || ' TO ユーザ'; END LOOP; (DEFAULT PRIVILEGESの代わりは無い) 13
- 14. EXPLAIN (ANALYZE, BUFFERS) SELECT … ◦ 実行計画や処理時間と共に、バッファアクセス数も取得 ◦ メモリ割り当てに関連するパラメータチューニングに有用 例1 : “shared read” が多いからshared_buffersを増やそう 例2 : “temp read” が多いからwork_memを増やそう contrib/auto_explain (8.4～) ◦ 上記のEXPLAINを、スロークエリに対して自動的に行う ◦ SQLの流し直しが難しい場合、スロークエリログより役立つ contrib/pg_stat_statements (8.4～) ◦ SQLの種類 (文字列の一致) ごとに集計 ◦ 回数や時間 (8.4) に加え、バッファアクセス数 (9.0) も取得 ◦ ログを眺めず済むので楽。収集に必要なコストも低い 14
- 15. $ EDIT $PGDATA/postgresql.conf => shared_preload_libraries = 'pg_stat_statements' $ psql -f $PGHOME/share/contrib/pg_stat_statements.sql $ psql –c "SELECT query, round(100.0 * shared_blks_hit / (shared_blks_hit + shared_blks_read), 2) AS hitpct FROM pg_stat_statements ORDER BY shared_blks_read DESC" 見どころはreadの数 メモリ設定に関連 shared_blks_read クエリモードは 共有バッファ (shared_buffers) prepared or extended local_blks_read 例 $ pgbench -s10 -M prepared 一時テーブル (temp_buffers) shared_buffers 24MB 256MB temp_blks_read query | hitpct | hitpct ソート用メモリ (work_mem) -------------------------+--------+-------- UPDATE pgbench_accounts | 84.92 | 87.71 INSERT INTO pgbench_his | 99.00 | 99.23 UPDATE pgbench_tellers | 99.98 | 99.95 UPDATE pgbench_branches | 99.98 | 99.98 15
- 16. contrib/hstore ◦ {キー, 値} の配列を保持する型。いわゆるハッシュ型 ◦ 利用例1 : 柔軟な「正規化崩し」で検索性能UP ◦ 利用例2 : 頻繁なALTER COLUMNの代わりに ◦ 利用例3 : ほとんどNULLのたくさんの列を集約 =# SELECT hstore('a=>1,b=>2'); hstore 9.0にて大幅に強化 -------------------- "a"=>"1", "b"=>"2” ◦ サイズ制限 : 64kB → 1GB ◦ 要素検索 (GiST, GIN) に加えて、全体一致検索も (btree) ◦ レコード型との相互変換 (列名=値のhstore扱い) 次期9.1に向けて、さらにJSON型が開発中 16
- 17. 8.4 vs. 9.0 with pgbench –S -c64 (参照系) ◦ 8.4: 11,600tps (-j1相当) ◦ 9.0: 52,725tps (-j8) 4.5倍のスコア! …実際にはベンチマークの差 –j<スレッド数> オプションの追加 ◦ これまではpgbenchがシングルスレッドだったため、処理が 追い付かず、サーバに十分な負荷をかけられなかった ◦ 性能測定や高負荷試験に便利なツールとして ユーザSQLの 開発中のPG本体にあった、高負荷での 実行も可能 更新処理の不具合の発見にも貢献 17
- 19. ファイルサーバ (NAS) を用意 ◦ アーカイブログとオンライン・バックアップをNASに配置 利点 ◦ バックアップとレプリケーションを一括して設定できる ◦ 日々のバックアップを使ってスタンバイDBを追加できる マスタDB スタンバイDB バックアップ用ファイルサーバ 19
- 20. バックアップ元 アーカイブログを直接 バックアップサーバへ送る archive_command rsync = cp, scp ベース・ バックアップ バックアップ用ファイルサーバ 20
- 21. リストア先 必要なアーカイブログを バックアップから復元 restore_command rsync = cp, scp バックアップ用ファイルサーバ 21
- 22. バックアップとリカバリを マスタDB スタンバイDB 同時に行う構成 restore_command = pg_standby アーカイブが届くごとに リカバリを継続する バックアップ用ファイルサーバ 22
- 23. アーカイブログぶんが追いついたら マスタDB それ以降はストリーミング スタンバイDB primary_conninfo 参照OK restore_command = cp, scp pg_standbyではなく 単なるファイルコピー バックアップ用ファイルサーバ 23
- 24. できる できない スタンバイでの参照 参照 スタンバイでの更新 更新 全体の複製 全体 DBクラスタ全体 複製データの選択 選択 非同期での差分ログ転送 非同期 同期での差分ログ転送 同期 複数のスタンバイサーバ 複数 スタンバイのカスケード カスケード構成 カスケード すべての すべてのSQL スレーブでの一時テーブル 一時テーブル ノードの停止/再開 停止/ 停止 (ソート用一時ファイルはOK) 手動フェイルオーバー 手動 自動フェイルオーバー 自動 スタンバイの追加、削除 追加、削除 再構築無しの再組込 再組込は困難 再組込 24
- 25. 空のファイルシステム リストア ベース バックアップ スタンバイの停止/再開は自由 停止① リストアは最初だけ pg_ctl start pg_ctl start standby_mode = ‘off’ standby_mode = ‘on’ pg_ctl stop 通常運用 スタンバイ pg_ctl stop trigger_file 外部からトリガを与えて マスタに昇格 (フェイルオーバー) 停止② いったん通常運用に入ると スタンバイには戻せない 25
- 26. wal_level = hot_standby archive_mode = on マスタ archive_command = 'cp %p /arclog/%f' max_wal_senders = 1 EDIT $PGDATA1/postgresql.conf EDIT $PGDATA1/pg_hba.conf pg_ctl start -D $PGDATA1 host replication postgres ::1/128 trust SELECT pg_start_backup(‘label’) rsync など (ベース・バックアップ) rsync -av –delete SELECT pg_stop_backup() --exclude=pg_xlog --exclude=postmaster.pid $PGDATA1/* $BACKUP/pgdata スタンバイ rsync など (ベース・バックアップ復元) ◦ mkdir $PGDATA2/pg_xlog hot_standby = on EDIT $PGDATA2/postgresql.conf EDIT $PGDATA2/recovery.conf pg_ctl start -D $PGDATA2 restore_command = 'cp /arclog/%f %p' standby_mode = 'on' primary_conninfo = 'host=::1/128' 26
- 27. リプレイが必要なWALの “量” で遅れ具合がわかる ◦ マスタ：どこまでWALを作成したか? ① pg_current_xlog_location() ◦ スタンバイ：どこまでWALを受け取った / リプレイした ② pg_last_xlog_receive_location() ③ pg_last_xlog_replay_location() 遅れの計算 ◦ 送信遅れ : ① － ② 引き算の仕方に工夫が要る (単純な整数ではないため) ◦ リプレイ遅れ : ① － ③ 遅れの時間は時間当たりのWAL生成量から別途計算する 面倒な手順が必要なので、改善していきたいところ ◦ pgpool-II 3.0 の機能を利用する手も 27
- 28. PostgreSQLコアには無いが、pgpoolが対応予定 pgpool-II 3.0 新機能 ◦ マスタ=スレーブモードが9.0レプリケーション対応 ◦ トランザクション内のSELECTもロードバランス ◦ 拡張プロトコル (特にJDBC) のクエリも適切に振り分け ◦ スレーブの遅延をチェック。遅れすぎたら振り分けを手加減 ◦ クライアントアプリにエラーを返すノードを切り離し ◦ レプリケーションを利用したオンライン・リカバリ 役割分担 • PostgreSQL本体 = データのレプリケーション • Pgpool = クエリの振り分け, ノード管理 マスタDB App スタンバイDB pgpool 28
- 30. PostgreSQL “9.0” は開発者からの “ぜひ使って欲しい” というメッセージ ◦ レプリケーションを初め、数多くの機能強化 ◦ その他の機能紹介は “Let’s Postgres” にて http://lets.postgresql.jp/documents/technical/9.0/1 組み込みのレプリケーションが採用された ◦ SQL制約、遅延、性能オーバーヘッドの少ない方式 ◦ マスタ=スタンバイ レプリケーションの決定版！ 複数スタンバイ 単独マスタ 30
- 31. PostgreSQL 9.0 を お楽しみに！