pg_stats_reporter読影会をやってみた
2016-09-10 第8回PostgreSQLアンカンファレンス
自己紹介
PostgreSQL歴
- 2000年代初めのエンタープライズ製品での利用から接触
- 実装・運用経験は2013年から
- 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アンチパターン』社内読書会には喜んで遊びに行きますので、お気軽にお声がけください。これまで何社もお邪魔しましたが、社内でないとできない実システムを肴にしたアンチパターンの話が盛り上がります。
— Takuto Wada (@t_wada) 2016年5月20日
SQLアンチパターン社内読書会
DBServer読影会
- お医者さんがCTやMRIの画像診断力を上げるために、症例を持ちよってみんなで画像をみるやつを真似しよう
©富山大学 - pg_stats_reporter, munin の画面を見ながら各プロダクトのDB担当が問題発見の仕方を発表/議論
読影会のいいところ
- 発表者の事前準備不要
- ふだん監視している画面をさらすだけ
- 問題発生時のsnapshotがあるとよい
- 技能向上の即効性
- そこを見るのか
- これヤバかった?
pg_stats_reporter
きょうはスクショで。一部伏せ字、関係ないところは数値を捏造しているのでご安心を。
更新リリースで障害発生
応答時間 5sec 〜 17sec もかかるAPIが散発
- Long Transactions
- SQL特定
- Heavily Accessed Tables
- seq_tup_read でインデックスの利いてないTABLE
- SQLチューニングへ
改善結果
弊社読影会の様子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が不足している