Automattic / Automattic/Cron-Control

Ready-event selection loads the entire pending queue into PHP

Open
#517 0 comments 0 reactions 0 assignees View on GitHub
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.