Uploaded image for project: 'Bitbucket Data Center'
  1. Bitbucket Data Center
  2. BSERV-14405

Optimise SQL queries called by DefaultRepositoryManagementService.getRecentActivity() to reduce the load on database


    • 13
    • We collect Bitbucket feedback from various sources, and we evaluate what we've collected when planning our product roadmap. To understand how this piece of feedback will be reviewed, see our Implementation of New Features Policy.

      Problem Definition

      SQL queries called by DefaultRepositoryManagementService.getRecentActivity() are causing frequent high CPU load on database. Investigate options to reduce load on database caused by calls to DefaultRepositoryManagementService.getRecentActivity().

      The following SQL query is being triggered on Bitbucket database causing the high load on database host.

      "select max(internalre0_1_.created_timestamp) as col_0_0_ from sta_repo_activity internalre0_ inner join sta_activity internalre0_1_ on internalre0_.activity_id=internalre0_1_.id left outer join sta_cmt_disc_activity internalre0_2_ on internalre0_.activity_id=internalre0_2_.activity_id left outer join bb_cmt_disc_comment_activity internalre0_3_ on internalre0_.activity_id=internalre0_3_.activity_id left outer join sta_repo_created_activity internalre0_4_ on internalre0_.activity_id=internalre0_4_.activity_id left outer join sta_repo_push_activity internalre0_5_ on internalre0_.activity_id=internalre0_5_.activity_id left outer join sta_repo_updated_activity internalre0_6_ on internalre0_.activity_id=internalre0_6_.activity_id where internalre0_.repository_id=$1 and (internalre0_5_.trigger_id is null or internalre0_5_.trigger_id<>$2) group by internalre0_.repository_id","calls":"19814838","total_time":xx,"rows":"xxx","hit_percent":"xx"

            74d6667aa35c Josh Aguilar
            de843d56add4 Kalyan Kumar
            7 Vote for this issue
            17 Start watching this issue