github-starred/gitea
2
0
Fork 0
mirror of https://github.com/go-gitea/gitea.git synced 2026-07-15 03:09:45 -05:00
Code Issues 2.6k Packages Projects Releases 100 Wiki Activity

Slow MySQL Query when loading startpage #12058

New Issue
Closed
opened 2025-11-02 09:56:23 -06:00 by GiteaMirror · 7 comments
GiteaMirror commented 2025-11-02 09:56:23 -06:00
Owner
Copy Link

Originally created by @gunman808 on GitHub (Nov 21, 2023).

Description

Every time the startpage of gitea is loading it took a very long time (>8s). We have activated the mysql-slow-query-log to inspect what is happening and there is a query which tooks a very long time:

SELECT `action`.* FROM `action` INNER JOIN `repository` ON `repository`.id = `action`.repo_id WHERE user_id=7 AND is_deleted=0 ORDER BY `action`.`created_unix` DESC LIMIT 20;
# Time: 2023-11-20T10:19:43.302946Z
# User@Host: gitea[gitea] @  [198.18.4.243]  Id:   266
# Query_time: 8.203707  Lock_time: 0.000028 Rows_sent: 20  Rows_examined: 64560

After using mysql explain to analyze the query, it shows the query is not using the correct index:

mysql> explain SELECT `action`.* FROM `action` INNER JOIN `repository` ON `repository`.id = `action`.repo_id WHERE user_id=7 AND is_deleted=0 ORDER BY `action`.`created_unix` DESC LIMIT 20;
+----+-------------+------------+------------+-------+------------------+------------------------+---------+---------------------------------+------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys    | key                    | key_len | ref                             | rows | filtered | Extra                                        |
+----+-------------+------------+------------+-------+------------------+------------------------+---------+---------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | repository | NULL       | index | PRIMARY          | IDX_repository_is_fork | 1       | NULL                            |  170 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | action     | NULL       | ref   | IDX_action_r_u_d | IDX_action_r_u_d       | 19      | gitea.repository.id,const,const |  273 |   100.00 | NULL                                         |
+----+-------------+------------+------------+-------+------------------+------------------------+---------+---------------------------------+------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

For testing purpose we removed the index IDX_action_r_u_d and started the explain again:

mysql> drop index IDX_action_r_u_d on action;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT `action`.* FROM `action` INNER JOIN `repository` ON `repository`.id = `action`.repo_id WHERE user_id=7 AND is_deleted=0 ORDER BY `action`.`created_unix` DESC LIMIT 20;
+----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key              | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | action     | NULL       | index  | NULL          | IDX_action_c_u_d | 19      | NULL                 |   20 |     1.00 | Using where |
|  1 | SIMPLE      | repository | NULL       | eq_ref | PRIMARY       | PRIMARY          | 8       | gitea.action.repo_id |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

Now the query is using the IDX_action_c_u_d and the primary index.
The query is now incredibly fast in comparison. It took
20 rows in set (0.00 sec)
Before removing the index it took 20 rows in set (8.18 sec).

Can you check this behaviour of the index in mysql. Maybe the removed index is needed in other situations.

The workaround of removing the index is working fine for us, but every time restarting the gitea instance, the index will be created again.

Gitea Version

1.20.5

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Screenshots

No response

Git Version

2.40.1

Operating System

Debian 11

How are you running Gitea?

It is running in the official docker container.
The following images are used:
image: gitea:1.20.5
image: mysql:5.7
image: gitea-runner

Database

MySQL/MariaDB

Originally created by @gunman808 on GitHub (Nov 21, 2023). ### Description Every time the startpage of gitea is loading it took a very long time (>8s). We have activated the mysql-slow-query-log to inspect what is happening and there is a query which tooks a very long time: ``` SELECT `action`.* FROM `action` INNER JOIN `repository` ON `repository`.id = `action`.repo_id WHERE user_id=7 AND is_deleted=0 ORDER BY `action`.`created_unix` DESC LIMIT 20; # Time: 2023-11-20T10:19:43.302946Z # User@Host: gitea[gitea] @ [198.18.4.243] Id: 266 # Query_time: 8.203707 Lock_time: 0.000028 Rows_sent: 20 Rows_examined: 64560 ``` After using mysql explain to analyze the query, it shows the query is not using the correct index: ``` mysql> explain SELECT `action`.* FROM `action` INNER JOIN `repository` ON `repository`.id = `action`.repo_id WHERE user_id=7 AND is_deleted=0 ORDER BY `action`.`created_unix` DESC LIMIT 20; +----+-------------+------------+------------+-------+------------------+------------------------+---------+---------------------------------+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+------------------+------------------------+---------+---------------------------------+------+----------+----------------------------------------------+ | 1 | SIMPLE | repository | NULL | index | PRIMARY | IDX_repository_is_fork | 1 | NULL | 170 | 100.00 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | action | NULL | ref | IDX_action_r_u_d | IDX_action_r_u_d | 19 | gitea.repository.id,const,const | 273 | 100.00 | NULL | +----+-------------+------------+------------+-------+------------------+------------------------+---------+---------------------------------+------+----------+----------------------------------------------+ 2 rows in set, 1 warning (0.01 sec) ``` For testing purpose we removed the index **IDX_action_r_u_d** and started the explain again: ``` mysql> drop index IDX_action_r_u_d on action; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 ``` ``` mysql> explain SELECT `action`.* FROM `action` INNER JOIN `repository` ON `repository`.id = `action`.repo_id WHERE user_id=7 AND is_deleted=0 ORDER BY `action`.`created_unix` DESC LIMIT 20; +----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+ | 1 | SIMPLE | action | NULL | index | NULL | IDX_action_c_u_d | 19 | NULL | 20 | 1.00 | Using where | | 1 | SIMPLE | repository | NULL | eq_ref | PRIMARY | PRIMARY | 8 | gitea.action.repo_id | 1 | 100.00 | Using index | +----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+ 2 rows in set, 1 warning (0.01 sec) ``` Now the query is using the IDX_action_c_u_d and the primary index. The query is now incredibly fast in comparison. It took `20 rows in set (0.00 sec)` Before removing the index it took `20 rows in set (8.18 sec)`. Can you check this behaviour of the index in mysql. Maybe the removed index is needed in other situations. The workaround of removing the index is working fine for us, but every time restarting the gitea instance, the index will be created again. ### Gitea Version 1.20.5 ### Can you reproduce the bug on the Gitea demo site? No ### Log Gist _No response_ ### Screenshots _No response_ ### Git Version 2.40.1 ### Operating System Debian 11 ### How are you running Gitea? It is running in the official docker container. The following images are used: image: gitea:1.20.5 image: mysql:5.7 image: gitea-runner ### Database MySQL/MariaDB
GiteaMirror added the performance/speedtype/bug labels 2025-11-02 09:56:23 -06:00
GiteaMirror commented 2025-11-02 09:56:24 -06:00
Author
Owner
Copy Link

@katsusan commented on GitHub (Nov 27, 2023):

In the first case it seems that mysql made a weird decision of using IDX_repository_is_fork when do joining between two tables.

AFAIK, this index probably wouldn't make any work when producing cartesian product between action and repository on the condition of repository.id = action.repo_id. It's hard to understand why mysql chosen to do this(maybe a decison-making mistake of specified version?), however you can try give a index hint like USE INDEX FOR JOIN (`PRIMARY`) and see how will mysql decide to execute.

EXPLAIN SELECT
	`action`.* 
FROM
	`action`
	INNER JOIN 
	`repository` USE INDEX FOR JOIN (`PRIMARY`)
ON `repository`.id = `action`.repo_id
WHERE
	user_id = 7 
	AND is_deleted = 0 
ORDER BY
	`action`.`created_unix` DESC 
LIMIT 20;
@katsusan commented on GitHub (Nov 27, 2023): In the first case it seems that mysql made a weird decision of using **IDX_repository_is_fork** when do joining between two tables. AFAIK, this index probably wouldn't make any work when producing cartesian product between `action` and `repository` on the condition of `repository.id = action.repo_id`. It's hard to understand why mysql chosen to do this(maybe a decison-making mistake of specified version?), however you can try give a index hint like ```USE INDEX FOR JOIN (`PRIMARY`)``` and see how will mysql decide to execute. ```mysql EXPLAIN SELECT `action`.* FROM `action` INNER JOIN `repository` USE INDEX FOR JOIN (`PRIMARY`) ON `repository`.id = `action`.repo_id WHERE user_id = 7 AND is_deleted = 0 ORDER BY `action`.`created_unix` DESC LIMIT 20; ```
GiteaMirror commented 2025-11-02 09:56:24 -06:00
Author
Owner
Copy Link

@gunman808 commented on GitHub (Nov 28, 2023):

Yes, by giving the index hint the explain shows the using of the PRIMARY index for the repository table:

mysql> EXPLAIN SELECT `action`.*  FROM `action` INNER JOIN  `repository` USE INDEX FOR JOIN (`PRIMARY`) ON `repository`.id = `action`.repo_id WHERE user_id = 7  AND is_deleted = 0  ORDER BY `action`.`created_unix` DESC  LIMIT 20;
+----+-------------+------------+------------+-------+------------------+------------------+---------+---------------------------------+------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys    | key              | key_len | ref                             | rows | filtered | Extra                                        |
+----+-------------+------------+------------+-------+------------------+------------------+---------+---------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | repository | NULL       | index | PRIMARY          | PRIMARY          | 8       | NULL                            |  167 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | action     | NULL       | ref   | IDX_action_r_u_d | IDX_action_r_u_d | 19      | gitea.repository.id,const,const |  119 |   100.00 | NULL                                         |
+----+-------------+------------+------------+-------+------------------+------------------+---------+---------------------------------+------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

But it doesn't help to optimize the query. It is as slow as before, due to the order in which the query is processed. You can see it by the count of the rows which will be processed.

To change the order of the internal query process, you need to give an index hint (IDX_action_c_u_d) for the action table too.

EXPLAIN SELECT `action`.*  FROM `action` USE INDEX FOR JOIN (`IDX_action_c_u_d`) INNER JOIN  `repository` USE INDEX FOR JOIN (`PRIMARY`) ON `repository`.id = `action`.repo_id WHERE user_id = 7  AND is_deleted = 0  ORDER BY `action`.`created_unix` DESC  LIMIT 20;

Output:

mysql> EXPLAIN SELECT `action`.*  FROM `action` USE INDEX FOR JOIN (`IDX_action_c_u_d`) INNER JOIN  `repository` USE INDEX FOR JOIN (`PRIMARY`) ON `repository`.id = `action`.repo_id WHERE user_id = 7  AND is_deleted = 0  ORDER BY `action`.`created_unix` DESC  LIMIT 20;
+----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key              | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | action     | NULL       | index  | NULL          | IDX_action_c_u_d | 19      | NULL                 |   20 |     1.00 | Using where |
|  1 | SIMPLE      | repository | NULL       | eq_ref | PRIMARY       | PRIMARY          | 8       | gitea.action.repo_id |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

Here the table action is processed at first with the where and limit condition and after that it matches against the repository table. There are much less rows processed and so the query is significant faster.
So it would be nice if this query could be fixed with this index hint for the action table.

@gunman808 commented on GitHub (Nov 28, 2023): Yes, by giving the index hint the explain shows the using of the PRIMARY index for the repository table: ``` mysql> EXPLAIN SELECT `action`.* FROM `action` INNER JOIN `repository` USE INDEX FOR JOIN (`PRIMARY`) ON `repository`.id = `action`.repo_id WHERE user_id = 7 AND is_deleted = 0 ORDER BY `action`.`created_unix` DESC LIMIT 20; +----+-------------+------------+------------+-------+------------------+------------------+---------+---------------------------------+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+------------------+------------------+---------+---------------------------------+------+----------+----------------------------------------------+ | 1 | SIMPLE | repository | NULL | index | PRIMARY | PRIMARY | 8 | NULL | 167 | 100.00 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | action | NULL | ref | IDX_action_r_u_d | IDX_action_r_u_d | 19 | gitea.repository.id,const,const | 119 | 100.00 | NULL | +----+-------------+------------+------------+-------+------------------+------------------+---------+---------------------------------+------+----------+----------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) ``` But it doesn't help to optimize the query. It is as slow as before, due to the order in which the query is processed. You can see it by the count of the rows which will be processed. To change the order of the internal query process, you need to give an index hint (IDX_action_c_u_d) for the action table too. ```sql EXPLAIN SELECT `action`.* FROM `action` USE INDEX FOR JOIN (`IDX_action_c_u_d`) INNER JOIN `repository` USE INDEX FOR JOIN (`PRIMARY`) ON `repository`.id = `action`.repo_id WHERE user_id = 7 AND is_deleted = 0 ORDER BY `action`.`created_unix` DESC LIMIT 20; ``` Output: ``` mysql> EXPLAIN SELECT `action`.* FROM `action` USE INDEX FOR JOIN (`IDX_action_c_u_d`) INNER JOIN `repository` USE INDEX FOR JOIN (`PRIMARY`) ON `repository`.id = `action`.repo_id WHERE user_id = 7 AND is_deleted = 0 ORDER BY `action`.`created_unix` DESC LIMIT 20; +----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+ | 1 | SIMPLE | action | NULL | index | NULL | IDX_action_c_u_d | 19 | NULL | 20 | 1.00 | Using where | | 1 | SIMPLE | repository | NULL | eq_ref | PRIMARY | PRIMARY | 8 | gitea.action.repo_id | 1 | 100.00 | Using index | +----+-------------+------------+------------+--------+---------------+------------------+---------+----------------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) ``` Here the table action is processed at first with the where and limit condition and after that it matches against the repository table. There are much less rows processed and so the query is significant faster. So it would be nice if this query could be fixed with this index hint for the action table.
GiteaMirror commented 2025-11-02 09:56:24 -06:00
Author
Owner
Copy Link

@42wim commented on GitHub (Dec 13, 2023):

blocked by https://gitea.com/xorm/xorm/issues/1456 and #16665 also related

@42wim commented on GitHub (Dec 13, 2023): blocked by https://gitea.com/xorm/xorm/issues/1456 and #16665 also related
GiteaMirror commented 2025-11-02 09:56:25 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Dec 13, 2023):

I think I can take a look at that.

@lunny commented on GitHub (Dec 13, 2023): I think I can take a look at that.
GiteaMirror commented 2025-11-02 09:56:25 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Dec 16, 2023):

https://gitea.com/xorm/xorm/pulls/2375 merged. I think we can upgrade xorm and use the new feature.

@lunny commented on GitHub (Dec 16, 2023): https://gitea.com/xorm/xorm/pulls/2375 merged. I think we can upgrade xorm and use the new feature.
GiteaMirror commented 2025-11-02 09:56:25 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Dec 30, 2023):

Can you confirm #28546 fix the problem?

@lunny commented on GitHub (Dec 30, 2023): Can you confirm #28546 fix the problem?
GiteaMirror commented 2025-11-02 09:56:26 -06:00
Author
Owner
Copy Link

@github-actions[bot] commented on GitHub (Mar 1, 2024):

Automatically locked because of our CONTRIBUTING guidelines

@github-actions[bot] commented on GitHub (Mar 1, 2024): Automatically locked because of our [CONTRIBUTING guidelines](https://github.com/go-gitea/gitea/blob/main/CONTRIBUTING.md#issue-locking)
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
$20
$250
$50
$500
backport/done
💎 Bounty
docs-update-needed
good first issue
hacktoberfest
issue/bounty
issue/confirmed
issue/critical
issue/duplicate
issue/needs-feedback
issue/not-a-bug
issue/regression
issue/stale
issue/workaround
lgtm/need 2
modifies/api
modifies/translation
outdated/backport/v1.18
outdated/theme/markdown
outdated/theme/timetracker
performance/bigrepo
performance/cpu
performance/memory
performance/speed
pr/breaking
proposal/accepted
proposal/rejected
pr/wip
pull-request
Mirrored from GitHub Pull Request
 reviewed/wontfix
💰 Rewarded
skip-changelog
status/blocked
topic/accessibility
topic/api
topic/authentication
topic/build
topic/code-linting
topic/commit-signing
topic/content-rendering
topic/deployment
topic/distribution
topic/federation
topic/gitea-actions
topic/issues
topic/lfs
topic/mobile
topic/moderation
topic/packages
topic/pr
topic/projects
topic/repo
topic/repo-migration
topic/security
topic/theme
topic/ui
topic/ui-interaction
topic/ux
topic/webhooks
topic/wiki
type/bug
type/deprecation
type/docs
type/enhancement
type/feature
type/miscellaneous
type/proposal
type/question
type/refactoring
type/summary
type/testing
type/upstream
No Label performance/speed type/bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
GiteaMirror ninjasurge
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/gitea#12058
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?