pulls/issue page very slow with >19000 repos

[somera]

Description

On my gitea instance with 19000 repositories and 3200 ogas the pulls page need

in 1.20.5 it was fast.

Possible SQL query changes.

Gitea Version

1.21.0

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Screenshots

No response

Git Version

2.42.0

Operating System

Ubuntu 20.04.x

How are you running Gitea?

gitea-1.21.0-linux-amd64

Database

PostgreSQL

[somera]

In older gitea versions I reported some performance issues with many repos/mirrors and orgas. ;)

I try to analyse which query is the problem here.

[somera]

I see

==> gitea.log <==
2023/11/14 17:45:15 ...eb/routing/logger.go:68:func1() [W] router: slow      GET /pulls for xx.xx.xx.xx:0, elapsed 3047.5ms @ user/home.go:329(user.Pulls)

and this

gitea.log:2023/11/14 17:51:40 ...sues/issue_search.go:499:IssueIDs() [I] [SQL] SELECT count(*) FROM "issue" INNER JOIN "repository" ON "issue".repo_id = "repository".id WHERE issue.repo_id=$1 AND (issue.is_closed=$2) AND (issue.is_pull=$3) [7954 false true] - 958.121µs
gitea.log:2023/11/14 17:51:40 ...sues/issue_search.go:499:IssueIDs() [I] [SQL] SELECT "issue".id FROM "issue" INNER JOIN "repository" ON "issue".repo_id = "repository".id WHERE issue.repo_id=$1 AND (issue.is_closed=$2) AND (issue.is_pull=$3) ORDER BY "issue"."updated_unix" DESC, "issue"."created_unix" DESC, "issue"."id" DESC [19500 false true] - 1.283654ms

is the problem:

$ grep "sues/issue_search.go:499:IssueIDs()" gitea.log | wc -l
38280

38280 queries on my instance for the pulls page. Wow.

Two queries for every repo in my gitea.

[KazzmanK]

what is the use for join here? Just to be sure what issue is still referenced by existing repo?
looks like repository join can easily be dropped here.

[somera]

I have no issues in my gitea instance. I have a lot of mirrors and for some projects I'm using pull requests. At the moment all pull requests are closed.

The question is, why the change in 1.21.0? Why the new two selets for every repository?

[somera]

@KazzmanK the design here is wrong.

When I have no issues but 1000000 project, then you check for every project; "is there an issue".

How did you come up with the idea of doing it like that?

[somera]

No issues but ~39000 SQL queries.

issues:

pulls:

In 1.20.x it was fast. Why this change?

Or how much queries will be made if there are 2000 issues/pulls?

[lng2020]

Or how much queries will be made if there are 2000 issues/pulls?

We refactor the search engine here to support multiple engines, such as melisearch, es, etc. But when using the default db engine, it indeed somehow includes some duplicate search. I notice this abnormal behavior too.
The refactor PR: #25174

[somera]

It's possible to fix this in 1.21.1?

[somera]

@delvh will this be not fixed? Or not fixed in 1.21.1?

[somera]

I sav whis was removed from 1.21.1

This is the time for issues page (with no existing issues) with 1.21.1

What is the status here?

[somera]

Any updates here? Will this be fixed?

If yes -> in which release?

If not -> why not?

[lng2020]

If not -> why not?

AFAIK, no one is working on it.

[somera]

Sounds not good. You changed the process. Cause with no issues and closed pull requests my Gitea instance needs 26-30 seconds.

[wolfogre]

It could be fixed by

// gitea/modules/indexer/issues/indexer.go
// CountIssuesByRepo counts issues by options and group by repo id.
// It's not a complete implementation, since it requires the caller should provide the repo ids.
// That means opts.RepoIDs must be specified, and opts.AllPublic must be false.
// It's good enough for the current usage, and it can be improved if needed.
// TODO: use "group by" of the indexer engines to implement it.
func CountIssuesByRepo(ctx context.Context, opts *SearchOptions) (map[int64]int64, error) {
+	if opts.Keyword == "" {
+		opt, _ := db.ToDBOptions(ctx, opts)
+		return issue_model.CountIssuesByRepo(ctx, opt)
+	}

But it doesn't really help because if you search for issues with a non-empty keyword, it will still be very slow when you have 19,000 repos.

And I believe #28304 can fix it by giving up the counts grouped by repo.

You may be curious as to why there are no counts grouped by repo in your screenshot. Actually, there are counts grouped by 19000 repos (that's why it's slow), but most of them have been hidden because the counts are zero.

And why was the old version fast? Because the search results could be incomplete, which is worse.

[somera]

I'm using my gitea instance only with one user. I have 19200 repos and ~3100 orgas.

Issues

Pull-Requests

My question is, why the design is so?

When I take a look on my case. No issues, but the indexer is checking all repos? And all my pull-requests are closed. Means, the page should be just "loaded". If I open the 310 closed pull-requests the details will be loaded.

[somera]

And with 1.20.x I saw the pull request when I opened the pull-requests tab. There was no problems and it needed ~500ms. Here is a screenshot from 1.20.5.

You see the difference.

[wolfogre]

@somera I fully understand your question, but I cannot explain it in a few sentences. If you want to understand the whole story, you need to read

• Refactor and enhance issue indexer to support both searching, filtering and paging #26012
• Include public repos in doer's dashboard for issue search #28304

Or, you may simply hope that the problem can be resolved. Please wait patiently.

[somera]

@wolfogre thx. I will take a look.

[somera]

I can't reopen it.

This is how it looks like in 1.21.2 -> with the fix.

Nothing changes.

I don't understand why so much queries to show TWO open pull requests and just show the number with 338 closed pull requests.

The logged user hat ~3000 orgas and ~19000 repos.

@wolfogre ?

[wolfogre]

@somera It hasn't been backported to v1.21.2 because it's breaking, see #28385 (comment)

Could you please try the latest nightly version? Or you can wait for v1.22.

[somera]

Thx for the info. I can wait for 1.22.x