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
   

   View Slide

2. What is it?

   RDBMSの死はサービスの死

   

   View Slide

3. What is it?

   ● DBが突然遅くなった…

   ● コネクションが溢れてサービス停止…

   ● INDEXを貼ろうとしたらエラー…

   現場の声

   

   View Slide

4. What is it?

   現場の 悲鳴 声のお届けします

   

   View Slide

5. What is it?

   大前提！！

   今日は全部PostgreSQLの話

   

   View Slide

6. あじぇんだ

   1. 自己紹介

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

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

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

   5. 番外編：何もしてないのにDBが壊れた

   6. まとめ

   

   View Slide

7. あじぇんだ

   1. 自己紹介

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

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

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

   5. 番外編：何もしてないのにDBが壊れた

   6. まとめ

   

   View Slide

8. 自己紹介

   曽根 壮大（34歳）

   株式会社 オミカレ 副社長 CTO

   

   そ　 ね　　 たけ とも

   ● 日本PostgreSQLユーザ会 勉強会分科会 担当

   ● 3人の子供がいます（長女、次女、長男）

   ● 技術的にはWeb/LL言語/RDBMSが好きです

   ● コミュニティが好き
   

   View Slide

9. 婚活といえばオミカレ

   https://party-calendar.net/
   

   View Slide

10. 本書きました

    

    View Slide

11. あじぇんだ

    1. 自己紹介

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

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

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

    5. 番外編：何もしてないのにDBが壊れた

    6. まとめ

    

    View Slide

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

    サービスを殺すには刃物は要らぬ。

    スロークエリがあれば良い。

    

    View Slide

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

    スロークエリの見つけ方

    

    View Slide

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

    

    ● スロークエリログ

    ● 実行計画

    ● モニタリング

    ● 職人の勘

    スロークエリログの主な見つけ方

    

    View Slide

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

    

    ● スロークエリログ

    ● 実行計画

    ● モニタリング

    ● 職人の勘

    スロークエリログの主な見つけ方

    

    View Slide

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

    スロークエリログを使う

    

    View Slide

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

    スロークエリログを使う

    ↓

    指定秒以上の実行したSQLをログに出力

    

    View Slide

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

    しかしデフォルトは無効になってる

    

    View Slide

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=許容できないレスポンス時間(ミリ秒)

    

    

    View Slide

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

    スロークエリログの見方

    

    View Slide

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

    生ログを見るのは辛い

    

    View Slide

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

    生ログを見るのは辛い

    ↓

    ちょっとした工夫をする

    

    View Slide

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

    Let's Postgres 「スロークエリの分析」

    

    https://lets.postgresql.jp/documents/technical/query_analysis/1 

    

    View Slide

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

    ＃PostgreSQL

    logging_collector=on or off

    # CSVでログを出力する

    log_destination = 'csvlog'

    log_min_duration_statement=許容できないレスポンス時間(ミリ秒)

    

    

    View Slide

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

    CSVにすると？

    

    View Slide

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

    CSVにすると？

    ↓

    Excelでもプログラムでも集計が楽

    

    View Slide

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

    もちろんテーブルにインポートも楽

    

    

    View Slide

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

    もちろんテーブルにインポートも楽

    ↓

    SQLで検索出来る

    

    View Slide

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

    CSVlogを使わない場合でも

    集計してくれるツールは結構ある

    

    View Slide

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

    そもそも最初から集計してくれる機能がある

    

    

    View Slide

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

    そもそも最初から集計してくれる機能がある

    ↓

    pg_stat_statements

    

    

    View Slide

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

    DBaaSを使ってるからfileに吐けない時

    

    

    View Slide

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

    DBaaSを使ってるからfileに吐けない時

    ↓

    こうやってテーブル(統計情報)の

    選択肢もちゃんとある

    

    View Slide

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

    スロークエリを見つけたら？

    

    

    View Slide

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

    スロークエリを見つけたら？

    ↓

    まずは実行計画を見よう

    

    View Slide

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

    実行計画を見る

    ↓

    EXPLAINを使う

    

    View Slide

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

    

    

    

    

    https://speakerdeck.com/soudai/shi-xing-ji-hua-falsehua
    

    

    View Slide

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

    

    ● スロークエリログ

    ● 実行計画

    ● モニタリング

    ● 職人の勘

    スロークエリログの主な見つけ方

    

    View Slide

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

    実行計画を集める

    

    View Slide

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

    実行計画を集める

    ↓

    auto_explain

    

    View Slide

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

    

    

    View Slide

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

    トリガーや外部キー制約経由の参照なども

    ログに吐ける

    

    View Slide

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

    

    ● スロークエリログ

    ● 実行計画

    ● モニタリング

    ● 職人の勘

    スロークエリログの主な見つけ方

    

    View Slide

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

    スロークエリを事前に察知する

    

    View Slide

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

    スロークエリを事前に察知する

    ↓

    インフラから知る

    

    View Slide

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

    

    ● メモリが足りずtemp落ち

    ● ロック待ち

    ● 様々な理由で実行計画が変わる

    …etc

    突然SQLが遅くなるとき

    

    View Slide

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

    

    ● メモリが足りずtemp落ち

    ● ロック待ち

    ● 様々な理由で実行計画が変わる

    …etc

    突然SQLが遅くなるとき

    

    View Slide

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

    

    https://gihyo.jp/dev/feature/01/dex_postgresql/0002
    

    

    View Slide

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

    

    

    View Slide

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

    

    

    View Slide

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

    

    ● メモリが足りずtemp落ち

    ● ロック待ち

    ● 様々な理由で実行計画が変わる

    …etc

    突然SQLが遅くなるとき

    

    View Slide

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

    スロークエリログには

    ロックの待ち時間は含まれない

    

    View Slide

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

    スロークエリログには

    ロックの待ち時間は含まれない

    ↓

    pg_locks＋pg_stat_activity 

    

    View Slide

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

    Let's Postgres「稼動統計情報を活用しよう」

    

    https://lets.postgresql.jp/documents/technical/statistics

    

    

    View Slide

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;

    

    View Slide

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)
    

    View Slide

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

    ツールを使って可視化する

    

    View Slide

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

    

    ● Mackerel + plugin

    ● Zabbix + pg_monz

    ● pg_statsinfo + pg_stats_reporter 

    …etc

    メジャーなモニタリングツール

    

    View Slide

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

    

    

    

    

    

    https://speakerdeck.com/soudai/postgresql-architecture-and-performance-monitoring 

    

    View Slide

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

    

    ● スロークエリログ

    ● 実行計画

    ● モニタリング

    ● 職人の勘

    スロークエリログの主な見つけ方

    

    View Slide

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

    プロはsshで入る瞬間にわかる（ときがある）

    

    View Slide

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

    topでもわかる（ときがある）

    

    View Slide

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

    職人の勘→DBAの仕事

    職人の勘を仕組みにする→DBREの仕事

    

    View Slide

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

    日本PostgreSQLユーザ会の動画を活用

    

    

    

    View Slide

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

    

    

    

    https://www.youtube.com/channel/UCeenIljXnSwrwYEU-YBE2qA/feed

    

    View Slide

66. あじぇんだ

    1. 自己紹介

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

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

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

    5. 番外編：何もしてないのにDBが壊れた

    6. まとめ

    

    View Slide

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

    エラーログ

    「実を言うとDBはもうだめです。

    突然こんなこと言ってごめんね。

    でも本当です。」

    

    View Slide

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

    エラーログは答えを教えてくれてる

    

    

    View Slide

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

    

    View Slide

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 

    

    View Slide

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 

    

    View Slide

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

    まずはググる

    

    

    View Slide

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

    まずはググる

    ↓

    エラーログは英語の方が良い

    

    View Slide

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

    最初に公式ドキュメントを信じる

    

    

    View Slide

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

    最初に公式ドキュメントを信じる

    ↓

    スタックオーバーフローやQiitaで満足しない

    

    View Slide

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

    ？？？「エラーログで調べると

    自分のblogしか出てこないんですけど」

    

    

    View Slide

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

    ？？？「わかるぅ〜〜〜」

    (CV:そーだい)

    

    View Slide

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

    そうなったら上級者

    

    View Slide

79. あじぇんだ

    1. 自己紹介

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

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

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

    5. 番外編：何もしてないのにDBが壊れた

    6. まとめ

    

    View Slide

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

    突然の謎のエラーログ

    

    

    View Slide

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

    突然の謎のエラーログ

    ↓

    ググっても謎

    

    View Slide

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

    日本語コミュニティのSlackを活用する

    

    

    View Slide

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

    日本語コミュニティのSlackを活用する

    ↓

    mysql-casualとpostgresql-jp

    

    View Slide

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

    Slackで有識者に聞く

    

    

    View Slide

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

    Slackで有識者に聞く

    ↓

    バグだったりすることもある

    

    View Slide

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

    それでもわからない

    

    View Slide

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

    広大なインターネットにも

    無いものは無い

    

    View Slide

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

    誰も知らないことは

    自分が言葉にするしか無い

    

    View Slide

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

    振る舞いを知る

    

    View Slide

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

    Source Code Reading

    http://higepon.hatenablog.com/entry/19700110/1233646390
    

    https://lets.postgresql.jp/node/10
    

    

    

    View Slide

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

    まぁソースコード、言うほど読む必要ない

    

    View Slide

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

    まぁソースコード、言うほど読む必要ない

    ↓

    試して挙動を理解する方が大事

    

    View Slide

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

    

    View Slide

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

    しっかりと動作を知ることができたら？

    

    

    View Slide

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

    しっかりと動作を知ることができたら？

    ↓

    アウトプットしていこう

    

    View Slide

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

    あなたが検索して出てこなかった

    そのエラーメッセージに誰かも困っています

    

    View Slide

97. あじぇんだ

    1. 自己紹介

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

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

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

    5. 番外編：何もしてないのにDBが壊れた

    6. まとめ

    

    View Slide

98. 1. 番外編：何もしてないのにDBが壊れた

    続きはBuildersconで！

    

    View Slide

99. あじぇんだ

    1. 自己紹介

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

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

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

    5. 番外編：何もしてないのにDBが壊れた

    6. まとめ

    

    View Slide

100. まとめ

     今日、学びはありましたか？

     

     View Slide

101. まとめ

     今日、学びはありましたか？

     ↓

     DBAの仕事のほんの一部です

     

     View Slide

102. まとめ

     でもそのDBAの仕事を

     みんなが出来るようにするのが

     DBREの仕事です

     

     View Slide

103. まとめ

     仕組みで課題を解決する

     

     View Slide

104. まとめ

     技術で課題を解決する

     

     View Slide

105. まとめ

     理想への一歩目を踏み出すのは

     自分自身

     

     View Slide

106. まとめ

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

     

     View Slide