$30 off During Our Annual Pro Sale. View Details »

理屈で考える、データベースのチューニング / Database tuning How-To

forrep
October 01, 2020

理屈で考える、データベースのチューニング / Database tuning How-To

Oracle データベースの内部構造に着目して、さらなるチューニングを行うために必要な基礎知識をまとめた資料です。

forrep

October 01, 2020
Tweet

More Decks by forrep

Other Decks in Programming

Transcript

  1. はじめに • DBの内部構造を知りましょう • 処理速度・処理時間を具体的な数値でイメージしましょう ◦ その数値は多少間違ってても問題ありません • 各処理を分析すると問題の原因を把握できます •

    分析のためには内部構造の知識が生きてきます • 問題の原因が分かれば解決策を考えられます ◦ やみくもに解決策を考えるのは風邪で胃薬を飲むようなもの ◦ DBの中でなにが起きているのか理解しましょう • 計測結果を疑う、みかけの数値に騙されないこと ◦ パフォーマンス測定では内部構造を知らないと騙されやすい ◦ 2回目以降の実行は速いなど問題の再現性が低いことがある 2
  2. アジェンダ • DBの性能を知る • 内部構造を知る • データブロックを理解する • ROWIDとは何か •

    MySQLとの比較 • レコードサイズとパフォーマンス • レコードの分散 • 10年後を考える 3
  3. ストレージの性能を知る • DB性能で重要なのがストレージのランダムI/O性能 • ここでは 4,000 IOPS(I/O per Second)として考える (※

    性能としては少し遅めの水準) • 1分間に行えるI/Oが24万回、これが絶対的な限界値 • 具体的な数値でイメージすることが大切 4,000回/秒 (= 24万回/分) 5
  4. 4,000 IOPS とはどの程度の性能か • AWS RDS で Multi-AZ 4,000 IOPS

    プロビジョンドIOPS ストレージを割り当てると月10万円以上かかる ➢ 価格から分かるように低すぎる性能ではない • ストレージI/O 1回を8KBとする(≒ランダムアクセス) 1秒間 ⇒ 8KB × 4,000回 ≒ 32MB/秒 1分間 ⇒ 32MB × 60秒 = 1,920MB ≒ 2GB/分 プロビジョンド IOPS ストレージ 4,000 IOPS Multi-AZ アジアパシフィック(東京) Multi-AZ プロビジョンド IOPS 料金 ⇒ 毎月 0.24USD/IOPS 4,000 プロビジョンドIOPS x 0.24 USD = 960.00 USD/月 2020年9月現在 6
  5. ストレージ性能と最適化 • 2GBのテーブルを読み込むには1分かかる、意外と遅い • 実際の読み込みでは様々な最適化がある ◦ DBバッファキャッシュ ◦ マルチブロックリード(※後ほど説明) •

    DBバッファキャッシュはメモリ上に確保されるキャッシュ ◦ メモリ読み込みはストレージよりも圧倒的に速い 読み込み済のデータを メモリに保持する ストレージ DBバッファキャッシュ 8
  6. DBバッファキャッシュ • 読み込み速度が「メモリ : ストレージ = 1,000 : 1」とする メモリ読込で1秒の処理

    ⇒ 以下の条件でかかる時間は? ◦ キャッシュヒット率 100% (⇒ メモリ 100%) ⇒ 1秒 ◦ キャッシュヒット率 0% (⇒ ストレージ 100%) ⇒ 1秒 × 1,000倍 = 1,000秒 ◦ キャッシュヒット率 99% (⇒ メモリ99% ストレージ1%) ⇒ 0.99秒 + (0.01秒 × 1,000倍) = 10.99秒 ≒ 11秒 難しいなぁ・・・ 10
  7. 読み込み速度比 ⇒ メモリ : ストレージ = 1,000 : 1 キャッシュヒット率

    ⇒ 99% 読込時間と所要時間の関係 読込データ量の割合 ストレージ 1% DBバッファ キャッシュ 99% 所要時間の割合 ストレージ 91% DBバッファキャッシュ 9% 11
  8. • パフォーマンスの領域では内部構造の理解が不可欠 • DBは魔法の箱ではない、無理な処理をすれば時間がかかる • 例えば、順不同に並ぶ英和辞典で単語を探す ◦ 単語を見つけるまで最初のページから全て探す必要がある ◦ 荒唐無稽に聞こえるがDBでは意外とやりがち

    ➢ 実はテーブルフルスキャンしてるSQL ◦ 見出し語のアルファベット順に並んでれば一瞬で探せる ➢ DBでもインデックスがあれば速くなる ◦ DBの立場になって、その処理に無理がないか考える なぜDBの内部構造の理解が必要か? 英和辞典 17 順不同に並ぶ英和辞書
  9. なぜDBの内部構造の理解が必要か? • 図書館で本を探す場合 ◦ 蔵書のエリア配置・並び順を把握していて最短でたどり着く ◦ 情報端末で蔵書検索をする • たどり着き方は複数あり、どれが最適かは状況により変わる •

    DBは最適な方法を選択(=オプティマイザ)してくれる ◦ 開発者はDBの立場になって「どう処理するか?」を考える • DBとして最適は全体の最適ではない、開発者の手助けが必要 ◦ そもそも必要のない処理である可能性 ◦ さらに効率的な絞り込み条件を追加できる ◦ 運用を変更したら効率的な処理にできる ◦ バッチ処理だから処理速度が遅くてもよい • DBの内部構造を知らないと開発者はうまく手助けできない 20
  10. テーブルのデータは、ストレージの表領域のセグメントの エクステントのデータブロックに保存される ⇒知らなくてOK Oracle DBのデータ構造 表領域 セグメント 表領域 セグメント セグメント

    セグメント セグメント セグメント セグメント セグメント エクステント エクステント エクステント エクステント エクステント エクステント データブロック ストレージ 22
  11. • 覚えてもらいたいのは「データブロック」だけ • データブロックのサイズは選択できる ◦ 2KB, 4KB, 8KB, 16KB, 32KBなど

    • テーブルやインデックスの実データが入っている • データブロックがOracleにおけるI/Oの最小単位 • DBバッファキャッシュへのキャッシュもデータブロック単位 • 今回は8KBとする データブロック データブロック レコードデータ 空き領域 23
  12. • 1ブロックに200レコード入るテーブルを考える • 200万レコード取得する場合、200万 ÷ 200 = 1万ブロック • 4,000

    IOPS なので、10,000 ブロック ÷ 4,000 IOPS = 2.5秒 • (うまくいけば)2.5秒で200万レコード取得できそう • 具体的な数値で考えることが重要、おおよそで問題なし ◦ ただしマルチブロックリードならもっと速い データブロック データブロック 26 1ブロックに200 レコード入るとする
  13. データブロックに関する問題 A: 「2019年5月の注文」の方が早く取得可能 これはなぜか? • レコード挿入は空きデータブロックに対して連続で行われる • その結果、同一タイミングで挿入されたレコードは同一の データブロックに含まれやすい •

    「注文日」と「レコード挿入タイミング」には相関があり 同一ブロック内に条件に当てはまるデータが連続しやすい ◦ データブロック1つに200レコード含むなら、その大半が条件に 当てはまるレコードとなりやすい ◦ 200万 ÷ 200 = 1万ブロック の取得で済む ◦ 分散していると200万ブロック取得が必要 28
  14. 使用可能/使用済みの状態遷移 使用可能 31 使用可能 使用済み INSERT 使用済み INSERT UPDATE DELETE

    使用済み DELETE INSERT 使用可能 INSERT 空き領域が一定未満に なるまでは使用可能 INSERT INSERT INSERT INSERT INSERT 十分空きができるま では使用済みのまま ① ② ③ ④ ⑤ ⑥
  15. データブロックサイズの考察 • データが分散している場合 ◦ 各データブロックに必要なレコードが数行しか含まれない ◦ 沢山のデータブロックを取得する必要がある ◦ それならば、ブロックサイズを小さくした方が有利? ➢

    大抵の場合はNO、なぜならば • 一般的なDBストレージ(SAS/iSCSI/FCなど)はブロックサ イズを変えてもIOPSはさほど変化しない ◦ 4KB ⇒ 2KB IOPSにさほど影響なし ◦ 8KB ⇒ 16KB IOPSが1~2割程度の減少(あくまで一例) 32
  16. ブロックサイズとIOPSとスループット • 厳密な数値は考えず、おおまかに理解する • ブロックサイズが 8KB ⇒ 16KB に変化した場合、 IOPSは2割減少すると考える

    4,000 IOPS ⇒ 3,200 IOPS • スループットはブロックサイズが大きい方が有利 16KB 8KB 4,000 IOPS ブロックサイズ IOPS 3,200 IOPS 32MB/秒 51MB/秒 スループット 33 2倍 0.8倍 1.6倍
  17. 大きいブロックサイズの考察 • ブロックサイズが大きいと IOPS は若干減少する • 1レコードだけ取得する場合は不利 ◦ 巨大ブロックを読み込んでも、利用するのは1レコードだけ ◦

    INDEX UNIQUE SCAN ⇒ TABLE ACCESS BY INDEX ROWID たった1レコードのために ブロック全体を取得する データブロック 大半のデータは 読み込んでも使わない 34
  18. • フルスキャンの場合だけ、スループットを高くできる ◦ マルチブロックリード機能で複数ブロックを同時取得 ◦ TABLE ACCESS FULL, INDEX FAST

    FULL SCAN ◦ しかしフルスキャンが許されるのはバッチ処理くらい 小さいブロックサイズの考察 複数ブロックを1回のI/Oで 取得するので速い データブロック ざくっと一気に マルチブロックリード 狙ったブロックを1つずつ 通常リード 39
  19. • DBバッファキャッシュの無駄が少ない ◦ キャッシュするのはデータブロック単位 ◦ 小さいため沢山のデータブロックをキャッシュ可能 小さいブロックサイズの考察 DBバッファキャッシュ データブロック データブロック

    データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック 1ブロックが小さいため 沢山のブロックが入る 40
  20. ブロックサイズはなにが正解か? • ブロックサイズは表領域単位で変更できる、 可能ならばテーブルごとにチューニングするのがベスト • しかし現実的には難易度が高い、実施コストが高くなりがち • 開発者としてはDBのブロックサイズを把握して、 それに合わせたチューニングを行うのがベター ◦

    構造的にパフォーマンスがでないからバッチ処理にする ◦ 1ページで取得するアイテム数を減らして速度をだす ス◦ーピー「配られたカードで勝負するしかないのさ」 孫子「彼を知り己を知れば百戦殆うからず」 41
  21. ROWIDの構造 • 「AAAVREAAEAAAACXAAA」 という形式 ◦ 6桁: AAAVRE ⇒ データオブジェクト番号 ◦

    3桁: AAE ⇒ 相対ファイル番号 ◦ 6桁: AAAACX ⇒ データブロック番号 ◦ 3桁: AAA ⇒ 行番号 • dbms_rowidパッケージで解析できる select rowid, dbms_rowid.rowid_object(rowid) as data_object_id, dbms_rowid.rowid_relative_fno(rowid) as relative_fno, dbms_rowid.rowid_block_number(rowid) as block_number, dbms_rowid.rowid_row_number(rowid) as row_number, scott.emp.empno, scott.emp.ename from scott.emp ; 44
  22. • scott.emp テーブルのROWIDを解析してみる • 各レコードはすべてファイル番号4、データブロック番号151 に入っていることが分かる • このテーブルはフルスキャンに必要なI/O回数が1回と分かる ROWIDの解析例 ROWID

    DATA_OBJECT_ID RELATIVE_FNO BLOCK_NUMBER ROW_NUMBER EMPNO ENAME AAAVREAAEAAAACXAAA 87108 4 151 0 7369 SMITH AAAVREAAEAAAACXAAB 87108 4 151 1 7499 ALLEN AAAVREAAEAAAACXAAC 87108 4 151 2 7521 WARD AAAVREAAEAAAACXAAD 87108 4 151 3 7566 JONES AAAVREAAEAAAACXAAE 87108 4 151 4 7654 MARTIN AAAVREAAEAAAACXAAF 87108 4 151 5 7698 BLAKE AAAVREAAEAAAACXAAG 87108 4 151 6 7782 CLARK AAAVREAAEAAAACXAAH 87108 4 151 7 7788 SCOTT AAAVREAAEAAAACXAAI 87108 4 151 8 7839 KING AAAVREAAEAAAACXAAJ 87108 4 151 9 7844 TURNER AAAVREAAEAAAACXAAK 87108 4 151 10 7876 ADAMS AAAVREAAEAAAACXAAL 87108 4 151 11 7900 JAMES AAAVREAAEAAAACXAAM 87108 4 151 12 7902 FORD AAAVREAAEAAAACXAAN 87108 4 151 13 7934 MILLER 45
  23. • ROWIDの主な利用用途はインデックス • B+tree構造で「インデックス対象のデータ値」と 「レコードへのROWID(参照)」を持つ ROWIDはどこで使われるか 0..40 41..80 81..250 0..10

    11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,ACXAAA 3,ADXAAC … 10,ADXABG 11,ADXAAD 16,AEAAAA … 25,AEBAAB 231,AGXAAC 232,AHAAAB … 250,AHBAAK ・・・・・・・・・ ブランチブロック リーフブロック データ値とROWIDを持つ 47
  24. • 例えば ID=232 のレコードを探す場合を考える ◦ ブランチブロックを2つたどる ⇒ リーフブロックでROWIDを 取得 ⇒

    ROWIDでテーブルのレコードを取得 ROWIDはどこで使われるか 0..40 41..80 81..250 0..10 11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,ACXAAA 3,ADXAAC … 10,ADXABG 11,ADXAAD 16,AEAAAA … 25,AEBAAB 231,AGXAAC 232,AHAAAB … 250,AHBAAK ・・・・・・・・・ ブランチブロック リーフブロック このROWIDでテーブル からレコードを取得 48
  25. プライマリキーでレコード取得 • プライマリキーでレコードを取得する例 ◦ インデックスのブランチノード取得 × 2回 ◦ インデックスのリーフノード取得(ROWIDを取得) ◦

    ROWIDからテーブルのレコード取得 ➢ 計4回程度I/Oのラウンドトリップ、遅くないけど速くもない • テーブルからデータを直接取得する場合 ◦ マルチブロックリード機能、高いスループットで取得可能 0..40 41..80 81..250 0..10 11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,ACXAAA 3,ADXAAC … 10,ADXABG 11,ADXAAD 16,AEAAAA … 25,AEBAAB 231,AGXAAC 232,AHAAAB … 250,AHBAAK ・・・・・・・・・ 3 CC 500 2018-06-12 インデックス走査 レコード取得 49
  26. • それぞれ10,000レコード取得に必要なコストを考える • インデックス走査とレコード取得が10,000レコード分必要 • ブランチノードはキャッシュのヒット率 が高い テーブル直接とインデックス経由のレコード取得 0..40 41..80

    81..250 0..10 11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,ACXAAA 3,ADXAAC … 10,ADXABG 11,ADXAAD 16,AEAAAA … 25,AEBAAB 231,AGXAAC 232,AHAAAB … 250,AHBAAK ・・・・・・・・・ 3 CC 500 2018-06-12 インデックス走査 レコード取得 10,000回 インデックス経由で10,000レコード取得する場合 50
  27. • テーブルの場合はマルチブロックリード機能で 複数ブロック・複数レコードをまとめて取得可能 • ただしテーブル内の全行取得が必要 • どちらが速いかは状況次第 ◦ 全体で5万行程度なら?ブロックの分散状況は? ◦

    1レコードが巨大なら?逆に小さいなら? • 時間経過でレコードが増加するならインデックス利用が必須 ◦ マルチブロックリードが速くてもいつかは破綻する テーブル直接とインデックス経由のレコード取得 テーブルから直接10,000レコード取得する場合 ID Name Price Updated 1 AA 100 2012-10-04 2 BB 53 2014-12-01 3 CC 500 2018-06-12 4 DD 12 2019-03-28 5 EE 212 2020-05-03 ... ... ... ... 1回のI/Oで複数行取得できる 51
  28. • InnoDBのテーブルはクラスタインデックス構成 • テーブル全体がB+treeインデックス構造になっていて、 レコードのデータをリーフページに持つ MySQL(InnoDB)との比較 0..40 41..80 81..250 0..10

    11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,AA,100,2012 3,BB,120,2019 … 10,CC,99,2020 11,DD,210,2009 16,EE,180,2010 … 25,FF,510,1980 231,KK,615,2015 232,LL,880,2002 … 250,PP,980,1995 ・・・・・・・・・ 非リーフページ リーフページ インデックス構造で レコードデータも持つ 54
  29. • プライマリキーでレコード取得する場合はインデックス走査 だけでレコードも取得できて有利 • Oracleの場合はROWIDからレコードを取得する一手間が必要 MySQL(InnoDB)との比較 0..40 41..80 81..250 0..10

    11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,AA,100,2012 3,BB,120,2019 … 10,CC,99,2020 11,DD,210,2009 16,EE,180,2010 … 25,FF,510,1980 231,KK,615,2015 232,LL,880,2002 … 250,PP,980,1995 ・・・・・・・・・ 非リーフページ リーフページ インデックス走査だけで レコードデータも取得可能 55
  30. • InnoDBにはROWIDにあたるものがない • セカンダリインデックス(プライマリキー以外のインデック ス)は、レコードのポインタとしてプライマリキー値を持つ • セカンダリインデックスはインデックス走査が2回必要、 ROWIDでダイレクトに取得できるOracleよりも遅い • プライマリキーのデータサイズが大きいと無駄が多くなる

    InnoDBにおけるセカンダリインデックス 0..40 41..80 81..250 0..10 11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,ACXAAA 3,ADXAAC … 10,ADXABG 11,ADXAAD 16,AEAAAA … 25,AEBAAB 231,AGXAAC 232,AHAAAB … 250,AHBAAK ・・・・・・・・・ セカンダリインデックス走査 (プライマリキー値の取得) 0..40 41..80 81..250 0..10 11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,ACXAAA 3,ADXAAC … 10,ADXABG 11,ADXAAD 16,AEAAAA … 25,AEBAAB 231,AGXAAC 232,AHAAAB … 250,AHBAAK ・・・・・・・・・ クラスタインデックス走査 (レコードの取得) 56
  31. • クラスタインデックスのため、テーブルフルスキャンは プライマリキー順に取得される ⇒ 開発者に優しい • Oracleと比べてチューニングの余地は制限される MySQL(InnoDB)との比較 0..40 41..80

    81..250 0..10 11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,AA,100,2012 3,BB,120,2019 … 10,CC,99,2020 11,DD,210,2009 16,EE,180,2010 … 25,FF,510,1980 231,KK,615,2015 232,LL,880,2002 … 250,PP,980,1995 ・・・・・・・・・ 先頭から順に取得 57
  32. 1レコードのサイズを設計 • 絞り込み/並び替えのカラムを小さいテーブルに切り出す • レコードサイズを小さく設計、テーブルサイズが小さいと 様々なシーンでパフォーマンスを出しやすい 巨大な1レコード 巨大な1レコード 1レコード 大きい

    1レコード 小さい 読み込むレコードが多い ⇒ 読み込むブロック数が多い ⇒ 遅い 必要なカラムを 小さいテーブルに 切り出す 61 読み込むレコードが多い ⇒ 読み込むブロック数は少ない ⇒ 速い
  33. • 題材としてフォロー型のSNSを考える • フォロー関係は N:N となる • あるユーザーのフォロワーの一覧を取得するコストは? データの分散状況とパフォーマンス 63

    FOLLOWEE_ID FOLLOWER_ID CREATED_ON ... ... ... 256 284 2007-09-05 255 544 2007-09-05 440 729 2007-09-05 591 932 2007-09-05 139 926 2007-09-05 289 460 2007-09-05 350 64 2007-09-05 68 397 2007-09-05 902 581 2007-09-05 666 45 2007-09-05 978 260 2007-09-05 696 208 2007-09-05 ... ... ... フォローテーブル フォローした人 フォローされた人
  34. • フォロー関係は時間と共に徐々に増える • 1ユーザーで絞ると各レコードのブロックは分散している • フォロワーが10万人、10万レコードが7~8万ブロックに分散 ◦ 8万ブロック ⇒ 4,000

    IOPS なら20秒かかる データの分散状況とパフォーマンス フォローテーブル (FOLLOWEE_ID = 350) FOLLOWEE_ID FOLLOWER_ID CREATED_ON ... ... ... 350 468 2004-08-22 350 174 2004-08-22 ... ... ... 350 537 2008-04-08 ... ... ... 350 860 2018-08-24 ... ... ... 350 64 2007-09-05 ... ... ... 350 326 2019-06-05 ... ... ... 350 841 2005-05-01 ... ... ... 64
  35. • 分散したレコードをDELETEしてINSERTするだけでも ブロックはまとまる • ブロックの分散が解消するとI/O回数は激減する データの分散とパフォーマンス フォローテーブル FOLLOWEE_ID FOLLOWER_ID CREATED_ON

    ... ... ... 350 284 2004-08-22 350 544 2017-03-27 ... ... ... 350 932 2008-04-08 ... ... ... 350 460 2018-08-24 ... ... ... 350 64 2007-09-05 ... ... ... 350 45 2019-06-05 ... ... ... 350 208 2005-05-01 ... ... ... FOLLOWEE_ID FOLLOWER_ID CREATED_ON ... ... ... 350 284 2004-08-22 350 544 2017-03-27 350 932 2008-04-08 350 460 2018-08-24 350 64 2007-09-05 350 45 2019-06-05 350 208 2005-05-01 ... ... ... フォローテーブル DELETE/INSERT 10万レコード 8万ブロック ⇒ 20秒 ※1.25レコード/ブロック 10万レコード 400ブロック ⇒ 0.1秒 ※250レコード/ブロック 65
  36. DB設計のライフサイクル • システムのライフサイクルは部品ごとに異なる • アプリケーションプログラムは数年でリプレースを検討する • DB設計はそれより長い間使われる、ライフサイクルが長い • 今作ったテーブルが10年後も使われる想定での設計が必要 FOLLOWEE_ID

    FOLLOWER_ID CREATED_ON ... ... ... 256 284 2007-09-05 255 544 2007-09-05 440 729 2007-09-05 591 932 2007-09-05 139 926 2007-09-05 289 460 2007-09-05 350 64 2007-09-05 68 397 2007-09-05 902 581 2007-09-05 666 45 2007-09-05 978 260 2007-09-05 696 208 2007-09-05 ... ... ... FOLLOWEE_ID FOLLOWER_ID CREATED_ON 25 28 2000-09-05 25 54 2000-09-05 44 72 2000-09-05 59 93 2000-09-05 13 92 2000-09-05 28 46 2000-09-05 ... ... ... 数千行だったテーブル 数億行になってもパフォー マンスを維持できるか? 10年後 67
  37. DB設計のライフサイクル • 処理対象のレコードを限定できる設計を導入 ◦ 履歴レコードは参照範囲を過去1年に限定など ▪ 範囲内のレコードはDBバッファキャッシュに含む前提 ▪ ブロック分散に気をつける ◦

    大規模なソートを排除、order by狙いのインデックスを利用 ▪ ソートには対象の全レコードが必要(=フルスキャン) ▪ CPU・メモリだけを消費するソートは許容できる ◦ 生きてるレコードと死んだレコードを区別する ▪ 死んだレコードを処理速度に影響させない などなど 69