Agent Skills
› NeverSight/learn-skills.dev
› perf-profile
perf-profile
GitHub用于Python应用性能瓶颈分析。支持CPU、内存及SQLite查询 profiling，遵循先测量后优化原则。适用于慢速响应、内存泄漏检测及性能基线建立。
Trigger Scenarios
应用运行缓慢需定位瓶颈
Bot守护进程内存持续增长
SQLite查询耗时过长
嵌入生成需要优化
变更前后建立性能基线
Install
npx skills add NeverSight/learn-skills.dev --skill perf-profile -g -y
SKILL.md
Frontmatter
{
"name": "perf-profile",
"description": "Use this skill when the application is slow and you need to find the bottleneck, memory usage is growing over time in the bot daemon, SQLite queries are taking too long, embedding generation needs optimization, or you want to establish performance baselines before and after changes. This covers CPU profiling with py-spy and cProfile, memory profiling with tracemalloc and scalene, SQLite query analysis with EXPLAIN QUERY PLAN, bot daemon memory leak detection, and before\/after measurement discipline. Always measures first, then optimizes, then verifies improvement. Distinguished from pr-review (which flags performance risks in a diff but does not run profilers) and from dep-audit (which analyzes dependency bloat but not runtime performance)."
}
Performance Profiler
Systematic performance profiling for the Second Brain Python application. Identifies CPU, memory, and I/O bottlenecks. Always measures before and after.
Golden Rule: Measure First
Never optimize without profiling first. Establish a baseline, identify the bottleneck, fix it, then verify the improvement.
Steps
1. Establish Baseline
Record current metrics before touching anything:
# Quick project scan for performance risk indicators
python3 .claude/skills/perf-profile/scripts/performance_profiler.py ./file://scripts/brain-bot/
# Measure test suite speed
time cd ./file://scripts/brain-bot && python -m pytest --tb=no -q 2>/dev/null
# Check bot process memory
ps -o rss,vsz,pid -p $(pgrep -f "app.py") 2>/dev/null
2. Identify the Bottleneck
Choose the right tool based on the symptom:
| Symptom | Tool | Command |
|---|---|---|
| Slow overall | py-spy | py-spy top --pid $(pgrep -f app.py) |
| Slow function | cProfile | python -m cProfile -s cumtime script.py |
| Memory growth | tracemalloc | See recipes below |
| Slow query | EXPLAIN | sqlite3 data/brain.db "EXPLAIN QUERY PLAN ..." |
| Embedding slow | time | time python -c "from core.embedding_store import ..." |
3. Profile
CPU Profiling (Python)
# Live top-like view of running bot
py-spy top --pid $(pgrep -f "app.py")
# Record flamegraph
py-spy record -o profile.svg --pid $(pgrep -f "app.py") --duration 30
# Profile a specific script
python -m cProfile -s cumtime ./file://scripts/brain-bot/core/vault_indexer.py 2>&1 | head -30
Memory Profiling
# Quick RSS check over time
while true; do ps -o rss -p $(pgrep -f "app.py") | tail -1; sleep 60; done
# tracemalloc snapshot (add to code temporarily)
import tracemalloc
tracemalloc.start()
# ... run your code ...
snapshot = tracemalloc.take_snapshot()
for stat in snapshot.statistics('lineno')[:10]:
print(stat)
SQLite Query Profiling
-- Check query plan (look for SCAN TABLE = bad, SEARCH = good)
EXPLAIN QUERY PLAN
SELECT * FROM vault_nodes WHERE type = 'daily' ORDER BY last_modified DESC LIMIT 10;
-- Check indexes exist
.indices vault_nodes
.indices vault_edges
.indices vault_chunks
-- Time a query
.timer on
SELECT count(*) FROM vec_vault_chunks WHERE embedding MATCH ? AND k = 10;
Embedding Performance
# Time embedding generation for a single file
time python3 -c "
from sentence_transformers import SentenceTransformer
m = SentenceTransformer('nomic-ai/nomic-embed-text-v1.5', trust_remote_code=True)
m.encode(['test document'], convert_to_numpy=True)
print('Model loaded and encoded')
"
# Count files to embed
sqlite3 data/brain.db "SELECT count(*) FROM vault_nodes WHERE node_type='document';"
4. Analyze Results
- Flamegraph: Wide bars = hot functions. Look for unexpected time in I/O or serialization.
- EXPLAIN QUERY PLAN:
SCAN TABLEmeans no index hit. Add an index. - RSS growth: Steady growth over hours = memory leak. Check embedding model lifecycle.
- tracemalloc: Top allocators show where memory is being consumed.
5. Apply Fix
One change at a time. Never batch multiple optimizations — you won't know which one helped.
6. Verify Improvement
Re-run the same measurement from Step 1. Compare before/after.
7. Document the Win
## Performance Optimization: [What You Fixed]
**Date:** YYYY-MM-DD
### Problem
[What was slow, how was it observed]
### Root Cause
[What the profiler revealed]
### Baseline (Before)
| Metric | Value |
|--------|-------|
| [metric] | [value] |
### Fix Applied
[What changed]
### After
| Metric | Before | After | Delta |
|--------|--------|-------|-------|
| [metric] | [old] | [new] | [%] |
Quick Wins Checklist
SQLite
- Missing indexes on WHERE/ORDER BY columns
- N+1 queries (DB calls in loops in context_loader.py)
- SELECT * when only 2-3 columns needed
- No LIMIT on unbounded queries
- Missing WAL mode (should be set via db_connection.py)
Python Bot
- Embedding model loaded once at boot (not per-request)
- sentence_transformers.encode() uses batching
- No sync I/O in async handlers (use async_utils.py)
- Graph cache used for repeated vault traversals
- Connection pool reused (not new connection per query)
Search Pipeline
- RRF fusion doesn't recompute embeddings unnecessarily
- Metadata filters applied BEFORE vector search (CTE pre-filtering)
- FTS5 queries use proper tokenization
- Chunk embeddings cached (content-hash dedup)
Bot Daemon Memory Leak Detection
The brain-bot runs as a long-lived daemon via launchd. Watch for:
- RSS growth: Check hourly with
ps -o rss -p $(pgrep -f app.py) - Embedding model: SentenceTransformer should be loaded once, not per-request
- SQLite connections: Verify centralized connection pooling via db_connection.py
- Thread pool: ThreadPoolExecutor should have max_workers set (not unbounded)
- Graph cache:
graph_cache.pyshould evict stale entries
Common Pitfalls
- Optimizing without measuring — you'll optimize the wrong thing
- Testing with small data — 10 vault files vs 1000 reveals different bottlenecks
- Ignoring the daemon lifecycle — memory leaks only show after hours of running
- Premature optimization — fix correctness first, then performance
- Not re-measuring — always verify the fix actually improved things
Version History
- e0220ca Current 2026-07-05 23:22
