今さら聞けない!?大規模テーブルのパフォーマンスチューニング ～パーティショニング～

Transcription

1 Oracle Direct Seminar <Insert Picture Here> 今さら聞けない!? 大規模テーブルのパフォーマンスチューニング ~ パーティショニング ~ 日本オラクル株式会社

2 Agenda 大規模テーブル運用の管理課題 パーティショニングとは? パーティショニングのメリット ケーススタディー Oracle Partitioning 2

3 大規模テーブル運用の問題点 1. パフォーマンスの低下 データ量が増えると検索が遅くなる 2. 管理作業が大変 バックアップやデータのローディングに時間がかかる 3. 障害 / 保守時の影響が大きい 障害やメンテナンスの際 表の全てのデータにアクセスができない 3

4 大規模テーブル運用の問題点 問題点 1: : パフォーマンスの低下 データ量増加に伴い検索が非常に遅くなる売上表 Select 四半期ごとの売上データを集計したい 結果 4

5 大規模テーブル運用の問題点 問題点 1: : パフォーマンスの低下 データ量増加に伴い検索が非常に遅くなる 売上表 Select 四半期ごとの売上データを集計したい データ量が増加! 結果 まったく同じ Select 文を投げたとしても データ量によって結果が返ってくる時間が著しく異なる 5

6 大規模テーブル運用の問題点 FULL SCAN で遅いならば索引を 使えばよいのでは? 索引は万能ではない! Select 四半期ごとの売上データを集計したい 索引 売上表 6

7 大規模テーブル運用の問題点 FULL SCAN で遅いならば索引を 使えばよいのでは? 索引は万能ではない! Select 四半期ごとの売上データを集計したい 結果 索引 売上表 7

8 大規模テーブル運用の問題点 大量の索引読み込み + 大量データアクセスは非効率 Select DISK I/O が大量に発生 四半期ごとの売上データを集計したい 索引が選択されない 可能性が高い 一定の範囲を検索する場合 索引を使用すると逆に遅くなってしまう場合もある 索引 売上表 8

9 大規模テーブル運用の問題点 問題点 2: : 管理作業が大変 索引の再構築に時間がかかる! (INSERT, DELETE, UPDATE) 本日の業務処理を夜間に更新 表 索引索引の再構築 索引のフラグメンテーションが発生 9

10 大規模テーブル運用の問題点 問題点 2: : 管理作業が大変 索引の再構築に時間がかかる! (INSERT, DELETE, UPDATE) 本日の業務処理を夜間に更新 表 索引 索引の再構築 夜の間に夜間バッチ処理が完了しない!! 索引のフラグメンテーションが発生 データ量が多いと 索引の再構築に長時間かかってしまう 10

11 大規模テーブル運用の問題点 問題点 2: : 管理作業が大変 バックアップやリカバリに時間がかかる! バックアップ バックアップリカバリ リストア 11

12 大規模テーブル運用の問題点 問題点 2: : 管理作業が大変 バックアップやリカバリに時間がかかる! バックアップ バックアップリカバリ 障害復旧がシステム要件の時間内に完了しない!! データ量が多いと バックアップやリカバリに長時間かかってしまう リストア データ量が増加! 12

13 大規模テーブル運用の問題点 問題点 3: : 障害 / 保守時の影響が大きい 破損範囲がごく一部でも表すべてが閲覧不可になる データ破損が発生!! 13

14 Agenda 大規模テーブル運用の管理課題 パーティショニングとは? パーティショニングのメリット ケーススタディー Oracle Partitioning 14

15 パーティショニングとは? 大きな表や索引をデータベース内部で 複数の領域に分割して管理する 通常の 1 つの表 パーティション化された表 4-6 月 1-3 月 月 7-9 月 内部的に表を分割 ユーザやアプリケーションからは一つの表に見える 15

16 このような売上表をパーティショニングすると 売上日 製品 支店 2007/04/03 液晶テレビ 丸の内 2007/04/03 液晶テレビ 丸の内 2007/04/04 炊飯器 神奈川 2007/04/05 携帯電話 多摩 顧客名 井上憲三郎 椛田正臣 藤田佳子 井上あつし 売上 83,620,000 43,380,000 96,320,000 43,620, /06/26 腕時計 丸の内 松沢惇 155,490, /12/31 電気ポット 埼玉 影山治子 265,420, /01/01 携帯電話 新潟 山本健 35,830,000 16

17 売上日をキーとしてパーティショニングします 売上日製品支店 顧客名 売上 2007/04/03 液晶テレビ 丸の内 井上憲三郎 83,620, /04/03 液晶テレビ 丸の内 椛田正臣 43,380, /04/04 炊飯器 神奈川 藤田佳子 96,320, /04/05 携帯電話 多摩 井上あつし 43,620, /06/26 腕時計 丸の内 松沢惇 155,490, /12/31 電気ポット 埼玉 影山治子 265,420, /01/01 携帯電話 新潟 山本健 35,830,000 17

18 DB 内部で分割して管理します 2008/01/01 携帯電話新潟山本健 35,830, /01/07 携帯電話丸の内 2007/02/13 液晶テレビ丸の内 2007 年 Q1 売上日製品支店顧客名売上 2007/04/03 液晶テレビ丸の内井上憲三郎 83,620,000 売上日 製品 支店 2007/04/03 液晶テレビ丸の内椛田正臣 43,380, /08/03 携帯電話 丸の内 2007/04/04 炊飯器神奈川藤田佳子 96,320, /09/03 液晶テレビ 丸の内 2007/04/05 携帯電話 多摩 井上あつし 43,620, /09/10 炊飯器 埼玉 2007 年 Q2 2007/06/26 腕時計丸の内松沢惇 155,490, /12/31 電気ポット 埼玉 影山治子 265,420,000 売上日 製品 支店 売上日 2007/04/ /04/ /04/04 液晶テレビ 液晶テレビ 炊飯器 製品 2007/03/20 炊飯器 2007 年 Q4 アプリケーションから見たら 支店 丸の内 丸の内 神奈川 埼玉 顧客井上憲三郎椛田正臣藤田佳子顧客井上あつし田中大輔千葉由顧客影山治子山本健林順 売上 83,620,000 43,380,000 96,320,000 売上 83,620,000 43,380,000 96,320,000 売上 83,620,000 43,380,000 96,320,000 1 つの表ですが 内部的には表を分割して管理します 18

19 Agenda 大規模テーブル運用の管理課題 パーティショニングとは? パーティショニングのメリット ケーススタディー Oracle Partitioning 19

20 パーティショニングのメリット 1. パフォーマンスの低下 データ量が増えると検索が遅くなる パーティションプルーニングで解決!! 2. 管理作業が大変 バックアップやデータのローディングに時間がかかる 3. 障害 / 保守時の影響が大きい 障害やメンテナンスの際 表の全てのデータにアクセスができない 20

21 パーティション プルーニング 対象のデータが格納されているパーティションだけにアクセスし 不要なパーティションを読み飛ばす 今期 (Q4) の売上の平均値を見たい Q1 (sales_date) Q2 (sales_date) オプティマイザ Q3 (sales_date) SELECT area, period, avg(sales_rev) FROM sales_history WHERE sales_date in Q4 GROUP BY area, period Q4 (sales_date) 21

22 パーティションが有効なテーブルサイズ 1 回あたりの検索処理時間 日本 HP 社との共同検証結果より 1GB のパーティション テーブルの検索処理時間を 1 とした場合の相対処理時間 ( 実際の処理時間に任意の数を掛けています ) Partition Non-Partition 4GB GB GB 以上なら非常に有効! 1GB 時間 150,000 レコード検索の結果 22

23 パーティショニングのメリット 1. パフォーマンスの低下解決 データ量が増えると検索が遅くなる パーティションプルーニングで解決!! 2. 管理作業が大変 バックアップやデータのローディングに時間がかかる パーティション単位での管理で解決!! 3. 障害 / 保守時の影響が大きい 障害やメンテナンスの際 表の全てのデータにアクセスができない 23

24 管理作業もパーティション単位 索引の再構築や統計情報の取得もパーティション単位! 作業中も他のパーティションは影響を受けない! パーティション単位で更新処理ができる! 7 月 15 日分の更新 索引再構築 7 月 6 月 他のパーティションは影響を受けない!! 5 月 4 月 24

25 管理作業もパーティション単位 データの大量挿入 / 削除はシステム全体のパフォーマンスに悪影響 大規模なテーブルで時間がかかっていた データの更新 / 削除などをパーティション単位で行う事で高速化 古いパーティションの高速な削除!! パーティションごとに削除が可能!! 条件指定の DELETE とは違い REDO/UNDO の生成が無く 高速な削除が可能!! Q1 Q2 Q3 Q4 25

26 管理作業もパーティション単位 データの大量挿入 / 削除はシステム全体のパフォーマンスに悪影響 大規模なテーブルで時間がかかっていた データの更新 / 削除などをパーティション単位で行う事で高速化 テーブルのメンテナンス作業が早い!! 古いパーティションの高速な削除!! New Q1 Q1 パーティションごとに削除が可能!! 条件指定の DELETE とは違い REDO/UNDO の生成が無く 高速な削除が可能!! New Q1 Q2 Q3 新しいパーティションに高速で入れ替え!! Q4 26

27 パーティショニングのメリット 1. パフォーマンスの低下解決 データ量が増えると検索が遅くなる パーティションプルーニングで解決!! 2. 管理作業が大変解決 バックアップやデータのローディングに時間がかかる パーティション単位での管理で解決!! 3. 障害 / 保守時の影響が大きい 障害やメンテナンスの際 表の全てのデータにアクセスができない パーティション単位で障害の影響を限定!! 27

28 パーティション単位で障害の影響を限定 特定のパーティションに障害が発生しても 他のパーティションは影響を受けない パーティション単位でリカバリを行えるので 障害から短時間で復旧できる Q1~Q3 のデータは リカバリ中でも通常通りアクセスできる Q1 Q2 パーティション単位でリカバリ実行 Q3 Q4 障害発生 Q4 28

29 パーティショニングのメリット 1. パフォーマンスの低下解決 データ量が増えると検索が遅くなる パーティションプルーニングで解決!! 2. 管理作業が大変解決 バックアップやデータのローディングに時間がかかる パーティション単位での管理で解決!! 解決 3. 障害 / 保守時の影響が大きい 障害やメンテナンスの際 表の全てのデータにアクセスができない パーティション単位で障害の影響を限定!! 29

30 パーティショニングの種類 レンジパーティションレンジパーティション 日付でデータを管理したい 特定の連続するデータ ( 売上日 ログ取得日 etc) の範囲によって分割 リストパーティションリストパーティション 支店別 製品別など特定のカテゴリーごとにデータを管理したい 特定のデータ ( 製品名 店舗名 etc) のカテゴリーによって分割 ハッシュパーティションハッシュパーティション ディスクアクセスを均等化させ パフォーマンスを向上させたい パーティション表を使ってバッチ処理を高速化させたい 一意となるデータ ( 社員 ID 商品 ID etc) でデータを分散させて分割 詳しくは Oracle Direct Seminar 現場で使えるパーティションの極意!!~ 機能詳細編 ~ を御受講ください 30

31 パーティショニングの種類 レンジパーティション リストパーティション 日付などの連続するデータで分割 地域などデータのカテゴリーで分割 売上表 2007 年 1~3 月 (orderdate) 2007 年 4~6 月 (orderdate) Sales_p1 Sales_p2 売上表 東京神奈川 大阪京都 関東パーティション近畿パーティション 2007 年 7~9 月 (orderdate) Sales_p3 福岡長崎 九州パーティション 2007 年 10~12 月 (orderdate) Sales_p4 宮城青森 東北パーティション ハッシュパーティション ハッシュ値 1 一意となるデータ ( 社員 ID 商品 ID など ) でデータを分散させて分割 ハッシュ値 2 ハッシュ値 3 ハッシュ値 4 OLTP などの大量検索 & 更新に有効 詳しくは Oracle Direct Seminar 現場で使えるパーティションの極意!!~ 機能詳細編 ~ を御受講ください 31

32 パーティショニングの種類 コンポジットパーティション 基本のパーティションを組み合わせる事も可能 コンポジットレンジリストパーティション コンポジットレンジハッシュパーティション リスト サブパーティション A支店 B支店 C支店 ハッシュ サブパーティション D支店 レンジ パーティション レンジ パーティション 2008年 1 3月 2008年 1 3月 (date) (date) 2007年 10 12月 2007年 10 12月 (date) (date) 2007年 7 9月 2007年 7 9月 (date) (date) 2007年 4 6月 2007年 4 6月 (date) ハッシュ 値1 ハッシュ 値2 ハッシュ 値3 ハッシュ 値4 (date) 詳しくは Oracle Direct Seminar 現場で使えるパーティションの極意!! 機能詳細編 を御受講ください 32

33 Agenda 大規模テーブル運用の管理課題 パーティショニングとは? パーティショニングのメリット ケーススタディー Oracle Partitioning 33

34 ケーススタディー システムログを管理しているのだが 問題がたくさんあって 課題 テーブルデータが全体で 10GB ログデータのローディングに時間がかかりすぎる 検索の性能が出ない 対策 日付 + 支店名でのパーティショニング レンジリストコンポジットパーティションを採用 34

35 ケーススタディー システムログを管理しているのだが 問題がたくさんあって 課題 テーブルデータが全体で 10GB ログデータのローディングに時間がかかりすぎる 検索の性能が出ない 対策 日付 + 支店名でのパーティショニング レンジリストコンポジットパーティションを採用 ローディング時間の改善 遅い!! 6 月 22 日分ローディング 35

36 ケーススタディー システムログを管理しているのだが 問題がたくさんあって 課題 テーブルデータが全体で 10GB ログデータのローディングに時間がかかりすぎる 検索の性能が出ない 対策 日付 + 支店名でのパーティショニング レンジリストコンポジットパーティションを採用 ローディング時間の改善 パーティション単位でローディングしたことでかなり早くなった!! 6 月 22 日 A 支店ローディング 6 月 22 日 B 支店 6 月 22 日分ローディング 2007/06/ /06/ /06/24 A 支店 B 支店 C 支店 D 支店 6 月 22 日 C 支店ローディング 2007/06/25 現在 36

37 ケーススタディー システムログを管理しているのだが 問題がたくさんあって 課題 テーブルデータが全体で 10GB ログデータのローディングに時間がかかりすぎる 検索の性能が出ない 対策 日付 + 支店名でのパーティショニング レンジリストコンポジットパーティションを採用 検索時間の改善 遅い!! 6 月 22 日の A 支店の履歴は? 37

38 ケーススタディー システムログを管理しているのだが 問題がたくさんあって 課題 テーブルデータが全体で 10GB ログデータのローディングに時間がかかりすぎる 検索の性能が出ない 対策 日付 + 支店名でのパーティショニング レンジリストコンポジットパーティションを採用 検索時間の改善 A 支店 B 支店 C 支店 D 支店 範囲指定の検索がかなり早くなった!! 6 月 22 日の A 支店の履歴は? 2007/06/ /06/ /06/ /06/25 現在 38

39 パーティショニング採用事例 Oracle Partitioning 多くの企業がパーティショニングの採用により 処理速度の向上 管理コスト削減 可用性向上を実現! 事例の詳細については以下をご覧ください 39

40 まとめ 大規模テーブルは管理が大変! パーティション単位の管理で容易に! 検索スピードの高速化 メンテナンス作業の短時間化 Oracle Partitioning 可用性の向上 多くの採用事例 40

41 詳しい説明 システム導入のご相談は Oracle Direct まずはお問合せください

42 本文書は 日本オラクル Oracle Direct 主催の Oracle Direct Seminar のために作成された資料です 本文書の著作権は 日本オラクル株式会社に帰属しています 本文書を第三者に配布することはご遠慮下さい 本文書はあくまでも参考資料であり 掲載されている情報は予告なしに変更されることがあります Oracle Oracle8i Oracle9i Oracle10g はオラクル社の登録商標 または商標です 本文書で使用している製品やサービス名の名称は 各社の商標または登録商標です 42