you-music/QUERY_PERFORMANCE_DEBUG.md

Query Performance Investigation

Current Status

After adding indexes and noload(), the query should be fast but still reports slow performance.

Diagnostics Added

Added detailed timing logs to /api/music/ endpoint to identify the bottleneck:

logger.info(f"Music list query: total={total_time:.3f}s, query={query_time:.3f}s, fetch={fetch_time:.3f}s, rows={len(music_list)}, skip={skip}")

This will show:

• total: Total endpoint execution time
• query: Time spent in database query execution
• fetch: Time spent fetching/hydrating objects
• rows: Number of rows returned
• skip: Pagination offset

Check Logs After Deployment

kubectl logs -f deployment/youmusic | grep "Music list query"

Expected output:

Music list query: total=0.050s, query=0.020s, fetch=0.010s, rows=50, skip=0

Possible Bottlenecks

1. Pydantic Serialization (Most Likely)

If total time is high but query and fetch are low, the issue is FastAPI's response model validation.

Solution: Use response_model_exclude_unset=True or custom serialization

2. Network Latency (NFS)

If query time is high, the issue is NFS storage latency.

Solution: Increase SQLite cache size, or consider local SSD cache

3. Object Hydration

If fetch time is high, SQLAlchemy is slow at creating Python objects.

Solution: Use raw SQL or optimize model loading

4. Lock Contention

If times vary wildly, there's database lock contention.

Solution: Check concurrent requests, adjust busy_timeout

Next Steps Based on Logs

If query time is high (>1s):

• Check if indexes are used: EXPLAIN QUERY PLAN
• Increase cache size: PRAGMA cache_size = -64000; (64MB)
• Add composite index for file_exists + created_at

If fetch time is high (>1s):

• Use defer() to lazy-load large TEXT columns (lyrics)
• Profile object creation overhead

If total - (query + fetch) is high (>1s):

• It's Pydantic serialization
• Use custom JSON encoder
• Or use response_model=None and manual dict conversion

Deploy and Check

git push
kubectl logs -f deployment/youmusic | grep "Music list query"

Then we'll know exactly where the bottleneck is!