Markdown rendering in search results triggering significant database queries
Problem to Solve
Occasionally a result page can load very slowly. One reason this was happening is that markdown rendered as part of the result page can cause for hundreds of additional queries to load the page.
After optimizing the Markdown rendering and removing those extra queries we have cut the page load time in half.
Example
In the following search https://gitlab.com/search?search=auto+devops+mobile&group_id=9970&project_id=278964&scope=issues you can see that there are a total of 299
PG queries. The total time for Postgres is larger than Elasticsearch.
If you look at the queries you can see that many of them are done in the markdown rendering. If we want to render markdown the database queries are necessary for things like @
mentions and issue/MR links.
Solution
I can imagine a few possible options (in order least to most difficult):
- Simply remove the markdown rendering if we're not sure it's valuable
- Investigate the markdown rendering and confirm we're only rendering the markdown for the first part of the description. If we are rendering the entire description then truncating it in the view it's possible we're loading way more than necessary.
- Consider skipping parts of the markdown rendering that require DB queries. If we only care about the HTML formatting parts then we don't need to do the DB queries to lookup
@
mentions or issue/MR references. I'm not sure if this is easy to do with our markdown renderer.
Further details
It seems almost all the parser DB queries are generated in:
"lib/banzai/reference_parser/base_parser.rb:178:in `collection_objects_for_ids'",
"lib/banzai/reference_parser/base_parser.rb:140:in `grouped_objects_for_nodes'",
"lib/banzai/reference_parser/issue_parser.rb:60:in `records_for_nodes'",
"lib/banzai/reference_parser/issue_parser.rb:9:in `nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:113:in `block in nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:110:in `each'",
"lib/banzai/reference_redactor.rb:110:in `nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:37:in `redact_document_nodes'",
"lib/banzai/reference_redactor.rb:29:in `redact'",
"lib/banzai/filter/reference_redactor_filter.rb:15:in `call'",
"lib/banzai/pipeline/base_pipeline.rb:23:in `block (2 levels) in singleton class'",
"lib/banzai/renderer.rb:156:in `post_process'",
"lib/banzai.rb:32:in `post_process'",
"app/helpers/markup_helper.rb:307:in `prepare_for_rendering'",
"app/helpers/markup_helper.rb:285:in `render_markdown_field'",
"app/helpers/markup_helper.rb:116:in `markdown_field'",
"app/helpers/search_helper.rb:253:in `search_md_sanitize'",
"app/views/search/results/_issue.html.haml:11",
"app/views/search/_results.html.haml:35",
"app/views/search/show.html.haml:14",
Though I did also notice some permissions queries generated when rendering markdown in:
"lib/banzai/reference_parser/user_parser.rb:57:in `can_read_group_reference?'",
Some sample queries
Members query: SELECT "members".* FROM "members" WHERE "members"."source_type" = 'Namespace' AND "members"."source_type" = 'Namespace' AND "members"."type" = 'GroupMember' AND "members"."requested_at" IS NULL AND "members"."source_id" = 9970
"lib/banzai/reference_parser/base_parser.rb:178:in `collection_objects_for_ids'",
"lib/banzai/reference_parser/base_parser.rb:140:in `grouped_objects_for_nodes'",
"lib/banzai/reference_parser/issue_parser.rb:60:in `records_for_nodes'",
"lib/banzai/reference_parser/issue_parser.rb:9:in `nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:113:in `block in nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:110:in `each'",
"lib/banzai/reference_redactor.rb:110:in `nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:37:in `redact_document_nodes'",
"lib/banzai/reference_redactor.rb:29:in `redact'",
"lib/banzai/filter/reference_redactor_filter.rb:15:in `call'",
"lib/banzai/pipeline/base_pipeline.rb:23:in `block (2 levels) in singleton class'",
"lib/banzai/renderer.rb:156:in `post_process'",
"lib/banzai.rb:32:in `post_process'",
"app/helpers/markup_helper.rb:307:in `prepare_for_rendering'",
"app/helpers/markup_helper.rb:285:in `render_markdown_field'",
"app/helpers/markup_helper.rb:116:in `markdown_field'",
"app/helpers/search_helper.rb:253:in `search_md_sanitize'",
"app/views/search/results/_issue.html.haml:11",
"app/views/search/_results.html.haml:35",
"app/views/search/show.html.haml:14",
Issues query: SELECT "issues".* FROM "issues" WHERE "issues"."id" IN (25573290, ...)
"lib/banzai/reference_parser/base_parser.rb:178:in `collection_objects_for_ids'",
"lib/banzai/reference_parser/base_parser.rb:140:in `grouped_objects_for_nodes'",
"lib/banzai/reference_parser/issue_parser.rb:60:in `records_for_nodes'",
"lib/banzai/reference_parser/issue_parser.rb:9:in `nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:113:in `block in nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:110:in `each'",
"lib/banzai/reference_redactor.rb:110:in `nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:37:in `redact_document_nodes'",
"lib/banzai/reference_redactor.rb:29:in `redact'",
"lib/banzai/filter/reference_redactor_filter.rb:15:in `call'",
"lib/banzai/pipeline/base_pipeline.rb:23:in `block (2 levels) in singleton class'",
"lib/banzai/renderer.rb:156:in `post_process'",
"lib/banzai.rb:32:in `post_process'",
"app/helpers/markup_helper.rb:307:in `prepare_for_rendering'",
"app/helpers/markup_helper.rb:285:in `render_markdown_field'",
"app/helpers/markup_helper.rb:116:in `markdown_field'",
"app/helpers/search_helper.rb:253:in `search_md_sanitize'",
"app/views/search/results/_issue.html.haml:11",
"app/views/search/_results.html.haml:35",
"app/views/search/show.html.haml:14",
Issue Assignees query: SELECT "issue_assignees".* FROM "issue_assignees" WHERE "issue_assignees"."issue_id" IN (19059701, ...)
"lib/banzai/reference_parser/base_parser.rb:178:in `collection_objects_for_ids'",
"lib/banzai/reference_parser/base_parser.rb:140:in `grouped_objects_for_nodes'",
"lib/banzai/reference_parser/issue_parser.rb:60:in `records_for_nodes'",
"lib/banzai/reference_parser/issue_parser.rb:9:in `nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:113:in `block in nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:110:in `each'",
"lib/banzai/reference_redactor.rb:110:in `nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:37:in `redact_document_nodes'",
"lib/banzai/reference_redactor.rb:29:in `redact'",
"lib/banzai/filter/reference_redactor_filter.rb:15:in `call'",
"lib/banzai/pipeline/base_pipeline.rb:23:in `block (2 levels) in singleton class'",
"lib/banzai/renderer.rb:156:in `post_process'",
"lib/banzai.rb:32:in `post_process'",
"app/helpers/markup_helper.rb:307:in `prepare_for_rendering'",
"app/helpers/markup_helper.rb:285:in `render_markdown_field'",
"app/helpers/markup_helper.rb:116:in `markdown_field'",
"app/helpers/search_helper.rb:253:in `search_md_sanitize'",
"app/views/search/results/_issue.html.haml:11",
"app/views/search/_results.html.haml:35",
"app/views/search/show.html.haml:14",
Users query: SELECT "users".* FROM "users" WHERE "users"."id" IN (1212235, ...
"lib/banzai/reference_parser/base_parser.rb:178:in `collection_objects_for_ids'",
"lib/banzai/reference_parser/base_parser.rb:140:in `grouped_objects_for_nodes'",
"lib/banzai/reference_parser/issue_parser.rb:60:in `records_for_nodes'",
"lib/banzai/reference_parser/issue_parser.rb:9:in `nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:113:in `block in nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:110:in `each'",
"lib/banzai/reference_redactor.rb:110:in `nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:37:in `redact_document_nodes'",
"lib/banzai/reference_redactor.rb:29:in `redact'",
"lib/banzai/filter/reference_redactor_filter.rb:15:in `call'",
"lib/banzai/pipeline/base_pipeline.rb:23:in `block (2 levels) in singleton class'",
"lib/banzai/renderer.rb:156:in `post_process'",
"lib/banzai.rb:32:in `post_process'",
"app/helpers/markup_helper.rb:307:in `prepare_for_rendering'",
"app/helpers/markup_helper.rb:285:in `render_markdown_field'",
"app/helpers/markup_helper.rb:116:in `markdown_field'",
"app/helpers/search_helper.rb:253:in `search_md_sanitize'",
"app/views/search/results/_issue.html.haml:11",
"app/views/search/_results.html.haml:35",
"app/views/search/show.html.haml:14",
Project links query: SELECT "project_group_links"."id" AS t0_r0, "project_group_links"."project_id" AS t0_r1, "project_group_links"."group_id" AS t0_r2, "project_group_links"."created_at" AS t0_r3, "project_group_links"."updated_at" AS t0_r4, "project_group_links"."group_access" AS t0_r5 ...
"lib/banzai/reference_parser/base_parser.rb:178:in `collection_objects_for_ids'",
"lib/banzai/reference_parser/base_parser.rb:140:in `grouped_objects_for_nodes'",
"lib/banzai/reference_parser/issue_parser.rb:60:in `records_for_nodes'",
"lib/banzai/reference_parser/issue_parser.rb:9:in `nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:113:in `block in nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:110:in `each'",
"lib/banzai/reference_redactor.rb:110:in `nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:37:in `redact_document_nodes'",
"lib/banzai/reference_redactor.rb:29:in `redact'",
"lib/banzai/filter/reference_redactor_filter.rb:15:in `call'",
"lib/banzai/pipeline/base_pipeline.rb:23:in `block (2 levels) in singleton class'",
"lib/banzai/renderer.rb:156:in `post_process'",
"lib/banzai.rb:32:in `post_process'",
"app/helpers/markup_helper.rb:307:in `prepare_for_rendering'",
"app/helpers/markup_helper.rb:285:in `render_markdown_field'",
"app/helpers/markup_helper.rb:116:in `markdown_field'",
"app/helpers/search_helper.rb:253:in `search_md_sanitize'",
"app/views/search/results/_issue.html.haml:11",
"app/views/search/_results.html.haml:35",
"app/views/search/show.html.haml:14",
Members permissions CTE query: SELECT "members".* FROM ((SELECT "members".* FROM "members" LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "users"."state" = 'active' AND "members"."requested_at" IS NULL AND "members"."source_id" IN (WITH RECURSIVE "base_and_ancestors" ...
[
"app/models/group.rb:411:in `max_member_access_for_user'",
"app/policies/group_policy.rb:174:in `lookup_access_level!'",
"ee/app/policies/ee/group_policy.rb:290:in `lookup_access_level!'",
"app/policies/group_policy.rb:170:in `access_level'",
"app/policies/group_policy.rb:15:in `block in <class:GroupPolicy>'",
"lib/declarative_policy/condition.rb:23:in `instance_eval'",
"lib/declarative_policy/condition.rb:23:in `compute'",
"lib/declarative_policy/condition.rb:44:in `block in pass?'",
"lib/declarative_policy/base.rb:303:in `cache'",
"lib/declarative_policy/condition.rb:44:in `pass?'",
"lib/declarative_policy/rule.rb:81:in `pass?'",
"lib/declarative_policy/step.rb:81:in `pass?'",
"lib/declarative_policy/runner.rb:92:in `block in run'",
"lib/declarative_policy/runner.rb:180:in `block in steps_by_score'",
"lib/declarative_policy/runner.rb:149:in `loop'",
"lib/declarative_policy/runner.rb:149:in `steps_by_score'",
"lib/declarative_policy/runner.rb:82:in `run'",
"lib/declarative_policy/runner.rb:60:in `pass?'",
"lib/declarative_policy/base.rb:234:in `block in allowed?'",
"lib/declarative_policy/base.rb:234:in `all?'",
"lib/declarative_policy/base.rb:234:in `allowed?'",
"lib/declarative_policy/base.rb:226:in `can?'",
"app/models/ability.rb:72:in `allowed?'",
"lib/banzai/reference_parser/base_parser.rb:227:in `can?'",
"lib/banzai/reference_parser/user_parser.rb:57:in `can_read_group_reference?'",
"lib/banzai/reference_parser/user_parser.rb:35:in `block in nodes_visible_to_user'",
"lib/banzai/reference_parser/user_parser.rb:33:in `each'",
"lib/banzai/reference_parser/user_parser.rb:33:in `nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:113:in `block in nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:110:in `each'",
"lib/banzai/reference_redactor.rb:110:in `nodes_visible_to_user'",
"lib/banzai/reference_redactor.rb:37:in `redact_document_nodes'",
"lib/banzai/reference_redactor.rb:29:in `redact'",
"lib/banzai/filter/reference_redactor_filter.rb:15:in `call'",
"lib/banzai/pipeline/base_pipeline.rb:23:in `block (2 levels) in singleton class'",
"lib/banzai/renderer.rb:156:in `post_process'",
"lib/banzai.rb:32:in `post_process'",
"app/helpers/markup_helper.rb:307:in `prepare_for_rendering'",
"app/helpers/markup_helper.rb:285:in `render_markdown_field'",
"app/helpers/markup_helper.rb:116:in `markdown_field'",
"app/helpers/search_helper.rb:253:in `search_md_sanitize'",
"app/views/search/results/_issue.html.haml:11",
"app/views/search/_results.html.haml:35",
"app/views/search/show.html.haml:14",