github-starred/gitea
2
0
Fork 0
mirror of https://github.com/go-gitea/gitea.git synced 2026-03-10 22:06:34 -05:00
Code Issues 2.6k Packages Projects Releases 100 Wiki Activity

Feature Request for better performance on paging and lees memory usage on database #13352

New Issue
Closed
opened 2025-11-02 10:39:26 -06:00 by GiteaMirror · 26 comments
GiteaMirror commented 2025-11-02 10:39:26 -06:00
Owner
Copy Link

Originally created by @somera on GitHub (Aug 1, 2024).

Feature Description

I'm again. The gitea user with 3655 organisations and 22573 repos.

I wondered that my paging is slow. Not on the first pages.

First page need 1-3 seconds. 1 second if the data is precached.

image

On the last pages ...

image

But this is after my 1st optimisation.

You made this queries

  1. SELECT count(*) FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2
  2. SELECT action.* FROM action INNER JOIN repository ON repository.id = action.repo_id WHERE user_id='1' AND is_deleted='f' ORDER BY action.created_unix DESC LIMIT 20 OFFSET 2043740

on pagging.

Explain for the count query is

image

And than I changed the query to

SELECT count(action.id) FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2

and ...

image

it's faster.

Explain for the SELECT query is

image

very bad. I increased work_mem to 6GB and it's better now

image

After this fix it need ~11 seconds.

But it can be better. Cause the problem is this

SELECT action.* FROM action INNER JOIN repository ON repository.id = action.repo_id WHERE user_id='1' AND is_deleted='f' ORDER BY action.created_unix DESC LIMIT 20 OFFSET 2043740

query. When you change it to

SELECT action.id FROM action INNER JOIN repository ON repository.id = action.repo_id WHERE user_id='1' AND is_deleted='f' ORDER BY action.created_unix DESC LIMIT 20 OFFSET 2043740

-> you will get 20 action.id's than explain is

image

and need ~5 seconds and a very less memory on the database.

Than gitea should get the action.* infos only for the 20 id's in the new query.

This optimisation will be good for all gitea users.

Screenshots

No response

Originally created by @somera on GitHub (Aug 1, 2024). ### Feature Description I'm again. The gitea user with 3655 organisations and 22573 repos. I wondered that my paging is slow. Not on the first pages. First page need 1-3 seconds. 1 second if the data is precached. ![image](https://github.com/user-attachments/assets/bf6b89fb-ae81-4ff3-819e-d2f2a2aa7fae) On the last pages ... ![image](https://github.com/user-attachments/assets/210c9434-f106-4d2f-b80f-2dc613afe36f) But this is after my 1st optimisation. You made this queries 1. SELECT count(*) FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 2. SELECT action.* FROM action INNER JOIN repository ON repository.id = action.repo_id WHERE user_id='1' AND is_deleted='f' ORDER BY action.created_unix DESC LIMIT 20 OFFSET 2043740 on pagging. Explain for the count query is ![image](https://github.com/user-attachments/assets/65b6cbb4-ebf3-4a1b-ac40-11a4a0868c58) And than I changed the query to SELECT count(action.id) FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 and ... ![image](https://github.com/user-attachments/assets/d64de64e-d109-4327-ae93-5b971af6f402) it's faster. Explain for the SELECT query is ![image](https://github.com/user-attachments/assets/f11ccb17-de32-44d9-8fb6-e1795a8275f5) very bad. I increased work_mem to 6GB and it's better now ![image](https://github.com/user-attachments/assets/88c5bc6f-8bfa-47ce-a826-36766447d0b2) After this fix it need ~11 seconds. But it can be better. Cause the problem is this SELECT action.* FROM action INNER JOIN repository ON repository.id = action.repo_id WHERE user_id='1' AND is_deleted='f' ORDER BY action.created_unix DESC LIMIT 20 OFFSET 2043740 query. When you change it to SELECT action.id FROM action INNER JOIN repository ON repository.id = action.repo_id WHERE user_id='1' AND is_deleted='f' ORDER BY action.created_unix DESC LIMIT 20 OFFSET 2043740 -> you will get 20 action.id's than explain is ![image](https://github.com/user-attachments/assets/fef9d0b1-ea10-4ae4-b968-768732474543) and need ~5 seconds and a very less memory on the database. Than gitea should get the action.* infos only for the 20 id's in the new query. This optimisation will be good for all gitea users. ### Screenshots _No response_
GiteaMirror added the performance/speedtype/proposal labels 2025-11-02 10:39:26 -06:00
GiteaMirror commented 2025-11-02 10:39:27 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Aug 1, 2024):

I know why it's getting slower.

https://readyset.io/blog/optimizing-sql-pagination-in-postgres

@somera commented on GitHub (Aug 1, 2024): I know why it's getting slower. https://readyset.io/blog/optimizing-sql-pagination-in-postgres
GiteaMirror commented 2025-11-02 10:39:27 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Aug 1, 2024):

On slow paging I see this "cache context ..." log entries

2024/08/01 23:49:01 ...eb/routing/logger.go:68:func1() [W] router: slow      GET /?page=90000&date= for 192.168.178.21:0, elapsed 3969.6ms @ web/home.go:32(web.Home)
2024/08/01 23:49:07 ...activities/action.go:461:GetFeeds() [W] [Slow SQL Query] SELECT "action".* FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1799980 [1 false] - 9.526588923s
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:17 ...eb/routing/logger.go:68:func1() [W] router: slow      GET /?page=89999&date= for 192.168.178.21:0, elapsed 3604.4ms @ web/home.go:32(web.Home)
2024/08/01 23:49:23 ...activities/action.go:461:GetFeeds() [W] [Slow SQL Query] SELECT "action".* FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1799960 [1 false] - 9.456084079s
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
@somera commented on GitHub (Aug 1, 2024): On slow paging I see this "cache context ..." log entries ``` 2024/08/01 23:49:01 ...eb/routing/logger.go:68:func1() [W] router: slow GET /?page=90000&date= for 192.168.178.21:0, elapsed 3969.6ms @ web/home.go:32(web.Home) 2024/08/01 23:49:07 ...activities/action.go:461:GetFeeds() [W] [Slow SQL Query] SELECT "action".* FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1799980 [1 false] - 9.526588923s 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false} 2024/08/01 23:49:17 ...eb/routing/logger.go:68:func1() [W] router: slow GET /?page=89999&date= for 192.168.178.21:0, elapsed 3604.4ms @ web/home.go:32(web.Home) 2024/08/01 23:49:23 ...activities/action.go:461:GetFeeds() [W] [Slow SQL Query] SELECT "action".* FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1799960 [1 false] - 9.456084079s 2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} 2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false} ```
GiteaMirror commented 2025-11-02 10:39:27 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Aug 12, 2024):

Step 1:

OLD:
SELECT count(*) FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2

NEW:
SELECT count(action.id) FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2

Step 2:

OLD (expample with LIMIT and OFFSET -> SELECT action.* uses too much memory on database):
SELECT action.* FROM action INNER JOIN repository ON repository.id = action.repo_id WHERE user_id='1' AND is_deleted='f' ORDER BY action.created_unix DESC LIMIT 20 OFFSET 2043740

NEW:

  1. get XX action.id's (SELECT action.id -> uses less memory on database and it's faster)
    SELECT action.id FROM action INNER JOIN repository ON repository.id = action.repo_id WHERE user_id='1' AND is_deleted='f' ORDER BY action.created_unix DESC LIMIT 20 OFFSET 2043740
  2. Select for XX action.id's
    SELECT action.* FROM action WHERE action.id IN (....)
@somera commented on GitHub (Aug 12, 2024): **Step 1:** OLD: ```SELECT count(*) FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2``` NEW: `SELECT count(action.id) FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2` **Step 2:** OLD (expample with LIMIT and OFFSET -> SELECT action.* uses too much memory on database): `SELECT action.* FROM action INNER JOIN repository ON repository.id = action.repo_id WHERE user_id='1' AND is_deleted='f' ORDER BY action.created_unix DESC LIMIT 20 OFFSET 2043740` NEW: 1. get XX action.id's (SELECT action.id -> uses less memory on database and it's faster) `SELECT action.id FROM action INNER JOIN repository ON repository.id = action.repo_id WHERE user_id='1' AND is_deleted='f' ORDER BY action.created_unix DESC LIMIT 20 OFFSET 2043740` 2. Select for XX action.id's `SELECT action.* FROM action WHERE action.id IN (....)`
GiteaMirror commented 2025-11-02 10:39:28 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Aug 12, 2024):

Please can you help to confirm #31821 will resolve this issue?

@lunny commented on GitHub (Aug 12, 2024): Please can you help to confirm #31821 will resolve this issue?
GiteaMirror commented 2025-11-02 10:39:28 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Aug 12, 2024):

@lunny yes, I can help. I can check it on my 2nd instance with the same account of data like my main instance.

I need an executable gitea-1.21.*-linux-amd64? Nigtly?

@somera commented on GitHub (Aug 12, 2024): @lunny yes, I can help. I can check it on my 2nd instance with the same account of data like my main instance. I need an executable gitea-1.21.*-linux-amd64? Nigtly?
GiteaMirror commented 2025-11-02 10:39:28 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Aug 12, 2024):

@lunny yes, I can help. I can check it on my 2nd instance with the same account of data like my main instance.

I need an executable gitea-1.21.*-linux-amd64? Nigtly?

No. It's just a PR. I don't think you can easily replace it to do a test and revert back easily.

@lunny commented on GitHub (Aug 12, 2024): > @lunny yes, I can help. I can check it on my 2nd instance with the same account of data like my main instance. > > I need an executable gitea-1.21.*-linux-amd64? Nigtly? No. It's just a PR. I don't think you can easily replace it to do a test and revert back easily.
GiteaMirror commented 2025-11-02 10:39:28 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Aug 12, 2024):

Means I should compile it?

Tell me what I have to do? Cause I didn't compile gitea for my use with PostgreSQL.

@somera commented on GitHub (Aug 12, 2024): Means I should compile it? Tell me what I have to do? Cause I didn't compile gitea for my use with PostgreSQL.
GiteaMirror commented 2025-11-02 10:39:28 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Aug 14, 2024):

Means I should compile it?

Tell me what I have to do? Cause I didn't compile gitea for my use with PostgreSQL.

No. I can help to compile one but once your upgrade the version I provided, it's difficult for you to downgrade if there is no Gitea develop env for you.

@lunny commented on GitHub (Aug 14, 2024): > Means I should compile it? > > Tell me what I have to do? Cause I didn't compile gitea for my use with PostgreSQL. No. I can help to compile one but once your upgrade the version I provided, it's difficult for you to downgrade if there is no Gitea develop env for you.
GiteaMirror commented 2025-11-02 10:39:28 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Aug 14, 2024):

I have 2nd gitea instance for testing. I can backup the database and after test restore the backup.

@somera commented on GitHub (Aug 14, 2024): I have 2nd gitea instance for testing. I can backup the database and after test restore the backup.
GiteaMirror commented 2025-11-02 10:39:29 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Aug 14, 2024):

Please test https://gitea.com/lunny/packages/src/branch/main/gitea-pr-31821-amd64-linux.tar.gz

@lunny commented on GitHub (Aug 14, 2024): Please test https://gitea.com/lunny/packages/src/branch/main/gitea-pr-31821-amd64-linux.tar.gz
GiteaMirror commented 2025-11-02 10:39:29 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Aug 14, 2024):

@lunny thx. I tested this PR.

My 24/7 NUC with main gitea instance has an Intel Pentium Silver J5005 (4) @ 2.800GHz. The results in this PR was made on this NUC.

My test now I run on a faster Intel NUC with an Intel i5-8259U (8) @ 3.800GHz. Has more cores/threads and it's faster.

How I made the test.

  1. I started the gitea-1.22.1-linux-amd64 release and I opened some pages.
  2. I started the gitea-pr-31821-amd64-linux from you and I opened the same pages. Cause I expected the same projects on the pages.

My expectation is:

  • The same order on the pages.
  • Slightly slower times on the lower pages (due to the 3rd query) and the further you scroll, the more positive the 3rd query will be.

I see same projects on the same pages. But the order is reverted. Project which are on the 1. place on the page are in the PR version on the last place. But I see same projects!!!

Times:

image

@somera commented on GitHub (Aug 14, 2024): @lunny thx. I tested this PR. My 24/7 NUC with main gitea instance has an Intel Pentium Silver J5005 (4) @ 2.800GHz. The results in this PR was made on this NUC. My test now I run on a faster Intel NUC with an Intel i5-8259U (8) @ 3.800GHz. Has more cores/threads and it's faster. How I made the test. 1. I started the gitea-1.22.1-linux-amd64 release and I opened some pages. 2. I started the gitea-pr-31821-amd64-linux from you and I opened the same pages. Cause I expected the same projects on the pages. My expectation is: - The same order on the pages. - Slightly slower times on the lower pages (due to the 3rd query) and the further you scroll, the more positive the 3rd query will be. I see same projects on the same pages. But the order is reverted. Project which are on the 1. place on the page are in the PR version on the last place. But I see same projects!!! Times: ![image](https://github.com/user-attachments/assets/72fc44d7-7181-42a1-b074-548a1abb1f7f)
GiteaMirror commented 2025-11-02 10:39:29 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Aug 14, 2024):

Sorry, but I didn't open more page at the beginning. But the new version need less memory or tmp files in PostgreSQL as I show it in my description,

@somera commented on GitHub (Aug 14, 2024): Sorry, but I didn't open more page at the beginning. But the new version need less memory or tmp files in PostgreSQL as I show it in my description,
GiteaMirror commented 2025-11-02 10:39:29 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Aug 14, 2024):

Last page in 1.22.1

image

Last page in 1.22.0+PR

image

As you see the order is reverted.

@somera commented on GitHub (Aug 14, 2024): **Last page in 1.22.1** ![image](https://github.com/user-attachments/assets/16bfbe6b-15de-4177-9fc8-0dccdf3f52bc) **Last page in 1.22.0+PR** ![image](https://github.com/user-attachments/assets/c1aa580a-0084-48d1-a1f9-5c7e2b30bf7f) As you see the order is reverted.
GiteaMirror commented 2025-11-02 10:39:29 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Aug 14, 2024):

And on my slower NUC I have 11233 more pages.

I wait for you feedback before I restore my DB backup.

@somera commented on GitHub (Aug 14, 2024): And on my slower NUC I have 11233 more pages. I wait for you feedback before I restore my DB backup.
GiteaMirror commented 2025-11-02 10:39:30 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Aug 14, 2024):

And on my slower NUC I have 11233 more pages.

I wait for you feedback before I restore my DB backup.

Yes, it's a bug. Please try version 2 https://gitea.com/lunny/packages/src/branch/main/gitea-pr-31821-amd64-linux-2.tar.gz

@lunny commented on GitHub (Aug 14, 2024): > And on my slower NUC I have 11233 more pages. > > I wait for you feedback before I restore my DB backup. Yes, it's a bug. Please try version 2 https://gitea.com/lunny/packages/src/branch/main/gitea-pr-31821-amd64-linux-2.tar.gz
GiteaMirror commented 2025-11-02 10:39:30 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Aug 14, 2024):

I started this Version

1.22.0+rc1-415-gcd09625a76

and the sorting looks good. But I see the times are the same like in 1.22.1 and in PG log I see the action.* query

2024-08-15 00:09:56.653 CEST [6735] gitea@giteadb LOG: Dauer: 2259.538 ms Ausführen <unnamed>: SELECT "action".* FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819340

The second statement should be SELECT action.id FROM ...

Like here https://github.com/go-gitea/gitea/issues/31752#issuecomment-2283642707

In the first version it was ok

postgresql-Wed.log:2024-08-14 21:55:59.113 CEST [3399] gitea@giteadb LOG:  Dauer: 1029.815 ms  Ausführen <unnamed>: SELECT "action".id FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819420
postgresql-Wed.log:2024-08-14 21:56:04.272 CEST [3399] gitea@giteadb LOG:  Dauer: 1037.622 ms  Ausführen <unnamed>: SELECT "action".id FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819400
postgresql-Wed.log:2024-08-14 21:56:10.563 CEST [3399] gitea@giteadb LOG:  Dauer: 1020.780 ms  Ausführen <unnamed>: SELECT "action".id FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819380
postgresql-Wed.log:2024-08-14 21:56:21.066 CEST [3399] gitea@giteadb LOG:  Dauer: 1015.299 ms  Ausführen <unnamed>: SELECT "action".id FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819360
@somera commented on GitHub (Aug 14, 2024): I started this Version 1.22.0+rc1-415-gcd09625a76 and the sorting looks good. But I see the times are the same like in 1.22.1 and in PG log I see the action.* query `2024-08-15 00:09:56.653 CEST [6735] gitea@giteadb LOG: Dauer: 2259.538 ms Ausführen <unnamed>: SELECT "action".* FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819340` The second statement should be SELECT action.id FROM ... Like here https://github.com/go-gitea/gitea/issues/31752#issuecomment-2283642707 In the first version it was ok ``` postgresql-Wed.log:2024-08-14 21:55:59.113 CEST [3399] gitea@giteadb LOG: Dauer: 1029.815 ms Ausführen <unnamed>: SELECT "action".id FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819420 postgresql-Wed.log:2024-08-14 21:56:04.272 CEST [3399] gitea@giteadb LOG: Dauer: 1037.622 ms Ausführen <unnamed>: SELECT "action".id FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819400 postgresql-Wed.log:2024-08-14 21:56:10.563 CEST [3399] gitea@giteadb LOG: Dauer: 1020.780 ms Ausführen <unnamed>: SELECT "action".id FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819380 postgresql-Wed.log:2024-08-14 21:56:21.066 CEST [3399] gitea@giteadb LOG: Dauer: 1015.299 ms Ausführen <unnamed>: SELECT "action".id FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819360 ```
GiteaMirror commented 2025-11-02 10:39:30 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Aug 15, 2024):

In this version, it will use the old code when the page less than 10, otherwise it will use the new code.

@lunny commented on GitHub (Aug 15, 2024): In this version, it will use the old code when the page less than 10, otherwise it will use the new code.
GiteaMirror commented 2025-11-02 10:39:30 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Aug 15, 2024):

@lunny sounds good, but when you see the OFFSET

2024-08-15 00:09:56.653 CEST [6735] gitea@giteadb LOG: Dauer: 2259.538 ms Ausführen <unnamed>: SELECT "action".* FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819340

SELECT action.* should be uses, cause I'm over 10 pages. And the new method was not used.

@somera commented on GitHub (Aug 15, 2024): @lunny sounds good, but when you see the OFFSET `2024-08-15 00:09:56.653 CEST [6735] gitea@giteadb LOG: Dauer: 2259.538 ms Ausführen <unnamed>: SELECT "action".* FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819340` SELECT action.* should be uses, cause I'm over 10 pages. And the new method was not used.
GiteaMirror commented 2025-11-02 10:39:31 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Aug 16, 2024):

:(. I made a mistake. version 3 https://gitea.com/lunny/packages/src/branch/main/gitea-pr-31821-amd64-linux-3.tar.gz

@lunny commented on GitHub (Aug 16, 2024): :(. I made a mistake. version 3 https://gitea.com/lunny/packages/src/branch/main/gitea-pr-31821-amd64-linux-3.tar.gz
GiteaMirror commented 2025-11-02 10:39:31 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Aug 16, 2024):

Looks good.

Page 1-9 old method. Pages 10+ new method. It was good idea to do it.

And the sorting looks good too.

@somera commented on GitHub (Aug 16, 2024): Looks good. Page 1-9 old method. Pages 10+ new method. It was good idea to do it. And the sorting looks good too.
GiteaMirror commented 2025-11-02 10:39:31 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Aug 16, 2024):

@lunny can I restore my datanse now? Or should I wait?

@somera commented on GitHub (Aug 16, 2024): @lunny can I restore my datanse now? Or should I wait?
GiteaMirror commented 2025-11-02 10:39:31 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Aug 16, 2024):

@lunny can I restore my datanse now? Or should I wait?

Yes, you can restore now. Thank you for your testing.

@lunny commented on GitHub (Aug 16, 2024): > @lunny can I restore my datanse now? Or should I wait? Yes, you can restore now. Thank you for your testing.
GiteaMirror commented 2025-11-02 10:39:32 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Aug 16, 2024):

I have to thank you for the implementation of the PR.

@somera commented on GitHub (Aug 16, 2024): I have to thank you for the implementation of the PR.
GiteaMirror commented 2025-11-02 10:39:32 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Oct 9, 2024):

@lunny I installed 1.22.3 release. I saw the changes.

I see this

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

SQL on paging.

I was sure, the change will be:

SELECT
	"id"
FROM
	"action"
WHERE
	user_id=1 AND is_deleted='f'
ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1976620

and than on SELECT for all the 20 id's.

which in my case need 50% of the time in the higher pages and uses only 10% of the memory.

image

vs

image

@somera commented on GitHub (Oct 9, 2024): @lunny I installed 1.22.3 release. I saw the changes. I see this ``` 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 ``` SQL on paging. I was sure, the change will be: ``` SELECT "id" FROM "action" WHERE user_id=1 AND is_deleted='f' ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1976620 and than on SELECT for all the 20 id's. ``` which in my case need 50% of the time in the higher pages and uses only 10% of the memory. ![image](https://github.com/user-attachments/assets/49ae4696-e6b6-4a7b-94b0-3154070ffc53) vs ![image](https://github.com/user-attachments/assets/fc721b74-c400-4fff-8f34-db446a4f419f)
GiteaMirror commented 2025-11-02 10:39:32 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Oct 11, 2024):

Please follow #32224

@lunny commented on GitHub (Oct 11, 2024): Please follow #32224
GiteaMirror commented 2025-11-02 10:39:32 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Nov 25, 2024):

Just update for the start page ...

image

thx!

@somera commented on GitHub (Nov 25, 2024): Just update for the start page ... ![image](https://github.com/user-attachments/assets/5b86acac-50c5-46bd-bfb8-06559a69618e) thx!
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/proposal
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#13352
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?