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

Oracle Direct Seminar
<Insert Picture Here>
今さら聞けない!? 大規模テーブルのパフォーマンスチューニング
∼パーティショニング∼
日本オラクル株式会社
Agenda
• 大規模テーブル運用の管理課題
• パーティショニングとは？
• パーティショニングのメリット
• ケーススタディー
Oracle Partitioning
Copyright© 2007, Oracle. All rights reserved.
2
大規模テーブル運用の問題点
1. パフォーマンスの低下
データ量が増えると検索が遅くなる
2. 管理作業が大変
バックアップやデータのローディングに時間がかかる
3. 障害/保守時の影響が大きい
障害やメンテナンスの際、表の全てのデータにアクセスができない
Copyright© 2007, Oracle. All rights reserved.
3
大規模テーブル運用の問題点
問題点1：パフォーマンスの低下
問題点1
• データ量増加に伴い検索が非常に遅くなる
売上表
Select
四半期ごとの売上
データを集計したい
結果
Copyright© 2007, Oracle. All rights reserved.
4
大規模テーブル運用の問題点
問題点1：パフォーマンスの低下
問題点1
• データ量増加に伴い検索が非常に遅くなる
売上表
Select
四半期ごとの売上
データを集計したい
結果
データ量が増
加！
まったく同じSelect文を投げたとしても、
データ量によって結果が返ってくる時間が著しく異なる
Copyright© 2007, Oracle. All rights reserved.
5
大規模テーブル運用の問題点
FULL SCANで遅いならば索引を
SCANで遅いならば索引を
使えばよいのでは？
• 索引は万能ではない！
Select
四半期ごとの売上
データを集計したい
索引
Copyright© 2007, Oracle. All rights reserved.
売上表
6
大規模テーブル運用の問題点
FULL SCANで遅いならば索引を
SCANで遅いならば索引を
使えばよいのでは？
• 索引は万能ではない！
Select
結果
四半期ごとの売上
データを集計したい
索引
Copyright© 2007, Oracle. All rights reserved.
売上表
7
大規模テーブル運用の問題点
• 大量の索引読み込み＋
大量データアクセスは非効率
DISK I/Oが大量に発生
I/Oが大量に発生
Select
索引が選択されない
可能性が高い
四半期ごとの売上
データを集計したい
一定の範囲を検索する場合、
索引を使用すると
逆に遅くなってしまう場合もある
Copyright© 2007, Oracle. All rights reserved.
索引
売上表
8
大規模テーブル運用の問題点
問題点2：管理作業が大変
問題点2
• 索引の再構築に時間がかかる！
表
（INSERT, DELETE, UPDATE）
索引
索引の再構築
本日の業務処理を
夜間に更新
索引のフラグメンテーションが発生
Copyright© 2007, Oracle. All rights reserved.
9
大規模テーブル運用の問題点
問題点2：管理作業が大変
問題点2
• 索引の再構築に時間がかかる！
表
索引
索引の再構築
（INSERT, DELETE, UPDATE）
本日の業務処理を
夜間に更新
夜の間に夜間バッチ
処理が完了しない!!
索引のフラグメンテーションが発生
データ量が多いと、
索引の再構築に長時間かかってしまう
Copyright© 2007, Oracle. All rights reserved.
10
大規模テーブル運用の問題点
問題点2：管理作業が大変
問題点2
• バックアップやリカバリに時間がかかる！
バックアップ
バックアップリカバリ
リストア
Copyright© 2007, Oracle. All rights reserved.
11
大規模テーブル運用の問題点
問題点2：管理作業が大変
問題点2
• バックアップやリカバリに時間がかかる！
バックアップ
バックアップリカバリ
障害復旧が
システム要件の時間内
に完了しない!!
リストア
データ量が多いと、
バックアップやリカバリに
長時間かかってしまう
Copyright© 2007, Oracle. All rights reserved.
データ量が増
加！
12
大規模テーブル運用の問題点
問題点3
問題点
3：障害
：障害//保守時の影響が大きい
データ破損が発生!!
データ破損が発生
!!
破損範囲が
ごく一部でも
表すべてが
閲覧不可になる
Copyright© 2007, Oracle. All rights reserved.
13
Agenda
• 大規模テーブル運用の管理課題
• パーティショニングとは？
• パーティショニングのメリット
• ケーススタディー
Oracle Partitioning
Copyright© 2007, Oracle. All rights reserved.
14
パーティショニングとは？
大きな表や索引をデータベース内部で
複数の領域に分割して管理する
複数の領域に分割
して管理する
通常の１つの表
パーティション化された表
4-6月
内部的に
表を分割
1-3月
10-12月
7-9月
ユーザやアプリケーションからは
一つの表に見える
Copyright© 2007, Oracle. All rights reserved.
15
このような売上表をパーティショニングすると
…
売上日
製品
支店
顧 客 名
売上
・・・
2007/04/03
液晶テレビ
丸の内
椛田 正臣
¥43,380,000 ・・・
2007/04/04
炊飯器
神奈川
藤田 佳子
¥96,320,000 ・・・
2007/04/05
携帯電話
井上 あつし
¥43,620,000 ・・・
2007/06/26
腕時計
2007/12/31
電気ポット
2008/01/01
携帯電話
・
・
・
多摩
¥265,420,000 ・・・
新潟
山本 健
・
・
・
¥35,830,000 ・・・
・
・
・
・
・
・
Copyright© 2007, Oracle. All rights reserved.
・
・
・
影山 治子
・
・
・
埼玉
・
・
・
¥155,490,000 ・・・
・
・
・
・
・
・
・
・
・
松沢 惇
・
・
・
・
・
・
・
・
・
丸の内
・
・
・
¥83,620,000 ・・・
・
・
・
井上 憲三郎
・
・
・
丸の内
・
・
・
液晶テレビ
・
・
・
2007/04/03
16
売上日をキーとしてパーティショニングします
売上日
製品
支店
顧 客 名
売上
・・・
2007/04/03
液晶テレビ
丸の内
椛田 正臣
¥43,380,000 ・・・
2007/04/04
炊飯器
神奈川
藤田 佳子
¥96,320,000 ・・・
2007/04/05
携帯電話
井上 あつし
¥43,620,000 ・・・
2007/06/26
腕時計
2007/12/31
電気ポット
2008/01/01
携帯電話
・
・
・
多摩
¥265,420,000 ・・・
新潟
山本 健
・
・
・
¥35,830,000 ・・・
・
・
・
・
・
・
Copyright© 2007, Oracle. All rights reserved.
・
・
・
影山 治子
・
・
・
埼玉
・
・
・
¥155,490,000 ・・・
・
・
・
・
・
・
・
・
・
松沢 惇
・
・
・
・
・
・
・
・
・
丸の内
・
・
・
¥83,620,000 ・・・
・
・
・
井上 憲三郎
・
・
・
丸の内
・
・
・
液晶テレビ
・
・
・
2007/04/03
17
DB内部で分割して管理します
売上日
製品
支店
顧 客
売上
2007/04/03
液晶テレビ
丸の内
井上 憲三郎
¥83,620,000
2007/04/03
液晶テレビ
丸の内
椛田正臣
¥43,380,000
2007/04/04
炊飯器
神奈川
藤田佳子
¥96,320,000
2007年
2007
年Q1
売上日
製品
2007/04/03
液晶テレビ
丸の内
井上 憲三郎
¥83,620,000
2007/04/03
液晶テレビ
丸の内
椛田正臣
¥43,380,000
2007/04/04
炊飯器
神奈川
藤田佳子
¥96,320,000
2007/04/05
携帯電話
井上 あつし
¥43,620,000
影山治子
2008/01/01
携帯電話
新潟
山本 健
・
・
・
埼玉
・
・
・
電気ポット
¥265,420,000
¥35,830,000
・・
・
・・
・
・・
・
・・
・
・・
・
・
・
・
・
・
・
・
・
・
・
・
・
・
・
・
・
・
・
・・
・
・・
・
売上日
製品
支店
顧 客
売上
2007/08/03
携帯電話
丸の内
井上 あつし
¥83,620,000
2007/09/03
液晶テレビ
丸の内
田中 大輔
¥43,380,000
2007/09/10
炊飯器
千葉 由
¥96,320,000
埼玉
2007年
2007
年Q2
売上日
製品
支店
顧 客
売上
2007/01/07
携帯電話
丸の内
影山 治子
¥83,620,000
2007/02/13
液晶テレビ
丸の内
山本 健
¥43,380,000
2007/03/20
炊飯器
林順
¥96,320,000
埼玉
2007年
2007
年Q4
アプリケーションから見たら
1つの表ですが…
内部的には表を分割して管理します
Copyright© 2007, Oracle. All rights reserved.
・
・
・
・
・
・
・
・
・
・
・
・
・
・
・
¥155,490,000
・
・
・
・
・
・
・
・
・
・
・
・
2007/12/31
・
・
・
松沢惇
・・
・
売上
・
・
・
丸の内
顧客名
・
・
・
腕時計
多摩
・
・
・
・
・
・
・
・
・
2007/06/26
支店
・
・
・
・
・
・
・
・
・
・
・
・
18
・
・
・
・
・
・
・
・
・
・
・
・
Agenda
• 大規模テーブル運用の管理課題
• パーティショニングとは？
• パーティショニングのメリット
• ケーススタディー
Oracle Partitioning
Copyright© 2007, Oracle. All rights reserved.
19
パーティショニングのメリット
1. パフォーマンスの低下
データ量が増えると検索が遅くなる
パーティションプルーニングで解決!!
パーティションプルーニングで解決
!!
2. 管理作業が大変
バックアップやデータのローディングに時間がかかる
3. 障害/保守時の影響が大きい
障害やメンテナンスの際、表の全てのデータにアクセスができない
Copyright© 2007, Oracle. All rights reserved.
20
パーティション・プルーニング
• 対象のデータが格納されているパーティションだけに
アクセスし、不要なパーティションを読み飛ばす
Q1
今期（Q4）の売上の
平均値を見たい
(sales_date)
Q2
(sales_date)
Q3
オプティマイザ
SELECT area, period, avg(sales_rev) …
FROM sales_history
WHERE sales_date in Q4
GROUP BY area, period …
Copyright© 2007, Oracle. All rights reserved.
(sales_date)
Q4
(sales_date)
21
パーティションが有効なテーブルサイズ
※日本HP社との共同検証結果より
1回あたりの検索処理時間
※1GBのパーティション・テーブルの検索処理時間を1とした場合の
相対処理時間 ( 実際の処理時間に任意の数を掛けています )
Partition
Non-Partition
62.7
4GB
13.9
2GB以上なら
非常に有効！
27.1
2GB
6.3
3.5
1GB
1
0
10
20
30
40
※ 150,000 レコード検索の結果
Copyright© 2007, Oracle. All rights reserved.
50
60
70
時間
22
パーティショニングのメリット
1. パフォーマンスの低下
解決
データ量が増えると検索が遅くなる
パーティションプルーニングで解決!!
パーティションプルーニングで解決
!!
2. 管理作業が大変
バックアップやデータのローディングに時間がかかる
パーティション単位での管理で解決!!
パーティション単位での管理で解決
!!
3. 障害/保守時の影響が大きい
障害やメンテナンスの際、表の全てのデータにアクセスができない
Copyright© 2007, Oracle. All rights reserved.
23
管理作業もパーティション単位
• 索引の再構築や統計情報の取得もパーティション単位！
• 作業中も他のパーティションは影響を受けない！
パーティション単位で
更新処理ができる！
索引再構築
7月15日分
の更新
7月
6月
他のパーティションは
影響を受けない!!
5月
4月
Copyright© 2007, Oracle. All rights reserved.
24
管理作業もパーティション単位
• データの大量挿入/削除はシステム全体のパフォーマンスに悪影響
• 大規模なテーブルで時間がかかっていた、データの更新/削除などを
パーティション単位で行う事で高速化
古いパーティションの
高速な削除!!
高速な削除
!!
パーティションごとに削除が可能!!
条件指定のDELETEとは違い、
REDO/UNDOの生成が無く、
高速な削除が可能!!
Q1
Q2
Q3
Q4
Copyright© 2007, Oracle. All rights reserved.
25
管理作業もパーティション単位
• データの大量挿入/削除はシステム全体のパフォーマンスに悪影響
• 大規模なテーブルで時間がかかっていた、データの更新/削除などを
パーティション単位で行う事で高速化
テーブルのメンテナンス
作業が早い!!
古いパーティションの
高速な削除!!
高速な削除
!!
New
Q1Q1
Q2
パーティションごとに削除が可能!!
条件指定のDELETEとは違い、
REDO/UNDOの生成が無く、
高速な削除が可能!!
New Q1
新しいパーティションに
高速で入れ替え!!
高速で入れ替え
!!
Q3
Q4
Copyright© 2007, Oracle. All rights reserved.
26
パーティショニングのメリット
解決
1. パフォーマンスの低下
データ量が増えると検索が遅くなる
パーティションプルーニングで解決!!
パーティションプルーニングで解決
!!
2. 管理作業が大変
解決
バックアップやデータのローディングに時間がかかる
パーティション単位での管理で解決!!
パーティション単位での管理で解決
!!
3. 障害/保守時の影響が大きい
障害やメンテナンスの際、表の全てのデータにアクセスができない
パーティション単位で障害の影響を限定!!
パーティション単位で障害の影響を限定
!!
Copyright© 2007, Oracle. All rights reserved.
27
パーティション単位で障害の影響を限定
• 特定のパーティションに障害が発生しても、他のパーティションは
影響を受けない
• パーティション単位でリカバリを行えるので、障害から短時間で復
旧できる
Q1~Q3のデータは、
リカバリ中でも
通常通りアクセスできる
Q1
パーティション単位
でリカバリ実行
Q2
Q3
Q4
障害発生
Q4
Copyright© 2007, Oracle. All rights reserved.
28
パーティショニングのメリット
解決
1. パフォーマンスの低下
データ量が増えると検索が遅くなる
パーティションプルーニングで解決!!
パーティションプルーニングで解決
!!
2. 管理作業が大変
解決
バックアップやデータのローディングに時間がかかる
パーティション単位での管理で解決!!
パーティション単位での管理で解決
!!
3. 障害/保守時の影響が大きい
解決
障害やメンテナンスの際、表の全てのデータにアクセスができない
パーティション単位で障害の影響を限定!!
パーティション単位で障害の影響を限定
!!
Copyright© 2007, Oracle. All rights reserved.
29
パーティショニングの種類
【レンジパーティション
レンジパーティション】
】
• 日付でデータを管理したい
→ 特定の連続するデータ（売上日、ログ取得日 etc）の範囲によって分割
【リストパーティション
リストパーティション】
】
• 支店別、製品別など特定のカテゴリーごとにデータを管理したい
→ 特定のデータ（製品名、店舗名 etc）のカテゴリーによって分割
【ハッシュパーティション
ハッシュパーティション】
】
• ディスクアクセスを均等化させ、パフォーマンスを向上させたい
• パーティション表を使ってバッチ処理を高速化させたい
→ 一意となるデータ（社員ID、商品ID etc）でデータを分散させて分割
※ 詳しくは、
Oracle Direct Seminar「現場で使えるパーティションの極意!!∼機能詳細編∼」を御受講ください
Copyright© 2007, Oracle. All rights reserved.
30
パーティショニングの種類
レンジパーティション
リストパーティション
※日付などの連続するデータで分割
2007年 1∼3月
売上表
(orderdate)
2007年 4∼6月
(orderdate)
2007年 7∼9月
(orderdate)
2007年 10∼12月
(orderdate)
ハッシュパーティション
ハッシュ値1
※地域などデータのカテゴリーで分割
東京
神奈川
関東パーティション
Sales_p2
大阪
京都
近畿パーティション
Sales_p3
福岡
長崎
九州パーティション
Sales_p4
宮城
青森
東北パーティション
Sales_p1
売上表
※一意となるデータ（社員ID、商品IDなど）で
データを分散させて分割
ハッシュ値2
ハッシュ値3
OLTPなどの大量検索＆更新に有効
ハッシュ値4
※ 詳しくは、
Oracle Direct Seminar「現場で使えるパーティションの極意!!∼機能詳細編∼」を御受講ください
Copyright© 2007, Oracle. All rights reserved.
31
パーティショニングの種類
【コンポジットパーティション】
• 基本のパーティションを組み合わせる事も可能
コンポジットレンジリストパーティション
コンポジットレンジハッシュパーティション
リスト・サブパーティション
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「現場で使えるパーティションの極意!!∼機能詳細編∼」を御受講ください
Copyright© 2007, Oracle. All rights reserved.
32
Agenda
• 大規模テーブル運用の管理課題
• パーティショニングとは？
• パーティショニングのメリット
• ケーススタディー
Oracle Partitioning
Copyright© 2007, Oracle. All rights reserved.
33
ケーススタディー
• システムログを管理しているのだが、問題がたくさんあって…
課題
対策
テーブルデータが全体で10GB
・ ログデータのローディングに
時間がかかりすぎる
・ 検索の性能が出ない
日付 + 支店名でのパーティショニング
レンジリストコンポジットパーティション
Copyright© 2007, Oracle. All rights reserved.
を採用
34
ケーススタディー
• システムログを管理しているのだが、問題がたくさんあって…
課題
対策
テーブルデータが全体で10GB
・ ログデータのローディングに
時間がかかりすぎる
・ 検索の性能が出ない
日付 + 支店名でのパーティショニング
レンジリストコンポジットパーティション
を採用
ローディング時間の改善
遅い!!
6月22日分
ローディング
Copyright© 2007, Oracle. All rights reserved.
35
ケーススタディー
• システムログを管理しているのだが、問題がたくさんあって…
課題
対策
テーブルデータが全体で10GB
・ ログデータのローディングに
時間がかかりすぎる
・ 検索の性能が出ない
日付 + 支店名でのパーティショニング
レンジリストコンポジットパーティション
を採用
ローディング時間の改善
A支店
B支店
C支店
D支店
…
…
…
…
パーティション単位で
ローディングしたことで
かなり早くなった!!
6月22日A支店
ローディング
2007/06/22
6月22日B支店
6月22日分
ローディング
ローディング
2007/06/23
2007/06/24
2007/06/25
…
6月22日C支店
ローディング
現在
Copyright© 2007, Oracle. All rights reserved.
36
ケーススタディー
• システムログを管理しているのだが、問題がたくさんあって…
課題
対策
テーブルデータが全体で10GB
・ ログデータのローディングに
時間がかかりすぎる
・ 検索の性能が出ない
日付 + 支店名でのパーティショニング
レンジリストコンポジットパーティション
を採用
検索時間の改善
遅い!!
6月22日のA支店の
履歴は？
Copyright© 2007, Oracle. All rights reserved.
37
ケーススタディー
• システムログを管理しているのだが、問題がたくさんあって…
課題
対策
テーブルデータが全体で10GB
・ ログデータのローディングに
時間がかかりすぎる
・ 検索の性能が出ない
日付 + 支店名でのパーティショニング
レンジリストコンポジットパーティション
を採用
検索時間の改善
B支店
C支店
D支店
…
…
…
…
範囲指定の検索が
かなり早くなった!!
A支店
2007/06/22
2007/06/23
6月22日のA支店の
履歴は？
2007/06/24
2007/06/25
…
現在
Copyright© 2007, Oracle. All rights reserved.
38
パーティショニング採用事例
Oracle Partitioning
多くの企業がパーティショニングの採用により、
処理速度の向上、管理コスト削減、可用性向上を実現！
事例の詳細については以下をご覧ください
http://www.oracle.co.jp/showcase/
Copyright© 2007, Oracle. All rights reserved.
39
まとめ
大規模テーブルは管理が大変 !!
パーティション単位の管理で容易に!!
検索スピードの高速化
メンテナンス作業の短時間化
Oracle Partitioning
可用性の向上
多くの採用事例
Copyright© 2007, Oracle. All rights reserved.
40
詳しい説明、システム導入のご相談は
Oracle Direct
まずはお問合せください
http://www.oracle.co.jp/direct
0120-155-096
Copyright© 2007, Oracle. All rights reserved.
41
•
•
•
•
•
•
本文書は、日本オラクル Oracle Direct 主催の Oracle Direct Seminar のために作成された資料です
本文書の著作権は、日本オラクル株式会社に帰属しています
本文書を第三者に配布することはご遠慮下さい
本文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります
Oracle、Oracle8i、Oracle9i、Oracle10g はオラクル社の登録商標、または商標です
本文書で使用している製品やサービス名の名称は、各社の商標または登録商標です
Copyright© 2007, Oracle. All rights reserved.
42