Slow group pages and group merge request pages.
On an installation of GitLab with MySQL it is taking a long time when viewing a group page, or the issues and MR's for a group, and I can't see anything obvious that would be causing it. The group in question has 1 project in it with no issues or MR's. It isn't just this group that it happens on but all groups I've looked at. Even a page refresh takes the same amount of time so it appears that the views aren't being cached at all either.
I've copied the db contents onto a test server and see the same behaviour there as well. I've also upgraded the test server to 9.2 and configured InfluxDB and Prometheus if they can provide anything useful.
If I add profiling to the requests it will occasionally timeout the request but here are some screenshots of the typical timings I'm seeing:
Group Page Time to complete (https://host/groups/groupname):
Group Page Main details:
Group Page Merge requests (https://host/groups/groupname/merge_requests):
Group Page MR's details:
I did wonder if it was because of a large number of projects on the server, 29170, but I built up another test environment on which I created 1200 groups containing 30 projects each and group pages on that machine load fine. This leads me to think it must be something else in the db causing it but I'm not sure where it could be at the moment. For reference here are the stats we have across all projects:
Projects: 29170
Users: 10,581
Groups: 1,786
Issues: 8,811
Merge Requests: 114,498
Notes: 490,483
Jobs: 159,595
The group page will be looking for the CI status of the projects but I'm not sure what else gets pulled in to build the page view and I'm running out of ideas at the moment.
All checks on the server are OK so it shouldn't be a configuration issue. On the test machine I'm the only person using the hardware so there shouldn't be any contention issues.
DB schema appears OK and all migrations have run fine. The DB is one that has been around for a couple of years, starting with 7.something then last year moving through a number of 8.x releases, then to 9.1 last weekend. It has been happening for a few months, or I've noticed it, but haven't had the time to properly investigate it until now.
Let me know if there are any other things I can provide / look into. Being a test server I'm thinking that I can probably truncate some of the tables to see at what point things start to behave normally, I just need to know which tables would be best to clear out first.
Thanks.