pingcap/tidb

Consider using a disk-based hash table for hash join avoiding OOM

Open

#11,607 opened on 2019年8月5日

GitHub で見る
 (1 comment) (0 reactions) (1 assignee)Go (40,090 stars) (6,186 forks)batch import
epic/memory-managementhelp wantedsig/executiontype/enhancement

説明

Feature Request

Is your feature request related to a problem? Please describe:

Consider using a disk-based hash table for hash join avoiding OOM.

HashJoinExecutor uses a hash table describing the map of join keys and inner table rows.

TiDB's hash join is implemented by innerResult and mvmap.MVMap. The innerResult stores all the rows of the inner table, and the mvmap.MVMap stores the map of (join key, inner table pointer). This allows us to use these two structures to get a map of join keys and inner table rows. When the inner table is particularly large, the innerResult will take up a lot of memory; when the join key is particularly large, mvmap.MVMap will also take up a lot of memory. There will be problems with OOM at this time.

Describe the feature you'd like:

  1. We already have a config mem-quota-query, which set the memory quota for a query in bytes.
  2. Introduce a new config oom-use-tmp-storage, default is true. Set to true to enable use of temporary disk for some executors(in this issue, it is hash join) when mem-quota-query is exceeded.
  3. Show disk usage of an executor in explain analyze
  4. Show disk usage of a query in SELECT * FROM information_schema.processlist;
  5. Consider disk usage in cost model.

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

tasks:

  1. The improvement of mvmap.MVMap
  • hash join #11832
  • index join
  • performance and code clean #11937
  1. Disk-based innerResult
  • hash join
    • utilities: #12116
    • implement disk-based hash join: #12067
  • index join
  1. cost model, explain analyze, and disk usage control
  • change cost model of a hash join if it will be spilled #13246
  • show disk usage information in explain analyze #12625

Some tiny issues

  • [For new contributor]Show disk usage of a query in SELECT * FROM information_schema.processlist; #13931
  • [For new contributor] Show disk usage of a query in low query and statement summary #16883
  • add metrics for disk usage of a query #17263
  • [For new contributor]change the default value of mem-quota-query #12937
  • [For new contributor]temporary storage usage limitation of all queries. #13983
  • [For new contributor]Define temporary storage in config file. #13982
  • [help wanted]multiple instances of tidb-server may use the same temporary directy #13981

コントリビューターガイド

技術スタック
go
領域
databasebackendperformance
Issue 種別
feature
難度新しい貢献者にとっての実装難度です。1 は非常に小さな変更、5 は専門的な作業です。
5
推定時間経験ある貢献者が調査、実装、テスト、pull request 準備にかけるおおよその時間範囲です。
over 1 week
活動状況Issue が今どれだけ取り組みやすいかを示します。新しい、活発、古い、ブロック中、またはメンテナー入力待ちなどです。
stale
明確さ期待される変更、受け入れ条件、次の手順がどれだけ明確かを示します。
mostly clear
前提条件
Understanding of TiDB hash join executorGo programmingMemory management conceptsFamiliarity with TiDB configuration
初心者向け度初回貢献者にどれだけ取り組みやすいかを 1-100 で推定したスコアです。
10
調査方針
This is a large meta issue for disk based hash join. Although many sub tasks are completed, open tasks remain such as 'Show disk usage of a query in SELECT * FROM information schema.processlist;' (issue #13931) and 'Show disk usage of a query in low query and statement summary' (#16883). Newcomers should look into these smaller issues or review the linked PRs and code in executor/join.go and executor/join test.go for context. The main issue is not suitable for first time contributors due to its scope and complexity.
Consider using a disk-based hash table for hash join avoiding OOM · pingcap/tidb#11607 | Good First Issue