半空洞男女関係

思ったこととかプログラミングしてるときのメモとか色々かいてます。メールは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関数が使えないか検討する

参考文献