Skip to content

Primary key lookup query override experiment

Adam Hegyi requested to merge ah-another-sql-query-override-experiment into master

What does this MR do and why?

This MR attempts to replace primary key lookup SQL queries with a function-based lookup which aims to reduce the LWLOCK saturation on our DB. The previous attempt kinda failed because we didn't manage to "catch" all primary key lookups and doing this on the ActiveRecord level is quite challenging. See the related thread: !135196 (comment 1659969722)

This approach attempts to rewrite the database queries right before the execution. Since the logic needs to scan all database queries, performat execution is very important.

Algo:

  1. Cache a raw, standard primary key lookup query by splitting it into two parts:
    • Query: SELECT "users".* FROM "users" WHERE "users"."id" = 0 LIMIT 1, we split by 0
    • Part 1: SELECT "users".* FROM "users" WHERE "users"."id" =
    • Part 2: LIMIT 1
  2. When calling the rewriter for a query, check if the query starts with part 1 and ends with part 2.
  3. If no: return, do nothing.
  4. If yes: sub() the part 1 and part 2 substrings from the given query which should leave us the primary key value.
  5. Return a different SQL string where we use the function-based record lookup.

Related: gitlab-com/gl-infra&1129 (comment 1611749436)

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Adam Hegyi

Merge request reports