Oracle データベースの内部構造に着目して、さらなるチューニングを行うために必要な基礎知識をまとめた資料です。
理屈で考える、データベースのチューニング第1回 Oracle DBのさらなるパフォーマンスチューニングに必要な基礎知識2020年09月30日ラクーンホールディングス 羽山 純1
View Slide
はじめに● DBの内部構造を知りましょう● 処理速度・処理時間を具体的な数値でイメージしましょう○ その数値は多少間違ってても問題ありません● 各処理を分析すると問題の原因を把握できます● 分析のためには内部構造の知識が生きてきます● 問題の原因が分かれば解決策を考えられます○ やみくもに解決策を考えるのは風邪で胃薬を飲むようなもの○ DBの中でなにが起きているのか理解しましょう● 計測結果を疑う、みかけの数値に騙されないこと○ パフォーマンス測定では内部構造を知らないと騙されやすい○ 2回目以降の実行は速いなど問題の再現性が低いことがある2
アジェンダ● DBの性能を知る● 内部構造を知る● データブロックを理解する● ROWIDとは何か● MySQLとの比較● レコードサイズとパフォーマンス● レコードの分散● 10年後を考える3
DBの性能を知る4
ストレージの性能を知る● DB性能で重要なのがストレージのランダムI/O性能● ここでは 4,000 IOPS(I/O per Second)として考える（※ 性能としては少し遅めの水準）● 1分間に行えるI/Oが24万回、これが絶対的な限界値● 具体的な数値でイメージすることが大切4,000回/秒（= 24万回/分）5
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 IOPSMulti-AZアジアパシフィック(東京)Multi-AZ プロビジョンド IOPS 料金⇒ 毎月 0.24USD/IOPS4,000 プロビジョンドIOPS x 0.24 USD= 960.00 USD/月2020年9月現在6
4,000 IOPS / 8KBブロックの読み込み速度1秒間 8KB × 4,000回 ≒ 32MB1分間 32MB × 60秒 ≒ 2GB7
ストレージ性能と最適化● 2GBのテーブルを読み込むには1分かかる、意外と遅い● 実際の読み込みでは様々な最適化がある○ DBバッファキャッシュ○ マルチブロックリード（※後ほど説明）● DBバッファキャッシュはメモリ上に確保されるキャッシュ○ メモリ読み込みはストレージよりも圧倒的に速い読み込み済のデータをメモリに保持するストレージ DBバッファキャッシュ8
DBバッファキャッシュ● OLTP（オンライントランザクション）で利用されるDBではDBバッファキャッシュのヒット率99%以上が普通● ヒット率が高いならストレージ性能は重要ではない？⇒ NO、たった1%のストレージ読込がボトルネックになる読込データ量の割合ストレージ読込 1%DBバッファキャッシュ99%9
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
読み込み速度比 ⇒ メモリ : ストレージ = 1,000 : 1キャッシュヒット率 ⇒ 99%読込時間と所要時間の関係読込データ量の割合ストレージ 1%DBバッファキャッシュ99%所要時間の割合ストレージ 91%DBバッファキャッシュ 9%11
● 汎用目的のDBは「I/Oバウンド」な状態が多い● CPUは休みながらストレージ読み込みを待って処理する● I/Oバウンドならば「ストレージ性能」≒「DBの処理性能」I/Oバウンドストレージは遅いCPUは速いがデータがこないストレージを待ちながら処理ストレージ性能 ≒ 処理速度12
CPUバウンド● 「CPUバウンド」になりやすいDBは○ データがすべてメモリに収まる（メモリ多い、データ少ない）○ 利用用途が限定的でキャッシュヒット率が高い● 結果としてパフォーマンス的な課題がさほどないメモリは速い CPUの速度に負けないスピードでデータが来る13
CPU性能について● 大抵のDBはI/Oバウンド、今回はI/OバウンドなDBを扱う● I/Oバウンドの場合、CPU性能はDBパフォーマンスにさほど影響しないCPU性能が良くてもヒマで寝てるだけ14
内部構造を知る15
なぜDBの内部構造の理解が必要か？● 内部構造を知らなくてもSQLを知ればデータを取り出せる● アプリケーションはSQLを介して、DBの内部構造と疎結合● 開発者はSQLを介して、DBの内部構造と疎結合● 開発にsqlite、本番でMySQLなどDB実装の切り替えも可能だけれども16
● パフォーマンスの領域では内部構造の理解が不可欠● DBは魔法の箱ではない、無理な処理をすれば時間がかかる● 例えば、順不同に並ぶ英和辞典で単語を探す○ 単語を見つけるまで最初のページから全て探す必要がある○ 荒唐無稽に聞こえるがDBでは意外とやりがち➢ 実はテーブルフルスキャンしてるSQL○ 見出し語のアルファベット順に並んでれば一瞬で探せる➢ DBでもインデックスがあれば速くなる○ DBの立場になって、その処理に無理がないか考えるなぜDBの内部構造の理解が必要か？英和辞典17順不同に並ぶ英和辞書
なぜDBの内部構造の理解が必要か？● 人は無意識に道具の内部構造を考えて最適な利用をする○ DBではこの仕事をオプティマイザが担当● フランスの旅行ガイドブックから「ラメールプラール」というオムレツ屋を探す1. モンサンミシェル⇒レストランとたどって探す2. 巻末索引から探す3. 最初から最後まで全ページ探す1. 2.索引巻末索引で探す地域を絞って探す18
● 旅行ガイドブックの構造はDBの構造に近い○ 本編・観光情報（⇒テーブルのデータ）○ 本編の掲載順番は決まりがない（⇒レコードの保存順）○ 名称から探すために巻末索引がある（⇒インデックス）○ 巻末索引には本編への参照がある（⇒ROWID ※後述）なぜDBの内部構造の理解が必要か？索引巻末に索引がある観光情報は順不同19
なぜDBの内部構造の理解が必要か？● 図書館で本を探す場合○ 蔵書のエリア配置・並び順を把握していて最短でたどり着く○ 情報端末で蔵書検索をする● たどり着き方は複数あり、どれが最適かは状況により変わる● DBは最適な方法を選択（=オプティマイザ）してくれる○ 開発者はDBの立場になって「どう処理するか？」を考える● DBとして最適は全体の最適ではない、開発者の手助けが必要○ そもそも必要のない処理である可能性○ さらに効率的な絞り込み条件を追加できる○ 運用を変更したら効率的な処理にできる○ バッチ処理だから処理速度が遅くてもよい● DBの内部構造を知らないと開発者はうまく手助けできない20
データブロックを理解する21
テーブルのデータは、ストレージの表領域のセグメントのエクステントのデータブロックに保存される ⇒知らなくてOKOracle DBのデータ構造表領域 セグメント表領域セグメントセグメント セグメントセグメント セグメントセグメント セグメントエクステント エクステントエクステント エクステントエクステント エクステントデータブロックストレージ22
● 覚えてもらいたいのは「データブロック」だけ● データブロックのサイズは選択できる○ 2KB, 4KB, 8KB, 16KB, 32KBなど● テーブルやインデックスの実データが入っている● データブロックがOracleにおけるI/Oの最小単位● DBバッファキャッシュへのキャッシュもデータブロック単位● 今回は8KBとするデータブロックデータブロックレコードデータ空き領域23
● テーブルの各レコードはデータブロックに保存される● データブロックはCREATE TABLE時に割り当てられ、空きデータブロックがなくなると、再度割り当てられる● 1つのデータブロックには複数レコード入るデータブロックデータブロックID Name Price Updated1 AA 100 2012-10-042 BB 53 2014-12-013 CC 500 2018-06-124 DD 12 2019-03-285 EE 212 2020-05-03... ... ... ...24
● データブロックのうち大部分はレコードデータに利用● カラム型や保存するデータによって消費サイズが異なる● VARCHAR2はバイト列分を消費、数値型も可変長● 数値カラムを9個持つテーブルで1行がおおよそ36byteなら○ 8KBのデータブロック1つには200レコード以上入る○ 1回のI/Oで200レコードも取得できるデータブロックデータブロック25数値カラムを9個ほど持つテーブルなら1ブロックに200レコード程度入る
● 1ブロックに200レコード入るテーブルを考える● 200万レコード取得する場合、200万 ÷ 200 = 1万ブロック● 4,000 IOPS なので、10,000 ブロック ÷ 4,000 IOPS = 2.5秒● （うまくいけば）2.5秒で200万レコード取得できそう● 具体的な数値で考えることが重要、おおよそで問題なし○ ただしマルチブロックリードならもっと速いデータブロックデータブロック261ブロックに200レコード入るとする
データブロックに関する問題Q: 注文でレコードが挿入される「注文テーブル」がある。「2019年5月の注文」と「全期間の電化製品ジャンルの注文」が共に約200万レコードだったとすると、それぞれの条件で200万レコード取得にかかる時間に差はあるか？（※「注文日」及び「ジャンル」にはインデックスがあるものとする）1. 「2019年5月の注文」の方が早く取得可能2. 「全期間の電化製品ジャンルの注文」の方が早く取得可能3. 両方ともほとんど差はない27
データブロックに関する問題A: 「2019年5月の注文」の方が早く取得可能これはなぜか？● レコード挿入は空きデータブロックに対して連続で行われる● その結果、同一タイミングで挿入されたレコードは同一のデータブロックに含まれやすい● 「注文日」と「レコード挿入タイミング」には相関があり同一ブロック内に条件に当てはまるデータが連続しやすい○ データブロック1つに200レコード含むなら、その大半が条件に当てはまるレコードとなりやすい○ 200万 ÷ 200 = 1万ブロック の取得で済む○ 分散していると200万ブロック取得が必要28
データブロックの分散● ジャンルで絞り込む場合は、テーブル全体にバラバラにレコードがちらばっている● 1データブロック取得しても、必要なレコードは数行しか含まれない、8KBのブロックのうち大半が必要のないレコード注文日での絞り込み必要なレコードが1つのブロック内に連続している200レコード利用200レコード利用200レコード利用ジャンルでの絞り込み必要なレコードが様々なブロックに分散しているそれぞれ数レコード利用29
● 使用可能ブロックは挿入可能、使用済みブロックは挿入不可● 空き領域が残り少なくなると使用済みブロックに変化する● DELETEなど、十分な空き領域ができると使用可能へ戻る同一ブロックにデータが集まる理由データブロック データブロック空き領域が十分ある場合は新しいレコードを入れる空き領域が少ない場合は新しいレコードを入れない空き領域空き領域30
使用可能/使用済みの状態遷移使用可能31使用可能 使用済みINSERT使用済みINSERTUPDATEDELETE使用済みDELETEINSERT使用可能INSERT空き領域が一定未満になるまでは使用可能INSERTINSERT INSERTINSERTINSERT十分空きができるまでは使用済みのまま① ② ③④⑤⑥
データブロックサイズの考察● データが分散している場合○ 各データブロックに必要なレコードが数行しか含まれない○ 沢山のデータブロックを取得する必要がある○ それならば、ブロックサイズを小さくした方が有利？➢ 大抵の場合はNO、なぜならば● 一般的なDBストレージ（SAS/iSCSI/FCなど）はブロックサイズを変えてもIOPSはさほど変化しない○ 4KB ⇒ 2KB IOPSにさほど影響なし○ 8KB ⇒ 16KB IOPSが1～2割程度の減少（あくまで一例）32
ブロックサイズとIOPSとスループット● 厳密な数値は考えず、おおまかに理解する● ブロックサイズが 8KB ⇒ 16KB に変化した場合、IOPSは2割減少すると考える 4,000 IOPS ⇒ 3,200 IOPS● スループットはブロックサイズが大きい方が有利16KB8KB 4,000IOPSブロックサイズ IOPS3,200IOPS32MB/秒51MB/秒スループット332倍 0.8倍 1.6倍
大きいブロックサイズの考察● ブロックサイズが大きいと IOPS は若干減少する● 1レコードだけ取得する場合は不利○ 巨大ブロックを読み込んでも、利用するのは1レコードだけ○ INDEX UNIQUE SCAN ⇒ TABLE ACCESS BY INDEX ROWIDたった1レコードのためにブロック全体を取得するデータブロック大半のデータは読み込んでも使わない34
大きいブロックサイズの考察● ブロック内で連続するレコードを取得する場合は有利○ 日付範囲などは必要なレコードがブロック内で連続しやすい○ 同時期に挿入されたレコードは同一ブロック内に固まりやすい○ ブロック読込回数が減り、スループットが高くなるデータブロック取得したブロック内のデータはほとんどが必要なレコード35
● DBバッファキャッシュの無駄が多い○ キャッシュするのはデータブロック単位○ 1つが大きいためキャッシュできるデータブロック数が少ない大きいブロックサイズの考察データブロック データブロックDBバッファキャッシュ1ブロックが大きいため入るブロック数が少ない36
小さいブロックサイズの考察● ブロックサイズが小さいと IOPS は多少増加する● 1レコードだけ取得する場合は有利○ 読み込んだけど利用しないデータが最小○ INDEX UNIQUE SCAN ⇒ TABLE ACCESS BY INDEX ROWIDデータブロックブロックが小さいため無駄が少ない37
小さいブロックサイズの考察● ブロック内で連続するレコードを取得する場合は不利○ 日付範囲などは必要なレコードがブロック内で連続しやすい○ 同時期に挿入されたレコードは同一ブロック内に固まりやすい○ ブロック読込回数が増え、スループットが低くなるデータブロックブロックが小さいため沢山のブロック読込が必要38
● フルスキャンの場合だけ、スループットを高くできる○ マルチブロックリード機能で複数ブロックを同時取得○ TABLE ACCESS FULL, INDEX FAST FULL SCAN○ しかしフルスキャンが許されるのはバッチ処理くらい小さいブロックサイズの考察複数ブロックを1回のI/Oで取得するので速いデータブロックざくっと一気にマルチブロックリード狙ったブロックを１つずつ通常リード 39
● DBバッファキャッシュの無駄が少ない○ キャッシュするのはデータブロック単位○ 小さいため沢山のデータブロックをキャッシュ可能小さいブロックサイズの考察DBバッファキャッシュデータブロック データブロック データブロック データブロックデータブロック データブロック データブロック データブロックデータブロック データブロック データブロック データブロックデータブロックデータブロックデータブロックデータブロックデータブロックデータブロック1ブロックが小さいため沢山のブロックが入る40
ブロックサイズはなにが正解か？● ブロックサイズは表領域単位で変更できる、可能ならばテーブルごとにチューニングするのがベスト● しかし現実的には難易度が高い、実施コストが高くなりがち● 開発者としてはDBのブロックサイズを把握して、それに合わせたチューニングを行うのがベター○ 構造的にパフォーマンスがでないからバッチ処理にする○ 1ページで取得するアイテム数を減らして速度をだすス○ーピー「配られたカードで勝負するしかないのさ」孫子「彼を知り己を知れば百戦殆うからず」41
ROWIDとは何か42
ROWIDとは● ROWIDはDBストレージ内の住所のようなもの● データブロックとブロック内の位置などを特定可能● ROWIDが分かれば1回のI/Oで必要なレコードを取得できる必要なデータブロックの場所を特定できる膨大な数のデータブロック 43
ROWIDの構造● 「AAAVREAAEAAAACXAAA」 という形式○ 6桁: AAAVRE ⇒ データオブジェクト番号○ 3桁: AAE ⇒ 相対ファイル番号○ 6桁: AAAACX ⇒ データブロック番号○ 3桁: AAA ⇒ 行番号● dbms_rowidパッケージで解析できるselectrowid,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.enamefromscott.emp;44
● scott.emp テーブルのROWIDを解析してみる● 各レコードはすべてファイル番号4、データブロック番号151に入っていることが分かる● このテーブルはフルスキャンに必要なI/O回数が1回と分かるROWIDの解析例ROWID DATA_OBJECT_ID RELATIVE_FNO BLOCK_NUMBER ROW_NUMBER EMPNO ENAMEAAAVREAAEAAAACXAAA 87108 4 151 0 7369 SMITHAAAVREAAEAAAACXAAB 87108 4 151 1 7499 ALLENAAAVREAAEAAAACXAAC 87108 4 151 2 7521 WARDAAAVREAAEAAAACXAAD 87108 4 151 3 7566 JONESAAAVREAAEAAAACXAAE 87108 4 151 4 7654 MARTINAAAVREAAEAAAACXAAF 87108 4 151 5 7698 BLAKEAAAVREAAEAAAACXAAG 87108 4 151 6 7782 CLARKAAAVREAAEAAAACXAAH 87108 4 151 7 7788 SCOTTAAAVREAAEAAAACXAAI 87108 4 151 8 7839 KINGAAAVREAAEAAAACXAAJ 87108 4 151 9 7844 TURNERAAAVREAAEAAAACXAAK 87108 4 151 10 7876 ADAMSAAAVREAAEAAAACXAAL 87108 4 151 11 7900 JAMESAAAVREAAEAAAACXAAM 87108 4 151 12 7902 FORDAAAVREAAEAAAACXAAN 87108 4 151 13 7934 MILLER45
ROWIDはどこで使われるか● テーブルはヒープ表と呼ばれる● レコードは任意のブロックに格納される● 各レコードはROWIDによって格納位置を識別できる● では、ROWIDはなにに使われるのか？各レコードはROWIDを持つデータブロックID Name Price Updated1 AA 100 2012-10-042 BB 53 2014-12-013 CC 500 2018-06-124 DD 12 2019-03-285 EE 212 2020-05-03... ... ... ...46
● ROWIDの主な利用用途はインデックス● B+tree構造で「インデックス対象のデータ値」と「レコードへのROWID（参照）」を持つROWIDはどこで使われるか0..4041..8081..2500..1011..2526..4041..5152..8081..121122..230231..2500,ACXAAA3,ADXAAC…10,ADXABG11,ADXAAD16,AEAAAA…25,AEBAAB231,AGXAAC232,AHAAAB…250,AHBAAK・・・・・・・・・ブランチブロックリーフブロックデータ値とROWIDを持つ47
● 例えば ID=232 のレコードを探す場合を考える○ ブランチブロックを2つたどる ⇒ リーフブロックでROWIDを取得 ⇒ ROWIDでテーブルのレコードを取得ROWIDはどこで使われるか0..4041..8081..2500..1011..2526..4041..5152..8081..121122..230231..2500,ACXAAA3,ADXAAC…10,ADXABG11,ADXAAD16,AEAAAA…25,AEBAAB231,AGXAAC232,AHAAAB…250,AHBAAK・・・・・・・・・ブランチブロックリーフブロックこのROWIDでテーブルからレコードを取得48
プライマリキーでレコード取得● プライマリキーでレコードを取得する例○ インデックスのブランチノード取得 × 2回○ インデックスのリーフノード取得（ROWIDを取得）○ ROWIDからテーブルのレコード取得➢ 計4回程度I/Oのラウンドトリップ、遅くないけど速くもない● テーブルからデータを直接取得する場合○ マルチブロックリード機能、高いスループットで取得可能0..4041..8081..2500..1011..2526..4041..5152..8081..121122..230231..2500,ACXAAA3,ADXAAC…10,ADXABG11,ADXAAD16,AEAAAA…25,AEBAAB231,AGXAAC232,AHAAAB…250,AHBAAK・・・・・・・・・3 CC 500 2018-06-12インデックス走査レコード取得49
● それぞれ10,000レコード取得に必要なコストを考える● インデックス走査とレコード取得が10,000レコード分必要● ブランチノードはキャッシュのヒット率が高いテーブル直接とインデックス経由のレコード取得0..4041..8081..2500..1011..2526..4041..5152..8081..121122..230231..2500,ACXAAA3,ADXAAC…10,ADXABG11,ADXAAD16,AEAAAA…25,AEBAAB231,AGXAAC232,AHAAAB…250,AHBAAK・・・・・・・・・3 CC 500 2018-06-12インデックス走査レコード取得10,000回インデックス経由で10,000レコード取得する場合50
● テーブルの場合はマルチブロックリード機能で複数ブロック・複数レコードをまとめて取得可能● ただしテーブル内の全行取得が必要● どちらが速いかは状況次第○ 全体で5万行程度なら？ブロックの分散状況は？○ 1レコードが巨大なら？逆に小さいなら？● 時間経過でレコードが増加するならインデックス利用が必須○ マルチブロックリードが速くてもいつかは破綻するテーブル直接とインデックス経由のレコード取得テーブルから直接10,000レコード取得する場合ID Name Price Updated1 AA 100 2012-10-042 BB 53 2014-12-013 CC 500 2018-06-124 DD 12 2019-03-285 EE 212 2020-05-03... ... ... ...1回のI/Oで複数行取得できる51
MySQL(InnoDB)との比較52
なぜ他のDBを知る必要があるのか● プロダクトとして長期間生き残り利用されているDBはそれだけの間エンジニア達によって磨かれている● 各DBの構造は違いがあり速度特性も異なる、ただそれは特徴の違いであり、どちらかが圧倒的に優れているわけではない● いかなるDBも銀の弾丸で高速化されているわけではない、速いのには理由があるし、遅いのにも必ず理由がある● 各DBの構造の違いを知ることは、チューニングにおける限界点を理解する助けになる● 卓越したエンジニア達が磨いてきたプロダクトには「存在する理由」がある53
● InnoDBのテーブルはクラスタインデックス構成● テーブル全体がB+treeインデックス構造になっていて、レコードのデータをリーフページに持つMySQL(InnoDB)との比較0..4041..8081..2500..1011..2526..4041..5152..8081..121122..230231..2500,AA,100,20123,BB,120,2019…10,CC,99,202011,DD,210,200916,EE,180,2010…25,FF,510,1980231,KK,615,2015232,LL,880,2002…250,PP,980,1995・・・・・・・・・非リーフページリーフページインデックス構造でレコードデータも持つ54
● プライマリキーでレコード取得する場合はインデックス走査だけでレコードも取得できて有利● Oracleの場合はROWIDからレコードを取得する一手間が必要MySQL(InnoDB)との比較0..4041..8081..2500..1011..2526..4041..5152..8081..121122..230231..2500,AA,100,20123,BB,120,2019…10,CC,99,202011,DD,210,200916,EE,180,2010…25,FF,510,1980231,KK,615,2015232,LL,880,2002…250,PP,980,1995・・・・・・・・・非リーフページリーフページインデックス走査だけでレコードデータも取得可能55
● InnoDBにはROWIDにあたるものがない● セカンダリインデックス（プライマリキー以外のインデックス）は、レコードのポインタとしてプライマリキー値を持つ● セカンダリインデックスはインデックス走査が2回必要、ROWIDでダイレクトに取得できるOracleよりも遅い● プライマリキーのデータサイズが大きいと無駄が多くなるInnoDBにおけるセカンダリインデックス0..4041..8081..2500..1011..2526..4041..5152..8081..121122..230231..2500,ACXAAA3,ADXAAC…10,ADXABG11,ADXAAD16,AEAAAA…25,AEBAAB231,AGXAAC232,AHAAAB…250,AHBAAK・・・・・・・・・セカンダリインデックス走査（プライマリキー値の取得）0..4041..8081..2500..1011..2526..4041..5152..8081..121122..230231..2500,ACXAAA3,ADXAAC…10,ADXABG11,ADXAAD16,AEAAAA…25,AEBAAB231,AGXAAC232,AHAAAB…250,AHBAAK・・・・・・・・・クラスタインデックス走査（レコードの取得）56
● クラスタインデックスのため、テーブルフルスキャンはプライマリキー順に取得される ⇒ 開発者に優しい● Oracleと比べてチューニングの余地は制限されるMySQL(InnoDB)との比較0..4041..8081..2500..1011..2526..4041..5152..8081..121122..230231..2500,AA,100,20123,BB,120,2019…10,CC,99,202011,DD,210,200916,EE,180,2010…25,FF,510,1980231,KK,615,2015232,LL,880,2002…250,PP,980,1995・・・・・・・・・先頭から順に取得57
レコードサイズとパフォーマンス58
1レコードのサイズ● レコードのデータはデータブロックに格納される● ブロックサイズは2～32KB、数値や日付データなら十分大きいが、文字列を入れると意外と小さい● 商品説明カラムに4000byte割り当てた items テーブルでは、8KBのブロックに2レコード程度しか入らないデータブロック 59巨大な1レコード巨大な1レコード
1レコードのサイズ● 1レコードのサイズを考慮してパフォーマンスを考える● レコードサイズが大きいと大量レコード取得で遅い● ROWIDで1レコード取得するコストはさほど変わらない○ ブロックが分散した10,000レコード取得は？○ ブロック内に連続した10,000レコードの取得は？巨大な1レコード巨大な1レコード1レコード 大きい 1レコード 小さい60
1レコードのサイズを設計● 絞り込み/並び替えのカラムを小さいテーブルに切り出す● レコードサイズを小さく設計、テーブルサイズが小さいと様々なシーンでパフォーマンスを出しやすい巨大な1レコード巨大な1レコード1レコード 大きい 1レコード 小さい読み込むレコードが多い⇒ 読み込むブロック数が多い⇒ 遅い必要なカラムを小さいテーブルに切り出す61読み込むレコードが多い⇒ 読み込むブロック数は少ない⇒ 速い
レコードの分散62
● 題材としてフォロー型のSNSを考える● フォロー関係は N:N となる● あるユーザーのフォロワーの一覧を取得するコストは？データの分散状況とパフォーマンス63FOLLOWEE_ID FOLLOWER_ID CREATED_ON... ... ...256 284 2007-09-05255 544 2007-09-05440 729 2007-09-05591 932 2007-09-05139 926 2007-09-05289 460 2007-09-05350 64 2007-09-0568 397 2007-09-05902 581 2007-09-05666 45 2007-09-05978 260 2007-09-05696 208 2007-09-05... ... ...フォローテーブルフォローした人フォローされた人
● フォロー関係は時間と共に徐々に増える● 1ユーザーで絞ると各レコードのブロックは分散している● フォロワーが10万人、10万レコードが7～8万ブロックに分散○ 8万ブロック ⇒ 4,000 IOPS なら20秒かかるデータの分散状況とパフォーマンスフォローテーブル (FOLLOWEE_ID = 350)FOLLOWEE_ID FOLLOWER_ID CREATED_ON... ... ...350 468 2004-08-22350 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
● 分散したレコードをDELETEしてINSERTするだけでもブロックはまとまる● ブロックの分散が解消するとI/O回数は激減するデータの分散とパフォーマンスフォローテーブルFOLLOWEE_ID FOLLOWER_ID CREATED_ON... ... ...350 284 2004-08-22350 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-22350 544 2017-03-27350 932 2008-04-08350 460 2018-08-24350 64 2007-09-05350 45 2019-06-05350 208 2005-05-01... ... ...フォローテーブルDELETE/INSERT10万レコード8万ブロック ⇒ 20秒※1.25レコード/ブロック10万レコード400ブロック ⇒ 0.1秒※250レコード/ブロック65
10年後を考える66
DB設計のライフサイクル● システムのライフサイクルは部品ごとに異なる● アプリケーションプログラムは数年でリプレースを検討する● DB設計はそれより長い間使われる、ライフサイクルが長い● 今作ったテーブルが10年後も使われる想定での設計が必要FOLLOWEE_ID FOLLOWER_ID CREATED_ON... ... ...256 284 2007-09-05255 544 2007-09-05440 729 2007-09-05591 932 2007-09-05139 926 2007-09-05289 460 2007-09-05350 64 2007-09-0568 397 2007-09-05902 581 2007-09-05666 45 2007-09-05978 260 2007-09-05696 208 2007-09-05... ... ...FOLLOWEE_ID FOLLOWER_ID CREATED_ON25 28 2000-09-0525 54 2000-09-0544 72 2000-09-0559 93 2000-09-0513 92 2000-09-0528 46 2000-09-05... ... ...数千行だったテーブル数億行になってもパフォーマンスを維持できるか？10年後67
DB設計のライフサイクル● 大規模テーブルのフルスキャンは原則として行わない○ レコード数と処理速度を比例関係にしない○ レコード数nに対して、計算量が O(n) ならいずれ破綻する68レコード数 n計算量=処理速度O(n2)O(n)O(log n)O(1)
DB設計のライフサイクル● 処理対象のレコードを限定できる設計を導入○ 履歴レコードは参照範囲を過去1年に限定など■ 範囲内のレコードはDBバッファキャッシュに含む前提■ ブロック分散に気をつける○ 大規模なソートを排除、order by狙いのインデックスを利用■ ソートには対象の全レコードが必要（＝フルスキャン）■ CPU・メモリだけを消費するソートは許容できる○ 生きてるレコードと死んだレコードを区別する■ 死んだレコードを処理速度に影響させないなどなど69
まとめ70
まとめ今回はOracle データベースの内部構造に着目して「遅い理由」「速い理由」を考えるための基礎を学んでみましたどんな事象にも必ず理由があります仕組みを知り、バックグラウンドを把握してそれではじめてベストな方法を見つけだすことができます71
forrep
faithandbrave
zakky21
niftycorp
tanakahisateru
hitsuji1991
deepu105
twada
fwbrasil
qnighy
akkyie
kinocoboy2
ivargrimstad
jlugia
eileencodes
sachag
holman
tenderlove
denniskardys
schacon
caitiem20
ufuk
morganepeng
sferik
robhawkes