MySQL Server 5.0 InnoDB データベース 大量データ投入 日本ヒューレット パッカード株式会社オープンソース コンピテンスセンタ 2008 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice
Agenda データベースへの大量データ投入について 試験環境 InnoDBテーブルへの大量データ投入性能試験 投入後データの検索性能試験 OPTIMIZE TABLE 実施試験 OPTIMIZE TABLE 実施後の検索性能試験 試験結果からの結論 試験結果とInnoDBの仕組みについて
データベースへの大量データ投入について
データベースへの大量データ投入とは? 何をするのか? システム移行や新規システム構築時 バックアップのリストア時に 必要とされるデータをシステムに投入します 一般に CSV ファイルに記録されたデータをデータベースに読込ませます Oracle では SQL*Loader ツールを利用します MySQL では LOAD DATA IN FILE 文を利用します Legacy システム New システム データ出力 CSV データ データ投入
データベースへの大量データ投入の問題 大規模なシステムになるほど 大きなデータ (GB 単位 ) の投入が必要となり 投入時間も長くなります 例えば 4GB の CSV データファイルを MySQL に投入した時 誤った方法で投入すると約 6 時間かかります ( 詳細は後でご説明します ) 可能な限り高速にデータを投入したい! どうすれば高速に投入できるのか? また 投入後の性能に影響があるのか? プライマリ キーで整列したデータを投入すると高速らしい 実際に試験で確認!
運用時のデータベース性能劣化問題 運用中のデータベースに対して挿入 削除が頻繁に実施されると データベースの性能が劣化していきます どのデータベースでも定期的にメンテナンスの実施が推奨されています MySQL では OPTIMIZE TABLE の定期的な実施が推奨されています 本当にパフォーマンスは向上するのか? その実施時間は? 実際に試験で確認!
実施試験概要 4 つの試験を実施しました 試験 1: 大量データ投入性能試験 整列済および未整列の CSV データファイルをデータベースへ投入し その処理時間を計測 試験 2: 大量データ投入後の検索性能試験 大量データ投入後に整列させた CSV データファイルの生成を行い その処理時間を計測 試験 3: 投入後の OPTIMIZE TABLE 実施性能試験 大量データ投入後に OPTIMIZE TABLE を実施し その実施時間を計測 試験 4: OPTIMIZE TABLE 実施後の検索性能試験 OPTIMIZE TABLE 後に整列させた CSV データファイルの生成を行い その処理時間を計測
試験環境
試験環境 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 クライアント 自作スクリプト
実施条件 (1) テーブル定義 テーブル名 : loadtest 利用ストレージエンジン : InnoDB カラム定義 : 1 行がほぼ 512Bytes となるように定義 カラム名 id1 id2 id3 id4 data1 data2 data3 データ型 decimal(8) decimal(4) decimal(4) decimal(5) char(25) char(255) char(207) キーとして利用したカラム キーの定義 : 定義方法で 2 種類のテーブルを用意 プライマリ キーとして data1 を定義したテーブル インデックス キーとして data1 を定義したテーブル
実施条件 (2) 試験用 CSV データファイル テーブル loadtest に適合した CSV データファイル 6 種類のデータファイルを用意 ファイルサイズとデータの整列有無の組み合わせ データファイルの整列は sort コマンドを利用 ファイルサイズ 1G 2G 4G data1が昇順で整列している
実施条件 (3) 試験用 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
試験実施結果マトリクス テーブル定義 投入データ種別 投入データサイズ データ整列時間 試験 1 大量データ投入 試験 2 投入後検索性能 試験 3 最適化実施時間 試験 4 最適化後検索性能 1G プライマリ キー利用 未整列データ 2G 4G 1G 85 未整列データを sort コマンドで整列した時間です 整列済データ 2G 163 4G 365 1G 未整列データ 2G インデックス キー利用 4G 1G 85 整列済データ 2G 163 4G 365 ( 単位は秒 )
試験 1: InnoDB テーブルへの 大量データ投入性能試験
試験 1: InnoDB テーブルへの大量データ投入性能試験 試験目的 投入データの整列有無による 投入性能について試験します 試験概要 CSV データファイルを LOAD DATA IN FILE 命令を用いて MySQL データベースに投入し その実施時間を計測します 対象テーブル loadtest ( プライマリ キー利用時とインデックス利用時 ) 投入方法 LOAD DATA INFILE filename INTO loadtest FIELDS... 投入データ 未整列データ : 1GB, 2GB, 4GB 整列済データ : 1GB, 2GB, 4GB 未整列データを data1 で整列して作成します
試験 1: InnoDB テーブルへの 大量データ投入性能試験結果 大量データ投入時間 25000 投入時間 ( 秒 ) 20000 15000 10000 データ プライマリ未整列 ( 秒 ) プライマリ整列 ( 秒 ) インデックス未整列 ( 秒 ) インデックス整列 ( 秒 ) 1GB 169 94 125 107 2GB 2,750 203 301 213 4GB 21,654 ( 約 6 時間 ) 399 (6 分 39 秒 ) 612 (10 分 12 秒 ) 461 (7 分 41 秒 ) 5000 0 1G 2G 4G 1G 2G 4G 1G 2G 4G 1G 2G 4G 未整列データ投入整列済データ投入未整列データ投入整列済データ投入 プライマリ キー利用時 インデックス キー利用時
試験実施結果マトリクス テーブル定義 投入データ種別 投入データサイズ データ整列時間 試験 1 大量データ投入 試験 2 投入後検索性能 試験 3 最適化実施時間 試験 4 最適化後検索性能 1G 169 プライマリ キー利用 インデックス キー利用 未整列データ 整列済データ 未整列データ 2G 2,750 4G 21,654 ( 約 6 時間 ) 1G 85 94 2G 163 203 4G 365 399 (6 分 39 秒 ) 1G 125 2G 301 4G 612 (10 分 12 秒 ) 1G 85 107 プライマリ キー利用時に未整列データを投入すると 2GB で 1GB の約 16.3 倍 4GB で 1GB の約 128.1 倍の処理時間が必要です プライマリ キー利用時に整列済データを投入すると 2GB で 1GB の約 2.2 倍 4GB で 1GB の約 4.2 倍とほぼデータサイズに正比例した処理時間が必要です インデックス利用時は未整列 整列済に関係なくプライマリ キー利用時の整列済データ投入時同様に高速に投入できます 整列済データ 2G 163 213 4G 365 461 (7 分 41 秒 ) ( 単位は秒 )
試験 2: 投入後データの検索性能試験
試験 2: 投入後データの検索性能試験 試験目的 整列済データおよび未整列データ投入後のシーケンシャルなデータ検索の性能差有無を確認するため data1 で整列した CSV データファイルの生成処理時間を計測しました 試験概要 対象テーブル loadtest ( プライマリ キー利用時とインデックス キー利用時 ) 生成方法 : SELECT * FROM loadtest ORDER BY data1 INTO OUTFILE filename... 対象データ 未整列データ投入後 (1GB, 2GB, 4GB) の CSV データファイル生成時間 整列済データ投入後 (1GB, 2GB, 4GB) の CSV データファイル生成時間
試験 2: 投入後データの検索性能試験 30000 投入後データの検索処理時間 処理時間 ( 秒 ) 25000 20000 15000 10000 データ プライマリ未整列 ( 秒 ) プライマリ整列 ( 秒 ) インデックス未整列 ( 秒 ) インデックス整列 ( 秒 ) 1GB 355 47 175 58 2GB 1,109 114 3,664 128 4GB 2,594 (43 分 14 秒 ) 243 (4 分 3 秒 ) 26,201 ( 約 7 時間 ) 267 (4 分 24 秒 ) 5000 0 1G 2G 4G 1G 2G 4G 1G 2G 4G 1G 2G 4G 未整列データ投入後の生成時間 プライマリ キー利用 整列済データ投入後の生成時間 未整列データ投入後の生成時間 インデックス キー利用 整列済データ投入後の生成時間
試験実施結果マトリクス テーブル定義 投入データ種別 投入データサイズ データ整列時間 試験 1 試験 2 試験 3 試験 4 プライマリ キー利用 インデックス利用 未整列データ 整列済データ 未整列データ 1G 169 355 2G 2,750 1109 4G 21,654 ( 約 6 時間 ) 2,594 (43 分 14 秒 ) 1G 85 94 47 2G 163 203 114 4G 365 399 (6 分 39 秒 ) 243 (4 分 3 秒 ) 1G 125 175 2G 301 3,664 4G 612 (10 分 12 秒 ) 26,201 ( 約 7 時間 ) 1G 85 107 58 プライマリ キー利用時未整列データ投入後の検索性能は 2GB で 1GB の約 3.1 倍 4GB で 1GB の約 7.3 倍であり インデックス利用時未整列データ投入後の結果と比較すれば非常に高速であると言えます インデックス利用時未整列データ投入後の検索性能が 2GB で 1GB の約 20.9 倍 4GB で 1GB の約 149.7 倍です 整列済データ投入後の検索性能は プライマリ キー利用時およびインデックス利用時ともに高速です 整列済データ 2G 163 213 128 4G 365 461 (7 分 41 秒 ) 267 (4 分 24 秒 ) ( 単位は秒 )
試験 3: OPTIMIZE TABLE 実施試験
試験 3: OPTIMIZE TABLE 実施試験 試験目的 挿入削除が頻繁に行われるデータベースでは OPTIMIZE TABLE の実施が推奨されています まず実際に実施した場合の実施時間ついて試験します 実施概要 非常に多くの挿入 削除処理を行ったデータベースの代替として 未整列データを投入したデータベースを利用 対象テーブル loadtest ( プライマリ キー利用時のみ ) 未整列データ投入後および整列済データ投入後に OPTIMIZE TABLE を実施し その処理時間を計測
試験 3: OPTIMIZE TABLE 実施試験 4000 OPTIMIZE TABLE 実施時間 実施時間 ( 秒 ) 3500 3000 2500 2000 1500 データ未整列 ( 秒 ) 整列済 ( 秒 ) 1GB 562 101 2GB 1435 206 4GB 3719 ( 約 1 時間 ) 471 (7 分 51 秒 ) 1000 500 0 1G 2G 4G 1G 2G 4G 未整列データ投入後 OPTIMIZE TABLE 実施 Primary Key 利用時 整列済データ投入後 OPTIMIZE TABLE 実施
試験実施結果マトリクス テーブル定義 投入データ種別 投入データサイズ データ整列時間 試験 1 大量データ投入 試験 2 投入後検索性能 試験 3 最適化実施時間 試験 4 最適化後検索性能 プライマリ キー利用 インデックス キー利用 未整列データ 整列済データ 未整列データ 整列済データ 1G 169 355 562 2G 2,750 1109 1,435 4G 21,654 ( 約 6 時間 ) 2,594 (43 分 14 秒 ) 3,719 ( 約 1 時間 ) 1G 85 94 47 101 2G 163 203 114 206 4G 365 399 (6 分 39 秒 ) 243 (4 分 3 秒 ) 471 (7 分 51 秒 ) 1G 125 175 OPTIMIZE TABLE 実施時間はデータサイズに 2G 応じて増加します 301 3,664 4G 612 未整列データ投入後の 26,201 OPTIMIZE TABLE 実 (10 分 12 施時間は長く またデータサイズによる処理時秒 ) ( 約 7 ) 1G 85 間増加率も整列済データ投入後より長くなりま 107 58 す 2G 163 213 128 4G 365 461 (7 分 41 秒 ) 267 (4 分 24 秒 ) ( 単位は秒 )
試験 4: OPTIMIZE TABLE 実施後の検索性能試験
試験 4: OPTIMIZE TABLE 実施後の検索性能試験 試験目的 OPTIMIZE TABLE 実施後に検索性能がどの程度向上するのかを確認します 実施概要 非常に多くの更新 挿入 削除処理を行ったデータベースの代替として 未整列データを投入したデータベースを利用 対象テーブル loadtest ( プライマリ キー利用時のみ ) 投入後データの検索性能試験と同様に OPTIMIZE TABLE 実施後に data1 で整列した CSV ファイルを生成する時間を計測
試験 4: OPTIMIZE TABLE 実施後の検索性能試験 OPTIMIZE TABLE 実施後の検索処理時間 処理時間 ( 秒 ) 3000 2500 2000 1500 1000 データ 未整列データ投入後 OPTIMIZE TABLE 未実施 整列データ投入後 OPTIMIZE TABLE 未実施 未整列データ投入後 OPTIMIZE TABLE 実施 整列データ投入後 OPTIMIZE TABLE 実施 1GB 355 47 31 31 2GB 1109 144 137 133 4GB 2594 (43 分 14 秒 ) 243 (4 分 3 秒 ) 274 (4 分 34 秒 ) 275 (4 分 35 秒 ) 500 0 1G 2G 4G 1G 2G 4G 1G 2G 4G 1G 2G 4G 未整列データ投入後整列済データ投入後未整列データ投入後整列済データ投入後 OPTIMIZE TABLE 未実施 OPTIMIZE TABLE 実施プライマリ キー利用
試験実施結果マトリクス テーブル定義 投入データ種別 投入データサイズ データ整列時間 試験 1 大量データ投入 試験 2 投入後検索性能 試験 3 最適化実施時間 試験 4 最適化後検索性能 プライマリ キー利用 インデックス キー利用 未整列データ 整列済データ 未整列データ 1G 169 355 562 31 2G 2,750 1109 1,435 137 4G 21,654 ( 約 6 時間 ) 2,594 (43 分 14 秒 ) 3,719 ( 約 1 時間 ) 274 (4 分 34 秒 ) 1G 85 94 47 101 31 2G 163 203 114 206 133 4G 365 399 (6 分 39 秒 ) 243 (4 分 3 秒 ) 471 (7 分 51 秒 ) 1G 125 175 投入データ種別によらず OPTIMIZE 2G 301 TABLE 3,664実施後の検索性能は 整列済 4G データ投入直後の検索性能とほぼ同 612 26,201 (10 分 12 秒 ) ( 約等の性能を示します 7 時間 ) 1G 85 107 58 275 (4 分 35 秒 ) 整列済データ 2G 163 213 128 4G 365 461 (7 分 41 秒 ) 267 (4 分 24 秒 ) ( 単位は秒 )
試験結果からの結論
試験実施結果マトリクス テーブル定義 投入データ種別 投入データサイズ データ整列時間 試験 1 大量データ投入 試験 2 投入後検索性能 試験 3 最適化実施時間 試験 4 最適化後検索性能 プライマリ キー利用 インデックス キー利用 未整列データ 整列済データ 未整列データ 1G 169 355 562 31 2G 2,750 1109 1,435 137 4G 21,654 ( 約 6 時間 ) 2,594 (43 分 14 秒 ) 3,719 ( 約 1 時間 ) 274 (4 分 34 秒 ) 1G 85 94 47 101 31 2G 163 203 114 206 133 4G 365 399 (6 分 39 秒 ) 243 (4 分 3 秒 ) 1G 125 175 2G 301 3,664 4G 612 (10 分 12 秒 ) 26,201 ( 約 7 時間 ) 1G 85 107 58 471 (7 分 51 秒 ) 275 (4 分 35 秒 ) 整列済データ 2G 163 213 128 4G 365 461 (7 分 41 秒 ) 267 (4 分 24 秒 ) ( 単位は秒 )
試験結果からの結論 大量データ投入時は 整列済データを用意しましょう 投入処理時間および検索性能に大きく影響します 整列済データの生成も sort コマンド等を利用すれば簡単です 挿入 削除が頻繁に発生する場合は OPTIMIZE TABLE を実施しましょう 検索性能の向上が期待できます 実施時間がある程度必要とされるため 計画的に実施することが必要です Reference Manual では 1 週間から 1 ヶ月に 1 回の実施が推奨されています
試験結果と InnoDB の仕組みについて
InnoDB テーブルのデータ構造 (1) 行データ格納領域 Clustered Index と呼ばれる一意な識別子で行データを管理 格納します インデックス格納領域 テーブルに定義されているインデックス キーと 対応する行データの Clustered Index がペアで格納されます Clustered Index となるキー以外のキーがテーブルに定義されていない場合 インデックス格納領域は作成されません InnoDB テーブル インデックス キー インデックス 1 格納領域 インデックス 2 格納領域 Clustered Index 行データ格納領域 行データ 行データ
InnoDB テーブルのデータ構造 (2) Clustered Index の決定 プライマリ キーが定義されている場合 プライマリ キーを Clustered Index として利用します ( 大量データ投入試験のプライマリ キー利用時に相当 ) プライマリ キーが定義されておらず Null ではないユニークインデックスが定義されている場合 ユニークインデックスを Clustered Index として利用します どちらも定義されていない場合 InnoDB が自動生成する ROW_ID を Clustered Index として利用します ( 大量データ投入試験の Index 利用時に相当 ) InnoDB テーブル インデックス 1 格納領域 Clustered Index 行データ格納領域 インデックス インデックス 2 格納領域 行データ 行データ
InnoDB テーブルのデータ構造 (3) 行データ格納領域の構造と検索処理 行データは B-Tree (m 分木 ) 構造に格納されます 枝の部分は 検索のための Clustered Index の範囲を格納 葉の部分にあたる Page に Clustered Index と行データを格納 Page サイズは 16KB 固定 B-Tree 構造を利用して検索を行います Page 単位で InnoDB データファイルに格納 検索 挿入処理時に対象行を含むPageをメモリに読込む InnoDB Table space Page 枝 葉 (Page 16KB) 1 行データ 2 行データ 3 行データ 4 行データ 1~32 35~59 1~10 15~32 35~42 43~49 Key データ Clustered Index と行データが格納されています Index データ select * from table where pk=3; Clustered Index の範囲 50-59
InnoDB テーブルのデータ構造 (4) インデックス格納領域の構造と検索処理 行データ格納領域と同様に B-Tree 構造に格納します インデックスを B-Tree での識別子に利用しています 行データの代わりに Clustered Index が格納されます インデックス格納領域から Clustered Index を検索して 行データ格納領域を検索 行データを取得します インデックスを識別子として Clustered Index を格納 AA-DE select * from table where idx=ba; DF-KZ AA-BZ CA-DE DF-JI JJ-KZ Clustered Index 得られた Clustered Index で検索 1~32 35~49 1~10 15~32 35~42 43~49 Clustered Index を識別子として行データを格納 ID データ Key データ Index データ ID データ Key データ Index データ AA Clustered Index 1 Clustered Index 1 Clustered index 1 行データ インデックス AB Clustered Index 2 Clustered index 2 Clustered Index 2 行データ AH Clustered Index 3 Clustered Index 3 Clustered Index 3 行データ BA Clustered Index 4 Clustered Index 4 Clustered Index インデックス格納領域 検索対象データ 4 行データ 行データ格納領域
InnoDB テーブルへの行挿入 (1) 行データ挿入先 Page を検索します Page に行データが格納可能な場合は その Page に格納します Page が一杯で行データを挿入できない場合 Page 分割を行います 各 Page には元の Page の行データが半分づつ格納されます B-Tree の枝の構造が修正されます B-Tree 構造も再構成されます ID データ 1 行データ 2 行データ 1~32 1~10 1~3 15~32 7~10 15~32 insert into table values (7, row_data) ID データ 7 行データ 8 行データ 35~49 35~42 43~49 3 行データ 10 行データ 1Page あたりの格納行数が最悪 Page の半分になります 6 行データ 10 行データ Page に格納可能な場合は行データを書き込みます Page に書き込めない場合は Page 分割を行います
InnoDB テーブルへの行挿入 (2) 整列済データの投入 整列済データ投入時は 常に一番最後の Page に行データが格納されます Page に行データを挿入できなくなると Page 分割することなく 新しい Page が作成され 行データを挿入します 最後の Page 以外では Page 全体に行データが格納されるため 全データの格納に必要となる Page 数は最小になります ID データ 1 行データ 2 行データ 3 行データ 6 行データ 10 行データ Page が一杯になった 1~10 整列済データの投入 15~32 ID データ 15 行データ 16 行データ 17 行データ 新しい Page を作成します
InnoDB テーブルへの行挿入 (3) インデックス キー利用時の挿入 行データは行データ格納領域に挿入します Clustered Index として適切なキーが存在しない場合 ROW_ID を利用します ROW_ID は挿入順に振られるため整列済データ投入と同じ挿入処理となります インデックス格納領域にインデックスと Clustered Index をデータとして挿入します 挿入方法は行データ格納領域への挿入と同様です 格納データが小さいため 未整列データの挿入でも比較的高速に実施可能です インデックスと特定された Clustered Index を挿入します 行データを先に挿入し Clustered Index の値を特定します AA-DE DF-KZ 1~32 35~49 AA-BZ CA-DE DF-JI JJ-KZ Clustered Index 1~10 15~32 35~42 43~49 ID データ Key データ Index データ ID データ Key データ Index データ AA Clustered Index 1 Clustered Index 1 Clustered index 1 行データ インデックス AB Clustered Index 2 Clustered index 2 Clustered Index 2 行データ AH Clustered Index 3 Clustered Index 3 Clustered Index 3 行データ BA Clustered Index 4 Clustered Index 4 Clustered Index 4 行データ インデックス格納領域 行データ格納領域
整列済データ投入時と未整列データ投入時のデータ保持状態比較 整列済データ投入時 Page は Page の生成順序 つまり B-Tree の配置順序で整列されて Disk に書き込まれています 未整列データ投入時 ランダムに Page が生成されることにより Disk 上の配置順序もランダムになります また Page 分割も発生します 1~32 16~49 1~32 35~49 1~10 15~32 35~42 42~49 1~10 15~32 35~42 43~49 1~5 6~10 11~21 22~32 ID データ 1 行データ Key データ Index データ ID データ 1 行データ Key データ Index データ Index データ Index データ Page 分割発生! 2 行データ 2 行データ 3 行データ 3 行データ 4 行データ 4 行データ シーケンシャルリードに有利 シーケンシャルリードに不利 1 2 3 1 3 2
OPTIMIZE TABLE の実施について OPTIMIZE TABLE 実施前テーブル ( 旧テーブル ) OPTIMIZE TABLE 実施後テーブル ( 新テーブル ) インデックス格納領域 行データ格納領域 インデックス格納領域 行データ格納領域 OPTIMIZE TABLE 実施 = テーブルコピー 1Page あたりの格納行数が増加し Page の Disk 上の配置順序が整列されます 旧テーブルから Cluster Index で整列したデータを 新テーブルへ挿入していきます 新テーブルの行データ格納領域は整列済データ投入直後と同等の状態になります
試験実施結果マトリクス テーブル定義 投入データ種別 投入データサイズ データ整列時間 試験 1 大量データ投入 試験 2 投入後検索性能 試験 3 最適化実施時間 試験 4 最適化後検索性能 プライマリ キー利用 インデックス利用 未整列データ 整列済データ 未整列データ 1G 169 355 562 31 2G 2,750 1109 1,435 137 4G 21,654 ( 約 6 時間 ) 2,594 (43 分 14 秒 ) 3,719 ( 約 1 時間 ) 274 (4 分 34 秒 ) 1G 85 94 47 101 31 2G 163 203 114 206 133 4G 365 399 (6 分 39 秒 ) 1G 125 175 2G 301 3,664 4G 612 (10 分 12 秒 ) 26,201 ( 約 7 時間 ) 1G 85 107 58 試験 1と試験 2のプライマ 243 471 リ キー利用時未整列 (4 分 3 秒 ) (7 分 51 秒 ) データ投入時の試験結果の原因を説明します 275 (4 分 35 秒 ) 整列済データ 2G 163 213 128 4G 365 461 (7 分 41 秒 ) 267 (4 分 24 秒 ) ( 単位は秒 )
試験 1 および試験 2: プライマリ キー利用時未整列データ投入 試験 1: 未整列データ投入が遅い理由 ランダムに行データを挿入するためです 挿入先 Page の検索が必要です Page 分割が発生します Disk 上の Page 配置順序がランダムになります 試験 2: シーケンシャルな検索が遅い理由 Page が Disk 上にランダムに配置されているためです シーケンシャルな Disk アクセス効率が低くなります 投入データサイズが大きくなればなるほど データ投入と検索処理は遅くなります ID データ 1 行データ 2 行データ 3 行データ 4 行データ 1~5 6~10 Key データ Index データ Index データ Index データ 1~10 1 3 2 15~32 11~21 1~32 22~32 16~49 35~42 42~49 シーケンシャルリードに不利
試験実施結果マトリクス テーブル定義 投入データ種別 投入データサイズ データ整列時間 試験 1 大量データ投入 試験 2 投入後検索性能 試験 3 最適化実施時間 試験 4 最適化後検索性能 プライマリ キー利用 インデックス利用 未整列データ 整列済データ 未整列データ 整列済データ 1G 169 355 562 31 2G 2,750 1109 1,435 137 4G 21,654 ( 約 6 時間 ) 2,594 (43 分 14 秒 ) 3,719 ( 約 1 時間 ) 274 (4 分 34 秒 ) 1G 85 94 47 101 31 2G 163 203 114 206 133 4G 365 399 (6 分 39 秒 ) 243 (4 分 3 秒 ) 1G 125 175 2G 301 3,664 4G 612 (10 分 12 秒 ) 26,201 ( 約 7 時間 ) 1G 85 107 58 2G 163 213 128 4G 365 461 (7 分 41 秒 ) 267 (4 分 24 秒 ) 471 (7 分 51 秒 ) 試験 1と試験 2のプライマリ キー利用時整列済データ投入時の試験結果の原因を説明します 275 (4 分 35 秒 ) ( 単位は秒 )
試験 1 および試験 2: プライマリ キー利用時整列済データ投入 試験 1: 整列済データ投入が早い理由 順序よくデータ挿入が行われるためです 行データは常に行データ格納領域の最終 Page に挿入されます 1Page あたりの格納行数が多くなります 試験 2: シーケンシャルな検索が速い理由 Disk 上に Page が整列された状態で配置されているためです シーケンシャルな Disk アクセス効率が高くなります 1~32 35~49 1~10 15~32 35~42 ID データ 1 行データ 2 行データ 3 行データ 4 行データ Key データ Index データ 1 2 3 43~49 シーケンシャルリードに有利
試験実施結果マトリクス テーブル定義 投入データ種別 投入データサイズ データ整列時間 試験 1 大量データ投入 試験 2 投入後検索性能 試験 3 最適化実施時間 試験 4 最適化後検索性能 プライマリ キー利用 インデックス利用 未整列データ 整列済データ 未整列データ 1G 169 355 562 31 2G 2,750 1109 1,435 137 4G 21,654 ( 約 6 時間 ) 2,594 (43 分 14 秒 ) 274 (4 分 34 秒 ) 1G 85 94 47 101 31 2G 163 203 114 206 133 4G 365 399 (6 分 39 秒 ) 243 (4 分 3 秒 ) 1G 125 175 2G 301 3,664 4G 612 (10 分 12 秒 ) 26,201 ( 約 7 時間 ) 1G 85 107 58 インデックス キー利用し 3,719 た未整列データの投入 ( 約 1 時間 ) 試験と 投入後の性能試験の結果について説明します 471 (7 分 51 秒 ) 275 (4 分 35 秒 ) 整列済データ 2G 163 213 128 4G 365 461 (7 分 41 秒 ) 267 (4 分 24 秒 ) ( 単位は秒 )
試験 1 および試験 2: インデックス キー利用時未整列データ投入 試験 1: 未整列データ投入が速い理由 プライマリ キーが無いため Clustered Index として ROW_ID を利用します ROW_ID は投入順に割り振られるため 整列済データとして 行データ格納領域にデータ投入されます インデックス格納領域の構築は データ量が小さいため インデックス キーがランダムでも作業量は小さいです 試験 2: シーケンシャルな検索が遅い理由 検索対象行データが 行データ格納領域内にランダムに配置されているためです 行データ取得のために Page の読込みが非常に多くなります インデックス格納領域 A1~A41 ID データ A1 ClI20 A2 CI31 A3 C52 A4 C33 A A1~B50 ID データ B1 ClI22 B2 CI53 B3 C33 B4 C1 B1~B50 B C1~D30 行データ格納領域 1~32 35~49 1~10 15~32 35~42 ID データ 1 行データ 2 行データ 3 行データ 4 行データ Key データ Index データ 1 2 3 43~49
試験実施結果マトリクス テーブル定義 投入データ種別 投入データサイズ データ整列時間 試験 1 大量データ投入 試験 2 投入後検索性能 試験 3 最適化実施時間 試験 4 最適化後検索性能 プライマリ キー利用 インデックス利用 未整列データ 整列済データ 未整列データ 1G 169 355 562 31 2G 2,750 1109 1,435 137 4G 21,654 ( 約 6 時間 ) 2,594 (43 分 14 秒 ) 3,719 ( 約 1 時間 ) 274 (4 分 34 秒 ) 1G 85 94 47 101 31 2G 163 203 114 206 133 4G 365 399 (6 分 39 秒 ) 243 (4 分 3 秒 ) 1G 125 175 2G 301 3,664 4G 612 (10 分 12 秒 ) 26,201 ( 約 7 時間 ) 1G 85 107 58 インデックス キー利用した 471 整列済データの投入試験と (7 分 51 秒 ) 投入後の性能試験の結果について説明します 275 (4 分 35 秒 ) 整列済データ 2G 163 213 128 4G 365 461 (7 分 41 秒 ) 267 (4 分 24 秒 ) ( 単位は秒 )
試験 1 および試験 2: インデックス キー利用時整列済データ投入 試験 1: 未整列データ投入が速い理由 インデックス格納領域 行データ格納領域 プライマリ キーが無いため Clustered Index として ROW_ID を利用します ROW_ID は投入順に割り振られるため 整列済データとして 行データ格納領域にデータ投入されます A1~B50 C1~D30 A1~A41 B1~B50 ID データ ID データ A1 ClI20 B1 ClI22 A2 CI31 B2 CI53 A3 C52 B3 C33 ID データ 1 行データ 2 行データ 1~10 1~32 15~32 Key データ 35~49 35~42 Index データ 43~49 インデックス キーが整列されているため インデックス格納領域の構築も高速です 試験 2: シーケンシャルな検索が速い理由 インデックス キーと同じ順序で行データが行データ格納領域に配置されているためです A4 C33 A B4 C1 B 3 行データ 4 行データ 1 2 3
試験実施結果マトリクス テーブル定義 投入データ種別 投入データサイズ データ整列時間 試験 1 大量データ投入 試験 2 投入後検索性能 試験 3 最適化実施時間 試験 4 最適化後検索性能 プライマリ キー利用 インデックス利用 未整列データ 整列済データ 未整列データ 1G 169 355 562 31 2G 2,750 1109 1,435 137 4G 21,654 ( 約 6 時間 ) 2,594 (43 分 14 秒 ) 3,719 ( 約 1 時間 ) 274 (4 分 34 秒 ) 1G 85 94 47 101 31 2G 163 203 114 206 133 4G 365 399 (6 分 39 秒 ) 1G 125 175 2G 301 3,664 4G 612 (10 分 12 秒 ) 26,201 ( 約 7 時間 ) 1G 85 107 58 試験 3の未整列データ利用時の試 243 471 (4 分 3 秒 ) (7 分 51 秒 ) 験結果について説明します 275 (4 分 35 秒 ) 整列済データ 2G 163 213 128 4G 365 461 (7 分 41 秒 ) 267 (4 分 24 秒 ) ( 単位は秒 )
試験 3: OPTIMIZE TABLE 実施試験 未整列データ投入時 OPTIMIZE TABLE の実施が遅い理由 未整列データ投入直後の状態 ( 旧テーブル ) から 整列済データ投入直後の状態 ( 新テーブル ) に変換します MySQL では 旧テーブルから Clustered Index 順に行データを取り出しつつ 新テーブルへのデータ投入を実施しています 旧テーブル 新テーブル 1~32 16~49 1~10 15~32 35~42 42~49 1~32 35~49 1~5 6~10 11~21 22~32 1~10 15~32 35~42 43~49 ID データ 1 行データ 2 行データ 3 行データ 4 行データ Index データIndex データIndex データ ID データ 1 行データ 2 行データ 3 行データ 4 行データ Key データ Index データ 1 3 2 1 2 3
試験実施結果マトリクス テーブル定義 投入データ種別 投入データサイズ データ整列時間 試験 1 大量データ投入 試験 2 投入後検索性能 試験 3 最適化実施時間 試験 4 最適化後検索性能 プライマリ キー利用 未整列データ 整列済データ 1G 169 355 562 31 2G 2,750 1109 1,435 137 4G 21,654 ( 約 6 時間 ) 2,594 (43 分 14 秒 ) 3,719 ( 約 1 時間 ) 274 (4 分 34 秒 ) 1G 85 94 47 101 31 2G 163 203 114 206 133 4G 365 399 (6 分 39 秒 ) 243 (4 分 3 秒 ) 471 (7 分 51 秒 ) 275 (4 分 35 秒 ) 1G 125 175 インデックス利用 未整列データ 整列済データ 2G 301 3,664 4G 612 (10 分 12 秒 ) 26,201 ( 約 7 時間 ) 1G 85 107 58 2G 163 213 128 4G 365 461 (7 分 41 秒 ) 試験 3の整列済データ利用時の試験結果について説明します 267 (4 分 24 秒 ) ( 単位は秒 )
試験 3: OPTIMIZE TABLE 実施試験 整列済データ投入時 OPTIMIZE TABLE の実施が速い理由 整列済データ投入直後の状態 ( 旧テーブル ) から 整列済データ投入直後の状態 ( 新テーブル ) に変換します 旧テーブルは整列済であるため データ取り出しが高速です 新テーブルへ投入するデータは整列済であるため データ投入は高速です 旧テーブル 新テーブル 1~32 35~49 1~32 35~49 1~10 15~32 35~42 43~49 1~10 15~32 35~42 43~49 ID データ Key データ Index データ ID データ Key データ Index データ 1 行データ 1 行データ 2 行データ 2 行データ 3 行データ 3 行データ 4 行データ 4 行データ 1 2 3 1 2 3
試験実施結果マトリクス テーブル定義 投入データ種別 投入データサイズ データ整列時間 試験 1 大量データ投入 試験 2 投入後検索性能 試験 3 最適化実施時間 試験 4 最適化後検索性能 プライマリ キー利用 未整列データ 整列済データ 1G 169 355 562 31 2G 2,750 1109 1,435 137 4G 21,654 ( 約 6 時間 ) 2,594 (43 分 14 秒 ) 3,719 ( 約 1 時間 ) 274 (4 分 34 秒 ) 1G 85 94 47 101 31 2G 163 203 114 206 133 4G 365 399 (6 分 39 秒 ) 243 (4 分 3 秒 ) 471 (7 分 51 秒 ) 275 (4 分 35 秒 ) 1G 125 175 インデックス利用 未整列データ 2G 301 3,664 4G 612 (10 分 12 秒 ) 26,201 ( 約 7 時間 ) 1G 85 107 58 試験 4 の試験結果について説明します 整列済データ 2G 163 213 128 4G 365 461 (7 分 41 秒 ) 267 (4 分 24 秒 ) ( 単位は秒 )
試験 4: OPTIMIZE TABLE 実施後検索性能試験 検索性能が向上する理由 OPTIMIZE TABLE 実施前の状態が 未整列データ投入直後と整列済データ投入直後のどちらであっても OPTIMIZE TABLE 実施によって 整列済データ投入直後の状態と同じ状態になるためです OPTIMIZE TABLE 実施後テーブル 1~32 35~49 1~10 15~32 35~42 43~49 ID データ 1 行データ 2 行データ 3 行データ 4 行データ Key データ Index データ 1 2 3
まとめ 大量データ投入時は プライマリ キーやインデックス キーで整列したデータを利用しましょう 実運用時には 定期的に OPTIMIZE TABLE を実施しましょう
www.hp.com/jp/opensource
お問い合わせはカスタマーインフォメーションセンターへ 03-5304-6660 月 ~ 金 9:00~19:00 土 10:00~18:00( 日 祝祭日 年末年始および 5/1 を除く ) Linux/ オープンソース製品に関する情報は http://www.hp.com/jp/opensource/ 記載されている会社名および商品名は 各社の商標または登録商標です 記載事項は2007 年 4 月現在のものです 本書に記載された内容は 予告なく変更されることがあります HP 製品とサービスに対する保証は それらに付属する保証書に記載された事項に限られます ここに記載した内容は一切追加の保証を意味するものではありません 本書中の技術的あるいは編集上の誤り 省略に対して いかなる責任も負いかねますのでご了承ください (c) Copyright 2008 Hewlett-Packard Development Company,L.P. 日本ヒューレット パッカード株式会社 102-0076 東京都千代田区五番町 7