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

builderscon2019の登壇資料です
https://builderscon.io/builderscon/tokyo/2019/timetable?date=2019-08-31

# 参考資料
- https://speakerdeck.com/soudai/rdb-troubleshooting
- https://soudai.hatenablog.com/entry/2018/05/01/204442

Transcript

1. RDBのトラブルの現場を追え！
 ~ 様々な現場を見る ~
 builderscon tokyo 2019
2. What is it?
 RDBMSの死はサービスの死

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

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

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

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

8. この話はこっちのスライドを見てください
 https://speakerdeck.com/soudai/rdb-troubleshooting
9. あじぇんだ
 1. 自己紹介
 2. demo 1
 3. demo 2
 4. demo 3
 5. demo 4
 6. まとめ

10. あじぇんだ
 1. 自己紹介
 2. demo 1
 3. demo 2
 4. demo 3
 5. demo 4
 6. まとめ

13. あじぇんだ
 1. 自己紹介
 2. スロークエリの現場
 3. 不正データの現場
 4. パフォーマンスチューニングの現場
 5. 突然DBが壊れた現場
 6. まとめ

14. あじぇんだ
 1. 自己紹介
 2. スロークエリの現場
 3. 不正データの現場
 4. パフォーマンスチューニングの現場
 5. 突然DBが壊れた現場
 6. まとめ

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

19. 先週のそーだいさん

20. 昨日のそーだいさん

21. あじぇんだ
 1. 自己紹介
 2. スロークエリの現場
 3. 不正データの現場
 4. パフォーマンスチューニングの現場
 5. 突然DBが壊れた現場
 6. まとめ

22. スロークエリの現場
 サービスを殺すには刃物は要らぬ。
 スロークエリがあれば良い。

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 );
24. スロークエリの現場
 もうおわかりですね？

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 );
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の独自構文

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 ); 一般的なサブクエリ

28. スロークエリの現場
 つまり…

29. スロークエリの現場
 MySQL 5.5 相当以下だと死ぬ

30. スロークエリの現場
 簡単でしたね

31. スロークエリの現場
 
 UPDATE hoge SET binary = ‘とてもでかい文字列’ WHERE id = 1;
32. スロークエリの現場
 バイナリをDBに保存するな？
 

33. スロークエリの現場
 バイナリをDBに保存するな？
 ↓
 それはそう

34. スロークエリの現場
 これがPostgreSQLだったら？

35. スロークエリの現場
 これがPostgreSQLだったら？
 ↓
 更新はコスト

36. スロークエリの現場
 HOT(Heap Only Tuples)があるでしょ？

37. スロークエリの現場
 HOT(Heap Only Tuples)があるでしょ？
 ↓
 更新後のデータが同じファイルでは無い時はHOTは 利用できません！！！

38. スロークエリの現場
 まぁPostgreSQLなのだから
 必要ならラージオブジェクトを使いましょう

39. スロークエリの現場
 まぁでかいデータをRDBMSに入れるな
 パスで良い
 （ファントムファイル？誤差です）

40. スロークエリの現場
 バルクインサート使ってます？

41. スロークエリの現場
 バルクインサート使ってます？
 ↓
 もっと早いのあります

42. スロークエリの現場
 MySQLならLOAD
 PostgreSQLならCOPY
 マジ便利なのでこれだけは覚えて

43. スロークエリの現場
 スロークエリは本当に様々ある

44. スロークエリの現場
 スロークエリは本当に様々ある
 ↓
 無知の豪腕と
 そもそもテーブル設計が悪いパターンがある

45. スロークエリの現場
 どちらに問題があるかを考えて
 適切に対応していくことが大事

46. スロークエリの現場
 スロークエリログを見て
 実行計画を見て
 TABLE設計を見直せば
 自然と道が見えてくるのじゃ

47. あじぇんだ
 1. 自己紹介
 2. スロークエリの現場
 3. 不正データの現場
 4. パフォーマンスチューニングの現場
 5. 突然DBが壊れた現場
 6. まとめ

48. 不正データの現場
 データは制約で守りましょう

49. 不正データの現場
 完

50. 不正データの現場
 …しかし制約でも守れないデータが！

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 ︙ ︙ ︙ ︙ ︙ ︙
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 ︙ ︙ ︙ ︙ ︙ ︙ 予約日時は必ず今よりも後

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 ︙ ︙ ︙ ︙ ︙ ︙ キャンセル日時は必ず予約日時よりも後

54. 不正データの現場
 素直にcheck制約で設定すると
 reservation_at > now()
 cancel_at > reservation_at

55. 不正データの現場
 これで安心！とはならない…

56. 不正データの現場
 過去のデータ、リストア出来ません！！！
 （テストでも困るし、リストアで困る）

57. 不正データの現場
 なので最初に遅延制約をつけましょう
 (DEFERRABLE INITIALLY DEFERRED)
 ※PostgreSQLの場合

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 ︙ ︙ ︙ ︙ ︙ ︙
59. 不正データの現場
 キャンセルされて無いのに
 同じ席を予約されてはいけない

60. 不正データの現場
 cancel_atが無い時は
 event_it+seat_idは組み合せユニーク
 

61. 不正データの現場
 それPostgreSQLなら出来るよ
 (UNIQUE制約にWHERE句を付けれる)

62. スロークエリの現場
 
 CREATE UNIQUE INDEX event_seat_idx ON reservation (event_id,seat_id) WHERE cancel_at IS NULL;
63. 不正データの現場
 完

64. 不正データの現場
 とはならない！

65. 不正データの現場
 レコードの連続性の正当性

66. 不正データの現場
 そもそも

67. 不正データの現場
 キャンセルされて無いのに
 同じ席を予約されてはいけない

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 ︙ ︙ ︙ ︙ ︙ ︙
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 ︙ ︙ ︙ ︙ ︙ ︙ キャンセルされてから、次のデータが正しい

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 ︙ ︙ ︙ ︙ ︙ ︙ キャンセルされてなかったのに次のレコードが入ってる

71. 不正データの現場
 実際の運用上、起こり得ないですよね

72. 不正データの現場
 これの制約を書くのは大変なので割愛します

73. 不正データの現場
 そもそもこんなデータ入らないでしょ？

74. 不正データの現場
 テストデータとかバグとか
 ヒューマンエラーとかで入るんですよね

75. 不正データの現場
 データはINSERTやUPDATEに成功しても
 それが正しいのは判断は難しい

76. 不正データの現場
 制約でデータを守る
 テストで振る舞いを守る
 モニタリングでサービスを守る

77. 不正データの現場
 適切な制約に
 適切なデータは宿る

78. 不正データの現場
 正しさを常に求めていきましょう

79. あじぇんだ
 1. 自己紹介
 2. スロークエリの現場
 3. 不正データの現場
 4. パフォーマンスチューニングの現場
 5. 突然DBが壊れた現場
 6. まとめ

80. パフォーマンスチューニングの現場
 そもそもさっきのreservationテーブル
 ロックが激しくて現実では厳しい
 （主にISUCON8）

81. パフォーマンスチューニングの現場
 seatテーブルには席の一覧がある

82. パフォーマンスチューニングの現場
 
 SELECT * FROM seat ORDER BY RAND() LIMIT 1;
83. パフォーマンスチューニングの現場
 空席考慮してないでしょ！
 (考慮したSQL、途端に長くなるので割愛)

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 ︙ ︙ ︙ ︙ ︙ ︙
85. パフォーマンスチューニングの現場
 飲み友達に聞きました

86. パフォーマンスチューニングの現場
 InnoDBを使いこなす
 ならこうやで！
 (プライバシー保護のため加工されています)

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 ︙ ︙ ︙ ︙
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でソートされえる

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 ︙ ︙ ︙ ︙ つまり先頭からとってランダム！

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)
91. パフォーマンスチューニングの現場
 https://soudai.hatenablog.com/entry/2018/05/01/204442
92. パフォーマンスチューニングの現場
 集合論から外れず
 しかしRDBMSの特性を活かした設計をする

93. あじぇんだ
 1. 自己紹介
 2. スロークエリの現場
 3. 不正データの現場
 4. パフォーマンスチューニングの現場
 5. 突然DBが壊れた現場
 6. まとめ

94. 突然DBが壊れた現場
 稀によくある

95. 突然DBが壊れた現場
 まず壊れた種類を考える

96. 壊れたとは何が？
 1. 突然パフォーマンスが悪化した
 2. データの不整合が発生している
 3. データベースが応答を返さない
 4. コネクションが溢れている
 5. 間違えてDROP TABLEしちゃった（バルス）
 ...etc

97. 突然DBが壊れた現場
 壊れたを知るために
 モニタリングが大事ｓ

98. 突然DBが壊れた現場

99. 突然DBが壊れた現場
 データベースは突然遅くなる

100. データベースは突然遅くなる
 1. データが増えて、tmp落ち
 2. CPUがサチってDisk ioに余力がない
 3. データ傾向が変わって実行計画が変わった
 4. そもそも意図した実行計画を使ってくれない
 5. 1つの小さな遅延がロックによって顕在化
 ...etc

101. 突然DBが壊れた現場
 データはカジュアルに壊れる

102. データはカジュアルに壊れる
 1. バグやヒューマンエラーでデータは壊れる
 2. 予想外のデータはいつでも入る
 3. データファイルがDisk障害で死ぬことも
 4. レプリケーションでも場合によっては壊れる
 5. そもそもDROPの前では無力
 ...etc

103. 突然DBが壊れた現場
 ヒューマンエラーやバグからは
 制約で守る

104. 突然DBが壊れた現場
 バックアップとログは大事

105. 突然DBが壊れた現場

106. 突然DBが壊れた現場
 
 ？？？「エラーログで調べると
 自分のblogしか出てこないんですけど」
 

107. 突然DBが壊れた現場
 
 ？？？「わかるぅ〜〜〜」
 (CV:そーだい)

108. 突然DBが壊れた現場
 未知の問題を解決することが
 エンジニアの腕の見せ所

109. 突然DBが壊れた現場
 
 ？？？「わかるぅ〜〜〜」
 (CV:そーだい)

110. 突然DBが壊れた現場
 日本語コミュニティのSlackを活用する
 ↓
 mysql-casualとpostgresql-jp

111. あじぇんだ
 1. 自己紹介
 2. スロークエリの現場
 3. 不正データの現場
 4. パフォーマンスチューニングの現場
 5. 突然DBが壊れた現場
 6. まとめ

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

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

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

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

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

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

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