Performance Audit

When to use

• The user wants a ranked report of caching and indexing opportunities — uncached hot paths, missing indexes, opcache disabled — prioritised by ROI so the cheapest big wins come first.
• The user needs to locate N+1 query patterns, unbounded loops issuing queries, or slow hot paths in a PHP or ORM codebase and quantify their cost per request.
• The user asks to performance-audit or profile an existing PHP codebase for N+1 queries, missing database indexes, disabled opcache, or uncached hot paths, wanting ranked findings.

When NOT to use

• Do not use this audit skill to actually wire up a cache or add an index; once the finding is recorded, hand off to databases/redis or the relevant query skill to implement it.
• Do not raise findings for speculative micro-optimizations (loop unrolling, echo vs print) that have no measured impact; the audit targets evidence-backed hot paths, not folklore.

Core guidance

• Cross-link findings to caching and query skills — Set each AUD-PERF finding's remediation to the implementation skill that fixes it — uncached hot paths to databases/redis, N+1 to eager-loading, missing indexes to a migration — so the audit hands off to executable optimizations.
• Detect N+1 by queries issued inside loops — Flag a query executed inside a loop or a lazy relation accessed per-item as an N+1 finding, cite the loop and the query call site, and estimate the multiplier (queries = 1 + N) against a realistic collection size.
• Ground findings in profiling evidence — Back each AUD-PERF finding with a measurement — query count from the debug bar or a query log, an EXPLAIN plan, a profiler timing, or opcache status — not a guess, so the impact rating and roi_score = (impact * severity * confidence) / effort are defensible.
• Flag filtered columns with no index — Cross-reference WHERE, JOIN, and ORDER BY columns in hot queries against the schema and raise a missing-index finding for any filtered high-cardinality column with no index, confirming with EXPLAIN showing a full table scan.
• Check opcache is enabled and sized in production — Inspect the effective ini for opcache.enable=1, an adequate opcache.memory_consumption and max_accelerated_files, and opcache.validate_timestamps=0 in production; raise a finding for each disabled or under-sized directive.

Decision Engine

Walk this section before choosing an implementation path.

Where does the measured cost come from — repeated queries in a loop, a slow scan
from a missing index, recomputation on every request, or PHP-runtime config?

├── an expensive stable result is recomputed every request: → Raise a caching AUD-PERF finding citing the hot path, size impact by frequency times
cost, state an invalidation plan, and set remediation to databases/redis.

├── EXPLAIN shows a full scan on a filtered column: → Raise a missing-index AUD-PERF finding citing the query and the EXPLAIN plan, and set
remediation to add the index in a migration.

├── the same query runs once per item in a collection: → Raise an N+1 AUD-PERF finding citing the loop and query, estimate the multiplier, and
set remediation to eager-load the relation via the ORM.

└── the cost is PHP-runtime config, not a query or code path: → Raise an opcache/runtime AUD-PERF finding citing the ini directive and its value, and
recommend enabling and sizing opcache for production.

Full engine: references/decision-engine.md — Decision Tree, Priority Matrix, Decision Table.

Workflows

Workflow: Quick Wins

Progress:
- [ ] Separate the config-only, near-zero-risk wins (enable opcache, add a missing index)
into a quick-wins section so they can ship ahead of code changes
- [ ] For the code-level findings (N+1, caching), link databases/redis or the ORM
eager-loading skill as remediation and note the invalidation or test preconditions
each needs

1. Separate the config-only, near-zero-risk wins (enable opcache, add a missing index) into a quick-wins section so they can ship ahead of code changes.
   • Verify: The report has a quick-wins section listing high-ROID config and index findings.
2. For the code-level findings (N+1, caching), link databases/redis or the ORM eager-loading skill as remediation and note the invalidation or test preconditions each needs.
   • Verify: Each code-level finding links a fix skill and states its preconditions.

Workflow: Scan Perf

Progress:
- [ ] Identify the highest-traffic endpoints and capture a baseline — query count and log,
EXPLAIN on slow queries, a profiler timing, and the opcache status — before judging
anything
- [ ] Route each measured bottleneck through the decision engine — N+1, missing index,
uncached hot path, or opcache/config — and confirm the root cause rather than a
symptom
- [ ] Write each bottleneck as an AUD-PERF finding with path:line and its metric, compute
roi_score, sort descending, and cross-link the caching or query fix skill

1. Identify the highest-traffic endpoints and capture a baseline — query count and log, EXPLAIN on slow queries, a profiler timing, and the opcache status — before judging anything.
   • Verify: You have measured query counts, EXPLAIN plans, and opcache status for the hot paths.
2. Route each measured bottleneck through the decision engine — N+1, missing index, uncached hot path, or opcache/config — and confirm the root cause rather than a symptom.
   • Verify: Each bottleneck is classified and its root cause is confirmed with a metric.
3. Write each bottleneck as an AUD-PERF finding with path:line and its metric, compute roi_score, sort descending, and cross-link the caching or query fix skill.
   • Verify: Findings are sorted by roi_score, each with a metric, evidence, and a remediation link.

Best practices

• Identify expensive computations or queries that repeat on every request with stable inputs (config lookups, catalogue aggregates, rendered fragments) and raise a caching finding, sizing impact by request frequency times per-call cost

Anti-patterns

See references/anti-patterns.md.

Debugging

1. Recommending a cache without an invalidation plan — Cache invalidation is the hard part; a cache with no invalidation strategy ships stale reads. Fix: Every caching finding must state a TTL or an event-based invalidation and note the staleness the business can tolerate.
2. Recommending eager loading everywhere — Eager loading unused relations wastes IO and memory and can be slower than the N+1 it replaced. Fix: Recommend eager loading only the relations the code path actually reads, and select the needed columns; measure the before/after query count and payload.
3. Guessing the bottleneck without profiling — The real hot path is often not where it looks; unmeasured findings misdirect effort and lower confidence. Fix: Attach a measurement to each finding (query count, EXPLAIN, timing) and lower the confidence rating when only static reasoning is available.

Performance notes

• Recommending a cache without an invalidation plan — A caching finding that omits how and when the entry is invalidated risks serving stale data, turning a performance win into a correctness bug.
• Recommending eager loading everywhere — Blanket "eager-load all relations" advice over-fetches columns and rows the request never uses, trading an N+1 for one huge query and higher memory.
• Guessing the bottleneck without profiling — Raising performance findings from reading code alone, with no query log, EXPLAIN, or profiler data, produces confident but frequently wrong bottleneck claims.
• Cross-link findings to caching and query skills — Set each AUD-PERF finding's remediation to the implementation skill that fixes it — uncached hot paths to databases/redis, N+1 to eager-loading, missing indexes to a migration — so the audit hands off to executable optimizations.
• Detect N+1 by queries issued inside loops — Flag a query executed inside a loop or a lazy relation accessed per-item as an N+1 finding, cite the loop and the query call site, and estimate the multiplier (queries = 1 + N) against a realistic collection size.
• Ground findings in profiling evidence — Back each AUD-PERF finding with a measurement — query count from the debug bar or a query log, an EXPLAIN plan, a profiler timing, or opcache status — not a guess, so the impact rating and roi_score = (impact * severity * confidence) / effort are defensible.
• Flag filtered columns with no index — Cross-reference WHERE, JOIN, and ORDER BY columns in hot queries against the schema and raise a missing-index finding for any filtered high-cardinality column with no index, confirming with EXPLAIN showing a full table scan.
• Check opcache is enabled and sized in production — Inspect the effective ini for opcache.enable=1, an adequate opcache.memory_consumption and max_accelerated_files, and opcache.validate_timestamps=0 in production; raise a finding for each disabled or under-sized directive.
• Flag recomputed hot paths as cache candidates — Identify expensive computations or queries that repeat on every request with stable inputs (config lookups, catalogue aggregates, rendered fragments) and raise a caching finding, sizing impact by request frequency times per-call cost.

Production checklist

• Every caching finding names a TTL or event-based invalidation and the tolerable staleness.
• Each AUD-PERF finding cites path:line for the loop, query, or config that is the bottleneck.
• Every AUD-PERF finding cites a metric (query count, EXPLAIN, timing, or opcache status), not just code reading.
• The production opcache status is checked and any disabled or under-sized directive is recorded as a finding.
• Each finding's remediation links databases/redis or the relevant query/eager-loading skill.
• Every AUD-PERF finding has a roi_score and the report is sorted descending by it.

Version notes

• Opcache preloading and JIT availability by version (PHP >=7.4) — Opcache preloading (opcache.preload) arrived in PHP 7.4 and the opcache JIT in 8.0; an audit should check whether a 7.4+ project uses preloading and whether a CPU-bound 8.0+ workload could benefit from JIT, recording each as an opportunity finding.

Security

• Do not recommend caching user-scoped or sensitive data unsafely — When a caching finding covers per-user or sensitive data, require a cache key that includes the user or tenant scope; a shared cache key on user-scoped data leaks one user's data to another and is a security bug, not just a performance one.

Testing

• Activation and decision behavior are verified in eval/scenarios/audit/performance.yaml.

Additional resources

• references/anti-patterns.md
• references/core.md
• references/decision-engine.md
• references/examples.md
• references/pitfalls.md
• references/recipes.md
• references/version-differences.md

Knowledge graph

Related: audit/project-analysis, databases/redis

Skill contract

Produces: audit.perf-findings
Requires: repo.php-sources
Risk: low · Confidence: 0.9