RDBのトラブルの現場を追え！ / rdb-Troubleshooting

そーだいなる DBRE Nightでの登壇資料です

https://connpass.com/event/138437/

# 紹介資料
- https://speakerdeck.com/soudai/shi-xing-ji-hua-falsehua
- https://www.youtube.com/channel/UCeenIljXnSwrwYEU-YBE2qA/feed
- https://speakerdeck.com/soudai/postgresql-architecture-and-performance-monitoring
- https://gihyo.jp/dev/feature/01/dex_postgresql/0002
- https://lets.postgresql.jp/documents/technical/query_analysis/1
- https://lets.postgresql.jp/documents/technical/statistics

Transcript

1. RDBのトラブルの現場を追え！
 
 そーだいなる DBRE Night
2. What is it?
 RDBMSの死はサービスの死

3. What is it?
 • DBが突然遅くなった…
 • コネクションが溢れてサービス停止…
 • INDEXを貼ろうとしたらエラー…
 現場の声

4. What is it?
 現場の 悲鳴 声のお届けします

5. What is it?
 大前提！！
 今日は全部PostgreSQLの話

6. あじぇんだ
 1. 自己紹介
 2. 初級編：スロークエリを追え！
 3. 中級編：エラーログを追え！
 4. 上級編：未知のエラーを追え！
 5. 番外編：何もしてないのにDBが壊れた
 6. まとめ

7. あじぇんだ
 1. 自己紹介
 2. 初級編：スロークエリを追え！
 3. 中級編：エラーログを追え！
 4. 上級編：未知のエラーを追え！
 5. 番外編：何もしてないのにDBが壊れた
 6. まとめ

8. 自己紹介
 曽根 壮大（34歳）
 株式会社 オミカレ 副社長 CTO
 
 そ　 ね　　 たけ とも
 • 日本PostgreSQLユーザ会 勉強会分科会 担当
 • 3人の子供がいます（長女、次女、長男）
 • 技術的にはWeb/LL言語/RDBMSが好きです
 • コミュニティが好き
9. 婚活といえばオミカレ
 https://party-calendar.net/
10. 本書きました

11. あじぇんだ
 1. 自己紹介
 2. 初級編：スロークエリを追え！
 3. 中級編：エラーログを追え！
 4. 上級編：未知のエラーを追え！
 5. 番外編：何もしてないのにDBが壊れた
 6. まとめ

12. 初級編：スロークエリを追え！
 サービスを殺すには刃物は要らぬ。
 スロークエリがあれば良い。

13. 初級編：スロークエリを追え！
 スロークエリの見つけ方

14. 初級編：スロークエリを追え！
 
 • スロークエリログ
 • 実行計画
 • モニタリング
 • 職人の勘
 スロークエリログの主な見つけ方

15. 初級編：スロークエリを追え！
 
 • スロークエリログ
 • 実行計画
 • モニタリング
 • 職人の勘
 スロークエリログの主な見つけ方

16. 初級編：スロークエリを追え！
 スロークエリログを使う

17. 初級編：スロークエリを追え！
 スロークエリログを使う
 ↓
 指定秒以上の実行したSQLをログに出力

18. 初級編：スロークエリを追え！
 しかしデフォルトは無効になってる

19. 初級編：スロークエリを追え！
 # MySQL
 slow_query_log=0(OFF) or 1(ON)
 slow_query_log_file=出力ファイル名
 long_query_time=許容できないレスポンス時間(秒)
 
 ＃PostgreSQL
 logging_collector=on or off
 log_min_duration_statement=許容できないレスポンス時間(ミリ秒)
 

20. 初級編：スロークエリを追え！
 スロークエリログの見方

21. 初級編：スロークエリを追え！
 生ログを見るのは辛い

22. 初級編：スロークエリを追え！
 生ログを見るのは辛い
 ↓
 ちょっとした工夫をする

23. 初級編：スロークエリを追え！
 Let's Postgres 「スロークエリの分析」
 
 https://lets.postgresql.jp/documents/technical/query_analysis/1 

24. 初級編：スロークエリを追え！
 ＃PostgreSQL
 logging_collector=on or off
 # CSVでログを出力する
 log_destination = 'csvlog'
 log_min_duration_statement=許容できないレスポンス時間(ミリ秒)
 

25. 初級編：スロークエリを追え！
 CSVにすると？

26. 初級編：スロークエリを追え！
 CSVにすると？
 ↓
 Excelでもプログラムでも集計が楽

27. 初級編：スロークエリを追え！
 もちろんテーブルにインポートも楽
 

28. 初級編：スロークエリを追え！
 もちろんテーブルにインポートも楽
 ↓
 SQLで検索出来る

29. 初級編：スロークエリを追え！
 CSVlogを使わない場合でも
 集計してくれるツールは結構ある

30. 初級編：スロークエリを追え！
 そもそも最初から集計してくれる機能がある
 

31. 初級編：スロークエリを追え！
 そもそも最初から集計してくれる機能がある
 ↓
 pg_stat_statements
 

32. 初級編：スロークエリを追え！
 DBaaSを使ってるからfileに吐けない時
 

33. 初級編：スロークエリを追え！
 DBaaSを使ってるからfileに吐けない時
 ↓
 こうやってテーブル(統計情報)の
 選択肢もちゃんとある

34. 初級編：スロークエリを追え！
 スロークエリを見つけたら？
 

35. 初級編：スロークエリを追え！
 スロークエリを見つけたら？
 ↓
 まずは実行計画を見よう

36. 初級編：スロークエリを追え！
 実行計画を見る
 ↓
 EXPLAINを使う

37. 初級編：スロークエリを追え！
 
 
 
 
 https://speakerdeck.com/soudai/shi-xing-ji-hua-falsehua 

38. 初級編：スロークエリを追え！
 
 • スロークエリログ
 • 実行計画
 • モニタリング
 • 職人の勘
 スロークエリログの主な見つけ方

39. 初級編：スロークエリを追え！
 実行計画を集める

40. 初級編：スロークエリを追え！
 実行計画を集める
 ↓
 auto_explain

41. 初級編：スロークエリを追え！
 session_preload_libraries = 'auto_explain'
 # 100ms以上かかっているクエリを自動でEXPLAINする
 auto_explain.log_min_duration = 許容できないレスポンス時間(ミリ秒)
 auto_explain.log_analyze = on
 auto_explain.log_nested_statements = on
 auto_explain.log_triggers = on
 

42. 初級編：スロークエリを追え！
 トリガーや外部キー制約経由の参照なども
 ログに吐ける

43. 初級編：スロークエリを追え！
 
 • スロークエリログ
 • 実行計画
 • モニタリング
 • 職人の勘
 スロークエリログの主な見つけ方

44. 初級編：スロークエリを追え！
 スロークエリを事前に察知する

45. 初級編：スロークエリを追え！
 スロークエリを事前に察知する
 ↓
 インフラから知る

46. 初級編：スロークエリを追え！
 
 • メモリが足りずtemp落ち
 • ロック待ち
 • 様々な理由で実行計画が変わる
 …etc
 突然SQLが遅くなるとき

47. 初級編：スロークエリを追え！
 
 • メモリが足りずtemp落ち
 • ロック待ち
 • 様々な理由で実行計画が変わる
 …etc
 突然SQLが遅くなるとき

48. 初級編：スロークエリを追え！
 
 https://gihyo.jp/dev/feature/01/dex_postgresql/0002 

49. 初級編：スロークエリを追え！
 

50. 初級編：スロークエリを追え！
 

51. 初級編：スロークエリを追え！
 
 • メモリが足りずtemp落ち
 • ロック待ち
 • 様々な理由で実行計画が変わる
 …etc
 突然SQLが遅くなるとき

52. 初級編：スロークエリを追え！
 スロークエリログには
 ロックの待ち時間は含まれない

53. 初級編：スロークエリを追え！
 スロークエリログには
 ロックの待ち時間は含まれない
 ↓
 pg_locks＋pg_stat_activity 

54. 初級編：スロークエリを追え！
 Let's Postgres「稼動統計情報を活用しよう」
 
 https://lets.postgresql.jp/documents/technical/statistics
 

55. 初級編：スロークエリを追え！
 =# SELECT l.locktype, c.relname, l.pid, l.mode,
 substring(a.current_query, 1, 6) AS query,
 (current_timestamp - xact_start)::interval(3) AS duration
 FROM pg_locks l LEFT OUTER JOIN pg_stat_activity a
 ON l.pid = a. procpid
 LEFT OUTER JOIN pg_class c ON l.relation = c.oid
 WHERE NOT l.granted ORDER BY l.pid;

56. 初級編：スロークエリを追え！
 locktype | relname | pid | mode | query | duration ---------------+----------+------+---------------+--------+-------------- tuple | tellers | 2700 | ExclusiveLock | UPDATE | 00:00:00.013 transactionid | | 2701 | ShareLock | INSERT | 00:00:00.004 transactionid | | 2702 | ShareLock | UPDATE | 00:00:00.014 tuple | tellers | 2703 | ExclusiveLock | UPDATE | 00:00:00.004 tuple | tellers | 2704 | ExclusiveLock | UPDATE | 00:00:00.009 tuple | branches | 2705 | ExclusiveLock | UPDATE | 00:00:00.001 transactionid | | 2706 | ShareLock | UPDATE | 00:00:00.001 transactionid | | 2707 | ShareLock | UPDATE | 00:00:00.017 transactionid | | 2708 | ShareLock | UPDATE | 00:00:00.007 (9 rows)
57. 初級編：スロークエリを追え！
 ツールを使って可視化する

58. 初級編：スロークエリを追え！
 
 • Mackerel + plugin
 • Zabbix + pg_monz
 • pg_statsinfo + pg_stats_reporter 
 …etc
 メジャーなモニタリングツール

59. 初級編：スロークエリを追え！
 
 
 
 
 
 https://speakerdeck.com/soudai/postgresql-architecture-and-performance-monitoring 

60. 初級編：スロークエリを追え！
 
 • スロークエリログ
 • 実行計画
 • モニタリング
 • 職人の勘
 スロークエリログの主な見つけ方

61. 初級編：スロークエリを追え！
 プロはsshで入る瞬間にわかる（ときがある）

62. 初級編：スロークエリを追え！
 topでもわかる（ときがある）

63. 初級編：スロークエリを追え！
 職人の勘→DBAの仕事
 職人の勘を仕組みにする→DBREの仕事

64. 初級編：スロークエリを追え！
 日本PostgreSQLユーザ会の動画を活用
 
 

65. 初級編：スロークエリを追え！
 
 
 
 https://www.youtube.com/channel/UCeenIljXnSwrwYEU-YBE2qA/feed

66. あじぇんだ
 1. 自己紹介
 2. 初級編：スロークエリを追え！
 3. 中級編：エラーログを追え！
 4. 上級編：未知のエラーを追え！
 5. 番外編：何もしてないのにDBが壊れた
 6. まとめ

67. 中級編：エラーログを追え！
 エラーログ
 「実を言うとDBはもうだめです。
 突然こんなこと言ってごめんね。
 でも本当です。」

68. 中級編：エラーログを追え！
 エラーログは答えを教えてくれてる
 

69. 中級編：エラーログを追え！

70. 中級編：エラーログを追え！
 
 # postgresql
 logging_collector=on # logの有効化
 # logの出力時のフォーマットの指定
 log_line_prefix='[%t]%u %d %p[%l] %h[%i]' 
 log_min_duration_statement=<許容できないレスポンス時間(ミリ秒)>
 # 出力したい深刻度レベル
 # 指定された深刻度レベルよりも重要なレベルが出力される
 # この場合、ERROR LOG FATAL PANIC
 log_min_error_statement=error 

71. 中級編：エラーログを追え！
 
 # mysql
 log_syslog=1
 log_syslog_include_pid=1
 log_error_verbosity=3
 slow_query_log = 1 # slow queryログの有効化
 slow_query_log_file=/usr/local/mysql/data/slow.log # ファイルパス
 long_query_time=<許容できないレスポンス時間(秒)>
 # インデックスが使用されていないクエリをログに出力
 log_queries_not_using_indexes = 1 

72. 中級編：エラーログを追え！
 まずはググる
 

73. 中級編：エラーログを追え！
 まずはググる
 ↓
 エラーログは英語の方が良い

74. 中級編：エラーログを追え！
 最初に公式ドキュメントを信じる
 

75. 中級編：エラーログを追え！
 最初に公式ドキュメントを信じる
 ↓
 スタックオーバーフローやQiitaで満足しない

76. 中級編：エラーログを追え！
 ？？？「エラーログで調べると
 自分のblogしか出てこないんですけど」
 

77. 中級編：エラーログを追え！
 ？？？「わかるぅ〜〜〜」
 (CV:そーだい)

78. 中級編：エラーログを追え！
 そうなったら上級者

79. あじぇんだ
 1. 自己紹介
 2. 初級編：スロークエリを追え！
 3. 中級編：エラーログを追え！
 4. 上級編：未知のエラーを追え！
 5. 番外編：何もしてないのにDBが壊れた
 6. まとめ

80. 上級者編：未知のエラーを追え！
 突然の謎のエラーログ
 

81. 上級者編：未知のエラーを追え！
 突然の謎のエラーログ
 ↓
 ググっても謎

82. 上級者編：未知のエラーを追え！
 日本語コミュニティのSlackを活用する
 

83. 上級者編：未知のエラーを追え！
 日本語コミュニティのSlackを活用する
 ↓
 mysql-casualとpostgresql-jp

84. 上級者編：未知のエラーを追え！
 Slackで有識者に聞く
 

85. 上級者編：未知のエラーを追え！
 Slackで有識者に聞く
 ↓
 バグだったりすることもある

86. 上級者編：未知のエラーを追え！
 それでもわからない

87. 上級者編：未知のエラーを追え！
 広大なインターネットにも
 無いものは無い

88. 上級者編：未知のエラーを追え！
 誰も知らないことは
 自分が言葉にするしか無い

89. 上級者編：未知のエラーを追え！
 振る舞いを知る

90. 上級者編：未知のエラーを追え！
 Source Code Reading
 http://higepon.hatenablog.com/entry/19700110/1233646390 
 https://lets.postgresql.jp/node/10 
 

91. 上級者編：未知のエラーを追え！
 まぁソースコード、言うほど読む必要ない

92. 上級者編：未知のエラーを追え！
 まぁソースコード、言うほど読む必要ない
 ↓
 試して挙動を理解する方が大事

93. 上級者編：未知のエラーを追え！

94. 上級者編：未知のエラーを追え！
 しっかりと動作を知ることができたら？
 

95. 上級者編：未知のエラーを追え！
 しっかりと動作を知ることができたら？
 ↓
 アウトプットしていこう

96. 上級者編：未知のエラーを追え！
 あなたが検索して出てこなかった
 そのエラーメッセージに誰かも困っています

97. あじぇんだ
 1. 自己紹介
 2. 初級編：スロークエリを追え！
 3. 中級編：エラーログを追え！
 4. 上級編：未知のエラーを追え！
 5. 番外編：何もしてないのにDBが壊れた
 6. まとめ

98. 1. 番外編：何もしてないのにDBが壊れた
 続きはBuildersconで！

99. あじぇんだ
 1. 自己紹介
 2. 初級編：スロークエリを追え！
 3. 中級編：エラーログを追え！
 4. 上級編：未知のエラーを追え！
 5. 番外編：何もしてないのにDBが壊れた
 6. まとめ

100. まとめ
 今日、学びはありましたか？

101. まとめ
 今日、学びはありましたか？
 ↓
 DBAの仕事のほんの一部です

102. まとめ
 でもそのDBAの仕事を
 みんなが出来るようにするのが
 DBREの仕事です

103. まとめ
 仕組みで課題を解決する

104. まとめ
 技術で課題を解決する

105. まとめ
 理想への一歩目を踏み出すのは
 自分自身

106. まとめ
 ご清聴ありがとうございました