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

RDBのトラブルの現場を追え! ~ 様々な現場を見る ~ / rdb-troubleshooting2

RDBのトラブルの現場を追え! ~ 様々な現場を見る ~ / rdb-troubleshooting2

soudai sone

August 30, 2019
Tweet

More Decks by soudai sone

Other Decks in Technology

Transcript

  1. RDBのトラブルの現場を追え!

    ~ 様々な現場を見る ~

    builderscon tokyo 2019

    View Slide

  2. What is it?

    RDBMSの死はサービスの死


    View Slide

  3. What is it?

    ● DBが突然遅くなった…

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

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

    現場の声


    View Slide

  4. What is it?

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


    View Slide

  5. あじぇんだ

    1. 自己紹介

    2. 初級編:スロークエリを追え!

    3. 中級編:エラーログを追え!

    4. 上級編:未知のエラーを追え!

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

    6. まとめ


    View Slide

  6. あじぇんだ

    1. 自己紹介

    2. 初級編:スロークエリを追え!

    3. 中級編:エラーログを追え!

    4. 上級編:未知のエラーを追え!

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

    6. まとめ


    View Slide

  7. View Slide

  8. この話はこっちのスライドを見てください

    https://speakerdeck.com/soudai/rdb-troubleshooting

    View Slide

  9. あじぇんだ

    1. 自己紹介

    2. demo 1

    3. demo 2

    4. demo 3

    5. demo 4

    6. まとめ


    View Slide

  10. あじぇんだ

    1. 自己紹介

    2. demo 1

    3. demo 2

    4. demo 3

    5. demo 4

    6. まとめ


    View Slide

  11. View Slide

  12. View Slide

  13. あじぇんだ

    1. 自己紹介

    2. スロークエリの現場

    3. 不正データの現場

    4. パフォーマンスチューニングの現場

    5. 突然DBが壊れた現場

    6. まとめ


    View Slide

  14. あじぇんだ

    1. 自己紹介

    2. スロークエリの現場

    3. 不正データの現場

    4. パフォーマンスチューニングの現場

    5. 突然DBが壊れた現場

    6. まとめ


    View Slide

  15. 自己紹介

    曽根 壮大(34歳)

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


    そ  ね   たけ とも

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

    ● 3人の子供がいます(長女、次女、長男)

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

    ● コミュニティが好き

    View Slide

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

    https://party-calendar.net/

    View Slide

  17. View Slide

  18. 本書きました


    View Slide

  19. 先週のそーだいさん


    View Slide

  20. 昨日のそーだいさん


    View Slide

  21. あじぇんだ

    1. 自己紹介

    2. スロークエリの現場

    3. 不正データの現場

    4. パフォーマンスチューニングの現場

    5. 突然DBが壊れた現場

    6. まとめ


    View Slide

  22. スロークエリの現場

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

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


    View Slide

  23. スロークエリの現場


    REPLACE INTO hoge(id, name)
    SELECT id, name
    FROM foo AS f
    INNER JOIN bar AS b ON f.event_id = b.id
    WHERE f.id in (
    SELECT max(event_id)
    FROM f
    WHERE canceled_at IS NULL
    GROP BY event_id, sheet_id
    );

    View Slide

  24. スロークエリの現場

    もうおわかりですね?


    View Slide

  25. スロークエリの現場


    REPLACE INTO hoge(id, name)
    SELECT id, name
    FROM foo AS f
    INNER JOIN bar AS b ON f.event_id = b.id
    WHERE f.id in (
    SELECT max(event_id)
    FROM f
    WHERE canceled_at IS NULL
    GROP BY event_id, sheet_id
    );

    View Slide

  26. スロークエリの現場


    REPLACE INTO hoge(id, name)
    SELECT id, name
    FROM foo AS f
    INNER JOIN bar AS b ON f.event_id = b.id
    WHERE f.id in (
    SELECT max(event_id)
    FROM f
    WHERE canceled_at IS NULL
    GROP BY event_id, sheet_id
    );
    REPLACE 構文はMySQLの独自構文


    View Slide

  27. スロークエリの現場


    REPLACE INTO hoge(id, name)
    SELECT id, name
    FROM foo AS f
    INNER JOIN bar AS b ON f.event_id = b.id
    WHERE f.id in (
    SELECT max(event_id)
    FROM f
    WHERE canceled_at IS NULL
    GROP BY event_id, sheet_id
    );
    一般的なサブクエリ


    View Slide

  28. スロークエリの現場

    つまり…


    View Slide

  29. スロークエリの現場

    MySQL 5.5 相当以下だと死ぬ


    View Slide

  30. スロークエリの現場

    簡単でしたね


    View Slide

  31. スロークエリの現場


    UPDATE hoge
    SET binary = ‘とてもでかい文字列’
    WHERE id = 1;

    View Slide

  32. スロークエリの現場

    バイナリをDBに保存するな?


    View Slide

  33. スロークエリの現場

    バイナリをDBに保存するな?

    ↓

    それはそう


    View Slide

  34. スロークエリの現場

    これがPostgreSQLだったら?


    View Slide

  35. スロークエリの現場

    これがPostgreSQLだったら?

    ↓

    更新はコスト


    View Slide

  36. スロークエリの現場

    HOT(Heap Only Tuples)があるでしょ?


    View Slide

  37. スロークエリの現場

    HOT(Heap Only Tuples)があるでしょ?

    ↓

    更新後のデータが同じファイルでは無い時はHOTは
    利用できません!!!


    View Slide

  38. スロークエリの現場

    まぁPostgreSQLなのだから

    必要ならラージオブジェクトを使いましょう


    View Slide

  39. スロークエリの現場

    まぁでかいデータをRDBMSに入れるな

    パスで良い

    (ファントムファイル?誤差です)


    View Slide

  40. スロークエリの現場

    バルクインサート使ってます?


    View Slide

  41. スロークエリの現場

    バルクインサート使ってます?

    ↓

    もっと早いのあります


    View Slide

  42. スロークエリの現場

    MySQLならLOAD

    PostgreSQLならCOPY

    マジ便利なのでこれだけは覚えて


    View Slide

  43. スロークエリの現場

    スロークエリは本当に様々ある


    View Slide

  44. スロークエリの現場

    スロークエリは本当に様々ある

    ↓

    無知の豪腕と

    そもそもテーブル設計が悪いパターンがある


    View Slide

  45. スロークエリの現場

    どちらに問題があるかを考えて

    適切に対応していくことが大事


    View Slide

  46. スロークエリの現場

    スロークエリログを見て

    実行計画を見て

    TABLE設計を見直せば

    自然と道が見えてくるのじゃ


    View Slide

  47. あじぇんだ

    1. 自己紹介

    2. スロークエリの現場

    3. 不正データの現場

    4. パフォーマンスチューニングの現場

    5. 突然DBが壊れた現場

    6. まとめ


    View Slide

  48. 不正データの現場

    データは制約で守りましょう


    View Slide

  49. 不正データの現場

    完


    View Slide

  50. 不正データの現場

    …しかし制約でも守れないデータが!


    View Slide

  51. 不正データの現場

    id user_id event_id seat_id reservation_at cancel_at
    1 1 1 1 2019/08/19 09:10 null
    2 1 2 1 2019/08/20 09:10 2019/08/21 09:10
    3 1 2 4 2019/08/22 09:10 null
    4 1 3 3 2019/08/23 09:10 null
    ︙ ︙ ︙ ︙ ︙ ︙

    View Slide

  52. 不正データの現場

    id user_id event_id seat_id reservation_at cancel_at
    1 1 1 1 2019/08/19 09:10 null
    2 1 2 1 2019/08/20 09:10 2019/08/21 09:10
    3 1 2 4 2019/08/22 09:10 null
    4 1 3 3 2019/08/23 09:10 null
    ︙ ︙ ︙ ︙ ︙ ︙
    予約日時は必ず今よりも後


    View Slide

  53. 不正データの現場

    id user_id event_id seat_id reservation_at cancel_at
    1 1 1 1 2019/08/19 09:10 null
    2 1 2 1 2019/08/20 09:10 2019/08/21 09:10
    3 1 2 4 2019/08/22 09:10 null
    4 1 3 3 2019/08/23 09:10 null
    ︙ ︙ ︙ ︙ ︙ ︙
    キャンセル日時は必ず予約日時よりも後


    View Slide

  54. 不正データの現場

    素直にcheck制約で設定すると

    reservation_at > now()

    cancel_at > reservation_at


    View Slide

  55. 不正データの現場

    これで安心!とはならない…


    View Slide

  56. 不正データの現場

    過去のデータ、リストア出来ません!!!

    (テストでも困るし、リストアで困る)


    View Slide

  57. 不正データの現場

    なので最初に遅延制約をつけましょう

    (DEFERRABLE INITIALLY DEFERRED)

    ※PostgreSQLの場合


    View Slide

  58. 不正データの現場

    id user_id event_id seat_id reservation_at cancel_at
    1 1 2 1 2019/08/19 09:10 null
    2 1 2 1 2019/08/20 09:10 2019/08/21 09:10
    3 1 2 1 2019/08/22 09:10 null
    4 1 3 3 2019/08/23 09:10 null
    ︙ ︙ ︙ ︙ ︙ ︙

    View Slide

  59. 不正データの現場

    キャンセルされて無いのに

    同じ席を予約されてはいけない


    View Slide

  60. 不正データの現場

    cancel_atが無い時は

    event_it+seat_idは組み合せユニーク


    View Slide

  61. 不正データの現場

    それPostgreSQLなら出来るよ

    (UNIQUE制約にWHERE句を付けれる)


    View Slide

  62. スロークエリの現場


    CREATE UNIQUE INDEX
    event_seat_idx
    ON reservation (event_id,seat_id)
    WHERE cancel_at IS NULL;

    View Slide

  63. 不正データの現場

    完


    View Slide

  64. 不正データの現場

    とはならない!


    View Slide

  65. 不正データの現場

    レコードの連続性の正当性


    View Slide

  66. 不正データの現場

    そもそも


    View Slide

  67. 不正データの現場

    キャンセルされて無いのに

    同じ席を予約されてはいけない


    View Slide

  68. 不正データの現場

    id user_id event_id seat_id reservation_at cancel_at
    1 1 2 1 2019/08/19 09:10 null
    2 1 2 1 2019/08/20 09:10 2019/08/21 09:10
    3 1 2 1 2019/08/22 09:10 null
    4 1 3 3 2019/08/23 09:10 null
    ︙ ︙ ︙ ︙ ︙ ︙

    View Slide

  69. 不正データの現場

    id user_id event_id seat_id reservation_at cancel_at
    1 1 2 1 2019/08/19 09:10 null
    2 1 2 1 2019/08/20 09:10 2019/08/21 09:10
    3 1 2 1 2019/08/22 09:10 null
    4 1 3 3 2019/08/23 09:10 null
    ︙ ︙ ︙ ︙ ︙ ︙
    キャンセルされてから、次のデータが正しい


    View Slide

  70. 不正データの現場

    id user_id event_id seat_id reservation_at cancel_at
    1 1 2 1 2019/08/19 09:10 null
    2 1 2 1 2019/08/20 09:10 2019/08/21 09:10
    3 1 2 1 2019/08/22 09:10 null
    4 1 3 3 2019/08/23 09:10 null
    ︙ ︙ ︙ ︙ ︙ ︙
    キャンセルされてなかったのに次のレコードが入ってる


    View Slide

  71. 不正データの現場

    実際の運用上、起こり得ないですよね


    View Slide

  72. 不正データの現場

    これの制約を書くのは大変なので割愛します


    View Slide

  73. 不正データの現場

    そもそもこんなデータ入らないでしょ?


    View Slide

  74. 不正データの現場

    テストデータとかバグとか

    ヒューマンエラーとかで入るんですよね


    View Slide

  75. 不正データの現場

    データはINSERTやUPDATEに成功しても

    それが正しいのは判断は難しい


    View Slide

  76. 不正データの現場

    制約でデータを守る

    テストで振る舞いを守る

    モニタリングでサービスを守る


    View Slide

  77. 不正データの現場

    適切な制約に

    適切なデータは宿る


    View Slide

  78. 不正データの現場

    正しさを常に求めていきましょう


    View Slide

  79. あじぇんだ

    1. 自己紹介

    2. スロークエリの現場

    3. 不正データの現場

    4. パフォーマンスチューニングの現場

    5. 突然DBが壊れた現場

    6. まとめ


    View Slide

  80. パフォーマンスチューニングの現場

    そもそもさっきのreservationテーブル

    ロックが激しくて現実では厳しい

    (主にISUCON8)


    View Slide

  81. パフォーマンスチューニングの現場

    seatテーブルには席の一覧がある


    View Slide

  82. パフォーマンスチューニングの現場


    SELECT
    *
    FROM seat
    ORDER BY RAND()
    LIMIT 1;

    View Slide

  83. パフォーマンスチューニングの現場

    空席考慮してないでしょ!

    (考慮したSQL、途端に長くなるので割愛)


    View Slide

  84. 再掲

    id user_id event_id seat_id reservation_at cancel_at
    1 1 1 1 2019/08/19 09:10 null
    2 1 2 1 2019/08/20 09:10 2019/08/21 09:10
    3 1 2 4 2019/08/22 09:10 null
    4 1 3 3 2019/08/23 09:10 null
    ︙ ︙ ︙ ︙ ︙ ︙

    View Slide

  85. パフォーマンスチューニングの現場

    飲み友達に聞きました


    View Slide

  86. パフォーマンスチューニングの現場

    InnoDBを使いこなす

    ならこうやで!

    (プライバシー保護のため加工されています)


    View Slide

  87. remainsテーブルの例

    id event_id seat_id reservation_at
    1 1 11049 null
    2 1 5467 null
    3 1 3333 null
    4 1 14 null
    ︙ ︙ ︙ ︙

    View Slide

  88. remainsテーブルの例

    id event_id seat_id reservation_at
    1 1 11049 null
    2 1 5467 null
    3 1 3333 null
    4 1 14 null
    ︙ ︙ ︙ ︙
    InnoDBはクラスタインデックスなので

    event_idのINDEXを利用した時

    暗黙的にPKでソートされえる


    View Slide

  89. remainsテーブルの例

    id event_id seat_id reservation_at
    1 1 11049 null
    2 1 5467 null
    3 1 3333 null
    4 1 14 null
    ︙ ︙ ︙ ︙
    つまり先頭からとってランダム!


    View Slide

  90. パフォーマンスチューニングの現場


    mysql> SELECT * FROM remains WHERE event_id = 1;
    +----+----------+---------+-------------+
    | id | event_id | seat_id | reserved_at |
    +----+----------+---------+-------------+
    | 1 | 1 | 29 | NULL |
    | 2 | 1 | 59 | NULL |
    | 3 | 1 | 10029 | NULL |
    | 4 | 1 | 2009 | NULL |
    | 5 | 1 | 2345 | NULL |
    | 6 | 1 | 8756 | NULL |
    +----+----------+---------+-------------+
    6 rows in set (0.00 sec)

    View Slide

  91. パフォーマンスチューニングの現場

    https://soudai.hatenablog.com/entry/2018/05/01/204442

    View Slide

  92. パフォーマンスチューニングの現場

    集合論から外れず

    しかしRDBMSの特性を活かした設計をする


    View Slide

  93. あじぇんだ

    1. 自己紹介

    2. スロークエリの現場

    3. 不正データの現場

    4. パフォーマンスチューニングの現場

    5. 突然DBが壊れた現場

    6. まとめ


    View Slide

  94. 突然DBが壊れた現場

    稀によくある


    View Slide

  95. 突然DBが壊れた現場

    まず壊れた種類を考える


    View Slide

  96. 壊れたとは何が?

    1. 突然パフォーマンスが悪化した

    2. データの不整合が発生している

    3. データベースが応答を返さない

    4. コネクションが溢れている

    5. 間違えてDROP TABLEしちゃった(バルス)

    ...etc


    View Slide

  97. 突然DBが壊れた現場

    壊れたを知るために

    モニタリングが大事s


    View Slide

  98. 突然DBが壊れた現場


    View Slide

  99. 突然DBが壊れた現場

    データベースは突然遅くなる


    View Slide

  100. データベースは突然遅くなる

    1. データが増えて、tmp落ち

    2. CPUがサチってDisk ioに余力がない

    3. データ傾向が変わって実行計画が変わった

    4. そもそも意図した実行計画を使ってくれない

    5. 1つの小さな遅延がロックによって顕在化

    ...etc


    View Slide

  101. 突然DBが壊れた現場

    データはカジュアルに壊れる


    View Slide

  102. データはカジュアルに壊れる

    1. バグやヒューマンエラーでデータは壊れる

    2. 予想外のデータはいつでも入る

    3. データファイルがDisk障害で死ぬことも

    4. レプリケーションでも場合によっては壊れる

    5. そもそもDROPの前では無力

    ...etc


    View Slide

  103. 突然DBが壊れた現場

    ヒューマンエラーやバグからは

    制約で守る


    View Slide

  104. 突然DBが壊れた現場

    バックアップとログは大事


    View Slide

  105. 突然DBが壊れた現場


    View Slide

  106. 突然DBが壊れた現場


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

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


    View Slide

  107. 突然DBが壊れた現場


    ???「わかるぅ〜〜〜」

    (CV:そーだい)


    View Slide

  108. 突然DBが壊れた現場

    未知の問題を解決することが

    エンジニアの腕の見せ所


    View Slide

  109. 突然DBが壊れた現場


    ???「わかるぅ〜〜〜」

    (CV:そーだい)


    View Slide

  110. 突然DBが壊れた現場

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

    ↓

    mysql-casualとpostgresql-jp


    View Slide

  111. あじぇんだ

    1. 自己紹介

    2. スロークエリの現場

    3. 不正データの現場

    4. パフォーマンスチューニングの現場

    5. 突然DBが壊れた現場

    6. まとめ


    View Slide

  112. まとめ

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


    View Slide

  113. まとめ

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

    ↓

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


    View Slide

  114. まとめ

    でもそのDBAの仕事を

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

    DBREの仕事です


    View Slide

  115. まとめ

    仕組みで課題を解決する


    View Slide

  116. まとめ

    技術で課題を解決する


    View Slide

  117. まとめ

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

    自分自身


    View Slide

  118. まとめ

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


    View Slide