半空洞男女関係

思ったこととかプログラミングしてるときのメモとか色々かいてます。メールはidそのままgmail

Presto Performance Tuning読んだ

TreasureData Presto

今日は新人ブートキャンプ研修で id:beniyama 先生によるデータの研修だったのだけれど、Prestoなどの分散SQLクエリエンジンの話があり、その中でどうやったら効率良いクエリ書けるかという話が面白かったのでもう少し調べていた。

TDからPresto Performance Tuningという記事が出ていて、面白かったしためになったので簡単に訳してみた。

tl;dr

  • 必要なものだけSELECTする
  • timeは整数との比較になるようにする
  • TD_TIME_RANGEのUDFを活用する
  • GROUP BYするときは濃度が大きい方から(種類が多い方から)
  • ORDER BYするときはなるべくLIMIT
  • 精密さが求められないときはapproximate aggregate functionの活用を検討する
  • 複数のLIKE句はregexp_likeでまとめる
  • JOINするときは軽い方を手前にする
  • 上位N件を示したいときはRANK関数ではなくROW_NUMBER関数が使えないか検討する

前提条件

必要なカラムだけ指定する

Treasure Dataでは、特定のカラムのみを扱うことに絞ったクエリに最適化された、列指向フォーマットでデータを保存している。必要なカラムのみSELECTすることでパフォーマンスを劇的に改善することができる。ワイルドカードの代わりに、必要なカラムのみ選択するようにすべき。

[GOOD]: SELECT time,user,host FROM tbl
[BAD]:  SELECT * FROM tbl

時間指定の影響

インポートされた全てのデータはtimeフィールドを参考に1時間ごとに分けてパーティションに格納されている。時間指定を行えば、無駄なデータ読み込みを避けることが出来るので、スピードアップにつながる。

WHERE time <=> Integerになるようにする

WHERE句でtimeフィールドを使うとき、クエリーパーザはどのパーティションを使うべきか自動判別している。この時、比較元がintではなくfloatだと自動判別が効かない。

[GOOD]: SELECT field1, field2, field3 FROM tbl WHERE time > 1349393020
[GOOD]: SELECT field1, field2, field3 FROM tbl WHERE time > 1349393020 + 3600
[GOOD]: SELECT field1, field2, field3 FROM tbl WHERE time > 1349393020 - 3600
[BAD]:  SELECT field1, field2, field3 FROM tbl WHERE time > 13493930200 / 10
[BAD]:  SELECT field1, field2, field3 FROM tbl WHERE time > 1349393020.00
[BAD]:  SELECT field1, field2, field3 FROM tbl WHERE time BETWEEN 1349392000 AND 1349394000

TD_TIME_RANGEを使う

データをスライスする時にはTD_TIME_RANGE UDFを使うと便利

[GOOD]: SELECT ... WHERE TD_TIME_RANGE(time, '2013-01-01 PDT')
[GOOD]: SELECT ... WHERE TD_TIME_RANGE(time, '2013-01-01', NULL, 'PDT')
[GOOD]: SELECT ... WHERE TD_TIME_RANGE(time, '2013-01-01',
                                       TD_TIME_ADD('2013-01-01', '1day', 'PDT'))

ただ、TD_TIME_RANGEを割ってしまうと、パーティションを自動判別する最適化が効かないので注意。

[BAD]: SELECT ... WHERE TD_TIME_RANGE(time, TD_SCHEDULED_TIME() / 86400 * 86400))
[BAD]: SELECT ... WHERE TD_TIME_RANGE(time, 1356998401 / 86400 * 86400))

GROUP BYの順番に気をつける

GROUP BYするとき、濃度(列の中での種類の多さ)が高い方を手前に持ってきたほうが少しだけ早くなる。

[GOOD]: SELECT GROUP BY uid, gender
[BAD]:  SELECT GROUP BY gender, uid

また、文字列でGROUP BYするよりも、数字で行ったほうが使用メモリが少なく、素早く比較できる。

ORDER BYするときにはLIMITを使う

ORDER BYするときには一つのワーカーにすべてのデータを送る必要があるので、たくさんのメモリを使ってしまう場合がある。LIMIT句を使うことで、ソートするコストとメモリ使用量を抑えることができる。

[GOOD]: SELECT * FROM tbl ORDER BY time LIMIT 100
[BAD]:  SELECT * FROM tbl ORDER BY time

近似した集計値を使う

少しの誤差を許す代わりに、性能改善が行えるapproximate aggregation functionsがPrestoには備わっている。例えばapprox_distinct()関数は、COUNT(DISTINCT x)の近似値が標準誤差2.3%で取得できる。

SELECT
  approx_distinct(user_id)
FROM
  access
WHERE
  TD_TIME_RANGE(time,
    TD_TIME_ADD(TD_SCHEDULED_TIME(), '-1d', 'PDT'),
    TD_SCHEDULED_TIME())

LIKE句の集合は一つのregexp_likeにまとめる

Prestoのクエリオプティマイザは複数のLIKE句が使われているとクエリを改善できない。Prestoネイティブのregexp_likeにまとめると速度改善が期待できる。

SELECT
  ...
FROM
  access
WHERE
  method LIKE '%GET%' OR
  method LIKE '%POST%' OR
  method LIKE '%PUT%' OR
  method LIKE '%DELETE%'

このクエリ内で、4つのLIKE句は一つのregexp_like句に置き換えられる。

SELECT
  ...
FROM
  access
WHERE
  regexp_like(method, 'GET|POST|PUT|DELETE')

JOIN句では大きいテーブルを先に書く

Prestoでのデフォルトのjoinアルゴリズムは broadcast joinになっていて、内部表(joinする方, right-hand side table)が各ワーカーに送信される仕組みになっている(後ろに書いたテーブルが送信される)。内部表が十分に小さく、一つのノードに収まるとうまくjoinができ、目安としてはたいてい2GB以下。もしExceeded max memory xxGBが出たときは、内部表が大きすぎることを意味している事が多い。

Prestoは自動でjoinの順序を調整してくれないので、大きなテーブルは先に書き、小さくて軽いテーブルをあとに書くようにすべき。

Hashに基づくDistributed JOINをチューニングする

もしまだメモリの問題が解決しなかったら、distributed hash joinを試してみるのがよい。２つのテーブルのハッシュ値をjoinのキーとして使うアルゴリズムで、内部表のサイズが大きくとも動作する。ただし、データ転送量が増大するので、速度は遅くなってしまう。distributed hash joinに切り替えるには、次のようにコメントすればよい。

-- set session distributed_join = 'true'
SELECT ... FROM large_table l, small_table s WHERE l.id = s.id

上位Nレコードを取得するためにはROW_NUMBER()の代わりにRANK()を使う

最高/最低Nレコードを取ってきたいことがある。しかし、row_number()はrank()より遅い。

-- BAD QUERY
SELECT checksum(rnk)
FROM (
  SELECT row_number() OVER (PARTITION BY l_orderkey, l_partkey ORDER BY l_shipdate DESC) AS rnk
  FROM lineitem
) t
WHERE rnk = 1

RANK関数を使ったほうが、ROW_NUMBER関数よりも性能が良い。

-- GOOD QUERY
SELECT checksum(rnk)
FROM (
  SELECT rank() OVER (PARTITION BY l_orderkey, l_partkey ORDER BY l_shipdate DESC) AS rnk
  FROM lineitem
) t
WHERE rnk = 1

まとめ

  • 必要なものだけSELECTする
  • timeは整数との比較になるようにする
  • TD_TIME_RANGEのUDFを活用する
  • GROUP BYするときは濃度が大きい方から(種類が多い方から)
  • ORDER BYするときはなるべくLIMIT
  • 精密さが求められないときはapproximate aggregate functionの活用を検討する
  • 複数のLIKE句はregexp_likeでまとめる
  • JOINするときは軽い方を手前にする
  • 上位N件を示したいときはRANK関数ではなくROW_NUMBER関数が使えないか検討する

参考文献

TreasureDataで出力したSpreadSheetでグラフを書く

TreasureData

tl;dr

https://docs.google.com/spreadsheets/d/1y_gnVIvwuAQn2h8M5Gbf15MhCK2uzBjbJXCCbI_gMlw/pubhtml

概要

Treasure DataではQueryの結果をGoogle SpreadSheetに出力できるのだが、出力方式の問題で書き出すたびにシートのIDが変わってしまう。書き出すシートの名前がdataの時、グラフの範囲に=data!A:Aと書くことでdataシートのA列をグラフのソースに指定できるものの、TDのQueryをRunしてシートを書き換えるとグラフが壊れる。

=data!A:Aと書いた値は、SpreadSheet内部ではシートのIDに置き換わっていて、シートのIDが変わった時にグラフが壊れるという仕組みだと思う。

そこで、INDIRECT関数を使ってまるっきり同じシートを作ってやり、グラフはそのシートを参照するようにする。中間シートを作るための関数はこれ。1行目に必要な列分貼り付ければ良い。

=INDIRECT(CONCATENATE("data!", ADDRESS(1, COLUMN(), 4), ":", LEFT(ADDRESS(1, COLUMN(), 4), 1)))

CONCATENATE("data!", ADDRESS(1, COLUMN(), 4), ":", LEFT(ADDRESS(1, COLUMN(), 4), 1))では"data!A:A"といった文字列を生成している。

もっといいやり方があれば教えてください…。

普段のコミット小話 とか

Git 仕事 開発

新人エンジニア向けのブートキャンプでドキュメントの話があって、その中でコミットメッセージ大事ですねという話があった。近い将来としては、レビューのためとかは考えられるけど、遠い将来も考えてみましょうといっていて、こんな文書を引用していた。

Re-establishing the context of a piece of code is wasteful. We can’t avoid it completely, so our efforts should go to reducing it [as much] as possible. Commit messages can do exactly that

Who-T: On commit messages

確かにコードに落とし込んだ時は、コンテキストがかなり少ない状態になっていて、命名の工夫やコメントで手がかりを少しばかり残しておくことは出来るけど、それでもその時のコンテキストを再構築するのは大変だと思う。そのためにコミットメッセージがあるという話だ。

そこで、コミットのテンプレとしてこんな感じで書くとよいでしょう、というのが挙げられてた。

* このコミットを適用するとどうなるのか

* なんでこのコミットが必要なのか

* チケットのリンクとか参照情報

この話をしているのは僕の上司なのだけれど、たしかにコミットがこんな感じになっている。これは理想だけど、とはいえいちいち開発中考えてられなくて、だいたい普段は

ファイル追加した
大枠書いた
wip
wip
頼む
lint
これでどうだ
治ってくれ
今度こそ頼む
テスト忘れてた
うごいた

みたいになる。これは問題なくて、プルリク出す前に考えればよかろうと言ってた。そうですね。普段のワークフローもこんな感じだったんだけど、最近iOS開発始めてから状況が変わってきた感じがしてる。

iOSはプロジェクトファイルやGUIのファイルがXMLで構成されているので、なんかうまい感じにマージされるのが難しくなる。なので適当にコミットし続けていると、あとでgit rebase -i して歴史改変した時にうまくいくか不安だし、結果としてきれいなコミットログが残しにくくなる気がしてる。

そもそもプルリク出す前には割と時間かけてgit rebase -iとかしてるんだけど、なかなか時間が無駄なのでもっと普段から組み換えやすいコミットにしたほうが良いですね。

メンターの人から git push --force-with-lease 教えてもらってから、プルリク中でもガンガン歴史改変してる。ワークフロー整理してみると

  • 適当にコミット
  • git rebase -i
  • PR出す
    • 修正コミットしてそのままpush
  • Approveされたらgit rebase -iし直して修正コミットとかを統合しちゃう
  • マージ

みたいな感じになっている。みなさんのgitワークフロー気になってきた。

ところで最近はiOSとかRailsとか書いてる。プログラミングでこう書けておしゃれですねみたいな話よりも、アーキテクチャとかどうしたら良いか、何処に何を置いたら複数人開発時に便利ですかね、みたいな工夫話の方が大事な気がしている。