One thing we should probably check is how long the problematic queries take to complete on the primary host:

• Do they take longer to complete in the replica because of RBR replication or do they take a very long time in the primary too?
• Are they getting logged in the slow-query log?
• Would setting a limit to query execution time in the primary help?

Do they take longer to complete in the replica because of RBR replication or do they take a very long time in the primary too?
Are they getting logged in the slow-query log?
Would setting a limit to query execution time in the primary help?

The latest query to cause a replication lag is T368250: [toolsdb] ToolsToolsDBReplicationLagIsTooHigh - 2024-06-21 and it is not logged in the primary slow-query log. We already have a limit to query execution time (introduced in this patch) but it's not preventing this issue.

We are currently using binlog_format=ROW. Setting binlog_format=MIXED (the same that is used in production) is likely to help, because it will hopefully choose the "statement" mode for the problematic queries that are now causing issues with replication. Docs: https://mariadb.com/kb/en/binary-log-formats/

We are currently using slave_parallel_mode=conservative. Setting slave_parallel_mode=optimistic (the same that is used in production) is likely to help because it should lead to more efficient CPU usage. Docs: https://mariadb.com/kb/en/parallel-replication/

We are currently using slave_parallel_mode=conservative. Setting slave_parallel_mode=optimistic (the same that is used in production) is likely to help because it should lead to more efficient CPU usage.

Since the upgrade to MariaDB 10.6 (T352206: [toolsdb] Upgrade to MariaDB 10.6), we are now using slave_parallel_mode=optimistic as it's the new default in MariaDB.

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE "%parallel%";
+-------------------------------+------------+
| Variable_name                 | Value      |
+-------------------------------+------------+
| slave_domain_parallel_threads | 0          |
| slave_parallel_max_queued     | 131072     |
| slave_parallel_mode           | optimistic |
| slave_parallel_threads        | 12         |
| slave_parallel_workers        | 12         |
+-------------------------------+------------+