Bug: Job.refresh() takes minutes when key_source is restricted by part tables (MySQL)
Mantenedores costumam responder em até 6 dias
Avaliação
- Dificuldade
- 4/5
- Tempo estimado
- 3-5 dias
- Facilidade para iniciantes
- 35/100
- Tipo de issue
- Bug
- Clareza
- Claramente especificada
- Status de atividade
- Estagnada
- Stack de tecnologia
- mysql, postgresql, python
- Domínio
- data-engineering, databases
Direção de pesquisa
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.
Escrita pelo modelo de indexação a partir do texto da issue.
Descrição
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 deduplicationover aNested loop inner joinof about 270 million rows, collapsed to 2 at the end. - Step 3a: 140-164 s. It took the same time after
ANALYZE TABLEon 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_sourceas a top-level query: about 1 ms. - The same check written with
key_sourceat the top level, restricted to the candidate jobs: about 0.2 ms. - With
optimizer_switch='semijoin=off', or with a/*+ SUBQUERY(INTOEXISTS) */hint on theNOT INsubquery, 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_sourceon its own is fast. Step 1 querieskey_sourceat the top level and step 2 uses it insideIN; with only steps 3a and 3b rewritten, the wholerefresh()takes about 0.01 s on the dataset above. - In the measurements above, step 3b also nests
key_sourceinNOT IN, but it ran in under 1 ms there. Step 3a combines it with a secondNOT 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.
- Linguagem predominante
- Python
- Estrelas
- 198
- Forks
- 103
- Merge médio
- 6d 1h
- PRs com merge (30d)
- 10
Preparar o ambiente
- Inclui um Dockerfile ou arquivo Docker Compose
- Sem modelo de pull request
- Ler o guia de contribuição
Primeiros passos
- Leia a issue inteira e depois o guia de contribuição do projeto.
- Comente na issue dizendo que vai assumir — evita que duas pessoas façam o mesmo trabalho.
- Faça um fork do repositório e trabalhe em uma branch.
- Abra um pull request que referencie o número da issue.
Mais de datajoint/datajoint-python
-
bug
Dificuldade 2/5 1-3 horas Facilidade para iniciantes 78/100
datajoint/datajoint-python#1703 ·
Mantenedores costumam responder em até 6 dias
-
bug
Dificuldade 2/5 1-3 horas Facilidade para iniciantes 78/100
datajoint/datajoint-python#1702 ·
Mantenedores costumam responder em até 6 dias
-
bug
Dificuldade 2/5 1-3 horas Facilidade para iniciantes 78/100
datajoint/datajoint-python#1701 ·
Mantenedores costumam responder em até 6 dias
-
bug
Dificuldade 2/5 1-3 horas Facilidade para iniciantes 74/100
datajoint/datajoint-python#1691 ·
Mantenedores costumam responder em até 6 dias
-
dependencies needs-discussion
Dificuldade 2/5 1-3 horas Facilidade para iniciantes 68/100
datajoint/datajoint-python#1687 ·
Mantenedores costumam responder em até 6 dias
Todas as issues de datajoint/datajoint-python
Issues semelhantes
-
needs-triage
Dificuldade 1/5 1-3 horas Facilidade para iniciantes 85/100
gke-labs/kube-agents#6620 ·
Mantenedores costumam responder em até 1 dia
-
bug
Dificuldade 2/5 1-3 horas Facilidade para iniciantes 75/100
open-webui/open-webui#34719 · 2 comentários ·
Mantenedores costumam responder em até 1 dia
-
cognition
Dificuldade 2/5 1-3 horas Facilidade para iniciantes 82/100
Mantenedores costumam responder em até 1 dia
-
One non-object JSON record makes the session catalog exitTalvez já em andamento @he-yufeng assumiu hoje. Aberta
Dificuldade 2/5 1-3 horas Facilidade para iniciantes 68/100
Mantenedores costumam responder em até 1 dia
-
Dificuldade 1/5 Menos de uma hora Facilidade para iniciantes 78/100
collabnix/awesome-mcp-lists#433 ·
Mantenedores costumam responder em até 4 dias