Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

soudai sone

August 03, 2019
Tweet

More Decks by soudai sone

Other Decks in Technology

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