too slow when visit homepage( / )

[qianlongzt]

sorry for my poor engish.

• Gitea version (or commit ref): 1.3.2 built with: bindata, sqlite
• Git version: 2.15.1
• Operating system: Ubuntu 16.04 LTS
• Database (use [x]):
  • PostgreSQL
  • MySQL
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
  • Not relevant
• Log gist:

Here is the whole log for the request.

https://gist.github.com/qianlongzt/0c0a7536386b2c2f0dcdaf2189fd8672

Description

There is so many repeat SQL query, such as query same user (userid is 2) 205 times.

time spend

Page: 3379ms Template: 3124ms

Screenshots

[tonivj5]

I have been this problem too. It slowed me the dashboard to ~16 seconds!! 😱

I don't know why there's a SQL query per cooment from attachments table, but, that's the trouble. It generated me 6789 lines of SQL query logs. I have removed the issue and all of its comments from database and the problem finished.

Now, I have 79 lines from log when I load the dashboard 🎉

PD: All of comments of the issue was remove/add labels, it could be the problem...

[scofieldpeng]

i have been this problem too, too many sql queries in home page when i have some issues.

there are many improvments, group the split sqls to one query like below:

2018/10/25 17:41:41 [I] [SQL] SELECT `id`, `user_id`, `repo_id`, `mode` FROM `access` WHERE `user_id`=? AND `repo_id`=? LIMIT 1 []interface {}{1, 21}
2018/10/25 17:41:41 [I] [SQL] SELECT `id`, `user_id`, `repo_id`, `mode` FROM `access` WHERE `user_id`=? AND `repo_id`=? LIMIT 1 []interface {}{1, 28}
2018/10/25 17:41:41 [I] [SQL] SELECT `id`, `user_id`, `repo_id`, `mode` FROM `access` WHERE `user_id`=? AND `repo_id`=? LIMIT 1 []interface {}{1, 40}
2018/10/25 17:41:41 [I] [SQL] SELECT `id`, `user_id`, `repo_id`, `mode` FROM `access` WHERE `user_id`=? AND `repo_id`=? LIMIT 1 []interface {}{1, 27}
2018/10/25 17:41:41 [I] [SQL] SELECT `id`, `user_id`, `repo_id`, `mode` FROM `access` WHERE `user_id`=? AND `repo_id`=? LIMIT 1 []interface {}{1, 54}
2018/10/25 17:41:41 [I] [SQL] SELECT `id`, `user_id`, `repo_id`, `mode` FROM `access` WHERE `user_id`=? AND `repo_id`=? LIMIT 1 []interface {}{1, 20}
2018/10/25 17:41:41 [I] [SQL] SELECT `id`, `user_id`, `repo_id`, `mode` FROM `access` WHERE `user_id`=? AND `repo_id`=? LIMIT 1 []interface {}{1, 26}
2018/10/25 17:41:41 [I] [SQL] SELECT `id`, `user_id`, `repo_id`, `mode` FROM `access` WHERE `user_id`=? AND `repo_id`=? LIMIT 1 []interface {}{1, 22}
2018/10/25 17:41:41 [I] [SQL] SELECT `id`, `user_id`, `repo_id`, `mode` FROM `access` WHERE `user_id`=? AND `repo_id`=? LIMIT 1 []interface {}{1, 39}
2018/10/25 17:41:41 [I] [SQL] SELECT `id`, `user_id`, `repo_id`, `mode` FROM `access` WHERE `user_id`=? AND `repo_id`=? LIMIT 1 []interface {}{1, 58}

and update to

select xxx from access where user_id=? and repo_id in(x,y,z)

of course, the ajax request is another better choice:-)

[lunny]

@qianlongzt @xxxTonixxx @scofieldpeng could you confirm that #4977 fix this?

[tonivj5]

I will try to build it and test it @lunny 👍

[tonivj5]

I can confirm #4977 improves a lot the performance 🎉🎉 . It removes all queries from attachments. In my tests my dashboard has gone from 600 queries to 200. And 800ms to 87ms 🎉🎉

However, as @scofieldpeng says, there are many improvments, like group the split sqls to one query (from tables like repo_unit (15 queries), user (30 queries), repository (15), ...)

Thank you very much for your work @lunny 🔥

[scofieldpeng]

i test it too, this problem has been solved now, i build a temp docker image for personal use, waiting for this pr to be merged into master branch, tks for your work @lunny :-)

[qianlongzt]

i test gitea master 1.6.0+rc1-174-g6db7dbd33 and pr_fix_dashboard 1.6.0+rc1-183-gb06009d47 , this problem has been solved now. thanks for your work @lunny.