Microsoft PowerPoint - 運用事例に学ぶPostgreSQL_発表版_修正.ppt

Size: px

Start display at page:

Download "Microsoft PowerPoint - 運用事例に学ぶPostgreSQL_発表版_修正.ppt"

けいざぶろううすい
4 years ago
Views:

1 運用事例に学ぶ PostgreSQL NTT OSS センタ勝俣智成坂本昌彦近藤光正

OSS センタのご紹介自己紹介を兼ねて - OSS センタ 1OSS トータルサポート

スタンバイ Apache UltraMonkey Tomcat JBoss PostgreSQL

年度実績 ) 各種ツール OSS 化 NTT グループ会社 OSS コミュニティ業務アプリ

2 OSS センタのご紹介自己紹介を兼ねて - OSS センタ 1OSS トータルサポートクライアント AP サーバ DB サーバ OSS トータルサポート例 Web サーバアクティブスタンバイ Apache UltraMonkey Tomcat JBoss PostgreSQL MySQL Heartbeat Amanda 2OSS 適用推進 3 技術開発 (DBMS クラスタ開発 ) 4 ソフトウェア基盤技術力向上 5 新規ビジネス検討サポートサービスプロダクト提供パッチ投稿周辺ツール整備パッチ件数 72 件 (H21 年度実績 ) 各種ツール OSS 化 NTT グループ会社 OSS コミュニティ業務アプリミドルウェア (OSS) OS (OSS) Copyright(c)2011 NTT, Inc. All Rights Reserved. 2

5 VACUUM をめぐる問合せの分類これまでのVACUUMの問合せを事象別にカテゴライズした本講演では比較的問合せが多いものを紹介するケース 1 ケース 2 ケース 3 問い合せ内容原因関連するPostgreSQLの仕様異常終了自動 VACUUM キャンセル機構終わらない ( 未応答 ) PostgreSQL 内部のロック終わらない ( 遅い ) 効果なし想定外の発動発動しない背景負荷 FREEZE ページ切詰ロングトランザクション FSM 不足 XID 周回防止機構統計情報リセットそもそも対象外 Copyright(c)2011 NTT, Inc. All Rights Reserved. 5

6 VACUUM 基礎 1/2 追記型アーキテクチャ PostgreSQL は追記型更新削除をしても元のデータが残る誰からも参照されなくなると不要なデータとみなされる DBサイズが大きくなっていく ID NAME 1 data1 2 data2 3 data3 ID NAME 1 data1 2 data2 3 data3 2 data22 3 data33 : 更新前タプル : 更新後タプル : ページ Update tablename set NAME= data22 where ID = 2; Update teblename set NAME= data33 where ID = 3; Copyright(c)2011 NTT, Inc. All Rights Reserved. 6

7 VACUUM 基礎 2/2 追記型アーキテクチャと VACUUM 不要なデータの回収作業が必要そのための処理が VACUUM 1 data1 2 data2 3 data3 VACUUM 1 data1 空き領域 FSM 2 data22 4 data4 3 data33 自動 VACUUM (8.3~) 2 data22 4 data4 3 data33 挿入更新時に再利用サイズ増を防ぐロック不要 : 不要タプル : ページ Copyright(c)2011 NTT, Inc. All Rights Reserved. 7

8 ケース 1. 自動 VACUUM がキャンセル事象ログに ERROR レベルで自動 VACUUM がキャンセルされた旨のログが出たが異常はないか? 再実施はされるのか? :31:55 JST ERROR: canceling autovacuum task 原因 / 仕様対策自動 VACUUM は競合する SQL 文によりキャンセルされる自動 VACUUM は再実施される特に意識をしなくてもよい本事象が起こりやすい例 : 夜間バッチ末尾でVACUUM コマンドを発行 CREATE INDEX, REINDEX などのDDLを発行業務 APが通常業務の中で LOCK TABLE を発行 Copyright(c)2011 NTT, Inc. All Rights Reserved. 8

9 ケース 1. 補足自動 VACUUM のキャンセル deadlock_timeout REINDEX( 待機 ) バッチ開始 REINDEX 時間シグナル起動 autovacuum worker キャンセル起動 autovacuum_naptime REINDEXはロック取得待ち状態となり一定時間後にデッドロック判定を行うその中でロック待ち相手が自動 VACUUMである場合にのみworkerにシグナルを送る workerはシグナルを受け取るとキャンセルされるが一定時間後にlauncher による再検査があり必要に応じて再度 VACUUMが実施される Copyright(c)2011 NTT, Inc. All Rights Reserved. 9

10 ケース 2. VACUUM が終わらない事象 VACUUM を実施したが ( もしくは自動 VACUUM が実施されたが ) 何時間たっても終わらない何が原因か? 原因 / 仕様対策 VACUUM は一時的にページへの排他ロックを取得するこれを阻害する要因があると VACUUM はロック取得待ちのままとなってしまうカーソルを利用しているロングトランザクションの閉じ忘れ防止該当ページを参照している backend がいる場合 (pin がたっている場合 ) VACUUM がとまる例 ( 行ロック取得待ち ) セッション 1: BEGIN; DELETE FROM tab1 WHERE id = 1; セッション 2: DELETE FROM tab1 WHERE id =1; ロック取得待ちセッション 3: VACUUM; VACUUM がとまる例 ( カーソルの使用 ) セッション 1: BEGIN; DECLARE cur CURSOR FOR SELECT * from tab1; FETCH 1 FROM cur; セッション 2: VACUUM; Copyright(c)2011 NTT, Inc. All Rights Reserved. 10

11 ケース 2. 補足 VACUUM( 自動 VACUUM) が停止している際の確認方法 pg_stat_activity/pg_locks では検出できない postgres=# select * from pg_stat_activity ; -[ RECORD 1 ] datid datname postgres procpid 8264 usesysid 10 usename postgres current_query vacuum; waiting f xact_start :33: query_start :33: backend_start :24: client_addr client_port -1 ps でのプロセスの状態コードを取得止まっている時 postgres ? Ss 19:24 0:02 postgres:postgres postgres[local] VACUUM 通常時 postgres ? Ds 19:48 0:02 postgres:postgres postgres[local] VACUUM postgres ? Rs 19:48 0:01 postgres:postgres postgres[local] VACUUM gdbでバックトレースを取得 LockBufferForCleanupでとまっていることを確認 Copyright(c)2011 NTT, Inc. All Rights Reserved. 11

12 ケース 3. VACUUM は成功するけれど事象原因 / 仕様対策不要領域の判定 VACUUM は定期的に行っているが ( もしくは自動 VACUUM を ON にしているが ) テーブルサイズが大きくなって困っている不要領域の判定は VACUUM 実施時点で動作している各トランザクションの可視性から判断されるロングトランザクションがいるといつまでたっても不要領域とみなさないロングトランザクション準備されたトランザクションが放置されていないかをチェック存在時にはそれを解消 A: 変更 / 削除 B: 変更 / 削除トランザクション X 開始 X: 未コミット状態 VACUUM ロングトランザクションの解消 postgres=# select pg_terminate_backend(9588); pg_terminate_backend t X のために B を不要領域とみなせない Copyright(c)2011 NTT, Inc. All Rights Reserved. 12

13 参考ケース 4. VACUUM が遅い事象原因 / 仕様対策事象原因 / 仕様対策ある日実施した VACUUM だけ非常に時間がかかったまた大量の WAL も吐かれているようだ何の問題があるのだろうか? XID 周回防止のため一定期間変更されていない行の可視化処理 (FREEZE 処理 ) を行うため log_min_freeze_age を大きくして明示的に VACUUM FREEZE できるためのメンテナンス時間を設けその際に実施する手動で VACUUM したところ自動 VACUUM の何倍も時間がかかった何が起きているのか? 自動 VACUUM では対象に付随した TOAST テーブルに対しては VACUUM を実施しない一方手動の VACUUM では TOAST テーブルインデックスも対象となる TOAST テーブルへの VACUUM は適切な時に自動 VACUUM が実施される事象原因 / 仕様対策バッチで大量レコード削除後に VACUUM を実施しているが毎回とても遅いように感じる VACUUM はテーブル末尾に空のページがあるとファイルサイズを縮小するその際に行われるページの切詰に伴う逆方向スキャンが遅い大量レコード削除などが必要な場合パーティショニング+TRUNCATE で行うことを検討する Copyright(c)2011 NTT, Inc. All Rights Reserved. 13

15 バックアップをめぐる問題の現状と分析バックアップリストアに関する問い合わせをカテゴライズした本講演では比較的問合せが多いものを紹介するケース1 ケース2 ケース3 ケース4 問い合せ内容エラーメッセージが出力オンラインバックアップから PostgreSQLを起動できないアーカイブ領域のディスク容量が圧迫どうすればよい? オンラインバックアップ取得の手順を教えてほしい原因関連するPostgreSQLの仕様リストアの仕様必要なアーカイブログの不足アーカイブログ削除の未実施外部ツールの利用 Copyright(c)2011 NTT, Inc. All Rights Reserved. 15

16 オンラインバックアップ基礎 1/3 オンラインバックアップ / リストアの基礎 WAL( トランザクションログ ) によるクラッシュリカバリを応用最新の状態だけでなく任意の時点にリストアできる設定項目オンライン中に取得した PGDATA( ベースバックアップ ) とアーカイブログにより実現します WALは定期的に再利用されるので別途アーカイブログとして保存しておく最低限必要な設定は以下の項目となる wal_level PostgreSQL 9.0 以降導入された項目 archive か hot_standby に設定する archive_mode on に設定する archive_command アーカイブ領域への保存方法を記述する項目 cpコマンドでok リストアの設定は recovery.confに設定する restore_command archive_command に対応するコマンドを記述する Copyright(c)2011 NTT, Inc. All Rights Reserved. 16

17 オンラインバックアップ基礎 2/3 オンラインバックアップ取得の流れ 1 pg_start_backup(now()); 3pg_stop_backup(); 4 故障発生! 2 rsync -r $PGDATA /backup/ 時間 A:DB クラスタのバックアップ B:1~3 の間に発生したアーカイブログ C:3~4 の間に発生したアーカイブログ +WAL この時点で取得されたデータ (A) は一貫性のないデータ A+B により 3 の時点における一貫性のあるデータとなる A+B+C により 4 の直前までの一貫性のあるデータとなる Copyright(c)2011 NTT, Inc. All Rights Reserved. 17

19 ケース 1. リストア中にエラーメッセージ事象原因 / 仕様対策処理の流れリストア時に以下のようなエラーメッセージが出力されるリストアに失敗してるのか? No such file or directory アーカイブログからのリストア時コピーに失敗するまでファイルをコピーしようとするまた本当に存在しないことを確かめるためわざと失敗したりしている無視しても問題ないリカバリ / リストア 1. pg_controlを読んで開始位置を決める 2. 開始位置を含むファイルをコピー 3. 成功しつづける限りファイルをコピー 4. 失敗したらそこでコピーをやめる 5. 履歴ファイルを更新するため本当に現在の履歴が正しいかファイルがないことで確認 6. ないことを確認したらひとつ前の履歴ファイルをコピー & 追記更新して終了 Base backup Base backup arc 領域 arc Copyright(c)2011 NTT, Inc. All Rights Reserved. 19

20 ケース 1. リストア中にエラーメッセージメッセージ例 LOG: starting archive recovery LOG: restored log file " " from archive LOG: redo starts at 0/ LOG: consistent recovery state reached at 0/ cp: cannot stat `/tmp/arc902/ ': No such file or directory LOG: could not open file "pg_xlog/ (log file 0, segment 4): No such file or directory 1 LOG: redo done at 0/30000A0 LOG: archive recovery complete LOG: autovacuum launcher started LOG: database system is ready to accept connections 1: ファイルをアーカイブ領域をコピーしようとして NG さらに pg_xlog を探してみて見つからなかったらリカバリ処理を完了する Copyright(c)2011 NTT, Inc. All Rights Reserved. 20

21 ケース 2. PostgreSQL が起動しない事象原因 / 仕様オンラインバックアップで取得したバックアップを用いて PostgreSQLを最新の状態にしようとしたが起動できなかった PANIC: could not locate required checkpoint record オンラインバックアップから最新の状態に復旧するにはベースバックアップとアーカイブログと停止直前のWALが必要であるアーカイブログとWALが歯抜けの状態になってはいけない 8.3 以前では pg_stop_backup() はアーカイブされるのを待たないため別途アーカイブされたことを確認する必要がある 8.4 以降では確実にアーカイブされるので対策は不要 PostgreSQL8.3のpg_stop_backup() の流れオンラインバックアップ対策 1. WALのスイッチを要求 2. backup_labelを読んで開始位置取得 3. 履歴ファイルに開始位置や終了位置時刻等を書き出す上記 1の処理では WALの切り替え要求のみ実施しているため実際にアーカイブされるのはもっと後になる pg_stop_backupがアーカイブより先に返却してしまうと必要なファイルが不足する PGDATA Base backup WAL arc 領域 Copyright(c)2011 NTT, Inc. All Rights Reserved. 21 arc

22 ケース 2. PostgreSQL が起動しない必要なWALがアーカイブされたことを確認することスクリプトファイル等でバックアップを実施しているときは注意が必要 pg_stop_backupの直後にアーカイブ領域をコピーしてバックアップセットとしていないか確認すること 8.3 以前の場合には以下のような確認を行う必要がある pg_xlogfile_name() 関数を用いてアーカイブ領域をチェック $ psql postgres -c "select pg_xlogfile_name(pg_stop_backup());" pg_xlogfile_name のかを把握する (1 row) $ psql postgres -c "select pg_xlogfile_name(pg_stop_backup());" pg_xlogfile_name (1 row) $ ls /tmp/arc902/ * /tmp/arc902/ $ ls /tmp/arc902/ *.backup /tmp/arc902/ backup pg_xlogfile_nameを利用しどののファイルがアーカイブされればファイルがアーカイブされればよいよいのかを把握します OS のコマンドでアーカイブ領域に対象のファイルおよびバックアップ履歴ファイル (xxx.backup) が存在することを確認する Copyright(c)2011 NTT, Inc. All Rights Reserved. 22

23 ケース 3. アーカイブ領域圧迫事象原因 / 仕様対策アーカイブ領域のディスク使用量がどんどん増加しているどのファイルを削除すればよいか? PostgreSQL 本体にはアーカイブログを削除する機能はないどのファイルが不要かを確実に見分けて定期的に不要なファイルを削除する不要なファイルの確認方法はいくつかあるので要件に合わせて選択するアーカイブ領域がいっぱいになってしまうと PostgreSQLはアーカイブに失敗しつづけるこのときPostgreSQLは pg_xlog 配下のWALを消さずに蓄えておいてくれるただしそのまま放置しつづけると次第に pg_xlogの領域もいっぱいになり PostgreSQLは利用できなくなる PANIC: could not write to file xxxx : No Space left on device 早めの対策を! PGDATA Base backup WAL オンラインバックアップ arc 領域 arc Copyright(c)2011 NTT, Inc. All Rights Reserved. 23

24 ケース 3. アーカイブ領域圧迫必要なファイルの確認手順ベースバックアップ配下にあるbackup_labelを確認し START WAL LOCATION: より古いものを削除する cat <base_backup>/backup_label START WAL LOCATION: xx/xxx (file xxxxxxxxxxxxxxxxxxxxx) 同様の情報はアーカイブ領域にあるバックアップ履歴ファイル (xxxx.backup) にも記載されるベースバックアップと対応するバックアップ履歴ファイルを確認し古いものを削除するいずれの場合も必要なファイルを消さないよう細心の注意を払って削除する対象のファイルは必要なファイルなのでそれより前のファイルを削除すること Copyright(c)2011 NTT, Inc. All Rights Reserved. 24

25 ケース 4. 具体的な手順事象原因 / 仕様対策オンラインバックアップ / リストアの手順が煩雑で分かりにくい具体的な手順を教えてほしいクラッシュリカバリ /WAL の機構を応用してバックアップ / リストアを実現しているので全体的な手順は煩雑注意点をきっちりおさえてスクリプト等を作成する公開されているツールを利用するのもよいリカバリ / リストアオンラインバックアップ Base backup arc 領域 arc PGDATA WAL arc 領域 arc Base backup 再構築 Base backup Copyright(c)2011 NTT, Inc. All Rights Reserved. 25

26 ケース 4. 具体的な手順 pg_rman オンラインバックアップ / リストアの補助ツール NTT OSSセンタで開発 GoogleCodeにて公開中 pg_dumpのようにいくつかのコマンドだけでオンラインバックアップ / リストアができるようになる再構築時にテーブルスペースも自動的に再現可能実行例 : $ pg_ctl start $ pg_rman backup --backup-mode=full --with-serverlog $ pg_rman validate バックアップの実行と取得したファイルの検証 $ pg_ctl stop -m immediate $ pg_rman restore リストアも1コマンド $ pg_ctl start Copyright(c)2011 NTT, Inc. All Rights Reserved. 26

27 補足. 周辺ツールのご紹介 pg_archivecleanup ケース 3 の対策として有効なツール 9.0 以降 contrib モジュールに付属スタンドアローンユーティリティとして機能させることが可能また recovery.conf の archive_cleanup_command に設定することでスタンバイ側で定期的にアーカイブ領域のクリーンアップが可能この場合はアーカイブ領域はレプリケーションのために利用されるだけで長期的な保管は意図してません実行例 : $ pg_archivecleanup d <arc_dir> xxxx.yyyy.backup バックアップ履歴ファイル (xxxx.yyyy.backup) を指定すると <arc_dir> ディレクトリからそのバックアップ履歴のバックアップをリストアするのに必要なアーカイブログより前のファイルを全て削除する Copyright(c)2011 NTT, Inc. All Rights Reserved. 27

29 チューニングをめぐる問題の現状と分析これまでの問い合わせをカテゴライズした本講演では比較的問合せが多いものを紹介する問い合せ内容性能が出ないバッファの効率的な利用についてバッチ処理について原因関連する PostgreSQL の仕様推奨のチューニングパラメータを教えてください一回目のアクセスが非常に遅い 2 回目以降は早いが対処策はないか? バッチ処理に時間がかかる良い対策はないか? Copyright(c)2011 NTT, Inc. All Rights Reserved. 29

30 チューニングの基本 shared_buffers 搭載メモリの10%~20% OS のページキャッシュと重複を少なくするため checkpoint_segments 16~64 checkpoint の回数を少なくすることで高速化 wal_buffers 4MB 以上トランザクション中のディスク書き込みを減らす effective_cache_size 搭載メモリの 50% 程度適切な index scan が多くなる random_page_costも2~3に設定すると更に良い work_mem sortが多い場合は多めに設定する( 要チューニング ) EXPLAIN ANALYZE で外部ソートが行われていないかをチェック暗記しよう! Copyright(c)2011 NTT, Inc. All Rights Reserved. 30

31 チューニングの基本 - バッファの仕組み - OS のバッファと PostgreSQL のバッファ既にバッファにあるデータはバッファから読み込まれるアクセスの無い古いバッファは置換される kernel page cache (OS) 共有バッファ (PostgreSQL) DB バッファの廃棄バッファに無いデータはディスクから読み込まれる I/O の速度メモリ >> ディスク DB から読み込んだデータは OS のキャッシュ領域を通じて PostgreSQL のバッファにも蓄積される ( バッファが重複する ) Copyright(c)2011 NTT, Inc. All Rights Reserved. 31

32 ケース 1. 性能が出ない事象原因 / 仕様対策性能がでない推奨するチューニングパラメータや高速化手法を教えて欲しい初期のパラメータから変更していない SQLに問題あり etc 先ほど紹介したチューニングパラメータを適用さらに以下の内容を確認することで性能改善を図る適切に index が張られているか? EXPLAIN ANALYZE / EXPLAIN で実行計画を確認不要なindexが張られていないか確認 HOT の活用 index 対象のカラムの更新はできるだけ避けるテーブル再編成を行う (CLUSTER, VACUUM FULL) DB 運用期間が長い場合は要チェック Copyright(c)2011 NTT, Inc. All Rights Reserved. 32

33 ケース 1. その他の主な対策 Prepared Statement を利用する SQL の構文解析結果や実行プランをキャッシュする機能典型的な OLTP や Web-DB では CPU 消費のうち25~50% がこのために消費されていた例も JDBCやlibpqなどのクライアントIFを用いる場合はそちらの機能から利用する注意点パーティショニングとの併用に難あり正確にはパーティションキーが変数化された場合に constraint_exclusionが働かなくなります返却される結果自体に矛盾はないが候補テーブルの絞込みが遅れるためかえって性能が劣化する場合あり 8.2 以前での利用について 8.3 以降の Prepared Statement は ANALYZE,VACUUM,DDL の変更の度に実行プランを再作成するが 8.2 以前では最初に生成した実行プランを使い続けるため悪影響を及ぼす可能性あり Copyright(c)2011 NTT, Inc. All Rights Reserved. 33

34 ケース 2.1 回目の SQL 処理だけ非常に遅い事象原因 / 仕様対策 2 回目の SQL 処理は 1 秒以内だが 1 回目の SQL 処理が 30 秒と非常に遅いサービス開始後に特に問題が出ている DB が非常に大きかったことまた夜間のバックアップ処理と PG の再起動によりバッファがリフレッシュされていた再起動後に頻繁に参照されるテーブルとインデックスをバッファに載せる特定のテーブルのみを事前にバッファに載せる SELECT * FROM テーブル名 ; OS の ( 注 ) Seq Scan の場合はインデックスはバッファに載りませんインデックスのみをバッファに載せる PostgreSQL の共有バッファには全件は載りません特定のテーブルデータで共有バッファが占有されるのを防ぐため PostgreSQL がそれを防止します contribのpgstattupleを使う例 )SELECT * FROM pgstatindex( インデックス名 ) インデックスサイズ大で搭載メモリが少ない場合に特に有効 Copyright(c)2011 NTT, Inc. All Rights Reserved. 34

35 ケース 3: バッチ処理を高速化したい事象原因 / 仕様対策夜間に現用系にてバッチ処理を実行しているが処理が朝まで続きそうこのままではサービスへの影響がでそうだ良い対策はないか? サービス開始後に予想以上のデータ量増加となりバッチ処理の処理量が多くなったケース1で紹介した内容の他にも 9.0で追加のホットスタンバイ (HS) 機能を利用する HA 構成レプリケーションレプリケーションバッチ処理 Master Slave(HS) 1 系 Master Slave(HS) レプリケーションバッチ処理 Slave(HS) 2 系 HA 構成の場合はマルチスレーブ構成にすると更に安全です Copyright(c)2011 NTT, Inc. All Rights Reserved. 35

スライド 1

スライド 1 Zabbix で PostgreSQL の監視を行おう ~pg_monz のご紹介 ~ SRA OSS,Inc. 日本支社盛宣陽 Copyright 2014 SRA OSS,Inc.Japan All rights reserved. 1 PostgreSQL の課題 DB としての基本機能性能は商用 DB と比べても引けをとらない運用面には課題ありどのようにして運用するのか? 効果的な監視方法は?