Bug: Job.refresh() takes minutes when key_source is restricted by part tables (MySQL)

未關閉
#1,673 0 則留言 0 個 reaction 已指派 0 人 在 GitHub 檢視

維護者通常 6 天內回覆

@dimitri-yatsenko 已經在處理了。

開始於 2026年11月10日。

  • #1674 來自 @dimitri-yatsenko —— 未關閉

評估

難度
4/5
預估耗時
3-5 天
新手友好度
35/100
Issue 類型
缺陷
描述清晰度
描述清楚
活躍度
停滯
技術堆疊
mysql, postgresql, python

研究方向

Read src/datajoint/jobs.py:426-443, especially cleanup steps 3a and 3b. Start by tracing how refresh() builds those deletion queries and compare them with the issue’s proposed top-level key_source check restricted to candidate jobs. Done means both backends retain and remove the same jobs while avoiding key_source nested inside NOT IN; an open pull request is already linked.

由索引模型根據 Issue 內容生成。

描述

bug

Bug Report

Description

Job.refresh() removes jobs whose keys have left key_source. On master (src/datajoint/jobs.py:426-443) it does this by nesting key_source inside a NOT IN subquery:

vanished = self.restrict(Not(self._target.key_source.proj()), semantic_check=False)
# 3a: success/error records whose key is in neither key_source nor the target
(vanished & "status in ('success', 'error')").restrict(Not(self._target.proj()), ...).delete_quick()
# 3b: stale jobs older than stale_timeout
(vanished & "status != 'ignore'" & "created_time < ...").delete_quick()

When key_source is restricted by part tables, MySQL can pick a plan for that subquery whose cost is the product of the part tables' row counts, not the size of key_source. Users with a key_source restricted by part tables see refresh() take minutes even though key_source holds only a handful of keys. Any loop that refreshes before each populate() pays that cost on every pass.

A typical key_source that triggers it requires its masters to have parts:

@property
def key_source(self):
    return A * B & A.Item & B.Item

DataJoint renders each part restriction as (pk) IN (SELECT ... FROM part). Inside the NOT IN (key_source) subquery, MySQL flattens both INs into inner joins (semijoin) and runs them with materialization with deduplication. Deduplication happens only once, at the end, so the materialized table is built from the full join of both part tables first.

Measured with EXPLAIN ANALYZE on a real dataset of this shape (MySQL 8.4; the same behavior on 8.0):

  • key_source: 2 keys. Part tables: about 42,000 and 6,400 rows.
  • Plan for step 3a: Materialize with deduplication over a Nested loop inner join of about 270 million rows, collapsed to 2 at the end.
  • Step 3a: 140-164 s. It took the same time after ANALYZE TABLE on every table involved, with the same plan, so stale statistics are not the cause. Every access is a covering primary-key lookup, so a missing index is not the cause either.
  • The same key_source as a top-level query: about 1 ms.
  • The same check written with key_source at the top level, restricted to the candidate jobs: about 0.2 ms.
  • With optimizer_switch='semijoin=off', or with a /*+ SUBQUERY(INTOEXISTS) */ hint on the NOT IN subquery, the step takes 0.2-3 ms.
Reproducibility
  • OS: macOS and Linux
  • Python 3.11 and 3.13
  • MySQL 8.0 and 8.4, local-docker
  • DataJoint master (2.3.4-42-g3c912c46)

Minimal reproduction on synthetic data:

import time
import datajoint as dj

schema = dj.Schema("refresh_repro")

@schema
class Session(dj.Manual):
    definition = """
    session_id : int32
    """

@schema
class Profile(dj.Manual):
    definition = """
    -> Session
    profile_id : int16
    """
    class Item(dj.Part):
        definition = """
        -> master
        item : int32
        """

@schema
class Grouping(dj.Manual):
    definition = """
    -> Session
    grouping_id : int16
    """
    class Item(dj.Part):
        definition = """
        -> master
        item : int32
        """

@schema
class Figure(dj.Computed):
    definition = """
    -> Profile
    -> Grouping
    ---
    n : int32
    """
    @property
    def key_source(self):
        return Profile * Grouping & Profile.Item & Grouping.Item

    def make(self, key):
        self.insert1(dict(key, n=len(Profile.Item & key)))

dj.config["jobs.keep_completed"] = True
Session.insert1(dict(session_id=1))
Grouping.insert1(dict(session_id=1, grouping_id=0))
Grouping.Item.insert(dict(session_id=1, grouping_id=0, item=i) for i in range(6_000))
for p in range(2):
    Profile.insert1(dict(session_id=1, profile_id=p))
    Profile.Item.insert(dict(session_id=1, profile_id=p, item=i) for i in range(20_000))

Figure.populate(reserve_jobs=True)  # 2 keys, 2 success jobs

start = time.monotonic()
Figure.jobs.refresh()
print(f"refresh: {time.monotonic() - start:.1f} s")  # nothing to remove, yet slow once MySQL materializes

Whether MySQL picks materialization depends on the row counts. With smaller tables, the plan can be forced to show the same blow-up:

SET SESSION optimizer_switch='firstmatch=off,loosescan=off,duplicateweedout=off';

With two masters of 3,000 parts each, one master of 2,000 parts and the switch above, refresh() takes about 10 s on MySQL 8.0, although nothing needs to be removed.

Expected Behavior

refresh() should cost about as much as querying key_source restricted to the jobs being checked, not the product of the part tables' sizes. Concretely, the cleanup should never nest key_source inside NOT IN. It can query key_source at the top level, restricted to the candidate jobs, and delete the candidates it does not return. The result should be the same as today: the same jobs removed, the same jobs kept.

Additional Research and Context
  • Only the NOT IN (key_source) shape is affected. key_source on its own is fast. Step 1 queries key_source at the top level and step 2 uses it inside IN; with only steps 3a and 3b rewritten, the whole refresh() takes about 0.01 s on the dataset above.
  • In the measurements above, step 3b also nests key_source in NOT IN, but it ran in under 1 ms there. Step 3a combines it with a second NOT IN (target) and got the materialized plan. This depends on the plan MySQL picks, so both steps should avoid the shape.
  • I have only measured this on MySQL. The fix should still apply to both backends so that they produce the same results.
主要語言
Python
星號
198
分支
103
平均合併
6 天 1 小時
30 天內合併 PR
10

環境準備

  • 提供 Dockerfile 或 Docker Compose 檔案
  • 沒有 Pull Request 範本
  • 閱讀貢獻指南

從這裡開始

  1. 先讀完整個 Issue,再讀專案的貢獻指南。
  2. 在 Issue 下留言說明你要接手 —— 這能避免兩個人做同樣的事。
  3. Fork 儲存庫,在一個分支上完成修改。
  4. 送出 Pull Request,並在描述裡引用這個 Issue 編號。

datajoint/datajoint-python 的其他 Issue

查看 datajoint/datajoint-python 的全部 Issue

相似的 Issue

更多 Python Issue

把新 issue 寄到你的電子郵件信箱

精選適合新手參與的 GitHub issue 摘要。