Unexpected high CPU usage with a `SELECT ... FOR UPDATE SKIP LOCKED` query

MySQL & MariaDB Percona Server for MySQL 8.0
1

Hello,

I’m seeing an unexpected high CPU usage with Percona Server for MySQL which I’m unable to reproduce on MySQL.

I’m triggering the issue from some PHP code implementing some sort of a queue system, the code looks like this:

while (true) {
     startTransaction();
     $task = executeQuery('SELECT id, topic, payload, enqueue_timestamp, enqueue_timestamp_microsecond, nb_added_in_queue FROM async_events WHERE queue_name = ? ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED', $queue_name);
     // process task if needed
     commitTransaction();
     sleep(1);
}

The table definition looks like this:

CREATE TABLE async_events (
    id BINARY(16) NOT NULL PRIMARY KEY,
    queue_name VARCHAR(255) NOT NULL,
    topic VARCHAR(255) NOT NULL,
    payload JSON NOT NULL,
    enqueue_timestamp INT UNSIGNED NOT NULL,
    enqueue_timestamp_microsecond MEDIUMINT UNSIGNED NOT NULL,
    nb_added_in_queue TINYINT UNSIGNED NOT NULL,
    INDEX idx_queue_name (queue_name)
);

With an empty async_events table (so only executing about 60 queries per minute) I have a CPU core busy doing only that.
The issue is completely gone if I remove the FOR UPDATE part of the query.

I have not been able to pinpoint the issue to something specific to Percona but the issue does not exist at all if I execute the same code against a MySQL server.

My test environment is fresh install on Rockly Linux 9 with the following /etc/mysql/my.cnf file:

[mysqld]
datadir=/var/lib/mysql
sql_mode="NO_ENGINE_SUBSTITUTION"

The issue also does not seem to be new I have found an old Reddit thread with the same symptoms: Reddit - Dive into anything

Does anyone have a clue on what’s going on?

2

Hello @Thomas_Gerbet,
if you have a reproducible test case, please create an issue at https://jira.percona.com/ so our engineering team can look into what is happening. If you have stack traces, pmp, flamegraph, etc of the query running on community mysql vs percona mysql, that would immensely help too.

3

Thanks for the feedback, I opened [PS-9252] - Percona JIRA and created a minimal reproducible test case.