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

オープン
  • 何か問題がありますか？
#746 コメント 0 件 リアクション 0 件 担当者 0 名 GitHub で見る

メンテナーはふだん 1 日以内に返信

まだ誰も着手していません。

評価

難易度
4/5
見積もり時間
3〜5日
初心者へのやさしさ
30/100
issue の種類
リファクタリング
明瞭さ
おおむね明確
活発さ
静か
技術スタック
python, sqlalchemy
領域
databases, desktop-dev, performance

調査の方向性

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.

索引モデルが issue の本文から書いたものです。

説明

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

主要言語
Python
スター
37
フォーク
17
平均マージ
21時間 5分
マージ済み PR（30日）
131

環境構築

はじめの一歩

  1. issue を最後まで読み、次にプロジェクトのコントリビューションガイドを読みます。
  2. 着手することを issue にコメントします — 二人が同じ作業をするのを防げます。
  3. リポジトリをフォークし、ブランチを切って変更します。
  4. issue 番号を参照したプルリクエストを送ります。

OpenSAK-Org/OpenSAK のほかの issue

OpenSAK-Org/OpenSAK の issue をすべて見る

似ている issue

Python の issue をもっと見る

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。