MySQL Server 5.0 Load Data ベンチマーク

(1)

The information contained herein is subject to change without notice

MySQL Server 5.0

InnoDBデータベース

大量データ投入

日本ヒューレット・パッカード株式会社

オープンソース・コンピテンスセンタ

(2)

Agenda

• データベースへの大量データ投入について

• 試験環境

• InnoDBテーブルへの大量データ投入性能試験

• 投入後データの検索性能試験

• OPTIMIZE

TABLE実施試験

• OPTIMIZE TABLE実施後の検索性能試験

• 試験結果からの結論

• 試験結果と

InnoDBの仕組みについて

(3)

(4)

データベースへの大量データ投入とは？

• 何をするのか？

−

システム移行や新規システム構築時、バックアップのリスト

ア時に、必要とされるデータをシステムに投入します。

−

一般に

CSVファイルに記録されたデータをデータベースに

読込ませます。

• Oracle では

SQL*Loader ツールを利用します。

• MySQLでは

LOAD DATA IN FILE 文を利用します。

CSV

データ

_{データ投入}

データ出力

(5)

データベースへの大量データ投入の問題

• 大規模なシステムになるほど、大きなデータ（

GB単位）の投入

が必要となり、投入時間も長くなります。

−

例えば、４

GBのCSVデータファイルをMySQLに投入した時、誤った方

法で投入すると約

6時間かかります（詳細は後でご説明します）。

• 可能な限り高速にデータを投入したい！

−

どうすれば高速に投入できるのか？

−

また、投入後の性能に影響があるのか？

−

プライマリ・キーで整列したデータを投入すると高速らしい

…

実際に試験で確認！

(6)

運用時のデータベース性能劣化問題

• 運用中のデータベースに対して挿入・削除が頻繁に実施され

ると、データベースの性能が劣化していきます。

• どのデータベースでも定期的にメンテナンスの実施が推奨さ

れています。

−

MySQLでは

OPTIMIZE TABLE の定期的な実施が推奨されています。

−

本当にパフォーマンスは向上するのか？

−

その実施時間は？

(7)

実施試験概要

• 4つの試験を実施しました。

−

試験１

: 大量データ投入性能試験

• 整列済および未整列の

CSVデータファイルをデータベースへ投入し、

その処理時間を計測

−

試験

2: 大量データ投入後の検索性能試験

• 大量データ投入後に整列させた

CSV データファイルの生成を行い、

その処理時間を計測

−

試験

3: 投入後のOPTIMIZE TABLE実施性能試験

• 大量データ投入後に

OPTIMIZE TABLEを実施し、その実施時間を計

測

−

試験

4: OPTIMIZE TABLE実施後の検索性能試験

• OPTIMIZE TABLE後に整列させた

CSV データファイルの生成を行

い、その処理時間を計測

(8)

(9)

試験環境

• HW: HP ProLiant

DL380G5

−

CPU: Intel Xeon E5320 (4Core 1.86GHz) x 1

−

RAM: 4GB

−

Storage:

HP Smart Array P400

• キャッシュ

Read 256MB, Write 256MB

• 147GB HDD x 5 (OS用RAID0、データ用RAID5）

• OS: Red Hat Enterprise Linux 4 AS Update 4 (x86)

• SW: MySQL Enterprise Server 5.0.36

−

mysql-enterprise-gpl-5.0.36-linux-i686-glibc23

−

my-innodb-heavy-4G.cnf をベースに利用

• 負荷ツール

−

mysql

クライアント

(10)

実施条件（１）

テーブル定義

• テーブル名

: loadtest

• 利用ストレージエンジン

: InnoDB

• カラム定義：

1行がほぼ512Bytesとなるように定義

• キーの定義

: 定義方法で2種類のテーブルを用意

−

プライマリ・キー

として

data1 を定義したテーブル

−

インデックス・キーとして

data1 を定義したテーブル

カラム名データ型 id1 decimal(8) id2 decimal(4) id3 decimal(4) id4 decimal(5) data1 char(25) data2 char(255) data3 char(207)

キーとして利

用したカラム

(11)

実施条件（２）

試験用

CSVデータファイル

• テーブル

loadtestに適合したCSVデータファイル

• 6種類のデータファイルを用意

−

ファイルサイズとデータの整列有無の組み合わせ

−

データファイルの整列は

sort コマンドを利用

ファイルサイズ

data1が昇順で整列している

1G

×

○ 2G

×

○ 4G

×

○

(12)

実施条件（３）

試験用

CSVデータファイル生成方法

• 未整列データの生成

−

ベンチマークツール

Super Smack

（

http://vegan.net/tony/supersmack/

）

の

データ生成ツールを利用し、未整列データを生成。

gen-data –n <行数> ¥

-f "%8d,%4d,%4d,%8d,%6-6s%n,%255-255s,%207-207s“

¥

> unsorted-data.csv

• 行数はファイルサイズに応じて

1GB=2097152行、2GB=4194304行、

4GB=8388608行としています。

• 整列済データの生成

−

未整列データから

sort コマンドを利用して整列済データを生成。

sort –t , +4 unsorted-data.csv

> sorted-data.csv

(13)

試験実施結果マトリクス

テーブル定義投入データ種別投入データサイズデータ整列時間試験１大量データ投入試験２投入後検索性能試験３最適化実施時間試験４最適化後検索性能プライマリ・キー利用未整列データ 1G 2G 4G 整列済データ 1G 85 2G 163 4G 365 インデックス・キー利用未整列データ 1G 2G 4G 整列済データ 1G 85 2G 163 4G 365

未整列データを

sort コマンドで整

列した時間です。

（単位は秒）

(14)

試験

1:

InnoDBテーブルへの

大量データ投入性能試験

(15)

試験

1:

InnoDBテーブルへの大量データ投入性能試験

• 試験目的

−

投入データの整列有無による、投入性能について試験します。

• 試験概要

−

CSVデータファイルを

LOAD DATA IN FILE 命令を用いて

MySQLデー

タベースに投入し、その実施時間を計測します。

−

対象テーブル

• loadtest

（プライマリ・キー利用時とインデックス利用時）

−

投入方法

• LOAD DATA INFILE ‘filename’

INTO loadtest

FIELDS...

−

投入データ

• 未整列データ：

1GB, 2GB, 4GB

• 整列済データ：

1GB, 2GB, 4GB

(16)

試験

1: InnoDBテーブルへの

大量データ投入性能試験結果

大量データ投入時間

0 5000

10000

15000

20000

25000

1G

2G

4G

1G

2G

4G

1G

2G

4G

1G

2G

4G

未整列データ投入

整列済データ投入

未整列データ投入

整列済データ投入

プライマリ・キー利用時

インデックス・キー利用時

投入時間(

秒)

データプライマリ未整列(秒) プライマリ整列(秒) インデックス未整列(秒） インデックス整列（秒） 1GB 169 94 125 107 2GB 2,750 203 301 213 4GB 21,654 (約6時間) (6分39秒)399 (10分12秒）612 (7分41秒)461

(17)

試験実施結果マトリクス

テーブル定義投入データ種別投入データサイズデータ整列時間試験１大量データ投入試験２投入後検索性能試験３最適化実施時間試験４最適化後検索性能プライマリ・キー利用未整列データ 1G 169 2G 2,750 4G _(約6時間)21,654 整列済データ 1G 85 94 2G 163 203 4G 365 _(6分39秒)399 インデックス・キー利用未整列データ 1G 125 2G 301 4G _{(10分12秒）}612 整列済データ 1G 85 107 2G 163 213 4G 365 _(7分41秒)461

（単位は秒）

•プライマリ・キー利用時に未整列データを

投入すると、

2GBで1GBの約16.3倍、4GB

で

1GBの約128.1倍の処理時間が必要です。

•プライマリ・キー利用時に整列済データを

投入すると、

2GBで1GBの約2.2倍、4GBで

1GBの約4.2倍とほぼデータサイズに正比

例した処理時間が必要です。

•インデックス利用時は未整列・整列済に関

係なくプライマリ・キー利用時の整列済デー

タ投入時同様に高速に投入できます。

(18)

試験

2:

(19)

試験

2: 投入後データの検索性能試験

• 試験目的

−

整列済データおよび未整列データ投入後のシーケンシャル

なデータ検索の性能差有無を確認するため、

data1で整列

した

CSVデータファイルの生成処理時間を計測しました。

• 試験概要

−

対象テーブル

• loadtest

（プライマリ・キー利用時とインデックス・キー利用時）

−

生成方法：

• SELECT * FROM loadtest

ORDER BY data1 INTO OUTFILE ‘filename’

...

−

対象データ

• 未整列データ投入後（

1GB, 2GB, 4GB）のCSVデータファイル生成

時間

• 整列済データ投入後（

1GB, 2GB, 4GB）のCSVデータファイル生成

時間

(20)

試験

2: 投入後データの検索性能試験

投入後データの検索処理時間

0 5000

10000

15000

20000

25000

30000

1G

2G

4G

1G

2G

4G

1G

2G

4G

1G

2G

4G

未整列データ投入後

の生成時間

整列済データ投入後

の生成時間

未整列データ投入後

の生成時間

整列済データ投入後

の生成時間

プライマリ・キー利用

インデックス・キー利用

処理時間(

秒)

データプライマリ未整列（秒）プライマリ整列(秒) インデックス未整列(秒) インデックス整列(秒) 1GB 355 47 175 58 2GB 1,109 114 3,664 128 4GB 2,594 (43分14秒) (4分3秒)243 (約7時間)26,201 (4分24秒)267

(21)

試験実施結果マトリクス

テーブル定義投入データ種別投入データサイズデータ整列時間試験１試験２試験３試験４プライマリ・キー利用未整列データ 1G 169 355 2G 2,750 1109 4G _(約6時間)21,654 _(43分14秒)2,594 整列済データ 1G 85 94 47 2G 163 203 114 4G 365 _(6分39秒)399 _(4分3秒)243 インデックス利用未整列データ 1G 125 175 2G 301 3,664 4G _{(10分12秒）}612 _(約7時間)26,201 整列済データ 1G 85 107 58 2G 163 213 128 4G 365 _(7分41秒)461 _(4分24秒)267

（単位は秒）

•プライマリ・キー利用時未整列

データ投入後の検索性能は、

2GB

で

1GBの約3.1倍、4GBで1GBの

約

7.3倍であり、インデックス利用

時未整列データ投入後の結果と比

較すれば非常に高速であると言え

ます。

•インデックス利用時未整列データ

投入後の検索性能が

2GBで1GB

の約

20.9倍、4GBで1GBの約

149.7倍です。

•整列済データ投入後の検索性能

は、プライマリ・キー利用時および

インデックス利用時ともに高速です。

(22)

試験

3:

(23)

試験

3:

OPTIMIZE TABLE実施試験

• 試験目的

−

挿入削除が頻繁に行われるデータベースでは

OPTIMIZE

TABLEの実施が推奨されています。まず実際に実施した場

合の実施時間ついて試験します。

• 実施概要

−

非常に多くの挿入・削除処理を行ったデータベースの代替

として、未整列データを投入したデータベースを利用

−

対象テーブル

• loadtest

（プライマリ・キー利用時のみ）

−

未整列データ投入後および整列済データ投入後に

OPTIMIZE TABLEを実施し、その処理時間を計測

(24)

試験

3:

OPTIMIZE TABLE実施試験

OPTIMIZE TABLE実施時間

0

500 1000

1500

2000

2500

3000

3500

4000

1G

2G

4G

1G

2G

4G

未整列データ投入後

OPTIMIZE TABLE実施

整列済データ投入後

OPTIMIZE TABLE実施

Primary Key利用時

実施時間(

秒)

データ未整列_(秒) 整列済_(秒) 1GB 562 101 2GB 1435 206 4GB 3719 （約1時間）（7分51秒）471

(25)

試験実施結果マトリクス

テーブル定義投入データ種別投入データサイズデータ整列時間試験１大量データ投入試験２投入後検索性能試験３最適化実施時間試験４最適化後検索性能プライマリ・キー利用未整列データ 1G 169 355 562 2G 2,750 1109 1,435 4G _(約6時間)21,654 _(43分14秒)2,594 _（約_1時間）3,719 整列済データ 1G 85 94 47 101 2G 163 203 114 206 4G 365 _(6分39秒)399 _(4分3秒)243 _（_7分51秒）471 インデックス・キー利用未整列データ 1G 125 175 2G 301 3,664 4G _{(10分12秒）}612 _(約7時間)26,201 整列済データ 1G 85 107 58 2G 163 213 128 4G 365 _(7分41秒)461 _(4分24秒)267

（単位は秒）

• OPTIMIZE TABLE実施時間はデータサイズに

応じて増加します。

•未整列データ投入後のOPTIMIZE TABLE実

施時間は長く、またデータサイズによる処理時

間増加率も整列済データ投入後より長くなりま

す。

(26)

試験

4:

(27)

試験

4:

OPTIMIZE TABLE実施後の検索性能試験

• 試験目的

−

OPTIMIZE TABLE実施後に検索性能がどの程度向上する

のかを確認します。

• 実施概要

−

非常に多くの更新・挿入・削除処理を行ったデータベースの

代替として、未整列データを投入したデータベースを利用

−

対象テーブル

• loadtest

（プライマリ・キー利用時のみ）

−

投入後データの検索性能試験と同様に、

OPTIMIZE TABLE

実施後に

data1で整列したCSVファイルを生成する時間を

計測

(28)

試験

4:

OPTIMIZE TABLE実施後の検索性能試験

OPTIMIZE TABLE実施後の検索処理時間

0

500 1000

1500

2000

2500

3000

1G

2G

4G

1G

2G

4G

1G

2G

4G

1G

2G

4G

未整列データ投入後

整列済データ投入後

未整列データ投入後

整列済データ投入後

OPTIMIZE TABLE未実施

OPTIMIZE TABLE実施

プライマリ・キー利用

処理時間(

秒

)

データ未整列データ投入後OPTIMIZE TABLE未実施 整列データ投入後 OPTIMIZE TABLE未実施 未整列データ投入後OPTIMIZE TABLE実施 整列データ投入後OPTIMIZE TABLE実施 1GB 355 47 31 31 2GB 1109 144 137 133 4GB 2594 （43分14秒）（4分3秒）243 （4分34秒）274 （4分35秒）275

(29)

試験実施結果マトリクス

テーブル定義投入データ種別投入データサイズデータ整列時間試験１大量データ投入試験２投入後検索性能試験３最適化実施時間試験４最適化後検索性能プライマリ・キー利用未整列データ 1G 169 355 562 31 2G 2,750 1109 1,435 137 4G _(約6時間)21,654 _(43分14秒)2,594 _（約_1時間）3,719 _（_4分34秒）274 整列済データ 1G 85 94 47 101 31 2G 163 203 114 206 133 4G 365 _(6分39秒)399 _(4分3秒)243 _（_7分51秒）471 _（_4分35秒）275 インデックス・キー利用未整列データ 1G 125 175 2G 301 3,664 4G _{(10分12秒）}612 _(約7時間)26,201 整列済データ 1G 85 107 58 2G 163 213 128 4G 365 _(7分41秒)461 _(4分24秒)267

（単位は秒）

投入データ種別によらず、

OPTIMIZE

TABLE実施後の検索性能は、整列済

データ投入直後の検索性能とほぼ同

等の性能を示します。

(30)

(31)

試験実施結果マトリクス

テーブル定義投入データ種別投入データサイズデータ整列時間試験１大量データ投入試験２投入後検索性能試験３最適化実施時間試験４最適化後検索性能プライマリ・キー利用未整列データ 1G 169 355 562 31 2G 2,750 1109 1,435 137 4G _(約6時間)21,654 _(43分14秒)2,594 _（約_1時間）3,719 _（_4分34秒）274 整列済データ 1G 85 94 47 101 31 2G 163 203 114 206 133 4G 365 _(6分39秒)399 _(4分3秒)243 _（_7分51秒）471 _（_4分35秒）275 インデックス・キー利用未整列データ 1G 125 175 2G 301 3,664 4G _{(10分12秒）}612 _(約7時間)26,201 整列済データ 1G 85 107 58 2G 163 213 128 4G 365 _(7分41秒)461 _(4分24秒)267

（単位は秒）

(32)

試験結果からの結論

• 大量データ投入時は、

整列済データを用意しましょう。

−

投入処理時間および検索性能に大きく影響します。

−

整列済データの生成も

sort コマンド等を利用すれば簡単です。

• 挿入・削除が頻繁に発生する場合は、

OPTIMIZE

TABLEを実施しましょう。

−

検索性能の向上が期待できます。

−

実施時間がある程度必要とされるため、計画的に実施することが必要

です。

• Reference Manualでは1週間から1ヶ月に1回の実施が推奨されています。

(33)

(34)

InnoDBテーブルのデータ構造（１）

• 行データ格納領域

−

Clustered Indexと呼ばれる一意な識別子で行データを管理・格納しま

す。

• インデックス格納領域

−

テーブルに定義されているインデックス・キーと、対応する行データの

Clustered Index がペアで格納されます。

−

Clustered Index となるキー以外のキーがテーブルに定義されていない

場合、インデックス格納領域は作成されません。

InnoDB

テーブル

格納領域行データインデックス1 格納領域インデックス₂ 格納領域 ClusteredIndex 行データ行データインデックス・キー

(35)

InnoDBテーブルのデータ構造（２）

Clustered Indexの決定

• プライマリ・キー

が定義されている場合

−

プライマリ・キー

を

Clustered Index として利用します。

（大量データ投入試験のプライマリ・キー利用時に相当）

• プライマリ・キー

が定義されておらず、

Nullではないユニークインデックス

が定義されている場合

−

ユニークインデックスを

Clustered Index として利用します。

• どちらも定義されていない場合

−

InnoDBが自動生成する

ROW_ID を

Clustered Index として利用しま

す。

（大量データ投入試験の

Index利用時に相当）

InnoDB

テーブル

格納領域行データインデックス1 格納領域インデックス₂ 格納領域 ClusteredIndex 行データ行データインデックス

(36)

InnoDBテーブルのデータ構造（３）

行データ格納領域の構造と検索処理

• 行データは

B-Tree

(m分木)構造に格納されます。

−

枝の部分は、検索のための

Clustered Indexの範囲を格納

−

葉の部分にあたる

Pageに

Clustered Index と行データ

を格納

• Pageサイズは16KB固定

• B-Tree構造を利用して

検索を行います。

InnoDB

Table space

•Page単位でInnoDB

データファイルに格納

•検索・挿入処理時に

対象行を含む

Pageを

メモリに読込む。

1～32 35～59 1～10 15～32 35～42 43～49 Index データ 1 データ 2 データ 3 データ 4 データ Key データ 1 データ 2 データ 3 データ 4 データ Page 1 行データ 2 行データ 3 行データ 4 行データ

枝

葉（

Page 16KB）

Clustered Index の範囲

Clustered Index

と行データが格

納されています。

50-59

select * from table where pk=3;

(37)

インデックス格納領域

AA-DE DF-KZ

AA-BZ CA-DE DF-JI JJ-KZ

Index データ 1 Clustered index 2 Clustered Index 3 Clustered Index 4 Clustered Index Key データ 1 Clustered Index 2 Clustered index 3 Clustered Index 4 Clustered Index ID データ AA Clustered Index AB Clustered Index AH Clustered Index BA Clustered Index

InnoDBテーブルのデータ構造（４）

インデックス格納領域の構造と検索処理

• 行データ格納領域と同様に

B-Tree構造に格納します。

−

インデックスを

B-Treeでの識別子に利用しています。

−

行データの代わりに

Clustered Index が格納されます。

• インデックス格納領域から

Clustered Indexを検索して、行データ格納領域

を検索、行データを取得します。

Clustered

Indexを

識別子として行

データを格納。

インデックスを識別

子として、

Clustered

Index

を格納。

1～32 35～49 1～10 15～32 35～42 43～49 Index データ 1 データ 2 データ 3 データ 4 データ Key データ 1 データ 2 データ 3 データ 4 データ ID データ 1 行データ 2 行データ 3 行データ 4 行データ

行データ格納領域

インデックス ClusteredIndex

select * from table where idx=BA;

得られたClustered Indexで検索

検索対象

データ

(38)

1～101～３ 15～327～10 15～32

InnoDBテーブルへの行挿入（１）

• 行データ挿入先

Pageを検索します。

• Pageに行データが格納可能な場

合は、その

Pageに格納します。

• Pageが一杯で行データを挿入で

きない場合、

Page分割を行います。

各

Pageには元のPageの行データ

が半分づつ格納されます。

B-Treeの枝の構造が修正されます。

• 1Pageあたりの格納行数が最悪

Pageの半分になります。

1～32 35～49 35～42 43～49 ID データ 1 行データ 2 行データ 3 行データ６行データ

insert into table values (7, row_data) １０行データ ID データ７行データ８行データ１０行データ

Pageに格納可能な場合

は行データを書き込みま

す。

Pageに書き込めない

場合は、

Page分割を行

います。

B-Tree構造も

再構成されま

す。

(39)

InnoDBテーブルへの行挿入（２）

整列済データの投入

• 整列済データ投入時は、常

に一番最後の

Pageに行

データが格納されます。

• Pageに行データを挿入でき

なくなると、

Page分割するこ

となく、新しい

Pageが作成さ

れ、行データを挿入します。

• 最後の

Page以外では、Page

全体に行データが格納され

るため、全データの格納に

必要となる

Page数は最小に

なります。

1～10 15～32 ID データ 1 行データ 2 行データ 3 行データ６行データ

整列済データの投入

１０行データ ID データ 15 行データ 16 行データ 17 行データ

Pageが一

杯になった。

新しい

_{を作成します。}

Page

(40)

インデックス格納領域

AA-DE DF-KZ

AA-BZ CA-DE DF-JI JJ-KZ

Index データ 1 Clustered index 2 Clustered Index 3 Clustered Index 4 Clustered Index Key データ 1 Clustered Index 2 Clustered index 3 Clustered Index 4 Clustered Index ID データ AA Clustered Index AB Clustered Index AH Clustered Index BA Clustered Index

InnoDBテーブルへの行挿入（３）

インデックス・キー利用時の挿入

• 行データは行データ格納領域に挿入します。

−

Clustered Index として適切なキーが存在しない場合、ROW_ID

を利用します。

−

ROW_IDは挿入順に振られるため整列済データ投入と同じ挿入処理となります。

• インデックス格納領域にインデックスと

Clustered Indexをデータとして挿入し

ます。挿入方法は行データ格納領域への挿入と同様です。

−

格納データが小さいため、未整列データの挿入でも比較的高速に実施可能です。

インデックスと特定

された

Clustered

Index を挿入します。

1～32 35～49 1～10 15～32 35～42 43～49 Index データ 1 データ 2 データ 3 データ 4 データ Key データ 1 データ 2 データ 3 データ 4 データ ID データ 1 行データ 2 行データ 3 行データ 4 行データ

行データ格納領域

インデックス ClusteredIndex

行データを先に挿入し、

Clustered Index の値

を特定します。

(41)

Index データ 1 データ 2 データ 3 データ 4 データ Index データ 1 データ 2 データ 3 データ 4 データ

整列済データ投入時と

未整列データ投入時のデータ保持状態比較

• PageはPageの生成順序、つまり

B-Treeの配置順序で整列されて

Diskに書き込まれています

。

• ランダムに

Pageが生成されること

により、

Disk上の配置順序もラン

ダムになります。また、

Page分割

も発生します。

1～10 1～32 35～49 35～42 43～49 1 2 3 Index データ 1 データ 2 データ 3 データ 4 データ 15～32 Key データ 1 データ 2 データ 3 データ 4 データ 6～10 Index データ 1 データ 2 データ 3 データ 4 データ Key データ 1 データ 2 データ 3 データ 4 データ 1～5 ID データ 1 行データ 2 行データ 3 行データ 4 行データ 1 3 2

整列済データ投入時

未整列データ投入時

1～32 16～49 11～21 22～32 1～10 15～32 35～42 42～49 シーケンシャルリードに有利シーケンシャル_{リードに不利} ID データ 1 行データ 2 行データ 3 行データ 4 行データ

Page分割

発生！

(42)

OPTIMIZE TABLEの実施について

OPTIMIZE

TABLE実施前テーブル

（旧テーブル）

OPTIMIZE

TABLE実施後テーブル

（新テーブル）

OPTIMIZE TABLE実施＝テーブルコピー

•旧テーブルからCluster Indexで整列したデータを、新テーブルへ挿入していきます。

•新テーブルの行データ格納領域は整列済データ投入直後と同等の状態になります。

インデックス

格納領域

行データ

格納領域

インデックス

格納領域

行データ

格納領域

1Pageあたりの格納行

数が増加し、

Pageの

Disk上の配置順序が

整列されます。

(43)

試験実施結果マトリクス

テーブル定義投入データ種別投入データサイズデータ整列時間試験１大量データ投入試験２投入後検索性能試験３最適化実施時間試験４最適化後検索性能プライマリ・キー利用未整列データ 1G 169 355 562 31 2G 2,750 1109 1,435 137 4G _(約6時間)21,654 _(43分14秒)2,594 _（約_1時間）3,719 _（_4分34秒）274 整列済データ 1G 85 94 47 101 31 2G 163 203 114 206 133 4G 365 _(6分39秒)399 _(4分3秒)243 _（_7分51秒）471 _（_4分35秒）275 インデックス利用未整列データ 1G 125 175 2G 301 3,664 4G _{(10分12秒）}612 _(約7時間)26,201 整列済データ 1G 85 107 58 2G 163 213 128 4G 365 _(7分41秒)461 _(4分24秒)267

（単位は秒）

試験１と試験２のプライマ

リ・キー利用時未整列

データ投入時の試験結

果の原因を説明します。

(44)

Index データ 1 データ 2 データ 3 データ 4 データ Index データ 1 データ 2 データ 3 データ 4 データ

試験

1および試験２: プライマリ・キー利用時

未整列データ投入

6～10 Index データ 1 データ 2 データ 3 データ 4 データ Key データ 1 データ 2 データ 3 データ 4 データ 1～5 ID データ 1 行データ 2 行データ 3 行データ 4 行データ 1 3 2 1～32 16～49 11～21 22～32 1～10 15～32 _35～42 _42～49 シーケンシャルリードに不利

• 試験

1:

未整列データ投入が遅い理由

−

ランダムに行データを挿入するた

めです。

• 挿入先

Pageの検索が必要です。

• Page分割が発生します。

• Disk上のPage配置順序がランダム

になります。

• 試験

2:

シーケンシャルな検索が遅い理由

−

PageがDisk上にランダムに配置さ

れているためです。

• シーケンシャルな

Diskアクセス効

率が低くなります。

• 投入データサイズが大きくなれば

なるほど、データ投入と検索処理

は遅くなります。

(45)

試験実施結果マトリクス

（単位は秒）

試験１と試験２のプライマ

リ・キー利用時整列済

データ投入時の試験結

果の原因を説明します。

(46)

試験

1および試験２:

プライマリ・キー利用時

整列済データ投入

1～10 1～32 35～49 35～42 43～49 1 2 3 Index データ 1 データ 2 データ 3 データ 4 データ 15～32 Key データ 1 データ 2 データ 3 データ 4 データシーケンシャルリードに有利 ID データ 1 行データ 2 行データ 3 行データ 4 行データ

• 試験

1:

整列済データ投入が早い理由

−

順序よくデータ挿入が行われるた

めです。

• 行データは常に行データ格納領域

の最終

Pageに挿入されます。

•

１ Pageあたりの格納行数が多くな

ります。

• 試験

2:

シーケンシャルな検索が速い理由

−

Disk上にPageが整列された状態

で配置されているためです。

• シーケンシャルな

Diskアクセス効

率が高くなります。

(47)

試験実施結果マトリクス

（単位は秒）

インデックス・キー利用し

た未整列データの投入

試験と、投入後の性能試

験の結果について説明し

ます。

(48)

行データ格納領域

インデックス格納領域

試験

1および試験２:

インデックス・キー利用時

未整列データ投入

• 試験

1:

未整列データ投入が速い理由

−

プライマリ・キーが無いため、

Clustered

IndexとしてROW_IDを利用します。

−

ROW_IDは投入順に割り振られるため、

整列済データとして、行データ格納領

域にデータ投入されます。

−

インデックス格納領域の構築は、デー

タ量が小さいため、インデックス・キー

がランダムでも作業量は小さいです。

• 試験

2:

シーケンシャルな検索が遅い理由

−

検索対象行データが、行データ格納領

域内にランダムに配置されているため

です。

• 行データ取得のために

Pageの読込み

が非常に多くなります。

1～10 1～32 35～49 35～42 43～49 1 2 3 Index データ 1 データ 2 データ 3 データ 4 データ 15～32 Key データ 1 データ 2 データ 3 データ 4 データ ID データ 1 行データ 2 行データ 3 行データ 4 行データ A B ID データ A1 ClI20 A2 CI31 A3 C52 A4 C33 ID データ B1 ClI22 B2 CI53 B3 C33 B4 C1 A1～A41 B1～B50 A1～B50 C1～D30

(49)

試験実施結果マトリクス

（単位は秒）

インデックス・キー利用した

整列済データの投入試験と、

投入後の性能試験の結果

について説明します。

(50)

行データ格納領域

インデックス格納領域

試験

1および試験２:

インデックス・キー利用時

整列済データ投入

• 試験

1:

未整列データ投入が速い理由

−

プライマリ・キーが無いため、

Clustered IndexとしてROW_IDを

利用します。

−

ROW_IDは投入順に割り振られる

ため、整列済データとして、行デー

タ格納領域にデータ投入されます

−

インデックス・キーが整列されてい

るため、インデックス格納領域の

構築も高速です。

• 試験

2:

シーケンシャルな検索が速い理由

−

インデックス・キーと同じ順序で行

データが行データ格納領域に配置

されているためです。

1～10 1～32 35～49 35～42 43～49 1 2 3 Index データ 1 データ 2 データ 3 データ 4 データ 15～32 Key データ 1 データ 2 データ 3 データ 4 データ ID データ 1 行データ 2 行データ 3 行データ 4 行データ A B ID データ A1 ClI20 A2 CI31 A3 C52 A4 C33 ID データ B1 ClI22 B2 CI53 B3 C33 B4 C1 A1～A41 B1～B50 A1～B50 C1～D30

(51)

試験実施結果マトリクス

（単位は秒）

試験

3の未整列

データ利用時の試

験結果について

説明します。

(52)

試験

3: OPTIMIZE

TABLE実施試験

未整列データ投入時

• OPTIMIZE TABLEの実施が遅い理由

−

未整列データ投入直後の状態

（旧テーブル）から、

整列済データ投入直

後の状態

（新テーブル）に変換します。

−

MySQLでは、旧テーブルからClustered Index順に行データを取り出し

つつ、新テーブルへのデータ投入を実施しています。

1～10 1～32 35～49 35～42 43～49 1 2 3 Index データ 1 データ 2 データ 3 データ 4 データ 15～32 Key データ 1 データ 2 データ 3 データ 4 データ ID データ 1 行データ 2 行データ 3 行データ 4 行データ Index データ 1 データ 2 データ 3 データ 4 データ Index データ 1 データ 2 データ 3 データ 4 データ 6～10 Index データ 1 データ 2 データ 3 データ 4 データ 1 データ 2 データ 3 データ 4 データ 1～5 ID データ 1 行データ 2 行データ 3 行データ 4 行データ 1 3 2 1～32 16～49 11～21 22～32 1～10 15～32 35～42 42～49

旧テーブル

新テーブル

(53)

試験実施結果マトリクス

（単位は秒）

試験

3の整列済

データ利用時の試

験結果について

説明します。

(54)

試験

3: OPTIMIZE

TABLE実施試験

整列済データ投入時

• OPTIMIZE TABLEの実施が速い理由

−

整列済データ投入直後の状態

（旧テーブル）から、

整列済データ投入直

後の状態

（新テーブル）に変換します。

−

旧テーブルは整列済であるため、データ取り出しが高速です。

−

新テーブルへ投入するデータは整列済であるため、データ投入は高速

です。

1～10 1～32 35～49 35～42 43～49 1 2 3 Index データ 1 データ 2 データ 3 データ 4 データ 15～32 Key データ 1 データ 2 データ 3 データ 4 データ ID データ 1 行データ 2 行データ 3 行データ 4 行データ

旧テーブル

新テーブル

(55)

試験実施結果マトリクス

（単位は秒）

試験４の試験

結果について

説明します。

(56)

試験４

:

OPTIMIZE

TABLE実施後検索性能試験

• 検索性能が向上する理由

−

OPTIMIZE

TABLE実施前の状態が、未整列データ投入直後と整列済

データ投入直後のどちらであっても、

OPTIMIZE TABLE実施によって、

整列済データ投入直後の状態

と同じ状態になるためです。

OPTIMIZE TABLE 実施後

テーブル

(57)

まとめ

• 大量データ投入時は、

プライマリ・キーやインデックス・キー

で整列したデータを利用しましょう。

• 実運用時には、

定期的に

OPTIMIZE TABLEを実施

しましょう。

(58)

(59)

お問い合わせはカスタマーインフォメーションセンターへ 03-5304-6660 月～金9:00～19:00 土10:00～18:00（日、祝祭日、年末年始および5/1を除く） Linux/オープンソース製品に関する情報は http://www.hp.com/jp/opensource/ 記載されている会社名および商品名は、各社の商標または登録商標です。記載事項は2007 年 4 月現在のものです。本書に記載された内容は、予告なく変更されることがあります。 HP製品とサービスに対する保証は、それらに付属する保証書に記載された事項に限られます。ここに記載した内容は一切追加の保証を意味するものではありません。本書中の技術的あるいは編集上の誤り、省略に対して、いかなる責任も負いかねますのでご了承ください。

日本ヒューレット・パッカード株式会社