Caching subscription data
In describing the "read-mostly pattern" in gitlab-com/www-gitlab-com!78560 (merged), noticed that the information from the gitlab_subscriptions
table may also be a good candidate for this pattern.
I haven't yet looked into this more, so this needs more investigation. The question is if we can cache a good part of the subscription data better - seeing that we very rarely write and quite often read this.
Designs
- Show closed items
Activity
-
Newest first Oldest first
-
Show all activity Show comments only Show history only
- Andreas Brandl added databasetriage groupdatabase labels
added databasetriage groupdatabase labels
- Andreas Brandl mentioned in merge request gitlab-com/www-gitlab-com!78560 (merged)
mentioned in merge request gitlab-com/www-gitlab-com!78560 (merged)
- Andreas Brandl mentioned in issue #326037 (closed)
mentioned in issue #326037 (closed)
- Maintainer
Setting label(s) Category:Database devopsenablement sectionenablement based on groupdatabase.
- 🤖 GitLab Bot 🤖 added Category:Database devopssystems sectioncore platform labels
added Category:Database devopssystems sectioncore platform labels
- 🤖 GitLab Bot 🤖 added Engineering Allocation label
added Engineering Allocation label
- Author Developer
- Author Developer
Looking at this
by (fqdn)
, we can see that the primary gets a good part of the reads (more than a single replica):
Looking at
pg_stat_statements
on the primary, this query stands out in terms of number of call/time used:/*application:sidekiq,correlation_id:01F4SRFK2PKD1VBP9HA6FFVN66,jid:c5987e1925f77b93637e3a97,endpoint_id:PipelineHooksWorker*/ SELECT DISTINCT "plans".* FROM "plans" INNER JOIN "gitlab_subscriptions" ON "gitlab_subscriptions"."hosted_plan_id" = "plans"."id" WHERE "plans"."name" IN ($1, $2, $3, $4, $5, $6, $7) AND "gitlab_subscriptions"."namespace_id" IN ($8, $9, $10)
This query shows up coming from different endpoints, so not only the above shown
PipelineHooksWorker
.I suspect this comes from
EE::Plan.hosted_plans_for_namespaces
which may be a good starting point to cache at.FWIW, there's also a join on the table in context of pull mirroring, which may contribute to the query frequency (I don't know how much though).
- Author Developer
The best guess I have in terms of ownership is grouplicense , so going to re-label accordingly.
@jameslopez I don't know this is particularly pressing, but perhaps something worth looking at and a rather low hanging
. Can this stay with the license group? Thanks! 1 Collapse replies - Maintainer
Thanks for creating this! I think this can be either License or Purchase
FYI @teresatison 1 - Contributor
@tgolubeva, do you have a performance related issues that exploring this could help with? Please weigh in. I'm kewl to schedule this within the license group as well.
- Developer
I'm always a fan of performance optimization! I'm not sure if we have a need for this yet, but perhaps @chris_baus can weigh in?
@tgolubeva @abrandl I'm not sure what is meant by "cache" here. Does this mean in a memory cache like Redis?
- Author Developer
@chris_baus Yes, we have different levels of caching available - Redis or process-based. There's some pending writing on that subject in gitlab-com/www-gitlab-com!78560 (merged).
This issue is about checking if there is a low hanging fruit to avoid loading subscription data from the database all the time. We've been looking for patterns like this ("read mostly") and subscription data is something we've identified to fit. There is no clear indicator that this is causing trouble on .com - but worth to check and if this is easily possible it would be good to remove that unnecessary database traffic. If we find this is not a low hanging fruit, I would recommend to put this on the backlog.
@tgolubeva I'm ok with taking a deeper look at this issue, maybe in %14.1?
- Developer
@chris_baus adding to %14.1 and assigning workflowsolution validation label.
1 @tgolubeva I think we need to punt this one again to %14.2 given other priorities.
- Andreas Brandl added bugperformance gitlab.com groupprovision labels and removed Engineering Allocation databasetriage groupdatabase labels
added bugperformance gitlab.com groupprovision labels and removed Engineering Allocation databasetriage groupdatabase labels
- Maintainer
Setting label(s) ~"Category:License" based on grouplicense.
- 🤖 GitLab Bot 🤖 added 1 deleted label
added 1 deleted label
- Tatyana Golubeva changed milestone to %14.1
changed milestone to %14.1
- Tatyana Golubeva added Category:Purchase backend grouppurchase [DEPRECATED] workflowsolution validation labels and removed groupprovision label
added Category:Purchase backend grouppurchase [DEPRECATED] workflowsolution validation labels and removed groupprovision label
- 🤖 GitLab Bot 🤖 added [deprecated] Accepting merge requests label
added [deprecated] Accepting merge requests label
- Tatyana Golubeva changed milestone to %14.3
changed milestone to %14.3
- Tatyana Golubeva changed milestone to %Next 1-3 releases
changed milestone to %Next 1-3 releases
- Tatyana Golubeva removed sectioncore platform label
removed sectioncore platform label
- Tatyana Golubeva added devopsfulfillment sectionfulfillment labels and removed devopssystems label
added devopsfulfillment sectionfulfillment labels and removed devopssystems label
- Tatyana Golubeva added groupfulfillment platform workflowvalidation backlog + 1 deleted label and removed grouppurchase [DEPRECATED] workflowsolution validation labels
added groupfulfillment platform workflowvalidation backlog + 1 deleted label and removed grouppurchase [DEPRECATED] workflowsolution validation labels
- Tatyana Golubeva removed 1 deleted label
removed 1 deleted label
- Tatyana Golubeva removed Category:Database label
removed Category:Database label
- Tatyana Golubeva removed Category:Purchase label
removed Category:Purchase label
- Tatyana Golubeva added to epic &7725 (closed)
added to epic &7725 (closed)
- Tatyana Golubeva changed epic to &7727 (closed)
changed epic to &7727 (closed)
- Tatyana Golubeva added typefeature label
added typefeature label
- 🤖 GitLab Bot 🤖 removed bugperformance label
removed bugperformance label
- Tatyana Golubeva removed from epic &7727 (closed)
removed from epic &7727 (closed)