Costly chained queries involving `commit_status (sha, repo_id)`

Gitea Version

1.27.3

What happened?

In some unknown but frequent cases, gitea generates extremely costly/large queries against thecommit_status table containing hundreds (or thousands) of OR sha = ? conditions.

SELECT max( `index` ) as `index`, sha
FROM `commit_status`
WHERE (repo_id = ?) AND (sha=? OR ... /* hundreds/thousands */ OR sha=?)
GROUP BY context_hash, sha
ORDER BY max( `index` ) desc;

These queries end up consuming the MariaDB connection limit and/or resources, eventually causing it to fail with Too many connections.

MariaDB [(none)]> show processlist;
+------+-------+--------------------+-------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
| Id   | User  | Host               | db    | Command | Time | State        | Info                                                                                                 | Progress |
+------+-------+--------------------+-------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
|   59 | gitea | 10.243.4.106:46712 | gitea | Execute | 3709 | Sending data | SELECT max( `index` ) as `index`, sha FROM `commit_status` WHERE (repo_id = ?) AND (sha=? OR sha=? O |    0.000 |
...
| 5232 | gitea | 10.243.4.23:39440  | gitea | Execute |   51 | Sending data | SELECT max( `index` ) as `index`, sha FROM `commit_status` WHERE (repo_id = ?) AND (sha=? OR sha=? O |    0.000 |
| 5260 | root  | localhost          | NULL  | Query   |    0 | starting     | show processlist                                                                                     |    0.000 |
+------+-------+--------------------+-------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
151 rows in set (0.001 sec)

Adding an index on (repo_id, sha) improves a bit the execution of these queries, but doesn't solve the issue:

CREATE INDEX IDX_commit_status_repo_sha_FIX ON commit_status (repo_id, sha);

As a temporary workaround, maximum statement time could be limited for gitea DB user, but it could also limit other legitimate queries.

GRANT USAGE ON *.* TO 'gitea'@'%' WITH MAX_STATEMENT_TIME 30;

Structure of our commit_status table:

MariaDB [gitea]> SHOW CREATE TABLE commit_status\G
*************************** 1. row ***************************
       Table: commit_status
Create Table: CREATE TABLE `commit_status` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `index` bigint(20) DEFAULT NULL,
  `repo_id` bigint(20) DEFAULT NULL,
  `state` varchar(7) NOT NULL,
  `sha` varchar(64) NOT NULL,
  `target_url` text DEFAULT NULL,
  `description` text DEFAULT NULL,
  `context_hash` varchar(64) DEFAULT NULL,
  `context` text DEFAULT NULL,
  `creator_id` bigint(20) DEFAULT NULL,
  `created_unix` bigint(20) DEFAULT NULL,
  `updated_unix` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UQE_commit_status_repo_sha_index` (`index`,`repo_id`,`sha`),
  KEY `IDX_commit_status_index` (`index`),
  KEY `IDX_commit_status_repo_id` (`repo_id`),
  KEY `IDX_commit_status_sha` (`sha`),
  KEY `IDX_commit_status_context_hash` (`context_hash`),
  KEY `IDX_commit_status_created_unix` (`created_unix`),
  KEY `IDX_commit_status_updated_unix` (`updated_unix`),
  KEY `IDX_commit_status_repo_sha_FIX` (`repo_id`,`sha`)
) ENGINE=InnoDB AUTO_INCREMENT=339871 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_as_cs ROW_FORMAT=DYNAMIC
1 row in set (0.000 sec)

How are you running Gitea?

Kubernetes (official helm chart) with MariaDB 10.11.4