ColoredCow / ColoredCow/performance-adapter-wp

Investigate stale order_itemmeta size readings — likely InnoDB stats lag, not WP cache

Open
#43 1 comment 0 reactions 1 assignee Claimed by @Ajay-Singh-Adhikari View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.