User profile pages are timing out

• https://gitlab.com/u/DouweM
• https://gitlab.com/u/rspeicher

cc @jacobvosmaer

mentioned in issue #3530 (closed)

cc @yorickpeterse

This will be resolved by !1790 (merged), though I'm not sure if I can get this fixed before the 22nd.

@yorickpeterse we can not have 502 on profiles. Can you please check what code gives timeout and post it here. Maybe I or other developer can help providing a fix.

Its actually quite bad and kinda urgent. People may reference to their GitLab.com public profile from blogs, websites. Having constant 502 there for more than few days is not acceptable.

It looks like we have some bad SQL queries in 8.2.0.rc1. Load on the DB server tripled after the deploy.

SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" IN ('Group') AND (public IS TRUE OR id IN(<snip> tens of thousands of IDs</snip>) ORDER BY "namespaces"."id" DESC

Our best guess is that 6051c28f made the GroupsFinder very slow. The GroupsFinder is used in UsersController#show, i.e. the /u/username page.

cc @ayufan @vsizov

It really does look like !1790 (merged) will drastically improve both the newly slow GroupsFinder query and several others. I think we have two options:

• revert 6051c28f in RC2
• finish+merge !1790 (merged) in RC2

Both take time. We already have Yorick working on !1790 (merged), and it has a greater impact outside of this issue.

@dzaporozhets I can look into it, maybe we can rewrite this group_finder. Or maybe you can do it? I'm finishing emoji today

If @yorickpeterse can finish MR before RC2 -> then wait for it. Otherwise revert commit.

Do we really need this SQL. I left a comment - https://gitlab.com/gitlab-org/gitlab-ce/issues/3541#note_2692170

I think @vsizov should rework https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/1751 with fact that Groups are now public. It will affect performance of profile page too

Please wait with reworking GroupsFinder until !1790 (merged) has been merged, otherwise I have to basically start all over again at this point (since I'm still writing all my commit messages and such).

This morning @ayufan tried adding an index, which seemed not to help. @yorickpeterse just tried again and now it did help. I can load profile pages now (although they are still too slow).

@yorickpeterse As I see, you are done with !1790 (merged), so I can remove public column from namespace?

@yorickpeterse nm. You created issue for that.

This can be closed because merged.

Status changed to closed