Claim query filesort exhausts InnoDB lock memory at multi-million-row scale

[chubes4]

Summary

The SKIP LOCKED claim path merged in #1259 prevents the earlier deadlock pattern, but the default claim ordering still lacks a covering index. At multi-million-row scale, MariaDB filesorts millions of due actions and creates millions of row locks to claim a batch of 50. This exhausted InnoDB lock memory and caused MariaDB 10.11 to abort.

This is the residual index/query-plan problem discussed in #1104 and #1250, with the complete index independently benchmarked in #1340.

Production Scale

Observed on a high-volume WordPress multisite workload using Action Scheduler 3.9.3:

• MariaDB 10.11.14
• 9,510,286 estimated action rows
• 24,634,169 estimated log rows
• Actions table: approximately 3.10 GB data + 4.76 GB indexes
• Logs table: approximately 1.94 GB data + 1.31 GB indexes
• Approximately 1.8M pending actions at incident diagnosis
• Eight concurrent batches
• Batch size 50
• The workload intentionally sustains high-volume fan-out; queue cardinality is not itself treated as the defect

The application-level duplicate producer responsible for the exceptional backlog was identified and fixed separately. The evidence below concerns Action Scheduler's generic claim path while draining the existing valid/pending queue.

Query and Plan

Default query shape from ActionScheduler_DBStore::claim_actions():

SELECT action_id
FROM wp_actionscheduler_actions
WHERE claim_id = 0
  AND scheduled_date_gmt <= UTC_TIMESTAMP()
  AND status = 'pending'
ORDER BY priority ASC,
         attempts ASC,
         scheduled_date_gmt ASC,
         action_id ASC
LIMIT 50
FOR UPDATE SKIP LOCKED;

The closest shipped index is:

(claim_id, status, priority, scheduled_date_gmt)

Live EXPLAIN for the default order:

key: status_scheduled_date_gmt
rows: 3746104
Extra: Using index condition; Using where; Using filesort

Concurrent UPDATE/JOIN claims spent 11-21 seconds in Creating sort index.

Removing only the unindexed attempts ordering causes MariaDB to choose the existing priority index:

key: claim_id_status_priority_scheduled_date_gmt
rows: 4250182
Extra: Using where; Using index

That experiment is diagnostic only; removing attempt fairness is not proposed as the durable fix.

Failure

InnoDB monitor output captured concurrent 50-action claims with:

• approximately 1.4-2.7M row locks per transaction
• approximately 102-164 MB lock heaps per transaction
• repeated warnings that over 67% of the 507 MB buffer pool was occupied by lock heaps

MariaDB then aborted:

[ERROR] [FATAL] InnoDB: Over 95 percent of the buffer pool is occupied by lock heaps or the adaptive hash index!
/usr/sbin/mariadbd got signal 6

The aborting statement was the standard Action Scheduler UPDATE ... JOIN ( SELECT ... FOR UPDATE SKIP LOCKED ) claim query with LIMIT 50.

Proposed Index

The index benchmarked in #1340 matches the exact predicate/order:

(claim_id, status, priority, attempts, scheduled_date_gmt, action_id)

#1340 reported UPDATE-claim median improvement from 97.222ms to 5.449ms on a 100k-row fixture. It was closed because the author did not intend to open the LLM-generated PR, not because the index or benchmark was rejected.

Requested Direction

Would maintainers accept a focused PR that:

1. bumps the Action Scheduler store schema version;
2. adds the complete claim-order index while retaining existing indexes for compatibility;
3. adds query-plan/large-fixture regression coverage where practical; and
4. documents safe upgrade considerations for existing multi-million-row stores?

For very large existing stores, automatic dbDelta() execution during ordinary request bootstrap may not be an acceptable operational migration. Guidance on whether the library should expose/coordinate an explicit online migration path for these installations would also be valuable.

Related

• Slow claim actions update query causing lock wait timeouts #1104
• #1250
• #1259
• #1340