データベース負荷テストツールまとめ(3)
データベース負荷テストツールまとめの第3回です。
- データベース負荷テストツールまとめ(1) TPC-B、TPC-Wベースのツールを6つ紹介
- データベース負荷テストツールまとめ(2) TPC-Cベースのツールを6つ紹介
かなり期間が空いてしまいましたが、今回はTPC-Hベースのツールを見ていきたいと思います。
TPC-Hとは
TPC-HはRDBMSベンチマーク仕様の一つで、意思決定支援システム(DSS)としての性能を測定するものです。大規模なデータを対象にアドホックなクエリを実行します。クエリは全部で22種類定義されています。
TPC-HはTPC-B/W/Cなどと異なり、実行するクエリそのものやテストデータ生成ツールがTPCから提供されています。試しに、一番負荷が高い9番のクエリを確認してみましょう。
-- $ID$ -- TPC-H/TPC-R Product Type Profit Measure Query (Q9) -- Functional Query Definition -- Approved February 1998 :x :o select nation, o_year, sum(amount) as sum_profit from ( select n_name as nation, extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount from part, supplier, lineitem, partsupp, orders, nation where s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and p_partkey = l_partkey and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like '%:1%' ) as profit group by nation, o_year order by nation, o_year desc; :n -1
SQLだけだと分かりづらいので、ER図も貼り付けておきます。
どうやら何かの売り上げデータのようですね。中央に注文テーブル(orders)と注文明細テーブル(lineitem)があって、注文明細テーブルには部品(part)と仕入れ先(supplier)をM:N結合したpartsuppテーブルとのリレーションが張られています。
お金のデータに注目すると、部品テーブルのp_retailpriceが希望小売価格、partsuppテーブルのps_supplycostが仕入れ先ごとの実際の卸価格、注文明細テーブルのl_extendedpriceは明細ごとの販売価格、そして注文テーブルのo_totalpriceが明細を束ねた合計価格となっています。
9番のクエリは「ある名前の部品について、仕入れ先の国および年度別の利益額を求めなさい」というものです。基本的には注文明細テーブルを集計すればよいのですが、レコードの絞り込みや利益額の計算に必要なデータが各テーブルに散らばってしまっているため、かなり厳しいクエリになっています。
厳しいというか、ER図をじっと眺めていても効率的なSQL実行計画がさっぱり見えてきません。これが仕事だったらテーブルを非正規化するかスタースキーマに作り変えてしまう、あるいはOracleでマテリアライズド・ビューを作ってしまうところですが…。それをやってしまっては反則なので、あくまでこのテーブル構造を守りながら、無茶なクエリにどれだけ耐えられるかというのがTPC-Hのポイントになるわけです。
DBT-3
- 対応RDBMS:PostgreSQL、SAP DB
- 対応OS:Linuxなど
- 言語:C、シェルスクリプト
- 作者:OSDL(現在のThe Linux Foundation)
- ライセンス:The Artistic License
- トランザクション仕様:TPC-Hベース
- URL:http://sourceforge.net/projects/osdldbt/
DBT-3は、OSDLによるTPC-Hの実装です。TPCが提供するクエリとテストデータ生成ツールをPostgreSQL、SAP DBで動作するようにカスタマイズし、データベース構築スクリプトと負荷テスト実行用のシェルスクリプトを追加した構成になっています。
このうちCで書かれているのはテストデータ生成ツールのみで、あとはシェルスクリプトになっています。以前紹介したDBT-1、DBT-2はあまりプログラムの品質が良いとは言えなかったのですが、DBT-3については処理の見通しもよく、大きな問題はないと思います。
DBT-3 IPA版
- 対応RDBMS:MySQL
- 対応OS:Linuxなど
- 言語:C、シェルスクリプト
- 作者:日本OSS推進フォーラム
- ライセンス:The Artistic License
- トランザクション仕様:TPC-Hベース
- URL:http://ossipedia.ipa.go.jp/documents/dbt3-mysql/dbt3-1.9_mysql_porting.html
DBT-3 IPA版は、オリジナルのDBT-1をMySQLに対応させたものです。実際の移植作業は日本HPが実施しています。HPさんの報告書はかなり出来が良いので、一度目を通しておくことをおすすめします。
DBT-3 IPA版はMySQL 5.1でもそのまま動作します。ということで試してみました。
スケールファクタが1のとき、注文明細テーブルは約2.3GB(テーブル約900MB、インデックス約1,400MB)となり、データベース全体のサイズはおよそ3GBとなります。1時間17分もかかった9番のクエリなどは、完全にディスクI/Oネックになっています。それにしても、遅いですね。
また、移植作業報告書の5.3節で18番のクエリの変更について言及されていますが、
5.3.クエリ番号18の修正 (実施上の問題回避)
MySQL5.0ではクエリ番号18内にあるIN演算子とSELECT文の組み合わせた演算コストが非常に大きく、演算が完了できなかった。このため、テンポラリテーブルを利用したクエリに展開した。 MySQLにおけるテンポラリテーブルは、作成されたセッション中にのみ有効なテーブルとなっているため、 Throughputテストにおいて、同一クエリが実施された場合でも問題なく、クエリを実施可能となっている。
この原因については、漢(オトコ)のコンピュータ道: なぜMySQLのサブクエリは遅いのか。で詳しく解説されています。それぞれのクエリがどうして速いのか、あるいは遅いのかは、今ではほぼ明らかになっていると思います。
MySQLは内部的にINを直接処理することができないので、EXISTSに変換することでSQL的には相関のないサブクエリも相関サブクエリになってしまうのである。これがまさにMySQLのサブクエリが遅い!と言われている原因だろう。
MySQLのDSSへの適用性
TPC-Hを題材にしたMySQLの性能改善については、MySQL Conference & Expo 2008におけるSergey Petrunia氏のセッションも参考になります(PDF資料)。本資料の25ページにおいて、18番のクエリはMaterializationというテクニックによりMySQLの将来のバージョンで大幅に改善されることが述べられています。ただ、Sergey氏は昨年Sun Microsystemsを辞めてしまったのでちょっと微妙な状況ではありますが…。
こうした複雑なクエリを実行するDSSに対して、現在のMySQLが向いていないのは残念ながら事実です。しかし逆に考えればそれはビジネスチャンスでもあります。MySQLがオープンソースソフトウェアであることを利用して、現在複数のベンチャーがDSSソリューションを提供しています。