Help us understand the problem. What is going on with this article?

pg_stats_reporter読影会をやってみた

More than 1 year has passed since last update.

pg_stats_reporter読影会をやってみた

by masudakz
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が不足している
masudakz
生SQL職人の朝は早い. 「余分なカンマ1つ、空白1つの不足でプロセスが死ぬんだ」職人の目が光る. 「JPAがよさそう?駄目だよ,自由がないから」執拗に手書きに拘る. 「実行計画には頼りませんよ」レガシーな処理系が消えるには約10年は掛かるという. 「これからはNoSQL?俺は認めんね」
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした