pg_stats_reporter読影会をやってみた

# pg_stats_reporter読影会をやってみた

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

## 自己紹介
- 増田和弘 Twitter: @masudakz  
- 所属と担当：株式会社ジャストシステム　[スマイルゼミ](https://smile-zemi.jp/)

![images.jpeg](https://qiita-image-store.s3.amazonaws.com/0/47549/f70e31f4-2cac-d07d-76a0-847694603735.jpeg)

----

## PostgreSQL歴

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

---- 

## 2015 若手に何かやらせよう

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


----

## PostgreSQLアンチパターン投稿

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

----

## データベース大臣

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

----

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

<blockquote class="twitter-tweet" data-lang="ja"><p lang="ja" dir="ltr">『SQLアンチパターン』社内読書会には喜んで遊びに行きますので、お気軽にお声がけください。これまで何社もお邪魔しましたが、社内でないとできない実システムを肴にしたアンチパターンの話が盛り上がります。</p>&mdash; Takuto Wada (@t_wada) <a href="https://twitter.com/t_wada/status/733633113440419840?ref_src=twsrc%5Etfw">2016年5月20日</a></blockquote>


----

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

- 第1回 2016-06-02
 - 特別ゲスト:@t_wadaさん
- 第2回
 - 特別ゲスト:@meijikさん
- 全5回で完走 2016-08-05

----

## DBServer読影会

- お医者さんがCTやMRIの画像診断力を上げるために、症例を持ちよってみんなで画像をみるやつを真似しよう  
 ![zenki_02w.jpg](https://qiita-image-store.s3.amazonaws.com/0/47549/726e178a-aca7-cee2-b266-586a66c23498.jpeg "zenki_02w.jpg")
[©富山大学](http://www.med.u-toyama.ac.jp/medicin1/benkyokenkyu/images/zenki_02w.jpg)
- pg_stats_reporter, munin の画面を見ながら各プロダクトのDB担当が問題発見の仕方を発表/議論
 
----

## 読影会のいいところ

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

----

## pg_stats_reporter

きょうはスクショで。一部伏せ字、関係ないところは数値を捏造しているのでご安心を。
![スクリーンショット](https://qiita-image-store.s3.amazonaws.com/0/47549/3655e228-35cc-ca97-9277-b64bdc0ef8bf.png "スクリーンショット 2016-09-10 0.47.19.png")

----

## 更新リリースで障害発生

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

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

## 改善結果

![Munin](https://qiita-image-store.s3.amazonaws.com/0/47549/5bd81cfa-1576-ef56-3d46-56fb2d5c3c98.png "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が不足している