1144: Review Board intermittently hangs due to slow DB queries

psc***@vmwar***** (Google Code) (Is this you? Claim this profile.)
chipx86
 chipx86
June 2, 2009 
We're running Review Board 1.0rc2. We've been seeing a number of
intermittent hangs. We're running apache, and at the time of the hangs
there's a large backup of processes which appear blocked, and which clear
out very quickly once they are able to start processing again.

We suspect that requests are blocked on the DB. We turned on slow query
logging, and identified a couple queries that are taking a long time.
Examples of these queries follow:


# Time: 090529 17:46:26
# User@Host: reviewboard[reviewboard] @ localhost []
# Query_time: 65  Lock_time: 0  Rows_sent: 50  Rows_examined: 990179
SELECT DISTINCT (
                SELECT COUNT(*) FROM reviews_review
                  WHERE reviews_review.review_request_id =
                        reviews_reviewrequest.id
                    AND reviews_review.public
                    AND reviews_review.ship_it
                    AND reviews_review.base_reply_to_id is NULL
            ) AS `shipit_count`, (
                SELECT reviews_review.timestamp FROM reviews_review
                  WHERE reviews_review.review_request_id =
                        reviews_reviewrequest.id
                    AND reviews_review.public
                  ORDER BY reviews_review.timestamp DESC
                  LIMIT 1
            ) AS `last_review_timestamp`, (
                SELECT
                  CASE
                    WHEN COUNT(reviews_review.timestamp) > 0
                         AND MAX(reviews_review.timestamp) >
                             reviews_reviewrequest.last_updated
                    THEN MAX(reviews_review.timestamp)
                    ELSE reviews_reviewrequest.last_updated
                  END
                  FROM reviews_review
                  WHERE reviews_review.review_request_id=
                        reviews_reviewrequest.id
                    AND reviews_review.public
                  ORDER BY (reviews_review.timestamp) DESC
                  LIMIT 1
            ) AS `last_activity_timestamp`, `reviews_reviewrequest`.`id`,
`reviews_reviewrequest`.`submitter_id`,`reviews_reviewrequest`.`time_added`, `reviews_reviewrequest`.`last_updated`,
`reviews_reviewrequest`.`status`, `reviews_reviewrequest`.`public`,
`reviews_reviewrequest`.`changenum`,
`reviews_reviewrequest`.`repository_id`,
`reviews_reviewrequest`.`email_message_id`,
`reviews_reviewrequest`.`time_emailed`, `reviews_reviewrequest`.`summary`,
`reviews_reviewrequest`.`description`,
`reviews_reviewrequest`.`testing_done`,
`reviews_reviewrequest`.`bugs_closed`,
`reviews_reviewrequest`.`diffset_history_id`,
`reviews_reviewrequest`.`branch`, `auth_user`.`id`, `auth_user`.`username`,
`auth_user`.`first_name`, `auth_user`.`last_name`, `auth_user`.`email`,
`auth_user`.`password`, `auth_user`.`is_staff`,`auth_user`.`is_active`,
`auth_user`.`is_superuser`, `auth_user`.`last_login`,
`auth_user`.`date_joined`, `scmtools_repository`.`id`,
`scmtools_repository`.`name`, `scmtools_repository`.`path`,
`scmtools_repository`.`mirror_path`, `scmtools_repository`.`username`,
`scmtools_repository`.`password`, `scmtools_repository`.`tool_id`,
`scmtools_repository`.`bug_tracker`, `scmtools_repository`.`encoding`,
`diffviewer_diffsethistory`.`id`, `diffviewer_diffsethistory`.`name`,
`diffviewer_diffsethistory`.`timestamp` FROM `reviews_reviewrequest` INNER
JOIN `auth_user` ON (`reviews_reviewrequest`.`submitter_id` =
`auth_user`.`id`) INNER JOIN `scmtools_repository` ON
(`reviews_reviewrequest`.`repository_id` = `scmtools_repository`.`id`)
INNER JOIN `diffviewer_diffsethistory` ON
(`reviews_reviewrequest`.`diffset_history_id` =
`diffviewer_diffsethistory`.`id`) WHERE (`reviews_reviewrequest`.`public` =
1  AND (`reviews_reviewrequest`.`status` = 'P'  OR
`reviews_reviewrequest`.`status` = 'S' )) ORDER BY
`last_activity_timestamp` DESC LIMIT 50;



# Time: 090529 17:48:36
# User@Host: reviewboard[reviewboard] @ localhost []
# Query_time: 62  Lock_time: 0  Rows_sent: 50  Rows_examined: 990323
SELECT DISTINCT (
                SELECT COUNT(*) FROM reviews_review
                  WHERE reviews_review.review_request_id =
                        reviews_reviewrequest.id
                    AND reviews_review.public
                    AND reviews_review.ship_it
                    AND reviews_review.base_reply_to_id is NULL
            ) AS `shipit_count`, (
                SELECT reviews_review.timestamp FROM reviews_review
                  WHERE reviews_review.review_request_id =
                        reviews_reviewrequest.id
                    AND reviews_review.public
                  ORDER BY reviews_review.timestamp DESC
                  LIMIT 1
            ) AS `last_review_timestamp`, (
                SELECT
                  CASE
                    WHEN COUNT(reviews_review.timestamp) > 0
                         AND MAX(reviews_review.timestamp) >
                             reviews_reviewrequest.last_updated
                    THEN MAX(reviews_review.timestamp)
                    ELSE reviews_reviewrequest.last_updated
                  END
                  FROM reviews_review
                  WHERE reviews_review.review_request_id=
                        reviews_reviewrequest.id
                    AND reviews_review.public
                  ORDER BY (reviews_review.timestamp) DESC
                  LIMIT 1
            ) AS `last_activity_timestamp`, (
                    SELECT COUNT(*)
                      FROM reviews_review, accounts_reviewrequestvisit
                      WHERE reviews_review.public
                        AND reviews_review.review_request_id =
                            reviews_reviewrequest.id
                        AND accounts_reviewrequestvisit.review_request_id =
                            reviews_reviewrequest.id
                        AND accounts_reviewrequestvisit.user_id = 1519
                        AND reviews_review.timestamp >
                            accounts_reviewrequestvisit.timestamp
                        AND reviews_review.user_id != 1519
                ) AS `new_review_count`, `reviews_reviewrequest`.`id`,
`reviews_reviewrequest`.`submitter_id`,
`reviews_reviewrequest`.`time_added`,
`reviews_reviewrequest`.`last_updated`, `reviews_reviewrequest`.`status`,
`reviews_reviewrequest`.`public`, `reviews_reviewrequest`.`changenum`,
`reviews_reviewrequest`.`repository_id`,
`reviews_reviewrequest`.`email_message_id`,
`reviews_reviewrequest`.`time_emailed`, `reviews_reviewrequest`.`summary`,
`reviews_reviewrequest`.`description`,
`reviews_reviewrequest`.`testing_done`,
`reviews_reviewrequest`.`bugs_closed`,
`reviews_reviewrequest`.`diffset_history_id`,
`reviews_reviewrequest`.`branch`, `auth_user`.`id`, `auth_user`.`username`,
`auth_user`.`first_name`, `auth_user`.`last_name`, `auth_user`.`email`,
`auth_user`.`password`, `auth_user`.`is_staff`, `auth_user`.`is_active`,
`auth_user`.`is_superuser`, `auth_user`.`last_login`,
`auth_user`.`date_joined`, `scmtools_repository`.`id`,
`scmtools_repository`.`name`, `scmtools_repository`.`path`,
`scmtools_repository`.`mirror_path`, `scmtools_repository`.`username`,
`scmtools_repository`.`password`, `scmtools_repository`.`tool_id`,
`scmtools_repository`.`bug_tracker`, `scmtools_repository`.`encoding`,
`diffviewer_diffsethistory`.`id`, `diffviewer_diffsethistory`.`name`,
`diffviewer_diffsethistory`.`timestamp` FROM `reviews_reviewrequest` INNER
JOIN `auth_user` ON (`reviews_reviewrequest`.`submitter_id` =
`auth_user`.`id`) INNER JOIN `scmtools_repository` ON
(`reviews_reviewrequest`.`repository_id` = `scmtools_repository`.`id`)
INNER JOIN `diffviewer_diffsethistory` ON
(`reviews_reviewrequest`.`diffset_history_id` =
`diffviewer_diffsethistory`.`id`) WHERE ((`reviews_reviewrequest`.`public`
= 1  OR `reviews_reviewrequest`.`submitter_id` = 1519 ) AND
(`reviews_reviewrequest`.`status` = 'P'  OR
`reviews_reviewrequest`.`status` = 'S' )) ORDER BY
`last_activity_timestamp` DESC LIMIT 50;


# Time: 090529 18:06:19
# User@Host: reviewboard[reviewboard] @ localhost []
# Query_time: 31  Lock_time: 0  Rows_sent: 50  Rows_examined: 576697
select distinct (SELECT
                  CASE
                    WHEN COUNT(reviews_review.timestamp) > 0
                         AND MAX(reviews_review.timestamp) >
                             reviews_reviewrequest.last_updated
                    THEN MAX(reviews_review.timestamp)
                    ELSE reviews_reviewrequest.last_updated
                  END
                  FROM reviews_review
                  WHERE reviews_review.review_request_id=
                        reviews_reviewrequest.id
                    AND reviews_review.public
                  ORDER BY (reviews_review.timestamp) DESC
                  LIMIT 1
            ) AS `last_activity_timestamp`, `reviews_reviewrequest`.`id`,
`reviews_reviewrequest`.`submitter_id`,
`reviews_reviewrequest`.`time_added`,
`reviews_reviewrequest`.`last_updated`, `reviews_reviewrequest`.`status`,
`reviews_reviewrequest`.`public`, `reviews_reviewrequest`.`changenum`,
`reviews_reviewrequest`.`repository_id`,
`reviews_reviewrequest`.`email_message_id`,
`reviews_reviewrequest`.`time_emailed`, `reviews_reviewrequest`.`summary`,
`reviews_reviewrequest`.`description`,
`reviews_reviewrequest`.`testing_done`,
`reviews_reviewrequest`.`bugs_closed`,
`reviews_reviewrequest`.`diffset_history_id`,
`reviews_reviewrequest`.`branch`, `auth_user`.`id`, `auth_user`.`username`,
`auth_user`.`first_name`, `auth_user`.`last_name`, `auth_user`.`email`,
`auth_user`.`password`, `auth_user`.`is_staff`, `auth_user`.`is_active`,
`auth_user`.`is_superuser`, `auth_user`.`last_login`,
`auth_user`.`date_joined`, `scmtools_repository`.`id`,
`scmtools_repository`.`name`, `scmtools_repository`.`path`,
`scmtools_repository`.`mirror_path`, `scmtools_repository`.`username`,
`scmtools_repository`.`password`, `scmtools_repository`.`tool_id`,
`scmtools_repository`.`bug_tracker`, `scmtools_repository`.`encoding`,
`diffviewer_diffsethistory`.`id`, `diffviewer_diffsethistory`.`name`,
`diffviewer_diffsethistory`.`timestamp` FROM `reviews_reviewrequest` INNER
JOIN `auth_user` ON (`reviews_reviewrequest`.`submitter_id` =
`auth_user`.`id`) INNER JOIN `scmtools_repository` ON
(`reviews_reviewrequest`.`repository_id` = `scmtools_repository`.`id`)
INNER JOIN `diffviewer_diffsethistory` ON
(`reviews_reviewrequest`.`diffset_history_id` =
`diffviewer_diffsethistory`.`id`) WHERE (`reviews_reviewrequest`.`public` =
1  AND (`reviews_reviewrequest`.`status` = 'P'  OR
`reviews_reviewrequest`.`status` = 'S' )) ORDER BY
`last_activity_timestamp` DESC LIMIT 50;
chipx86
#1 chipx86
  • +Started
  • -Priority-Medium
    +Priority-Furious
    +Milestone-Release1.0
    +Component-Reviews
  • +chipx86
chipx86
#2 chipx86 
Fixed in r1993.
  • -Started
    +Fixed