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

Đang mở
  • Có gì không ổn?
#746 0 bình luận 0 reaction 0 người được giao Xem trên GitHub

Maintainer thường phản hồi trong vòng 1 ngày

Chưa có ai nhận issue này.

Đánh giá

Độ khó
4/5
Thời gian dự kiến
3-5 ngày
Mức phù hợp với người mới
30/100
Loại issue
Tái cấu trúc
Độ rõ ràng
Khá rõ ràng
Mức độ hoạt động
Ít trao đổi
Công nghệ
python, sqlalchemy
Lĩnh vực
databases, desktop-dev, performance

Hướng nghiên cứu

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.

Do mô hình lập chỉ mục viết ra từ nội dung của issue.

Mô tả

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

Ngôn ngữ chính
Python
Star
37
Fork
17
Merge trung bình
21 giờ 5 phút
Pull request đã merge (30 ngày)
131

Chuẩn bị môi trường

Bắt đầu từ đâu

  1. Đọc hết issue, rồi đọc hướng dẫn đóng góp của dự án.
  2. Bình luận trên issue rằng bạn sẽ nhận — tránh hai người làm cùng một việc.
  3. Fork repository và làm thay đổi trên một nhánh.
  4. Mở pull request có tham chiếu số hiệu của issue.

Issue khác của OpenSAK-Org/OpenSAK

Tất cả issue của OpenSAK-Org/OpenSAK

Issue tương tự

Thêm issue về Python

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.