Database queries on Unleash endpoints sometimes become slow
Problem
While we were investigating #365575 (closed), we noticed that the database queries on Unleash endpoints sometimes become slow. The affected endpoints so far:
GET /api/:version/feature_flags/unleash/:project_id/client/features
POST /api/:version/feature_flags/unleash/:project_id/client/metrics
- https://log.gprd.gitlab.net/goto/8a7bb250-542c-11ed-b0ec-930003e0679c
- https://log.gprd.gitlab.net/goto/8ee6ad90-542c-11ed-8d37-e9a2f393ea2a
We can also observe this in slow query log that these queries are the main offenders:
SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = $1 AND "projects"."hidden" = $2 AND "projects"."id" = $3 LIMIT $4
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = $1 AND "routes"."source_type" = $2 LIMIT $3
SELECT "operations_feature_flags_clients".* FROM "operations_feature_flags_clients" WHERE "operations_feature_flags_clients"."project_id" = $1 AND "operations_feature_flags_clients"."token_encrypted" IN ($2, $3) LIMIT $4
These queries are executed by authorize_by_unleash_instance_id!
. These queries have already been properly indexed, however, the occasional spike happens.
Proposal
- Investigate if this is correlated to
ACCESS EXCLUSIVE
explicit locking. For example,VACUUM FULL
might be related. - Reduce unnecessary queries. For example, we can drop two queries by the following change:
diff --git a/app/models/operations/feature_flags_client.rb b/app/models/operations/feature_flags_client.rb
index e8c237abbc5f..12e3f190bcfa 100644
--- a/app/models/operations/feature_flags_client.rb
+++ b/app/models/operations/feature_flags_client.rb
@@ -19,11 +19,11 @@ class FeatureFlagsClient < ApplicationRecord
before_validation :ensure_token!
- def self.find_for_project_and_token(project, token)
- return unless project
+ def self.find_for_project_id_and_token(project_id, token)
+ return unless project_id
return unless token
- where(project_id: project).find_by_token(token)
+ where(project_id: project_id).find_by_token(token)
end
def self.update_last_feature_flag_updated_at!(project)
diff --git a/lib/api/unleash.rb b/lib/api/unleash.rb
index 1fbd7cf5afc8..b7e4a765e32b 100644
--- a/lib/api/unleash.rb
+++ b/lib/api/unleash.rb
@@ -71,7 +71,7 @@ def project
def feature_flags_client
strong_memoize(:feature_flags_client) do
- client = Operations::FeatureFlagsClient.find_for_project_and_token(project, unleash_instance_id)
+ client = Operations::FeatureFlagsClient.find_for_project_id_and_token(params[:project_id], unleash_instance_id)
client.unleash_app_name = unleash_app_name if client
client
end
Additional thought
The where(project_id: project_id).find_by_token(token)
query seems to have duplicate values. e.g.
SELECT
"operations_feature_flags_clients".*
FROM
"operations_feature_flags_clients"
WHERE
"operations_feature_flags_clients"."project_id" = 20
AND "operations_feature_flags_clients"."token_encrypted" IN (
'wjzHlmZTenckTFqE+3IbF3rQZRfqnyatfPsncd6c7fgLmmkc',
'wjzHlmZTenckTFqE+3IbF3rQZRfqnyatfPsncd6c7fgLmmkc'
)
LIMIT
1
(Note: these token and project IDs were printed in a demonstration purpose that generated in GDK. Not functional in any environments.)
This is because https://gitlab.com/gitlab-org/gitlab/-/blob/master/app/models/concerns/token_authenticatable_strategies/encrypted.rb#L97-101, which is related to dynamic_nonce
feature flag. Query plan