OSS iPedia

1.　はじめに

ここでは、DBT-3をMySQLで実行した結果に関す る考察を述べると共に、OSDL DBT-3 version 1.9 のMySQLへの 移植作業を行なう中で分かったことも報告する。

1.1.　考察概要

移植作業および評価実施時に以下の知見が得られた。

2.1.　Loadテストの実施順序

Loadテストを実施したところ試験実施時間が非常に長かった。 原因の調査を行ったところ、以下の事実が判明した。

• オリジナルDBT-3では、データを投入してからインデックス作成を行っている。
• MySQLのALTER TABLEはオリジナルのテーブルと同一内容のテンポラリテーブルを作成し、 テンポラリテーブルに対して指定された処理を行う。
  ("MySQL 5.0 Reference Manual 13.1.2. ALTER TABLE Syntax"の記述による)

上記の事実から、インデックスの作成実施後にデータを投入する試験を実施した。 図1に「DL145」の環境で試験した結果を示す。

図1: Loadテスト実施順序による影響(環境DL145)

図1の通り、MySQLではインデックスを作成してからデータを投入した方がデータ投入処理が高速になる。 この違いは ALTER TABLE の処理方法に起因すると推定される。

上記結果より、MySQL版DBT-3ではLoadテストでのデータ投入とインデックス作成順序を入れ替え、インデックスを作成してからデータを投入することにした。

2.2.　IN句とサブクエリを利用したクエリの実施問題

MySQL版DBT-3を用いてスケールファクタ1でPowerテストを実施したところ、1日経過後も試験が完了しなかった。

調査したところ、Powerテストにて実行される22個のクエリの中の1つのクエリ、クエリ18の 実施処理が異常に長いために試験が先に進んでいないことが分かった。 そして、このクエリ18はIN句とサブクエリを組み合わせた複雑なクエリであった。

この問題をMySQL AB.へ問い合わせたところ、使用したMySQL5.0.26ではサブクエリの最適化に一部問題があり、 特にIN句とサブクエリを組み合わせた場合のパフォーマンスに問題が発生する可能性が高いことが分かった。

そこで、この問題を回避するためのワークアラウンドとして以下の2つの方法を検討した。

• IN 句のかわりに EXISTS 句を利用する。
• テンポラリテーブルを利用し、IN句とサブクエリの組み合わせ利用をやめる。

上記の2つの方法を確認したところ、 いずれも妥当な時間でクエリの実施を完了することが出来た。 そこで2つの方法のうちより高速であった2番目テンポラリテーブルを利用する方法を MySQL版DBT-3 で採用することにした。 以下に変更前と変更後のクエリ18の概要を示す(表5参照)。

SELECT …
FROM customer, orders, lineitem
WHERE o_orderkey in
   (SELECT l_orderkey FROM lineitem
    GROUP BY l_orderkey
    HAVING sum(l_quantity) > :1)
    AND …
GROUP BY …
ORDER BY …;

CREATE TEMPORARY TABLE
    tmp_l_orderkey (t_orderkey integer);
INSERT INTO tmp_l_orderkey
    SELECT l_orderkey FROM lineitem
    GROUP BY l_orderkey
    HAVING sum(l_orderkey) > :1;

SELECT …
FROM customer, orders, lineitem, tmp_l_orderkey
WHERE o_orderkey = t_orderkey
    AND …
GROUP BY …
ORDER BY …;

2.3.　二重のサブクエリを含むクエリのEXPLAIN実施時間

DBT-3をスケールファクタ1かつストリーム1の条件で実施したところ、 Powerテストの結果に比較してThroughputテストの結果が異常に低かった。

PowerテストとThroughputテストの結果指標の計算方法が異なるため 厳密にはこれら結果指標が一致することはないが、このテストは ストリーム数を1にして試験したので、PowerテストとThroughputテストの 実施結果はほぼ同等になる予想であった。

調査したところThroughputテストはPowerテストと異なり、計測対象として各クエリのEXPLAIN実施時間を含めていることが分かった。また、特にクエリ22のEXPLAINの実施時間が異常に長いことも分かった。 表6にスケールファクタ1の時のクエリ22のEXPLAIN実施時間とクエリ実施時間を示す。

他のクエリのEXPLAIN実施時間は大半が1秒以内で完了し、長いものでも十数秒で完了しているにも かかわらず、表6に示すとおりクエリ22のEXPLAIN実施時間が約1.5時間となっている。 一方、クエリ22のクエリ実施時間は1秒以内で完了しているので、クエリの実施自体には問題が無いと考えられる。 また、クエリ22は二重のサブクエリを含む複雑なクエリであり、DBT-3のすべてのクエリの中で二重のサブクエリを 持つものはクエリ22のみである。

以上から、MySQLにおいては二重のサブクエリを含むような複雑なクエリのEXPLAIN処理に 問題が発生する場合があることが考えられる。

この問題はDBT-3の測定を進める上で大きな影響を与えると考えたため、 MySQL版DBT-3ではクエリ22のEXPLAIN実施を取りやめることとした。

2.4.　クエリ実施時間のばらつきの抑制

DBT-3のPowerテストを同一条件で複数回実施し、そのクエリ実施時間の合計を比較したところ、 いくつかの試行でクエリ実施時間合計が他と比較して2倍近い値となっている結果があった(図2参照)。

図2: SF=4の時のPowerテストのクエリ実施時間合計(環境DL385A)

調査の結果、クエリ8の実施時間が特に大きく異なっていることが分かった。 クエリ8について調査を行ったところ、EXPLAIN実施によって得られるクエリ実施計画が 上記の2つの試行で全く異なることが分かった。 そこで、この結果の違いがクエリ実施時間に大きく影響していると推定した。

MySQLでクエリのEXPLAINを実施すると、そのクエリを処理する際のテーブル参照順序、 利用インデックス、処理コストを得ることができる。

2.5.　ハードウェア性能の違いによる影響の考察

ハードウェア性能の違いが測定結果に与える影響を見るために、環境DL145と環境DL385Cの試験結果を比較する。 ここでは、特にPowerテストの結果を比較した(図4参照)。 比較した2つの試験の環境のCPUは同一であるが、メモリサイズ、MySQLのバッファサイズ、利用ディスクシステムが以下のように異なる。

図4: Powerテストにおけるハードウェアの影響(環境DL145とDL385CのPowerテスト実施結果の比較)

この結果からは、基本的に環境DL145より、多くのメモリを登載し良いディスクシステムを使用している 環境DL385Cの方が良い結果となっている。 そして、それぞれのスケールファクタにおける環境DL145の結果を1とした時の、環境DL385Cの結果の比率を 比べてみると、スケールファクターが大きくなるに従いその差(倍率)も大きくなる傾向が見られる。

次にPowerテスト実施時のCPU使用率を見てみる。(図5参照)。

図5: Powerテストにおけるハードウェアの影響 (環境DL145とDL385CのCPU利用率の比較)

この結果から、多くのメモリと良いハードディスクシステムを使用することで、 CPUのiowaitに費やす率が下がっている事が確認出来た。

しかし、今回の試験結果だけでは、これらの違いがメモリとハードディスクシステムの どちらにどの程度依存したものであるかということまでは特定することは出来なかった。

以上からMySQLを利用する場合、より大きなメモリと高性能ディスクを利用することで、より高いパフォーマンスを期待することが可能であると考えられる。 しかし、今回の試験結果だけでは、これらの違いがメモリとハードディスクシステムのどちらにどの程度依存ているかや、 メモリ量と性能との関係を特定することは出来なかった。

2.6.　x86環境とx86-64環境の違いによる影響の考察

x86環境とx86-64環境の違いを調査するため、x86環境で実施した環境DL385Aと x86-64環境で実施した環境DL385BのPowerテストの結果を比較した。(図6参照)。

ここで言うx86環境とx86-64環境の違いとは、具体的に以下のような違いを差している。

図6: Powerテストにおけるx86環境とx86-64環境の違いによる影響(環境DL385AとDL385BのPowerテスト実施結果の比較)

図6に示す通り、DBT-3のPowerテストの結果指標は環境を変更してもほとんど同じ結果となる。 しかし、クエリ実施時間を比較すると、x86-64環境のクエリ実施時間はx86環境のクエリ実施時間と 比較してSF=2で約10%、SF=4で約20%、SF=8で約30%の遅くなっている(図7参照)。

図7: Powerテストにおけるx86環境とx86-64環境の違いによる影響 (環境DL385AとDL385Bのクエリ実施時間の比較)

このように図6と図7で結果が矛盾しているように見えるため、 スケールファクタ8におけるPowerテストのクエリ実施時間などについて 両環境での比較調査を行ったところ、以下のことが分かった。

• x86環境に比べx86-64環境でのクエリ実施時間が長くなるクエリが5つある(合計3262秒低速化する)
  クエリ: 2,8,9,14,21
• 他の17個のクエリはx86環境に比べx86-64環境でのクエリ実施時間が短くなる(合計895秒高速化する)
• Powerテスト時に同時に実施されるリフレッシュストリームの実施時間については、有意な差は見られなかった。

このことから、 少数のクエリの実施時間が極端に遅くなることでクエリ実施時間の合計自体が大きくなっても、 より多くのクエリの実施時間が短くなる場合にDBT-3のPowerテストの結果指標の値が 良くなるために、図6と図7のような差が生じたと考えられる。

したがって、x86-64環境を使用した場合はx86環境を使用した場合と比べ、 クエリ実行時間が長くなりパフォーマンスが低下する可能性があることが分かる。 しかしこのような結果になった原因については、CPUのアーキテクチャ、 オペレーティングシステムやMySQLのバイナリなど様々な要因が考えられるが、 現時点では原因を特定できていない。

2.7.　MySQLのバッファサイズの違いによる影響の考察

x86環境と異なり、x86-64環境ではMySQLのバッファサイズ(innodb_buffer_pool_size)を2GB以上に設定することができる。 バッファサイズを2GBに設定した場合とより大きく14GBに設定した場合の違いを比較するため、 バッファサイズが2GBである環境DL385Bと14GBに設定したDL385CのPowerテストの結果を比較した(図8参照)。 どちらの環境もOSは64bitである。

図8: PowerテストにおけるMySQLバッファサイズの違いによる影響(環境DL385BとDL385CのPowerテスト実施結果の比較)

図8のPowerテストの結果におけるDL385Cのスケールファクタ2の突出した結果は、 データベース上のすべてのデータ(スケールファクタ2で約6GBのデータサイズ)が 14GBに設定されたMySQLのバッファ上に配置できていたためであると考えられる。 一方DL385Bのスケールファクタ2の結果は、MySQLのバッファが2GBしかないため、 データベース上のすべてのデータをMySQLバッファに配置できなかったために低下したと考えられる。

また、スケールファクタ8におけるデータベース上のデータサイズは約24GBとなるが、 このときのDL385BとDL385Cの結果を比較しても分かるとおり、 すべてのデータをMySQLバッファに配置できない場合であっても MySQLのバッファの大きさは有意な差としてパフォーマンスに現れている。

以上からMySQLを利用する際、十分なメモリサイズがあるならばより大きなMySQLのバッファを確保することで、パフォーマンス向上を期待できる。

2.8.　EXPLAIN実施時間に関する考察

2.9.　個々のクエリ実施時間に関する考察

個々のクエリ実施時間のスケールファクタによる影響を調査するため、 スケールファクタ8でPowerテストを実施した時のクエリ実施時間を環境DL385Bと環境DL385Cの測定結果を用いて比較調査した(図12参照)。

図12: SF=8の時のPowerテストのクエリ実施時間の比較(環境DL385BとDL385C)

図12では、DL385BとDL385Cにおいてクエリ8、9、14の実施時間が長いという共通な傾向がある。 一方、クエリ14について見るとDL385Cで一番遅がDL385Bでは3番目に遅いクエリとなっている。 このため、DL385BとDL385Cでのクエリ実施時間に関して共通の傾向を見つけることは出来なかった。

そこで、Powerテストの各クエリの実施時間をスケールファクタ1の時と、 スケールファクタ8のときで比較をし、その増加率についてなんらかの共通の 傾向がないか調査した(図13参照)。

図13: SF=1からSF=8へのPowerテスト時の個々のクエリのクエリ実施時間の増加率(環境DL385BとDL385C)

図13にあるとおり、クエリ14とクエリ2の増加率が他のクエリに比較して大きいことが分かった。 原因を調査するため、図13と同じスケールファクタ1とスケールファクタ8の増加率のグラフを ストリーム数1の時のThroughputテストのクエリ実施時間について作成した(図14参照)。

図14: SF=1からSF=8へのThroughputテスト時の個々のクエリのクエリ実施時間の増加率(環境DL385BとDL385C)

Powerテストの場合とThroughputテストの場合でその傾向が極端に異なっており、 Powerテストにおいて増加率の大きかったクエリ14とクエリ2がThroughputテストでは他に比較しても さほど増加率は大きいとはいえない結果となった。 一方、Throughputテストではクエリ20の増加率は非常に大きくなっている。

この原因の調査するために、PowerテストとThroughputテストそれぞれの実施方法の違いを調査した。

• Powerテストの実施手順
  
  1. データベースを再起動する
  2. 22個のクエリを以下の順序で実施する(常に一定)
     14, 2, 9, 20, 6, 17, 18, 8, 21, 13, 3, 22, 16, 4, 11, 15, 1, 10, 19, 5, 7, 12
• Throughputテストの実施手順
  
  1. データベースを再起動する
  2. 以下をストリームの数だけ並列に実行
     
     1. クエリ15と22を除く全20個のクエリをクエリ1から順にEXPLAIN実施する
     2. 22個のクエリを以下の順序で実施する(実施ストリームにより異なる)
        1番目に実行するストリームの場合(常に一定): 21, 3, 18, 5, 11, 7, 6, 20, 17, 12, 16, 15, 13, 10, 2, 8, 14, 19, 9, 22, 1, 4
        2番目、3番目とストリームが実施される場合、各々のクエリ実施順序は異なる。
  3. 上記と同時にリフレッシュストリームをストリーム数分実行する

2つのテストの実施手順と、上記増加率の大きいクエリの間には、次のような関係が見られた。

• Powerテストでの増加率の大きかったクエリ14とクエリ2は、Powerテストにおいては 最初に実行されるクエリである。
• Throughputテストで増加率の大きいクエリ20は、テストの最初に実施されていない。

Powerテストにおいては、クエリの実施順番と増加率には何らかの関係があるように見えるが、 Throughputテストではその傾向を見ることが出来なかった。

残念ながら、ここまでの分析ではクエリ実施時間に関する一般的な傾向を導き出すことは出来なかった。

3.　データダウンロード

4.　このデータの性能データ

5.　関連する考察データ