PostgreSQLプロモーション

Size: px

Start display at page:

Download "PostgreSQLプロモーション"

えみしもかさ
5 years ago
Views:

1 PgDay 2012 Japan 実践!PostgreSQL 運用 2012 NTT Software Innovation Center NTT OSS センタ坂本昌彦

2 アジェンダ目標商用環境での運用ノウハウを持ち帰る - 安定運用 ( 正しい設計 ) のトピックを中心に - 自身のシステムの運用で実践する対象初級者, 中級者運用する人, 設計する人トピック Part1: 接続数と安定運用 Part2: メモリ設定と安定運用 Part3: ディスク設定と安定運用免責トピックを詳細に掘り下げているため運用全体を網羅的に触れていない -1-

3 Part 1 接続数と安定運用

4 1-1. 接続数の考え方 (1/5) max_connections と superuser_reserved_connections の仕様をはっきりさせる一般ユーザ接続挙動を確認総接続数 : 最大 100 接続まで一般ユーザ : 最大 97 接続まで管理者 : 最大 100 接続まで管理者 : 最低 3 接続は確実に接続可能未割当 (backend 未起動 ) 管理者接続 superuser_reserved_ connections(default:3) max_connections(default:100) 運用時チェック AP は一般ユーザで接続している管理者の最大同時接続数が明確だ一般ユーザの最大同時利用数が明確だ superuser_r.. に余裕がある max_connections に余裕がある -3-

5 1-2. 接続数の考え方 (2/5) システムの挙動を把握して PG を使用するプロセスを完全に列挙する APサーバからの接続バッチサーバからの接続帳票サーバからの接続レプリケーション接続運用時チェック下記の接続数を考慮に入れた AP からの接続数バッチ / 帳票 / ジョブ管理サーバからの接続数 cron で呼び出されるジョブ DBLINK による接続レプリケーションによる接続 HA ソフトの死活監視 [ 管理者 ] 情報取得用接続 [ 管理者 ] 監視 M/W からの接続 [ 管理者 ] 緊急メンテ用接続 [ 管理者 ] -4-

6 1-3. 接続数の考え方 (3/5) 異常時における再接続に関連する挙動をしっかり把握する再接続参考 : TCP キープアライブ挙動を確認 N/W 断を PostgreSQL 側で気づきにくい気づくまで backend が残存する運用時チェック AP 再接続に耐えうる接続数の余裕がある PG 側で早期に検知する設定をしている 1.tcp keepalive packet 2. ack 1.tcp keepalive packet 2. retry 3. retry 4. die(backend) 関連パラメータ tcp_keepalives_idle tcp_keepalives_interval tcp_keepalives_count -5-

7 1-4. 接続数の考え方 (4/5) 接続おおすぎ常識的な同時接続数を知って現行設定を見返す挙動を確認多すぎるとスループットが落ちる少なすぎるとレスポンスが安定しない運用時チェック概ね以下の目安を守っている同時接続数 ~ 数百同時実行数 ~ 数十例 : CPU ネックのとき CPU 32Core 同時実行数 ~ CPU コア数 α(2~5) くらいが目安測定条件 : pgbench S s 10 T

8 1-5. 接続数の考え方 (5/5) 同時接続数を制御できないときはそのリスクをしっかり把握する運用時チェック接続数超過を想定し AP 側でリトライ処理を実装した接続エラーが生じうる負荷量をあらかじめ見積もったチェックポイント等の平滑化設計をした ( 万一の確率で ) 確保しているはずの管理者接続が一時的に確保できなくなりうる可能性を頭の片隅に入れた ( 後述 ) -7-

9 1-6. 接続数の一つの理想例一見して当たり前の設定でも多くの検討項目があったことを知る MaxClients 500 maxthreads 1000 maxactive max_connections 310 (300+10) PostgreSQL 設定項目設定値 max_connections 310 ( 接続 ) superuser_reserved_connections 10 ( 接続 ) tcp_keepalives_idle 60 ( 秒 ) tcp_keepalives_interval 5( 秒 ) tcp_keepalives_count 5( 秒 ) -8-

10 1-7. 接続数設計にまつわる失敗例穴があればすべからく落ちる事象 1 サービス停止 ( フェイルオーバ ) 詳細 AP からの接続をすべて postgres ユーザで実施特に接続数制限をかけていないため負荷集中時に max_connections を超過このタイミングで HA ミドルの死活監視がエラーとなり強制断された 2 性能劣化 max_connectionsをギリギリに取っていたために N/W 瞬断にともなうTomcatのコネクションプーリング再接続のうち一部が失敗し部分的な性能劣化を引き起こした 3 監視異常 backend 数がmax_connections 以下であることを監視していたが backend 数の大幅な超過を検知した認証前であってもbackendは起動可能なためである原理的に無制限にプロセスが立ち上がる可能性がある -9-

11 1-7. 落穂拾い Q. ALTER DATABASE CONNECTION LIMIT は? A. 必要に応じて設計が煩雑になりまた設定箇所がばらけるため設定時は文書化が必要 Q. 管理者用接続が確保できないことがある? A. 非常にごくまれに ( 一部の ) 最大接続数超過の判断が認証よりも前に行われるため最大接続数を大幅に超過してしまっている状況では管理者でも接続を拒否される可能性がある典型的な接続数超過状況接続数超超過状況 general resource OK auth ok connection NG admin resource NG admin resource OK auth ok connection OK 例 : PGPROC に自身を登録できない ( 認証中のものが max_connections を含めて多数いる ) 例 : 2*(max_connections) 以上の backend が存在している backend 数が多い ) -10-

12 Part 2 メモリ設定と安定運用

13 2-1. メモリ設定の考え方 (1/7) PG が主に使う 2 種のメモリ領域と密接に関連するファイルキャッシュを理解する shared_buffers 最大で (work_mem+α) N α: ~1MB N: max_connections File buffer/cache (Linux) 挙動を確認 shared_buffers は起動時にまとめて確保 backend プロセスは接続時に動的に生成 work_mem は必要に応じて動的に確保 backend プロセス自体のメモリも存在運用時チェックスワップが起きないことを確かめた (RAM 量 )>(PG メモリ最大使用量 )+(OS メモリ使用量 ) 繁忙時に si/so がない. free コマンドで buffer/cache 残 free コマンドで確認可能 RAM -12-

14 2-2. メモリ設定の考え方 (2/7) 搭載物理メモリ 24GB だったこれをどう割り当てるか? それが問題だ shared_buffers = 20GB File cache ~ 2GB Backends ~ 1GB A OS and misc~ 1GB shared_buffers = 4GB File cache ~ 16GB Backends ~ 1GB B OS and misc~ 1GB ( 総 DB サイズ ) ~ 20GB: Type A を選択 ( 総 DB サイズ ) ~ 数百 GB: Type B を選択 -13-

15 2-3. メモリ設定の考え方 (3/7) 一長一短決定打はないものの目安はある ( 前ページ参照 ) A B メモリ効率 ( 重複 ) 〇〇スループット?( やや低下 ) 〇?( 高 ) 応答時間のばらつき〇 ( 安定 )?( ばらつく ) 設計の柔軟度〇 ( 柔軟 )?( きめうち ) -14-

16 2-4. メモリ設定の考え方 (4/7) PG に適切なメモリのヒントを与える File buffer/cache (Linux) 運用時チェック適切な effective_cache_size を与えている DB サーバに同居している他の M/W の使用メモリを考慮した実際の値 (cache/buffers) と大きな乖離がないことを確認した H/W 特性を生かした設定をしている ( オプション ) SSD ドライブなので random_page_cost を小さくした -15-

17 2-5. メモリ設定の考え方 (5/7) オンライン系 TX work_mem=1mb work_mem を柔軟に運用すると設計運用に幅がでるバッチ系 TX よい例 work_mem=64mb BEGIN; SET LOCAL work_mem to 64MB ; -- SQLs that needs memory; COMMIT; 悪い例 : 無駄が多すぎ悪い例?: 改善の余地あり work_mem = 64MB work_mem = 1MB work_mem は必要に応じて確保されるので全接続が 64MB 使い切ることはないかもしれないがメモリオーバーコミットの設定を容認できないため (2-1 参照 ) 設計が窮屈になる特定のクエリ ( 特にバッチ系トランザクション ) に関しては work_mem を大きくすることで性能向上できるはず ( 上記例 ) なのにそれができていない -16-

18 2-6. メモリ設定の考え方 (6/7) そもそも work_mem はどんな時に使われるのかを理解する work_mem が大きくなるとどうなるか? 1 ソートディスクを使わずにインメモリでソートできる 2 JOIN ハッシュがインメモリでできる 3 Bitmapスキャン Bitmap Indexがメモリに収まると lossyな BitmapScanにならなくなる 4 集約 HashAggregate が選ばれやすくなる 5 一時表ディスクアクセスを減らすことができる -17-

19 2-7. メモリ設定の考え方 (7/7) RSS 実際のところ総使用メモリを正確に測定するのはあきらめた方がよい別の backend 別の backend backend 起動 ps コマンドの RSS/VSZ はあまり参考にならないことが多い見るべきは vmstat/sar の si/so や cache/buffers -18- t

20 2-8. メモリ設定の一つの理想例 shared_buffers=8gb effective_cache_size=20gb RAM: 32GB work_mem=1mb max_connections=300 work_mem = 64MB ( 業務 A のみ動的に設定 ) DB: 300GB RAM: 32GB DB: 20GB shared_buffers=20gb effective_cache_size=10gb work_mem=1mb max_connections=

21 2-9. メモリ設定にまつわる失敗例運用を重ねるうちにいつのまにかはまることも 1 遅い! shared_buffers effective_cache_sizeはデフォルトで小さすぎるデフォルト設定だと本来の実力の半分以下のスループットしかでない 2 特定の SQL が遅くなったり早くなったりプランが安定しないその業務に必要な work_mem が十分でなく実行計画が安定しない 3 負荷集中時メモリ不足 work_mem を大きく設定しすぎており同時実行数を上げるとメモリを大量に消費するようになりスワップが発生した -20-

22 2-10. 落穂ひろい Q. maintainance_work_mem は? A. VACUUM などの運用系 SQL 文を発行するときはこのパラメータが使われるこの分も一応考慮する ( 一般的には前項までの設計をしていれば十分バッファが取れているはず ) 自動 VACUUM は worker がそれぞれメモリを食うので気を付ける Q. 共有メモリに確保されるのは shared_buffers だけ? A. そんなことはない各種ロック管理プロセス間通信や状態管理などのための領域がある容量は大したことがないけれど -21-

23 Part 3 ディスク設定と安定運用

24 3-1. ディスク設計の考え方 (1/7) DB サーバの I/O を理解する考慮するべき対象を明確化する PG/OS のキャッシュ層バックグラウンド書込 ( ランダム ) バッファ置換 / チェックポイント ( ランダム ) 表 / 索引データ読込 ( ランダム ) 表領域表 / 索引データ読込 ( フルバックアップ ) バックアップ WAL 書込 WAL 読込 ( レプリケーション / アーカイビング ) WAL 読込 XLOG 領域アーカイブログ書込アーカイブ読込 ( 差分バックアップ ) ARCH 領域 BKUP 領域サーバログ書込サーバログ領域 -23-

25 3-2. ディスク設計の考え方 (2/7) XLOG 領域には最も高いレベルの安全性が求められる運用時チェック物理的に安全性が高いことを確認した RAID(1+0) が組まれているホットスペアがついているバッテリーキャッシュがついているディスク故障 + リビルド実施をすぐに検知できる WAL 書込論理的に安全性が高いことを確認した別パーティションとして切り出されている ( システム休止時などのタイミングで )fsck を実施している wal_sync_method が fsync(fdatasync) である full_page_writes/synchronous_commit が設定されている WAL 読込 ( レプリケーション / アーカイビング ) XLOG 領域十分な容量があることを確認した一定時間アーカイブ失敗しても問題ない容量を有している十分な応答性能があることを確認した writeback 設定がなされている ( バッテリーキャッシュ必須!) -24-

26 3-3. ディスク設計の考え方 (3/7) ARCH 領域には高い独立性拡張性が求められる運用時チェック独立性が高いことを確認した NFSマウントされた領域である ( 最善 ) 書込み先を複数登録している ( 次善 ) 表領域とは別に切り出している ( 最低限 ) アーカイブログ書込容量に十分余裕があることを確認した必要に応じて容量を増加させられる H/W 構成である設計時に 1 日当たりの量を見積もるのは難しいことを理解した今後の 1 日当たりの量を見積もるのは難しいことを理解した ARCH 領域 -25-

27 3-4. ディスク設計の考え方 (4/7) ARCH 領域と XLOG 領域には特徴的な I/O 特性がある Q. ARCH 領域の await が高いボトルネックなのか? A. 違う await が高いのはアーカイブ対象を cp などでまとめてコピーするため Q. でもアーカイブやめたら性能があがったやっぱりボトルネックでは? A. アーカイブにともなって表データが載っている OS のバッファを飛ばしたから Q. XLOG 領域の書込量が ARCH 領域への書込量よりかなり多い何故? A. XLOG 領域へはこまごまと書き込みをするのでどうしても ARCH 領域よりも書き込み量が多くなる Q. XLOG 領域の読込量がほぼゼロだがどのようにアーカイブしている? A. OS のバッファに残っており実 I/O が発生しない -26-

28 3-5. ディスク設計の考え方 (5/7) 表領域にはランダム I/O 性能が求められる容量にも注意挙動を確認一時領域や統計情報格納領域を含む ( 分離可 ) 基本的にランダム I/O が支配的一般的にディスクネックになるならココバックグラウンド書込表 / 索引データ読込バッファ置換チェックポイント表領域表インデックス一時領域 ( ソート用一時テーブル ) 稼働統計情報格納運用時チェック H/W 運用要件にあった形になっているストレージスナップショットの上限を意識している fsckしても問題ない時間で終わる運用コストは高くない容量に十分余裕があるストレージ容量に余裕がある ( 後述 ) 容量を追加に耐えうる構造である ( 例 : パーティショニング ) -27-

29 3-6. ディスク設計の考え方 (6/7) 表領域における容量見積もりの勘所をつかむ一時領域運用時チェック容量設計時に以下を考慮した論理レコード物理レコードのオーバーヘッドインデックスサイズ不要領域分メンテナンスに必要な一時領域一時テーブル領域ソート等で利用する一時領域想定外のデータ増加にも耐えられるようなバッファインデックス不要領域メンテナンス用 -28-

30 3-7. ディスク設計の考え方 (7/7) その他システム運用上必要な容量見積もりの勘所をつかむ表領域移行用領域移行用データファイル (CSVファイル/ ダンプファイル ) 運用時チェック容量設計時に以下を考慮した BKUP 領域に N+1 世代分のフルバックアップ領域がある上記世代に対応するアーカイブログ領域がある移行データ配置用の領域があるリカバリデータ配置用の領域がある -29-

31 3-8. ディスク設計の一つの理想例正解を見つけるよりも妥当かどうかの判断が重要視される DB サイズ 200GB 終年度 DB サイズ 400GB 表領域論理ディスク 1 600GB XLOG 領域論理ディスク 2 100GB BKUP 領域 1 ARCH 領域 1 運用作業領域論理ディスク 3 900GB ARCH 領域 2 BKUP 領域 2 ディスクアレイ RAID 1+0 実効容量 1.6TB NAS 15,000rpm 320GB ホットスペア空きスロット -30-

32 3-9. ディスク設計にまつわる失敗例失敗例 1 XLOG 領域あふれてサービス停止詳細 NFS マウントが失敗してアーカイブが失敗し続けていたため 2 メンテナンスできないどうしよう空き容量がなくなってきたので VACUUM FULL しようとしたがそのための一時領域が取れない 3 めちゃくちゃ遅いストレージの設定として writethrough 設定がされていた Writeback 設定にする注 : linux の mount オプションで writeback 設定をしてはいけないこれはバッテリーキャッシュのついたストレージの設定の話 -31-

スライド 1

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