ColoredCow / ColoredCow/performance-adapter-wp
Investigate stale order_itemmeta size readings — likely InnoDB stats lag, not WP cache
- Dominant language
- PHP
- Stars
- 0
- Forks
- 0
- PR merge metrics
- No merged PRs in 30d
Description
## Problem
Dashboard/BigQuery data showed `woo_order_itemmeta_size_mb` and `woo_order_items_size_mb` returning the exact same value (to 4 decimal places) across many consecutive days on a live site. Same for `woo_query_execution_ms` initially looking suspiciously static before closer inspection showed it does fluctuate day to day — it's specifically the two table-size fields that appear frozen.
If a metric is presented as "updated daily" but doesn't move for weeks, it undermines trust in the whole dashboard the moment anyone notices.
## Investigation so far
Checked `includes/class-data-collector.php` on `main` — the WordPress-level caching is **not** the cause. `collect_and_push()` already calls `self::bust_metrics_cache()` as its first line, before `get_data()` runs, which busts the WooCommerce metrics transient (and all other metric transients) before recomputing. This is the exact fix issue #24 asked for, and it's already merged.
## Two likely real causes (neither is fixed by the WP-level cache-bust already in place)
### 1. Deployed plugin version may predate this fix
Given the ZIP-and-manual-upload deployment process, it's possible the live site is running an older build than `main`. This needs to be confirmed against whatever site produced the frozen readings.
### 2. InnoDB statistics lag (likely the deeper root cause)
`order_items_size_mb` and `order_itemmeta_size_mb` are computed from `information_schema.TABLES` (`data_length + index_length`). For InnoDB, these are **estimated statistics**, not a live count — they only recalculate automatically after roughly a 10% change in modified rows (`innodb_stats_auto_recalc`). On a large table (tens of millions of rows), that threshold can take a long time to cross even with steady order volume, so the reported size can legitimately stay flat for stretches of time even though the code is working correctly.
Forcing a fresh reading (e.g. `ANALYZE TABLE`) before every push is not safe to do blindly — running it on a large, actively-written table carries real locking/performance risk, which is the exact kind of overhead this tool exists to help clients avoid.
## What needs deciding
- [ ] Confirm which plugin version is actually deployed on the site where this was observed, vs. `main`
- [ ] If already on latest and still flat: decide whether to periodically force fresh InnoDB stats (e.g. a scheduled `ANALYZE TABLE` run weekly, not on every push) and evaluate the performance/locking cost of doing so
- [ ] Decide whether the dashboard should surface "estimated, last refreshed on X" language for these two fields instead of implying they're always live-accurate
## Acceptance criteria
- [ ] Root cause confirmed (stale deployment vs. InnoDB stats lag vs. both)
- [ ] Decision documented on whether/how to force fresher stats, with the performance tradeoff explicitly weighed
- [ ] If dashboard language needs to change to reflect estimated/lagging values, that's scoped separately
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.