Create participants table to store discussion participants here instead of parsing all issue comments every time
Dev: https://dev.gitlab.org/gitlab/gitlabhq/issues/2444
Because issue.participants
cause huge amount of calculations and SQL queries affecting GitLab performance on a lot of pages
Benchmark for issue with 3 normal and 10 system comments:
puts Benchmark.measure { target.participants(note.author) }
Project Load (1.1ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (1.1ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 21]]
User Load (1.1ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
Note Load (1.4ms) SELECT "notes".* FROM "notes" WHERE "notes"."noteable_id" = $1 AND "notes"."noteable_type" = $2 [["noteable_id", 81], ["noteable_type", "Issue"]]
User Load (1.3ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (1.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (1.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 21]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
ProjectMember Load (1.1ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = 'Project' AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = $1 AND "members"."source_type" = $2 AND "members"."user_id" = 21 ORDER BY "members"."created_at" DESC, "members"."id" DESC LIMIT 1 [["source_id", 2], ["source_type", "Project"]]
Group Load (1.0ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" IN ('Group') AND "namespaces"."id" = $1 AND "namespaces"."type" = 'Group' ORDER BY "namespaces"."created_at" DESC, "namespaces"."id" DESC LIMIT 1 [["id", 3]]
GroupMember Load (1.1ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = 'Namespace' AND "members"."type" IN ('GroupMember') AND "members"."source_id" = $1 AND "members"."source_type" = $2 AND "members"."user_id" = 21 ORDER BY "members"."created_at" DESC, "members"."id" DESC LIMIT 1 [["source_id", 3], ["source_type", "Namespace"]]
CACHE (0.1ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = 'Project' AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = $1 AND "members"."source_type" = $2 AND "members"."user_id" = 21 ORDER BY "members"."created_at" DESC, "members"."id" DESC LIMIT 1 [["source_id", 2], ["source_type", "Project"]]
CACHE (0.0ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = 'Namespace' AND "members"."type" IN ('GroupMember') AND "members"."source_id" = $1 AND "members"."source_type" = $2 AND "members"."user_id" = 21 ORDER BY "members"."created_at" DESC, "members"."id" DESC LIMIT 1 [["source_id", 3], ["source_type", "Namespace"]]
CACHE (0.0ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = 'Project' AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = $1 AND "members"."source_type" = $2 AND "members"."user_id" = 21 ORDER BY "members"."created_at" DESC, "members"."id" DESC LIMIT 1 [["source_id", 2], ["source_type", "Project"]]
CACHE (0.0ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = 'Namespace' AND "members"."type" IN ('GroupMember') AND "members"."source_id" = $1 AND "members"."source_type" = $2 AND "members"."user_id" = 21 ORDER BY "members"."created_at" DESC, "members"."id" DESC LIMIT 1 [["source_id", 3], ["source_type", "Namespace"]]
CACHE (0.0ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = 'Project' AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = $1 AND "members"."source_type" = $2 AND "members"."user_id" = 21 ORDER BY "members"."created_at" DESC, "members"."id" DESC LIMIT 1 [["source_id", 2], ["source_type", "Project"]]
CACHE (0.0ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = 'Namespace' AND "members"."type" IN ('GroupMember') AND "members"."source_id" = $1 AND "members"."source_type" = $2 AND "members"."user_id" = 21 ORDER BY "members"."created_at" DESC, "members"."id" DESC LIMIT 1 [["source_id", 3], ["source_type", "Namespace"]]
GroupMember Load (1.5ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = 'Namespace' AND "members"."type" IN ('GroupMember') AND "members"."source_id" = $1 AND "members"."source_type" = $2 AND "members"."access_level" = 50 ORDER BY "members"."created_at" DESC, "members"."id" DESC [["source_id", 3], ["source_type", "Namespace"]]
User Load (1.5ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
User Load (3.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 21]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (1.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
Namespace Load (1.5ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."path" = 'root' ORDER BY "namespaces"."created_at" DESC, "namespaces"."id" DESC LIMIT 1
CACHE (0.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
User Load (1.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (1.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (1.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (1.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (1.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (0.9ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (0.9ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (1.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (1.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (1.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (1.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (1.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (1.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
User Load (1.1ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
Namespace Load (1.5ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."path" = 'simeon' ORDER BY "namespaces"."created_at" DESC, "namespaces"."id" DESC LIMIT 1
User Load (1.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 15]]
ProjectMember Load (1.1ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = 'Project' AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = $1 AND "members"."source_type" = $2 AND "members"."user_id" = 15 ORDER BY "members"."created_at" DESC, "members"."id" DESC LIMIT 1 [["source_id", 2], ["source_type", "Project"]]
GroupMember Load (1.1ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = 'Namespace' AND "members"."type" IN ('GroupMember') AND "members"."source_id" = $1 AND "members"."source_type" = $2 AND "members"."user_id" = 15 ORDER BY "members"."created_at" DESC, "members"."id" DESC LIMIT 1 [["source_id", 3], ["source_type", "Namespace"]]
User Load (1.1ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."created_at" DESC, "users"."id" DESC LIMIT 1 [["id", 1]]
CACHE (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT 1 [["id", 2]]
0.130000 0.000000 0.130000 ( 0.134427)
=> nil
Sytse
👍
Douwe
Note that not all users can see the same groups, it depends on the user's access to the group's projects, so the participants that will be visible to a user need to differ. Otherwise someone could find the existence of something like the @gitlabwatch group by watching if writing that adds participants to the issue.
Dmitriy
Can we make sure it has a
needs_response
flag that is set when there is a mention without a response from you? That would be super useful to show on people's dashboard.
sytse you propose to mix performance improvements with new feature. I am against this. We can extend it with proposed functionality in future but right now I am focused on replacing tons of unnecessary SQL queries.
I agree we don't need the feature now, just wanted to make sure you took the feature into account when designing your solution. Good to hear we can extend it with this functionality.
WIP merge request - https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/895
job this is much GitLab refactoring issue. Extracting
Participants
into model. Should be done by someone who knows GitLab quite well.
Douwe
Partly addressed in https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/1602.
Dmitriy
yorickpeterse do you think it worth doing?
Yorick
dzaporozhets Usually I'd say yes as the list of participants doesn't change that often it's a waste to re-generate it on every request. However, because douwe & friends are working on refactoring rendering of notes and caching things I'd probably go with that. Their setup doesn't really require any database changes and chances are it will perform similar.
Dmitriy
Cool. I leave destiny of this issue up to douwe