Automattic / Automattic/Cron-Control
Ready-event selection loads the entire pending queue into PHP
- Dominant language
- PHP
- Stars
- 134
- Forks
- 26
- Avg merge
- 3m
- Merged PRs (30d)
- 1
Description
Event selection pulls every candidate row out of the database, `args` longtext included, then reduces that set in PHP. On sites with a large pending queue that's a lot of wasted I/O and memory pressure.
Related: https://github.com/Automattic/Cron-Control/issues/516
## Steps to reproduce
On a site with a large pending queue - ours had about 37,000 pending rows - let normal traffic run, then check the statement digest:
```
SELECT DIGEST_TEXT, COUNT_STAR, SUM_ROWS_EXAMINED, SUM_ROWS_SENT,
ROUND(SUM_ROWS_EXAMINED/NULLIF(COUNT_STAR,0)) AS rows_per_exec
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%a8c_cron_control_jobs%'
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
```
```
DIGEST_TEXT SELECT * FROM wp_a8c_cron_control_jobs
WHERE ? = ? AND STATUS IN (...)
ORDER BY TIMESTAMP ASC LIMIT ? OFFSET ?
COUNT_STAR 14,885,421
SUM_ROWS_EXAMINED 582,715,227,883
SUM_ROWS_SENT 7,161,996,500
rows_per_exec 39,143 - 55,889
```
- **Rows examined per execution exceeds the table size.** The table holds 38,034 rows (37,428 of them pending) and each execution examines 39,000 to 56,000. That's `OFFSET` re-scanning from the start on every page.
- **Rows sent averages about 481 per execution.** At roughly 1.3 KB per row here (49.1 MB across 38,034 rows, mostly `args`), that's a lot of data crossing into PHP for events that were never selected to run.
Both scale with pending count, so it stays invisible until a site's queue grows.
The paging is here:
- https://github.com/Automattic/Cron-Control/blob/main/includes/class-events-store.php
```
$sql .= ' LIMIT %d';
$placeholders[] = $parsed_args['limit'];
if ( ! is_null( $parsed_args['page'] ) ) {
$offset = $parsed_args['limit'] * ( $parsed_args['page'] - 1 );
if ( $offset > 0 ) {
$sql .= ' OFFSET %d';
$placeholders[] = $offset;
}
}
```
## Prior art
This has come up before and the pattern is still in `main`:
- [#163](https://github.com/Automattic/Cron-Control/issues/163) - "gets exponentially slower as the jobs table grows... high offsets with low limits still look at all the rows"
- [#191](https://github.com/Automattic/Cron-Control/issues/191) - a site seeing 64 SELECTs per transaction against this table, taking up most of the request time
- [#159](https://github.com/Automattic/Cron-Control/issues/159) - large event arguments causing problems with caching, db load, and network transfer
Tracking: PLTFRM-2722
Contributor guide
No contributing guide indexed for this repository
Research direction
Start in includes/class-events-store.php, where the LIMIT/OFFSET paging SQL is assembled, and review how ready-event candidates and their args are selected. Use the supplied performance_schema query and a large pending queue to measure rows examined, rows sent, and memory impact. Done means ready-event selection avoids loading unselected candidate rows while preserving ordering and pagination behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- mysql, php
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100