Teradata SQLパフォーマンス改善Tips(1) - PIを使って結合する
Teradataは、Primary Index(PI)を結合カラムにしたときに結合が最速になる、とマニュアルに書いてある。
以下はこの性質を使ってパフォーマンス改善した事例。
夜間バッチの時間短縮のため、時間のかかっているジョブをチェックしてみると、こんな結合条件のSQLが見つかった。
insert into ... select ... from t1 /* (col1, col2) が Primary Index */ left outer join t2 on t1.col1 = t2.col1 and substr(t.col2, 1, 4) = t2.col2 and substr(t.col2, 5, 4) = t2.col3
Primary Index の一部であるcol2をsubstr()で分解しているため、PIを使ったmerge joinができず、t1, t2ともに再配置されていた。
結合条件を以下のように書き換えると、t1の再配置が抑止され、またt1のPIが結合に使用されるようになり、実行時間が1/8になった。
left outer join t2 on t1.col1 = t2.col1 and t1.col2 = t2.col2 || t2.col3
Teradataは単一テーブルから全件返すときもスプールを使う
Teradataで3000万件ぐらいのテーブルをただ全件引いてみると、
select * from t /* 3000万件 */
しばらくだんまりになった後で、猛然と結果が返ってくる。
このだんまりの時間に何をしているのかexplainで調べてみると、
3) We do an all-AMPs RETRIEVE step from TST.t by way of an all-rows
scan with no residual conditions into Spool 1 (group_amps), which
is built locally on the AMPs.
何と全件スプールに書き写している。
ここでテーブルを丸ごと転記する意味は何なのだろう？
TeradataはI/Oがあまりにも速いので（数100万件のテーブルから数件を選択するクエリなら、インデックス使ってるのかテーブルスキャンしてるのか、人間には区別が付かない）、この特性のために困ったことは一度もないのだが、スプールを使う理由と、もしあれば回避策が知りたい。
Teradataはselect句が返す列数を減らすと速くなる
Teradataで3本以上のテーブルを結合する場合、必ず中間結果のスプールへの(つまりディスクへの)書き出しが発生する。
例えばa, b, cの3本を結合するなら、aとbの結合結果をスプールに書いて、次にスプールとcを結合する。
b, cが件数の非常に少ないテーブルであっても、スプールを使うのを止めない。
ということは、中間結果のレコード長を減らせばスプールに書き出すバイト数も減少するので、DBMS内の処理時間を大幅に短縮できる。
100万件のトランザクション t に100件と10件のマスタ m1, m2 を結合するSQLを考えてみる。
それぞれのマスタには { コード, 名称, カナ名称, 短縮名, ... } といった列があり、いまマスタから取得したいのは短縮名だけだとする。
PostgreSQLなら、以下の a) b) はほとんど同じ速さで処理されるだろう。
a) select * from t inner join m1 on m1.x=t.x inner join m2 on m2.y=t.y b) select t.*, m1.短縮名, m2.短縮名 from t inner join m1 on m1.x=t.x inner join m2 on m2.y=t.y
PostgreSQLは t と m1 をハッシュ結合してできた1行1行を、さらに m2 とハッシュ結合する。中間結果をスプールに吐くことはない。
a) b) のオーバーヘッドの違いはすべてメモリ上でのことであり、select句で列名を列挙することにより結果表の行長を半分にしたところで、DBサーバ上の処理時間は1割も違わないだろう。
Teradataなら、結果表の行長を半分にするとクエリが本当に2倍速くなることがある。
よって、返す列数に神経質になる意味は十分にある。
Primary Indexは一意に近いほどよい、わけではない
Teradataはいわゆるshared-nothingアーキテクチャを採用していて、1つのテーブルのデータは、AMPと呼ばれる数十台の仮想マシンに分散して格納される。
Teradataは各レコードのキーすなわちPrimary Index(=PI)をハッシュ関数に食わせて、出てきた値からそのレコードを格納するAMPを決める。PIの値が同じレコードは必ず同じAMP(の、極力同じブロック)に配置されることになっている。
よって、PIの値が十分にばらけていないと、各AMPが持つデータ件数が偏り、テーブルスキャンの性能が下がるなどの悪影響が出る。
例えば出荷伝票テーブルのPrimary Indexが都道府県コードで、なおかつ出荷先の5割が東京都だったら、東京のデータを持つAMPにデータが偏って配置されてしまう。
逆にPIの値が分散するほどに、各AMPのデータ件数は均一に近づいていき、効率よく並列処理できるようになる。
今日は何の話をしたいのかというと、だからって複合キーを作ってまでPIを一意に近づけようとしてはならない、ということだ。
3億件のテーブルのPrimary Indexが顧客コード1列だけになっているのを指して「データに偏りが出ているはずだからPIを { 日付, 顧客コード, ... } にして一意にするべきだ」みたいな話してる人が居たんだけど、そんなことして大丈夫ですか、という話だ。
何がまずいかというと、PIを複合キーに変えることで倍以上遅くなってしまうクエリがあるかもしれないのだ。
TeradataのJOIN処理は各AMPで独立して行われる。そして、結合されるデータのペアは必ず同じAMP上になくてはならない。
だから、顧客コードをPIとする10万件の顧客マスタに、200万件の出荷伝票を結合するクエリ：
select count(*) from 顧客マスタ c /* 10万件 */ inner join 出荷伝票 t /* 200万件 */ on t.顧客コード=c.顧客コード
は、
- 出荷伝票のPIも顧客コードだけなら、結合相手のレコードが必ず同じAMP上にあるので、すぐに結合処理を開始できる
- 出荷伝票のPIが { 顧客コード, 日付 } だったら、結合相手のレコードは必ずしも同じAMP上にない。だから、200万件の出荷伝票を顧客コードに基づいて全AMPに再配置してから*1結合処理をしなくてはならない
ということになる。
1.は200万件を1回だけ読めばよいが、2.だと200万件を 読む->中間ファイルに書く->それをまた読む の3ステップで処理しなくてはならない。
実際、PIの定義だけが異なる200万件のテーブルを2本作ってexplainしてみると、予測実行時間が倍以上違う。
実行計画が違うところだけ切り出すとこんな感じ：
顧客コードだけがPI
4) We do an all-AMPs JOIN step from TST.c by way of a RowHash match
scan with no residual conditions, which is joined to TST.t by way
of a RowHash match scan with no residual conditions. TST.c and
TST.t are joined using a merge join, with a join condition of (
"TST.t.kcd = TST.c.kcd"). The result goes into Spool 3
(all_amps), which is built locally on the AMPs. The size of Spool
3 is estimated with low confidence to be 2,075,377 rows. The
estimated time for this step is 3.78 seconds.
日付＋顧客コードがPI
4) We do an all-AMPs RETRIEVE step from TST.c by way of an all-rows
scan with no residual conditions into Spool 4 (all_amps) fanned
out into 4 hash join partitions, which is built locally on the
AMPs. The size of Spool 4 is estimated with high confidence to be
277,832 rows. The estimated time for this step is 0.64 seconds.
5) We do an all-AMPs RETRIEVE step from TST.t by way of an all-rows
scan with a condition of ("NOT (TST.t.kcd IS NULL)") into
Spool 5 (all_amps) fanned out into 4 hash join partitions, which
is redistributed by hash code to all AMPs. The size of Spool 5 is
estimated with high confidence to be 2,035,056 rows. The
estimated time for this step is 4.49 seconds.
6) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an
all-rows scan, which is joined to Spool 5 (Last Use) by way of an
all-rows scan. Spool 4 and Spool 5 are joined using a hash join
of 4 partitions, with a join condition of ("kcd = kcd").
The result goes into Spool 3 (all_amps), which is built locally on
the AMPs. The size of Spool 3 is estimated with low confidence to
be 2,035,056 rows. The estimated time for this step is 2.47
seconds.
Teradataの奇妙な実行計画の読み方についてはいずれ書きます、ということで説明は省略させていただいて、ポイントは太字の部分。これは200万件のデータを全AMPに配置し直すことを示している。
TeradataでSQLのパフォーマンスチューニングをする場合、この再配置をどうやって殺すかがポイントになる。
PIはテーブルごとに1回しか指定できないので、大事に使わなくてはならない。
結合条件に使われないような複合キーをPIにしてしまうと、あらゆるJOINで再配置やファンアウト*2といった重い処理が発生する。このことへの手当てを考えずにPIを複合キーにしてはならない。
うちの会社は休み放題にすべきだ
勤怠管理なんてやめてしまえばいいのだ。休みたかったら休む。会社に行きたい日だけ行く。そういう会社を目指すべきだ。
行っても行かなくてもいいなら、たまには行きたくなる日もあるだろう。行くのが義務だから毎日行きたくないのだ。
40年前の実証研究で否定された、時代遅れの成果主義なんて止めてしまえばいいのだ。
「がんばった分だけ見返りがある」なんて仕組みには何の魅力もない。「やってもやらなくても見返りがある」なら魅力あるけど。
そんな会社なら職場を守るために狂ったように働いてもいい。
「そんなふざけた制度では誰も働かない」なんて言ってはいけない。会社はソ連じゃないのだ。参加者を選べるのだから、そういう会社をこの世に成り立たせることに使命感を持つ人間だけで回せばいい。俺なら狂ったように働くね。
気が向いたときだけ働いて、めんどくさくなったら休む。それでも誰も文句を言わない。そんな会社なら全身全霊をかけて守りたいと思うね。
朝から晩まで働けば高給をくれる会社を支えるのとどっちが魅力的だろうか。みんなわかってるんじゃないの。
何がビジネスとして成立するかなんて、誰にもわかりはしないのだ。5年ぐらい前、社長と全社員が憎悪し合って上から下まで全員が不機嫌で疲弊しきっている会社に常駐したことあるけど、そんな状態でもちゃんとビジネスが成り立つんだよな。その会社四ツ谷にあるんだけどこの前近く通ったらまだあったぜその会社。社員全員がうんざりしてる会社でも存続できるんだから、勤怠がふざけた会社にだって可能性あるよな。
twitterみたいなくだらないアイデアがここまで大きくなることが分かっていました、て人がどれだけ居るのかね。何がアリで何がナシかなんて、一般人に分かるわけないよな。そりゃ普通の会社を休み放題にしたらつぶれるだろうけど、休み放題を前提にして全部の制度を再設計したら上手くいくかもしれないんだよな。フィットが大事だってポーターの本にも書いてたぜ。関係ないけど。
... みたいなこと考えたんですけどどうでしょうか。と社長に言ってみたら、性犯罪者を見るような目で見られたので撤回した。
小林秀雄が素読について語ったこと
引き続き「人間の建設 (新潮文庫)」から。岡潔との対談の最後に、小林秀雄が素読について語ったこと。
論語を簡単に暗記してしまう。暗記するだけで意味が分からなければ、無意味なことだというが、
それでは論語の意味とはなんでしょう。それは人により年齢により、さまざまな意味にとれるもの
でしょう。一生かかったってわからない意味さえ含んでいるかもしれない。それなら意味を教える
ことは、実に曖昧な教育だとわかるでしょう。丸暗記させる教育だけが、はっきりした教育です。
論語はまず何を措いても、「万葉」の歌と同じように意味を孕んだ「すがた」なのです。古典は
みんな動かせない「すがた」です。その「すがた」に親しませるという大事なことを素読教育が
果たしたと考えればよい。「すがた」に親しませるということができるだけで、「すがた」を理解
させることはできない。とすれば、「すがた」教育の方法は、素読的方法以外には理論上ないはず
なのです。
実際問題としてこの方法が困難となったとしても、原理的にはこの方法の線からはずれることは
できないはずなんです。
岡本吏郎師は「フォトリーディングは現代の素読です」と言っていた。また「『××入門』みたいな本を読んじゃダメです。いきなり原典にあたることは鉄則です」とも。
我々は「意味を理解しなければ読んだことにはならない」という思い込みから自由になっていいのだ。
