Large `action` table may cause 500 error for home page

[wxiaoguang]

2022/02/08 11:26:51 .../web/feed/profile.go:22:RetrieveFeeds() [E] GetFeeds: Find: Error 1038: Out of sort memory, consider increasing server sort buffer size
2022/02/08 11:26:51 router: completed GET / for 106.38.223.242:0, 500 Internal Server Error in 87.5ms @ web/home.go:26(web.Home)

And related to:

• Improve admin dashboard summary display to prevent timeout #17506

[lunny]

I think we have two choices:

1. Split action table according years. i.e. action will contains this year, action-2021 will contain last year actions.
2. Create an action_stats table to save history(over 1 year) actions as statistics information and delete real actions from action table.

[lafriks]

For postgresql, mssql and mysql we could use partitioned tables this would still allow to select data from action table as we do right now and when date provided it would select only from partitions needed. I usually create also an background task that checks periodically if date is close enough and creates new partition for future records. This way we could easily create also table partitioned not only by year but also for month if needed.

PostgreSQL: https://www.postgresql.org/docs/14/ddl-partitioning.html
MySQL: https://dev.mysql.com/doc/refman/5.7/en/partitioning-management.html
MSSQL: https://docs.microsoft.com/en-us/sql/relational-databases/partitions/create-partitioned-tables-and-indexes?view=sql-server-ver15

As for sqlite we could probably create multiple tables and have view with union select of tables for last year

[wxiaoguang]

I have enough DBA knowledge and I know how action table works, so I can handle this problem easily (actually, I just deleted most data in my table).

However, some users do not know how to handle it, like

• 504 on /admin after Gitea upgrade because of large action table #17504
• Wrongly used index slows down queries on action table (dashboard view) #16665

So the question is:

• should we document about this problem and tell users how to handle it
• or should we delete old data periodically.

And I do not think Gitea should touch the partitioning mechanism by itself, we should keep things as simple as possible.

[lafriks]

I agree that partitioning can be done transparently to gitea. Like creating partitioned table manually, moving data from unpartitioned table and than adding crontab job to create partitions

[lafriks]

But documentation for this could be nice to have

[wxiaoguang]

The indices have been re-designed. Close this one.