Primary key lookup query override experiment
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:
- 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 by0
- Part 1:
SELECT "users".* FROM "users" WHERE "users"."id" =
- Part 2:
LIMIT 1
- Query:
- When calling the rewriter for a query, check if the query starts with part 1 and ends with part 2.
- If no: return, do nothing.
- If yes:
sub()
the part 1 and part 2 substrings from the given query which should leave us the primary key value. - 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.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Adam Hegyi