Horrible Dashboard Delay After Upgrade

[SoulSeekkor]

Description

Previously on 1.22.2 the dashboard for my instance loaded instantly, after upgrading to 1.22.3 when I load/refresh the dashboard the refreshing simply hangs (web page does nothing) for over 8 seconds then the entire dashboard is shown at once. Running Windows/Ubuntu with a MSSQL backend doesn't seem to encounter this problem, only with the MySQL8 backend is it doing this.

Relevant logs:
2024/10/09 08:45:53 cmd/web.go:194:serveInstalled() [W] Table oauth2_application column redirect_uris db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:53 cmd/web.go:194:serveInstalled() [W] Table oauth2_grant column scope db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:53 cmd/web.go:194:serveInstalled() [W] Table oauth2_grant column nonce db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:53 cmd/web.go:194:serveInstalled() [W] Table login_source column cfg db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:53 cmd/web.go:194:serveInstalled() [W] Table notice column description db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:53 cmd/web.go:194:serveInstalled() [W] Table system_setting Column version db default is , struct default is 1
2024/10/09 08:45:53 cmd/web.go:194:serveInstalled() [W] Table email_address Column lower_email db nullable is true, struct nullable is false
2024/10/09 08:45:53 cmd/web.go:194:serveInstalled() [W] Table external_login_user column raw_data db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:53 cmd/web.go:194:serveInstalled() [W] Table external_login_user column avatar_url db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:53 cmd/web.go:194:serveInstalled() [W] Table external_login_user column access_token db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:53 cmd/web.go:194:serveInstalled() [W] Table external_login_user column access_token_secret db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:53 cmd/web.go:194:serveInstalled() [W] Table external_login_user column refresh_token db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:53 cmd/web.go:194:serveInstalled() [W] Table user Column keep_activity_private db default is , struct default is false
2024/10/09 08:45:53 cmd/web.go:194:serveInstalled() [W] Table user Column keep_activity_private db nullable is true, struct nullable is false
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table language_stat Column language db nullable is true, struct nullable is false
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table mirror column lfs_endpoint db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table release column note db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table repository column description db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table repository Column original_service_type db default is 0, struct default is
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table repository column topics db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table repo_unit column config db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table task column payload_content db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table task column message db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table gpg_key column content db type is LONGTEXT(4294967295), struct type is MEDIUMTEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table gpg_key Column content db nullable is true, struct nullable is false
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table gpg_key column emails db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table gpg_key_import column content db type is LONGTEXT(4294967295), struct type is MEDIUMTEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table gpg_key_import Column content db nullable is true, struct nullable is false
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table public_key column content db type is LONGTEXT(4294967295), struct type is MEDIUMTEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table public_key Column content db nullable is true, struct nullable is false
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table project column board_type db type is INT, struct type is INT UNSIGNED
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table project column card_type db type is INT, struct type is INT UNSIGNED
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table project Column card_type db default is 0, struct default is
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table project Column card_type db nullable is false, struct nullable is true
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table project column type db type is INT, struct type is INT UNSIGNED
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table review_state Column commit_sha db nullable is true, struct nullable is false
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table hook_task has column repo_id but struct has not related field
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table webhook Column is_system_webhook db default is 0, struct default is
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table webhook Column is_system_webhook db nullable is false, struct nullable is true
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table webhook column url db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table webhook Column http_method db default is 'POST', struct default is
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table webhook column secret db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table webhook column events db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table webhook column meta db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table webhook column header_authorization_encrypted db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table commit_status column target_url db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table commit_status column description db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table commit_status column context db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table lfs_lock column path db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table protected_branch column whitelist_user_i_ds db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table protected_branch column whitelist_team_i_ds db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table protected_branch column merge_whitelist_user_i_ds db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table protected_branch column merge_whitelist_team_i_ds db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table protected_branch column status_check_contexts db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table protected_branch column approvals_whitelist_user_i_ds db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table protected_branch column approvals_whitelist_team_i_ds db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table protected_branch column protected_file_patterns db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table protected_branch column unprotected_file_patterns db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table comment has column old_assignee_id but struct has not related field
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table label Column archived_unix db default is , struct default is NULL
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table milestone column content db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table pull_request column conflicted_files db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table pull_request column changed_protected_files db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table review column type db type is VARCHAR(255), struct type is INT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table review column content db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table tracked_time Column time db nullable is true, struct nullable is false
2024/10/09 08:45:54 cmd/web.go:194:serveInstalled() [W] Table action column content db type is MEDIUMTEXT(16777215), struct type is TEXT
2024/10/09 08:46:24 ...eb/routing/logger.go:68:func1() [W] router: slow GET / for 10.12.10.6:53490, elapsed 3197.1ms @ web/home.go:32(web.Home)
2024/10/09 08:46:29 ...activities/action.go:459:GetFeeds() [W] [Slow SQL Query] SELECT count(*) FROM action WHERE user_id=? AND is_deleted=? [1 false] - 8.310601896s
2024/10/09 08:46:29 ...ls/avatars/avatar.go:120:LibravatarURL() [E] LibravatarService.FromEmail(email=github-actions[bot]@users.noreply.github.com): error mail: missing '@' or angle-addr
2024/10/09 08:46:32 ...eful/manager_unix.go:144:handleSignals() [W] PID 448390. Received SIGTERM. Shutting down...
2024/10/09 08:46:32 .../graceful/manager.go:168:doHammerTime() [W] Setting Hammer condition
2024/10/09 08:46:33 .../graceful/manager.go:184:doTerminate() [W] Terminating
2024/10/09 08:46:33 ...eful/manager_unix.go:154:handleSignals() [W] PID: 448390. Background context for manager closed - context canceled - Shutting down...

Gitea Version

1.22.3

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Screenshots

No response

Git Version

2.46.2

Operating System

Ubuntu 24.04

How are you running Gitea?

This is with the published release binary for 64-bit Linux running in a Ubuntu 24.04 incus (LXC) container.

Database

MySQL 8

[yp05327]

@lunny

[lunny]

@lunny

I will investigate it. How many records in the table action.

[bsofiato]

Hey @lunny, I have a somewhat large instance of Gitea running on my machine (at least I think it is large -- its actions table has almost 13 million rows).

I've created an index based on the action's user_id and is_deleted columns and the cost of the queries has dropped from 61923.66 to about 8). The database is Postgres.

I don't know if such an index would have other implications that I'm unaware of. But if you think that adding the index would be a sensible approach, I would gladly submit a PR for it.

[lunny]

According to the current index function, I think maybe you are right, we need that index. Please send a PR to fix this.

// TableIndices implements xorm's TableIndices interface
func (a *Action) TableIndices() []*schemas.Index {
	repoIndex := schemas.NewIndex("r_u_d", schemas.IndexType)
	repoIndex.AddColumn("repo_id", "user_id", "is_deleted")

	actUserIndex := schemas.NewIndex("au_r_c_u_d", schemas.IndexType)
	actUserIndex.AddColumn("act_user_id", "repo_id", "created_unix", "user_id", "is_deleted")

	cudIndex := schemas.NewIndex("c_u_d", schemas.IndexType)
	cudIndex.AddColumn("created_unix", "user_id", "is_deleted")

	indices := []*schemas.Index{actUserIndex, repoIndex, cudIndex}

	return indices
}

[lunny]

I think the 61923.66 to about 8 means ms?

[lunny]

Looks like we already have the index definition. cudIndex.AddColumn("created_unix", "user_id", "is_deleted") which will be used in find function, but for counting , the Desc("action.created_unix") will be ignored. @bsofiato Can you post your two SQL's execution time. One is for counting, and the other one is for finding.

[bsofiato]

I think the 61923.66 to about 8 means ms?

Nope, it is more like an arbitrary unit of costs. This article does a great job of explaining the concept.

Looks like we already have the index definition. cudIndex.AddColumn("created_unix", "user_id", "is_deleted") which will be used in find function, but for counting , the Desc("action.created_unix") will be ignored. @bsofiato Can you post your two SQL's execution time. One is for counting, and the other one is for finding.

For sure. The first screen shot shows the cost when using the indexes that are already in place. See that it uses the IDX_action_user_id index and it performs a scan filtering it by the is_deleted column.

For fetching whe get the results below.

If I apply the command CREATE INDEX action_user_id_idx ON public."action" (user_id,is_deleted); and rerun the query I get the results below.

The results below show the fetching results.

As of what is happening with the cudIndex.AddColumn("created_unix", "user_id", "is_deleted") index. AFAIK, a composite index can only be used if the criterion spans its leftmost columns. To check if this hypothesis is correct, I've created another index on on which the created_unix is placed last. The query cost were similar to the 2nd scenario.

Fetching results:

If the created_unix is not used in any criteria (i.e., only used to order results), I think we could safely reorder the index columns to make it more selective. Otherwise, I think the solution would be to create another index.

[lunny]

Can you try select * from action where user_id=? and is_deleted=? order by created_unix desc? This should be the SQL Gitea used in the dashboard.

[somera]

In my case I see improvements which was made in #31752. In my action table are 3977694 entries. And this
Dauer: 1471.509 ms Ausführen <unnamed>: SELECT count(*) FROM "action" WHERE user_id=$1 AND is_deleted=$2
query needs ~1,5 seconds, if the data is cached. First call after reboot need ~8 seconds. But this is ok. My Gitea and PostgreSQL 16 run on an 4 core NUC with 32 GB RAM.

Idea: The count query could by cached and invalidated when there are changes in the action table? this would speedup this. Depends how often something changes in the action table.

I missing only some improvements on the higher pages on pagging. Cause there sorting is consuming too much RAM.

I mean this query

SELECT
	"id", "user_id", "op_type", "act_user_id", "repo_id", "comment_id", "is_deleted", "ref_name", "is_private", "content", "created_unix"
FROM
	"action"
WHERE
	user_id=1 AND is_deleted='f'
ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1976620

And the optimizes query need less RAM

[somera]

I've created an index based on the action's user_id and is_deleted columns and the cost of the queries has dropped from 61923.66 to about 8). The database is Postgres.

How is your Postgres RAM configuration? In my case I saw a lot of tmp-files and I increased some parameters.

shared_buffers = 8GB
temp_buffers = 2GB
work_mem = 6GB
max_wal_size = 2GB
min_wal_size = 512MB
effective_cache_size = 4GB

This was an improvement.

And this https://explain.depesz.com/ make the explain result more readable.

[bsofiato]

Can you try select * from action where user_id=? and is_deleted=? order by created_unix desc? This should be the SQL Gitea used in the dashboard.

For sure. I just added limit to the end of the query (since the dashboard is paged, I assume it is)

The original index yields the results below:

In the other hand, we get the following results when using the (user_id, is_deleted) index. Notice that postgre still uses the same index.

After that, I've added created the (user_id, is_deleted, created_unix) index. Which resulted in:

Lastly, I've tried to create the index as (is_deleted, user_id, created_unix) index. The results was:

Tha last two index are pratically equal in terms of performance (the actual cost of the 4th being marginally lower).

I've created an index based on the action's user_id and is_deleted columns and the cost of the queries has dropped from 61923.66 to about 8). The database is Postgres.

How is your Postgres RAM configuration? In my case I saw a lot of tmp-files and I increased some parameters.

shared_buffers = 8GB
temp_buffers = 2GB
work_mem = 6GB
max_wal_size = 2GB
min_wal_size = 512MB
effective_cache_size = 4GB

This was an improvement.

And this https://explain.depesz.com/ make the explain result more readable.

I'll have to check it out. But I'm pretty sure no tuning was made to this particular database since it used mainly for development.

BTW, nice tool :)