Cache-list refresh is GIL-bound on large databases — reduce ORM hydration cost

Aperta
  • Qualcosa non va?
#746 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
30/100
Tipo di issue
Refactoring
Chiarezza
Abbastanza chiara
Stato di attività
Tranquilla
Stack tecnologico
python, sqlalchemy
Ambito
databases, desktop-dev, performance

Direzione di ricerca

Start with apply_filters_auto() and the _needs-based joinedload/noload logic in filters/engine.py, then check how cache_table.py and map_widget.py consume the results. Time a raw session.execute(select(...)) against the same query to split SQL cost from ORM hydration. Done means a measured breakdown and a decision on whether a lighter row representation is feasible.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

area: performance improvement
Current State

Follow-up to #740. The threading fix (RefreshWorker) solved the actual
problem — no more freeze/force-close on large databases — but measurement
during testing revealed the underlying query itself doesn't get faster from
being backgrounded, and is measurably slower on first (cold) access. This
issue tracks investigating whether that can be reduced.

Evidence

Two independent test rounds (one after a full reboot) on three real
databases (98k/198k/348k caches), compared against the pre-#740 synchronous
baseline:

Switch Before (sync) After (worker thread) Diff
Cold startup, 98k 2.87s 13.07s / 13.29s ~4.6x
→198k (1st touch) 5.61s 12.87s / 13.06s ~2.3x
→348k (1st touch) 9.49s 17.54s / 17.69s ~1.85x
→198k (warm) 5.86s 6.04s / 6.12s ~same
→348k (warm) 9.76s 9.83s / 10.04s ~same

The two "after" runs (one right after a reboot) land within 1-2% of each
other — this is a reproducible effect, not noise.

lsblk -d -o name,rota confirmed both disks are SSD/NVMe (ROTA=0), ruling
out physical seek contention as the explanation. htop screenshots during a
switch show python run.py briefly in D state (genuine I/O wait) only on
the very first-ever touch of a database, but otherwise in R state at up to
100% of a single core — i.e. the dominant cost is CPU-bound Python work, not
disk I/O.

Proposed Improvement

Hypothesis

apply_filters_auto()'s cost for large result sets is mostly SQLAlchemy ORM
row hydration (building a full Cache Python object per row) — CPU-bound
work that holds the GIL. Moving it to a background QThread doesn't
parallelize it (Python's GIL serializes CPU-bound work across threads
regardless of which thread it runs on) — it just means the worker thread and
the GUI thread's own Python-side work (painting, wait-cursor updates, Qt
event processing) now compete for GIL time instead of running strictly
sequentially, adding real wall-clock time on top of the unavoidable
first-touch I/O cost.

Possible directions (not yet investigated in depth)

  • Measure how much of apply_filters_auto()'s time is hydration vs. the
    SQL execution itself (e.g. time a raw session.execute(select(...))
    without ORM mapping against the same query)
  • For the table/map render path specifically, consider whether a
    lighter-weight representation (Core Row/dict instead of full mapped
    Cache objects) is feasible without a large refactor of
    cache_table.py/map_widget.py's consumption code
  • Check whether noload() is already applied as aggressively as
    possible for columns/relations the table/map don't need
    (filters/engine.py's _needs-based joinedload/noload already exists —
    confirm nothing is being loaded unnecessarily for typical column
    configurations)

Related

  • #740 (GUI thread blocks on large-database switch — fixed via RefreshWorker;
    this issue is the performance follow-up, not a regression of #740's fix)
Expected Benefits

No response

Lingua principale
Python
Stelle
37
Fork
17
Merge medio
16h 7m
PR unite (30g)
128

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di OpenSAK-Org/OpenSAK

Tutte le issue di OpenSAK-Org/OpenSAK

Issue simili

Altre issue su Python

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.