2017年データベースの選択は?(第3回)

TPCベンチマークで実行計画と処理時間を比較してみた

前回まで、機能の比較を行ってきましたが、机上で機能だけ比較してみてもデータベースの良さは分かりません。同じデータで同じSQLを実行してみてどのような実行計画でどのような処理時間になるのか?を調べてみたいと思います。実行する処理について特定のお客様で使用した処理では、客観性が無いことと処理内容を公開出来ないので、ここではTPCベンチマークを採用することにしました。
TPCベンチマークとは、TPC(Transaction Processing Performance Council)という組織が客観的かつ検証可能な性能データを広めるために策定したベンチマークテストです。そのベンチマークテストにはTPC-C / TPC-H / TPC-R / TPC-Wなどがあり、昔から有名なのは、更新性能(業務系システムを想定したワークロード定義)を計るTPC-Cと集計性能(情報系システムを想定したワークロード定義)を測るTPC-Hです。TPC-Cは比較的単純な処理で実行計画・処理時間ともに差が出にくいと思われるので、今回はより複雑な検索・集計SQLを実行するTPC-Hで実行計画を比較してみました。
TPC-Hベンチマークテストで用いられるテーブルのER図は下図です。TPC-HではScale Factorというパラメータでベンチマークデータ量を変えられるので、今回は最小のScale Facor 1でデータを作成することとしました。また、データの生成は、ベンチマークツールHammerDB( http://www.hammerdb.com/)を使用して生成しました。
TPC-H(*1)は、22のSQL文(Q1~Q22)からなりますがHammerDBでTPC-Hを実行したものをトレースしてSQL文を抜き出し、22のリテラルのSQL文を生成しました。データはHammerDBによって生成されたデータを、弊社で取り扱っているデータレプリケーション製品「Attunity Replicate」(*2)で同期を行い、件数・データ種別ともに一致させています。
*1) TPC-H定義書:http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-h_v2.17.3.pdf
*2) Attunity Replicate製品紹介ページ:https://www.insight-tec.com/products/Attunity/Replicate

データ件数は、最大件数のLINEITEMテーブルで600万件、次にORDERSテーブルで150万件となっており、情報系システムとしては多くはありませんが、データベースの処理を計るには充分な件数かと思います。SQLは、3テーブル以上を結合するものやサブクエリ、相関サブクエリと呼ばれるサブクエリが外部クエリのカラムを参照している複雑なSQLも多く含みます。ここまで複雑なSQLになると実行計画のオプションが少ないMySQLなどでは、かなり不利になるのでは無いかと心配されます。

実行環境は、大袈裟なものは用意出来ないので、私のPC上でVMWare Workstationを使用してOS(Oracle Enterprise Linux7)、Memory(8GB)、Storage(40GB/SSD)を揃えて実行しました。各データベースのパラメータは、デフォールトというわけにも行かないのでキャッシュを4GBとして、その他のパラメータは、PostgreSQLはpgTune(*3)、MySQLはMySQL Tuner(*4)という推奨パラメータを算出してくれるツールで設定しています。特にMySQL Tunerは、現在の設定から実際の処理状態に対して判断して推奨値を算出してくれるのでお薦めです。
SQL文は、データベースによってSQL文の記述形式が若干異なるため以下の修正を行っています。

  1. INTERVAL型の記述形式
  2. 上位n件レコード取得
  3. 外部参照キーは、MySQLではインデックスが生成されてしまうので、合わせて他のデータベースでもインデックスを生成
  4. LINEITEMテーブル(600万件)の検索では、HASHジョインが出来ないMySQLではあまりに厳しい条件になってしまうので、
    インデックスを2つ追加

従って、純粋なTPC-Hにはなっていません。処理時間は、SQL単体の処理をQ1からQ22まで順番に複数回実行して取得しています。比較にための環境なので細かい突っ込みはご遠慮ください・・・
*3) PgTune(http://pgtune.leopard.in.ua/)
*4) MySQL Tuner(https://github.com/major/MySQLTuner-perl)

TPCベンチマークで実行計画と処理時間を比較してみた結果

処理時間は?

処理時間の絶対値は色々と支障があるので基準となるデータベースをOracleとし、処理時間の比較で表しています。さらに実行結果の表では、差分だけでは同じ1秒でも0.1秒と1.1秒の差なのか10秒と11秒なのか分からないので、色分けして表記しています。オレンジ色になっているものは処理時間がOracleに比較して5倍以上のなっているもので、黄色になっているものは2倍以上になっているものです。
実際に比較してみると22のSQLトータル処理時間では差が大きいものの、一部のSQL実行時間以外はそれほど差がありませんでした。22のSQLの内、特にQ1とQ18で処理時間に大きな差が出ました。逆に言うとこの2つのSQLについて個別チューニング実施することでトータルの処理時間差を詰めることが出来るとも言えます。

実行計画は?

実際にクエリ時間差が大きかったQ1とQ18について詳しく調べて見ましょう

Q1での処理時間は、1:3:9と大きな差が付いていますがこれは、実行計画をみても分かるようにMySQLでは、パラレルクエリが無いためにフルスキャンが実行され一時表を作成してソーティングすることによってグルーピングしているため、パラレル処理とソーティング処理で大きな差が付いてしまっているようです。
PostgreSQLでは、パラレルクエリが実行計画として採用されますがHashでグルーピングしてクイックソートでソーティングしています。Oracleは、パラレルクエリ+Hash Group by+パラレルオーダーと豊富な実行計画で高速に処理が終了しています。各データベースが持っている実行計画に差があるため、各データベースの特徴が良くわかります。

Q18での処理時間は、1:4:6となりました。SQL文が複雑なため、Q1に比較すると採用される実行計画の種類と組み合わせが非常に複雑です。Oracleは、Nested Loop JoinとHash Joinを的確に組み合わせてジョインも他のデータベースに比較すると多段になっています。PostgreSQLは、Hash Joinのみが採用されており、MySQLは、Nested Loop Joinしか無いので上手く組み合わせて実行計画が立てられています。ここでもパラレル処理の有無は、大きな処理時間差になっています。

全体的にみると

  1. Oracle/PostgreSQLが高速に処理出来ているのは、Parallel Queryを使用していることが優位に働いています。
  2. JOIN処理については、Oracle/PostgreSQLともにほとんどのクエリでHASH JOINが採用されています。
  3. MySQLは、HASH JOINが無いのでNESTED LOOP/BLOCK NESTED LOOPでJOINされています
  4. Oracle/PostgreSQLでは、グルーピング処理でもHASHが多く採用されていてJOINでもGROUP BY/ORDER BYでもINDEXが使用されることが多くありません。
  5. MySQLでは、HASHが採用されていないため、INDEXを適切に使用することが重要です。
  6. MySQLは、サブクエリが遅いという話もありますが、最新のMySQLでは少ない実行計画でもうまく組み合わせられており、サブクエリ=遅いということはありません。
  7. 大きく差のついているQ1は、大規模なソーティング処理で差が出ています。

PostgreSQLは、work_memパラメータが、MySQLもsort_buffer_sizeパラメータが小さいため、大規模テーブルのソーティングで時間を要しています。全般的にParallel QueryとSORT処理時間の差が全体的な処理時間の差になっているように感じられます。
そこでParrallel Queryをオフにして処理時間を確認してみると、ここでは紹介しませんが全体的に処理時間が延びて、処理時間差が小さくなり、処理によっては処理時間が逆転するものも現れました。

どのデータベースを使いますか?

  1. Oracleは、大規模なテーブルを含むクエリでは、Parallel Query(PQ)が非常に効果的であるとともに実行計画も豊富で複雑なSQLにも適切な実行計画を適用していました。
  2. PostgreSQLは、PQが有効になってOracleと同等の実行計画が採用されて大規模&比較的複雑なSQLでも充分に満足することの出来る結果が得られました。
  3. MySQLは、実行計画が豊富とは言えない中でNLJ+αとINDEXを活用して大規模&複雑なクエリでも意外に!他DBに対抗出来るパフォーマンスが得られました。
    →HASH JOINがサポートされているMariaDBでは?
  4. MySQL/MariaDBは、豊富なストレージエンジン(VP/SPIDER/COLUMNARなどなど)を適用してOSSでスケーラブル且つ高速なデータベースへの期待大!!

といったところでしょうか?どのデータベースもバージョンアップが頻繁に行われており、機能も豊富になってきています。今回自分で検証してみた結果、OSSのデータベースは商用データベースにも充分に対抗出来る性能を持っていると感じられました。但し、商用データベースは、チューニングする事無くどのようなSQLでもコンスタントに良いパフォーマンスを発揮しており、やはり充分に有償の価値があります。

2018年になって公開された今回のブログでちょっと乗り遅れた感が否めません。データベースは、常に改善されていますのでこのブログでご紹介したツールを使って、実際に皆さん自身で検証してみてはいかがでしょうか?
ブログでは、処理時間などは、公表することが難しいですがセミナーでは、「ご紹介」しています。
今年は、SQL Server on Linux・MariaDBなど新しいデータベースも含めてデータ件数も拡大してご紹介したいと思います。(予定です・・・予定)恐らくセッションでしか公開できないネタが多くなりますので今年のdb tech showcase2018にもご来場ください。


【2017年データベースの選択は?】連載一覧

2017年データベースの選択は?(第1回)

  • どんなデータベースがあるの?
  • データベースライセンス比較
  • データベース機能比較

2017年データベースの選択は?(第2回)

  • データベース機能比較(続き)
  • Spiderによるスケールアウトパーティショニング

2017年データベースの選択は?(第3回)

  • TPCベンチマークで実行計画と処理時間を比較してみた
  • TPCベンチマークで実行計画と処理時間を比較してみた結果
  • 全体的にみると
  • どのデータベースを使いますか?