Ready-event selection loads the entire pending queue into PHP

[WRasada]

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: #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 - "gets exponentially slower as the jobs table grows... high offsets with low limits still look at all the rows"
• #191 - a site seeing 64 SELECTs per transaction against this table, taking up most of the request time
• #159 - large event arguments causing problems with caching, db load, and network transfer

Tracking: PLTFRM-2722