Controller ProfilesController#update_username executes more than 100 SQL queries (7 day: 3k hits, Avg: 40 queries)
Problem to solve
ProfilesController#update_username
controller executes more than 100 SQL queries, the maximum number of
queries allowed as per MR https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/16466.
The number of queries should be reduced to less than 100 and the corresponding
whitelisted sections of code (Gitlab::QueryLimiting.disable('...')
) should
be removed.
Further details
The User
model has a validation to prevent the user from being renamed when the Container Registry of the user namespace has Docker tags.
def namespace_move_dir_allowed
if namespace&.any_project_has_container_registry_tags?
errors.add(:username, _('cannot be changed if a personal project has container registry tags.'))
end
end
The Namespace
simply delegates to its projects:
def any_project_has_container_registry_tags?
all_projects.any?(&:has_container_registry_tags?)
end
Under the hood Project#has_container_registry_tags?
queries the database to get all the ContainerRepositories
models that might have tags, so there's one DB query per project.
def has_container_registry_tags?
return @images if defined?(@images)
@images = container_repositories.to_a.any?(&:has_tags?) ||
has_root_container_repository_tags?
end
And since tags aren't stored in the database the Rails backend uses, the Container Registry client is used for every single container repository found in the DB.
def repository_tags(name)
response_body faraday.get("/v2/#{name}/tags/list")
end
Overall checking the tags of all the container repositories might take a significant amount of time.
Links
Call stack:
- https://gitlab.com/gitlab-org/gitlab/-/blob/v13.10.2-ee/app/controllers/profiles_controller.rb#L74
- https://gitlab.com/gitlab-org/gitlab/-/blob/v13.10.2-ee/app/models/user.rb#L220
- https://gitlab.com/gitlab-org/gitlab/-/blob/v13.10.2-ee/app/models/user.rb#L848
- https://gitlab.com/gitlab-org/gitlab/-/blob/v13.10.2-ee/app/models/namespace.rb#L194
- https://gitlab.com/gitlab-org/gitlab/-/blob/v13.10.2-ee/app/models/project.rb#L979
- https://gitlab.com/gitlab-org/gitlab/-/blob/v13.10.2-ee/app/models/container_repository.rb#L92
- https://gitlab.com/gitlab-org/gitlab/-/blob/v13.10.2-ee/app/models/container_repository.rb#L69
- https://gitlab.com/gitlab-org/gitlab/-/blob/v13.10.2-ee/lib/container_registry/client.rb#L72
Solution
Long term solution: Ideally we would store the tags of the container repositories in the Rails database. This could be done using notification web hooks but unfortunately they aren't reliable to keep the DB in sync. In the future, we might leverage the new Container Registry Metadata Database to achieve this. See discussion. With tags stored in the database, we can query all the tags of all the container repos of all projects of a namespace in a single SELECT query. TODO: create a follow-up issue with Architecture decision label.
Immediate solution: To limit the number of queries without storing tags in the DB (as it is right now), we should retrieve all the ContainerRepository
objects for all projects (1 sql query), and then query the tags for these repositories (1 API request per container repository). That said, this won't have a signifiant impact on the time it takes to perform model validation, and we should consider async validation.
def any_project_has_container_registry_tags?
all_projects.container_repositories.to_a.any?(&:has_tags?)
end
The case where has_root_container_repository_tags?
is called needs to be supported as well.
Implementation plan
-
update Namespace#any_project_has_container_registry_tags?
so that it queries allContainerRepository
objects of allprojects
, and check if there are any tags for these -
update specs to prove that there's only 1 DB query even if the namespace has more than one project