Skip to content

GitLab Next

  • Projects
  • Groups
  • Snippets
  • Help
    • Loading...
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Sign in / Register
GitLab FOSS
GitLab FOSS
  • Project overview
    • Project overview
    • Details
    • Activity
    • Releases
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributors
    • Graph
    • Compare
    • Locked Files
  • Issues 0
    • Issues 0
    • List
    • Boards
    • Labels
    • Service Desk
    • Milestones
    • Iterations
  • Merge Requests 0
    • Merge Requests 0
  • Requirements
    • Requirements
    • List
  • Security & Compliance
    • Security & Compliance
    • Dependency List
    • License Compliance
  • Operations
    • Operations
    • Incidents
    • Environments
  • Packages & Registries
    • Packages & Registries
    • Container Registry
  • Analytics
    • Analytics
    • Code Review
    • Insights
    • Issue
    • Repository
    • Value Stream
  • Snippets
    • Snippets
  • Members
    • Members
  • Collapse sidebar
  • Activity
  • Graph
  • Create a new issue
  • Commits
  • Issue Boards
  • GitLab.org
  • GitLab FOSSGitLab FOSS
  • Issues
  • #62488

Closed (moved)
Open
Opened May 29, 2019 by yun guo@yguoContributor

Long running transactions in postgresql database

We get long running transactions in gitlab.com from 5/23. gitlab-com/gl-infra/production#854 (closed)

The transaction may be executed for over 30mins. It contains many queries where each query are relative fast. Below is a snapshot of such transaction and the query

attr value
txn_age 00:05:38.057067
pid 28168
query UPDATE "project_statistics" SET build_artifacts_size = COALESCE(build_artifacts_size, 0) + (-91450), storage_size = COALESCE(storage_size, 0) + (-91450) WHERE "project_statistics"."project_id" = 10339957
client_addr 127.0.0.1
application_name sidekiq 5.2.7 queues: post_rec...ndary_scheduler [5 of 27 busy]

we noticed this update query are executed multiple times in the transaction with different incremental values.

  1. is it necessary to combine all the queries in to a giant transaction instead of multiple small transactions?
  2. can we just update project_statistics once at the end of the transaction instead of multiple incremental updates? it has also produced large number of dead tuples which caused auto-vacuum to be executed extensively.
Assignee
Assign to
None
Milestone
None
Assign milestone
Time tracking
None
Due date
None
Reference: gitlab-org/gitlab-foss#62488