Hatena::ブログ(Diary)

SH2の日記 RSSフィード

2010-01-12 データベース負荷テストツールまとめ(3) このエントリーを含むブックマーク

データベース負荷テストツールまとめの第3回です。

かなり期間が空いてしまいましたが、今回は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図も貼り付けておきます。

f:id:sh2:20100111152441p:image

どうやら何かの売り上げデータのようですね。中央に注文テーブル(orders)と注文明細テーブル(lineitem)があって、注文明細テーブルには部品(part)と仕入れ先(supplier)をM:N結合したpartsuppテーブルとのリレーションが張られています。

お金のデータに注目すると、部品テーブルのp_retailpriceが希望小売価格、partsuppテーブルのps_supplycostが仕入れ先ごとの実際の卸価格、注文明細テーブルのl_extendedpriceは明細ごとの販売価格、そして注文テーブルのo_totalpriceが明細を束ねた合計価格となっています。

9番のクエリ「ある名前の部品について、仕入れ先の国および年度別の利益額を求めなさい」というものです。基本的には注文明細テーブルを集計すればよいのですが、レコードの絞り込みや利益額の計算に必要なデータが各テーブルに散らばってしまっているため、かなり厳しいクエリになっています。

f:id:sh2:20100111163933p:image

厳しいというか、ER図をじっと眺めていても効率的なSQL実行計画がさっぱり見えてきません。これが仕事だったらテーブルを非正規化するかスタースキーマに作り変えてしまう、あるいはOracleマテリアライズド・ビューを作ってしまうところですが…。それをやってしまっては反則なので、あくまでこのテーブル構造を守りながら、無茶なクエリにどれだけ耐えられるかというのがTPC-Hのポイントになるわけです。

DBT-3

DBT-3は、OSDLによるTPC-Hの実装です。TPCが提供するクエリとテストデータ生成ツールをPostgreSQLSAP DBで動作するようにカスタマイズし、データベース構築スクリプトと負荷テスト実行用のシェルスクリプトを追加した構成になっています。

このうちCで書かれているのはテストデータ生成ツールのみで、あとはシェルスクリプトになっています。以前紹介したDBT-1、DBT-2はあまりプログラムの品質が良いとは言えなかったのですが、DBT-3については処理の見通しもよく、大きな問題はないと思います。

DBT-3 IPA

DBT-3 IPA版は、オリジナルのDBT-1をMySQLに対応させたものです。実際の移植作業は日本HPが実施しています。HPさんの報告書はかなり出来が良いので、一度目を通しておくことをおすすめします。

DBT-3 IPA版はMySQL 5.1でもそのまま動作します。ということで試してみました。

f:id:sh2:20100111231548p:image

スケールファクタが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ソリューションを提供しています。

Kickfireは実際にTPC-Hでも優秀な結果を残しています。こうした動きもおさえておきたいですね。

続きます

というわけで第3回はTPC-Hベースの負荷テストツールを2つ見てきました。他にもご紹介したいツールはまだまだたくさんあるので、不定期ですがじっくり続けていきたいと思います。それでは。

Connection: close