LoginSignup
1

More than 5 years have passed since last update.

pg_stats_reporter読影会をやってみた

Last updated at Posted at 2016-09-10
1 / 21

pg_stats_reporter読影会をやってみた

2016-09-10 第8回PostgreSQLアンカンファレンス

自己紹介

images.jpeg


PostgreSQL歴

  • 2000年代初めのエンタープライズ製品での利用から接触
  • 実装・運用経験は2013年から :baby_chick: :baby_symbol:
    • PostgresSQL 9.2〜
    • CentOS on AWS

2015 若手に何かやらせよう

  • 創立37年、平均年齢39.5歳のIT会社
    • ヤバい。なんかしよう
  • 若手だけで全社課題毎に分科会 "Tech Factory"
  • 情報共有分科会がQiitaTeam試験導入(2015/10)

PostgreSQLアンチパターン投稿

  • レビューで潰したアンチパターン
  • 運用環境で発覚したアンチパターン
  • 週1本ペース
  • Fav.稼ぎまくり
    • 役員受けがとくに良かった
    • QiitaTeam 正式運用へ

データベース大臣

  • 2016/1/20 データベース大臣に指名
  • 大臣ミッション
    • 事業部越えて直接貢献(設計品質・効率化提案)
    • スキルアップ施策(OJT、ワークショップ、資格、書籍)
    • 情報共有、技術動向把握、技術戦略

SQLアンチパターン社内読書会


SQLアンチパターン社内読書会

  • 第1回 2016-06-02
  • 第2回
  • 全5回で完走 2016-08-05

DBServer読影会

  • お医者さんがCTやMRIの画像診断力を上げるために、症例を持ちよってみんなで画像をみるやつを真似しよう
    zenki_02w.jpg ©富山大学
  • pg_stats_reporter, munin の画面を見ながら各プロダクトのDB担当が問題発見の仕方を発表/議論

読影会のいいところ

  • 発表者の事前準備不要
    • ふだん監視している画面をさらすだけ
    • 問題発生時のsnapshotがあるとよい
  • 技能向上の即効性
    • そこを見るのか
    • これヤバかった?

pg_stats_reporter

きょうはスクショで。一部伏せ字、関係ないところは数値を捏造しているのでご安心を。
スクリーンショット


更新リリースで障害発生

応答時間 5sec 〜 17sec もかかるAPIが散発

  • Long Transactions
    • SQL特定
  • Heavily Accessed Tables
    • seq_tup_read でインデックスの利いてないTABLE
  • SQLチューニングへ

改善結果

Munin


弊社読影会の様子1

残念ながらpg_stats_reporterは持ち出せない

  • 開いたときのデフォールトのレポート期間が使いにくい
    • 深夜の分析・保存用pg_dump が入っていて、負荷を跳ね上げてるので、実需ピークがわからない
    • Create New Reportでスコープを限定する
    • スマゼミだと前日12:00-24:00とか

弊社読影会の様子2

  • え、なにこれ? 5/9 に2本の負荷スパイクがあるじゃないの
  • スパイクのところだけに snap_begin, snap_end を狭めて犯人捜し
    • "Long Transactions" で犯人みつかった
    • client address から事務局ツールの中に犯人
    • "Heavily Accessed Tables" も某TABLEの seq_tup_read がダントツ。58万行の seq.scan を10万回。
    • INDEX未設置だったので、追加する

弊社読影会の様子3

  • "Heavily Updated Tables" に database:postgres, schema:statsrepo のtableが上位に複数出てくる。pg_stats_report はそれなりの記録負荷がかかるもの。記録を欲張ると性能破綻するという本末転倒がありうる。
  • "Heavily Accessed Tables" で一番注目しているのは"seq_tup_read":シーケンシャルスキャンによって読みとられた行数
    • tup_per_seq が大きくてもアドホックなSQLによる単発なら性能問題にはならない。セキュリティ案件にはなるかも。

弊社読影会の様子4

  • "Statements"
    • 最初に見るのは "total time"降順
    • 次に time/call 降順でスロークエリ。今の某サービスは top でも 20msec。

弊社読影会の様子5

  • "Statements"
  • 認可のための契約状態確認のSQL数がダントツで、total time でも上位に出てきた。認可に今のままのSQLを使うのはそろそろ限界。KVSにするか?

弊社読影会の様子6

  • 1週間のレンジで見ると、やはり 0:00, 4:00 のpg_dump のスパイクがじゃま
    • 実需の長期傾向がスパイクの影に隠れて読めなくなってしまう
  • 5/16 だけスパイクが3本?
    • 09:05 ですね。誰か何かやった?
    • メンテ前の手順としてのpg_dumpでした

弊社読影会の様子7

  • Heavily Accessed Tables
    • Top 2 が user_info と member の TABLE
    • メンテ・分析専用カラムだったはずの update_at をビジネスロジックがSQLに取り込んでしまった。INDEX追加するよりは、ロジック修正する方向。
    • "Statetemts"の call の回数と seq_scan 回数がほぼ一致しているので、要修正のSQLはこの2つ

弊社読影会の様子8

  • レンジを 5/17 14:00-16:00 にすると別の現象が見える
    • pg_dump以外の負荷の山ができているところ
    • Disk Read のパイチャートが job TABLE 99%
    • seq_tup_read も user_info/memberを上回る job の圧勝
    • ”Statements” の Top 2 も job の SELECT
    • たぶん job TABLE に何かINDEXが不足している

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1