Status: New
Owner: ----
Labels: Type-Defect Priority-Medium

New issue 1144 by psc...@vmware.com: Review Board intermittently hangs due  
to slow DB queries
http://code.google.com/p/reviewboard/issues/detail?id=1144

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
# u...@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
# u...@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
# u...@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;

--
You received this message because you are listed in the owner
or CC fields of this issue, or because you starred this issue.
You may adjust your issue notification preferences at:
http://code.google.com/hosting/settings

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups 
"reviewboard-issues" group.
To post to this group, send email to reviewboard-issues@googlegroups.com
To unsubscribe from this group, send email to 
reviewboard-issues+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/reviewboard-issues?hl=en
-~----------~----~----~----~------~----~------~--~---

Reply via email to