Reduce amount of SQL queries triggered by Nuget package's metadata
What does this MR do and why?
During an investigation of red error budget it was noticed that Nuget package's metadata endpoint GET /api/:version/groups/:id/-/packages/nuget/metadata/*package_name/index
sometimes tiggers too many SQL queries 2000+
kibana (internal)
After an investigation a root cause of the issue was identified.
A good thing is that with this change we will improve both: group level and project level endpoints.
📸 Screenshots or screen recordings with explanation
It triggers two SQL queries to load Packages::Package
:
It triggers a SQL query to count Packages::Package
:
It triggers N+1 queries for Packages::Package
and associations, although some of them are grouped:
It triggers the following SQL queries:
- SELECT
Packages::Package
- SELECT
Packages::PackageFile
- SELECT
Packages::Nuget::Metadatum
- SELECT
Packages::Tag
- SELECT
Packages::DependencyLink
- SELECT
Packages::Dependency
- SELECT
Packages::Nuget::DependencyLinkMetadatum
🔬 How to set up and validate locally
-
First we need to prepare some data in rails console:
def fixture_file_upload(*args, **kwargs) Rack::Test::UploadedFile.new(*args, **kwargs) end # Create packages with package's files package1 = FactoryBot.create(:nuget_package, project: Project.first) package2 = FactoryBot.create(:nuget_package, project: Project.first, name: package1.name) # Create package's tags [package1, package2].each do |pkg| ['tag1', 'tag2'].each { |tag| FactoryBot.create(:packages_tag, package: pkg, name: tag) } end # Create package's dependencies and package's dependencies links with NuGet metadatum [package1, package2].each do |pkg| dependency1 = Packages::Dependency.find_by(name: 'Newtonsoft.Json', version_pattern: '12.0.3') || FactoryBot.create(:packages_dependency, name: 'Newtonsoft.Json', version_pattern: '12.0.3') dependency2 = Packages::Dependency.find_by(name: 'Castle.Core', version_pattern: '4.4.1') || FactoryBot.create(:packages_dependency, name: 'Castle.Core', version_pattern: '4.4.1') FactoryBot.create(:packages_dependency_link, :with_nuget_metadatum, package: pkg, dependency: dependency1) FactoryBot.create(:packages_dependency_link, package: pkg, dependency: dependency2) end # Grab package's name and group's id to use later in the API request package1.name Project.first.group.id
-
Trigger API request
curl --user <user>:<token> "http://gdk.test:3000/api/v4/groups/<group_id>/-/packages/nuget/metadata/<package_name>/index"
-
Verify SQL queries from
app/presenters/packages/nuget/packages_metadata_presenter.rb
in thedevelopment.log
It triggers a separate SQL query for every package's file and package's metadatum.
It triggers only one SQL query per data model.
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.
Related to #386051 (closed)