Skip to content

Reduce SQL requests number for CreateCommitSignatureWorker

Igor Drozdov requested to merge id-reduce-number-of-sql-requests-for-create-commit-signature-worker into master

What does this MR do?

Reduce SQL requests number for CreateCommitSignatureWorker. It correctly uses BatchLoader in order to eliminate N + 1

Database:

Without these changes, the worker performs N commits for fetching the signatures:

  GpgSignature Load (4.9ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\xa93893a7669ec2fb3715fd46d338165484e2d505'
  GpgSignature Load (0.3ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\x7ba2ba8d4f1c0e6171d47381e37d4dce0b1c912c'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\xd5547156c3260e807453634bb5ea3edbbea0fef8'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\x4fa1b76a81ac1e662f33985a9f64b69cc6ccacab'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\xbcccc30c274e0e92a78114887a6467f0afa16c8e'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\xf0a6e62da76c53a0853bcd0c3a6e0c9c384f5dea'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\xbee2c423858c0763cce91bcacf345eb0b45227a6'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\xede41ee451dd0aa6d0ecd958c7fadbdb3b63f3e4'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\xff46222a3a75467d6b22c94abb09a3bb6be60623'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\x83d11f4deeb20b852a0af3433190a0f7250a0027'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\x56e96d0f252d7762ae0e8c59b47c716ff39e7c9f'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\x8cf3f4c7c66580e234d3c2de9c23669cd49b7b5a'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\xdb43f331599753e627d9982b3aad2468f78c5b68'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\x9382efa37cb8891acb862a1c0a9d2291e937f9af'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\xe14cc772480d51abf647daf61139929a73ddc72c'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\x18096b5440e0818605add32538751c6be38e8343'
  GpgSignature Load (0.3ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\x8db304b4919519bca60a5b18ffe9b88dcde845af'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\x9aa55479626bd06cf041d3c474fdbdb748857d26'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\xa4df4b1f430cd107594d9790cad3ab3167e081ba'
  GpgSignature Load (0.2ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" = '\xc298f4c530a6784d25bb595880c713b0972a1ccf'

With the changes

  GpgSignature Load (0.9ms)  SELECT "gpg_signatures".* FROM "gpg_signatures" WHERE "gpg_signatures"."commit_sha" IN ('\xa93893a7669ec2fb3715fd46d338165484e2d505', '\x7ba2ba8d4f1c0e6171d47381e37d4dce0b1c912c', '\xd5547156c3260e807453634bb5ea3edbbea0fef8', '\x4fa1b76a81ac1e662f33985a9f64b69cc6ccacab', '\xbcccc30c274e0e92a78114887a6467f0afa16c8e', '\xf0a6e62da76c53a0853bcd0c3a6e0c9c384f5dea', '\xbee2c423858c0763cce91bcacf345eb0b45227a6', '\xede41ee451dd0aa6d0ecd958c7fadbdb3b63f3e4', '\xff46222a3a75467d6b22c94abb09a3bb6be60623', '\x83d11f4deeb20b852a0af3433190a0f7250a0027', '\x56e96d0f252d7762ae0e8c59b47c716ff39e7c9f', '\x8cf3f4c7c66580e234d3c2de9c23669cd49b7b5a', '\xdb43f331599753e627d9982b3aad2468f78c5b68', '\x9382efa37cb8891acb862a1c0a9d2291e937f9af', '\xe14cc772480d51abf647daf61139929a73ddc72c', '\x18096b5440e0818605add32538751c6be38e8343', '\x8db304b4919519bca60a5b18ffe9b88dcde845af', '\x9aa55479626bd06cf041d3c474fdbdb748857d26', '\xa4df4b1f430cd107594d9790cad3ab3167e081ba', '\xc298f4c530a6784d25bb595880c713b0972a1ccf', '\xba9b7c0e8acd7b3acb03086a5c1e132256fbd36e', '\x542b26139243c8fbed2af669d081f10c4ebbae40', '\x1a3e687f772047988b9bcaeb471d94d6f49a853e', '\xa5991377f2b58e6c2cb3bb58ed6a2faa6b135c77', '\xcf37fb39d4005f0cf31cc822425f7f6b9761cc57', '\x2e9e96686d34402bd67a1de6a0f244c4f47bb25c', '\xf1913fc949f5b15f89a34e12e7b388bba8bdee2c', '\xf62e3e7cf424f407358ae2525472aae840dd9066', '\x9237ac094a060dbb31c1ee4d37ad7ef38e17e878', '\x041538a548842d177818f938cc2817e5a45ae06f')

Related issue: gitlab-com/gl-infra/scalability#259 (closed)

Edited by Igor Drozdov

Merge request reports