861956 - Some of the mozillians queries nearly nuked the generic database.

Status: VERIFIED FIXED

(Participation Infrastructure :: Phonebook, defect)

Description

[Brandon Johnson [:cyborgshadow]]

Found these few queries in the mozillians database. 

They were taking more than 1000 seconds and eating more than 15GB of memory. I had to kill them in order to prevent the server from crashing. 

It should be noted these queries have cartesian product joins in them which can result in multiplicative datasets in the order of a power. (for example, an 137 MB table can become 137 MB x 137 MB = 18.7GB dataset, and it's all in memory because of how it's executed)

------------------------------- 

SELECT COUNT(*) FROM (SELECT `auth_user`.`id` AS `id`, `auth_user`.`username` AS `username`, `auth_user`.`first_name` AS `first_name`, `auth_user`.`last_name` AS `last_name`, `auth_user`.`email` AS `email`, `auth_user`.`password` AS `password`, `auth_user`.`is_staff` AS `is_staff`, `auth_user`.`is_active` AS `is_active`, `auth_user`.`is_superuser` AS `is_superuser`, `auth_user`.`last_login` AS `last_login`, `auth_user`.`date_joined` AS `date_joined`, COUNT(T3.`id`) AS `userprofile__vouchees__count` FROM `auth_user` LEFT OUTER JOIN `profile` ON (`auth_user`.`id` = `profile`.`user_id`) LEFT OUTER JOIN `profile` T3 ON (`profile`.`id` = T3.`vouched_by_id`) INNER JOIN `profile` T4 ON (`auth_user`.`id` = T4.`user_id`) WHERE (T4.`is_vouched` = 1  AND `auth_user`.`id` IN (SELECT U0.`user_id` FROM `profile` U0 WHERE (U0.`user_id` IN (SELECT U0.`id` FROM `auth_user` U0 LEFT OUTER JOIN `profile` U1 ON (U0.`id` = U1.`user_id`) LEFT OUTER JOIN `profile` U2 ON (U1.`id` = U2.`vouched_by_id`) INNER JOIN `profile` U3 ON (U0.`id` = U3.`user_id`) WHERE U3.`is_vouched` = 1  GROUP BY U0.`id`, U0.`username`, U0.`first_name`, U0.`last_name`, U0.`email`, U0.`password`, U0.`is_staff`, U0.`is_active`, U0.`is_superuser`, U0.`last_login`, U0.`date_joined` ORDER BY NULL) AND (U0.`privacy_website` = 4  OR U0.`privacy_bio` = 4  OR U0.`privacy_full_name` = 4  OR U0.`privacy_photo` = 4  OR U0.`privacy_groups` = 4  OR U0.`privacy_city` = 4  OR U0.`privacy_vouched_by` = 4  OR U0.`privacy_skills` = 4  OR U0.`privacy_country` = 4  OR U0.`privacy_region` = 4  OR U0.`privacy_languages` = 4  OR U0.`privacy_email` = 4  OR U0.`privacy_ircname` = 4 )))) GROUP BY `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` ORDER BY NULL) subquery 

-------------------------------

SELECT COUNT(*) FROM (SELECT `auth_user`.`id` AS `id`, `auth_user`.`username` AS `username`, `auth_user`.`first_name` AS `first_name`, `auth_user`.`last_name` AS `last_name`, `auth_user`.`email` AS `email`, `auth_user`.`password` AS `password`, `auth_user`.`is_staff` AS `is_staff`, `auth_user`.`is_active` AS `is_active`, `auth_user`.`is_superuser` AS `is_superuser`, `auth_user`.`last_login` AS `last_login`, `auth_user`.`date_joined` AS `date_joined`, COUNT(T3.`id`) AS `userprofile__vouchees__count` FROM `auth_user` LEFT OUTER JOIN `profile` ON (`auth_user`.`id` = `profile`.`user_id`) LEFT OUTER JOIN `profile` T3 ON (`profile`.`id` = T3.`vouched_by_id`) INNER JOIN `profile` T4 ON (`auth_user`.`id` = T4.`user_id`) WHERE (T4.`is_vouched` = 1  AND `auth_user`.`id` IN (SELECT U0.`user_id` FROM `profile` U0 WHERE (U0.`user_id` IN (SELECT U0.`id` FROM `auth_user` U0 LEFT OUTER JOIN `profile` U1 ON (U0.`id` = U1.`user_id`) LEFT OUTER JOIN `profile` U2 ON (U1.`id` = U2.`vouched_by_id`) INNER JOIN `profile` U3 ON (U0.`id` = U3.`user_id`) WHERE U3.`is_vouched` = 1  GROUP BY U0.`id`, U0.`username`, U0.`first_name`, U0.`last_name`, U0.`email`, U0.`password`, U0.`is_staff`, U0.`is_active`, U0.`is_superuser`, U0.`last_login`, U0.`date_joined` ORDER BY NULL) AND (U0.`privacy_website` = 4  OR U0.`privacy_bio` = 4  OR U0.`privacy_full_name` = 4  OR U0.`privacy_photo` = 4  OR U0.`privacy_groups` = 4  OR U0.`privacy_city` = 4  OR U0.`privacy_vouched_by` = 4  OR U0.`privacy_skills` = 4  OR U0.`privacy_country` = 4  OR U0.`privacy_region` = 4  OR U0.`privacy_languages` = 4  OR U0.`privacy_email` = 4  OR U0.`privacy_ircname` = 4 )))) GROUP BY `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` ORDER BY NULL) subquery 


I'll be posting an optimized version of these queries in the next update to this bug, optimizing them now.

Comment 1

[Brandon Johnson [:cyborgshadow]]

sancus pushed a new version. The updated query is:

SELECT COUNT(*) FROM (SELECT `auth_user`.`id` AS `id`, `auth_user`.`username` AS `username`, `auth_user`.`first_name` AS `first_name`, `auth_user`.`last_name` AS `last_name`, `auth_user`.`email` AS `email`, `auth_user`.`password` AS `password`, `auth_user`.`is_staff` AS `is_staff`, `auth_user`.`is_active` AS `is_active`, `auth_user`.`is_superuser` AS `is_superuser`, `auth_user`.`last_login` AS `last_login`, `auth_user`.`date_joined` AS `date_joined`, COUNT(T3.`id`) AS `userprofile__vouchees__count` FROM `auth_user` LEFT OUTER JOIN `profile` ON (`auth_user`.`id` = `profile`.`user_id`) LEFT OUTER JOIN `profile` T3 ON (`profile`.`id` = T3.`vouched_by_id`) LEFT OUTER JOIN `profile` T4 ON (`auth_user`.`id` = T4.`user_id`) LEFT OUTER JOIN `profile` T5 ON (`auth_user`.`id` = T5.`user_id`) WHERE (T4.`is_vouched` = 1  AND (T5.`privacy_website` = 4  OR T5.`privacy_bio` = 4  OR T5.`privacy_full_name` = 4  OR T5.`privacy_photo` = 4  OR T5.`privacy_groups` = 4  OR T5.`privacy_city` = 4  OR T5.`privacy_vouched_by` = 4  OR T5.`privacy_skills` = 4  OR T5.`privacy_country` = 4  OR T5.`privacy_region` = 4  OR T5.`privacy_languages` = 4  OR T5.`privacy_email` = 4  OR T5.`privacy_ircname` = 4 )) GROUP BY `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` ORDER BY NULL) subquery;

and it runs subsecond and MUCH better.

We can call this one closed.

Comment 2

[Brandon Johnson [:cyborgshadow]]

as a side note: we did confirm the original query CAN crash the generic server by itself. I removed generic2 from the load balancer and used it as a test.

Comment 3

[Matt Brandt [:mbrandt]]

Bumping to verified per comment 1