CiviCRM Community Forums (archive)

Hi,

Have had lots of problems in the last couple of days with Civi queries hanging in MySQL in state "Copying to tmp table", with mysqld using 100% CPU (per core per stuck query). This has occurred on 2 different sites, both on Civi 3.1.3 and on the same server, running MySQL 5.0.89, then upgraded to 5.0.90, same problem. MySQL has been tuned to a reasonably sane state, including InnoDB settings, and has generally been running fine.

Here are some example stuck/very slow queries from "show full processlist".

SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a
  LEFT JOIN civicrm_group_contact `civicrm_group_contact-11` ON contact_a.id = `civicrm_group_contact-11`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-23` ON contact_a.id = `civicrm_group_contact-23`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-32` ON contact_a.id = `civicrm_group_contact-32`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-33` ON contact_a.id = `civicrm_group_contact-33`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-34` ON contact_a.id = `civicrm_group_contact-34`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-35` ON contact_a.id = `civicrm_group_contact-35`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-36` ON contact_a.id = `civicrm_group_contact-36`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-37` ON contact_a.id = `civicrm_group_contact-37`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-38` ON contact_a.id = `civicrm_group_contact-38`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-40` ON contact_a.id = `civicrm_group_contact-40`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-42` ON contact_a.id = `civicrm_group_contact-42`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-44` ON contact_a.id = `civicrm_group_contact-44`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-45` ON contact_a.id = `civicrm_group_contact-45`.contact_id
    LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_11` ON contact_a.id = `civicrm_group_contact_cache_11`.contact_id
   WHERE  ( ( ( `civicrm_group_contact-11`.group_id IN ( 11 ) AND `civicrm_group_contact-11`.status IN ("Added") ) OR ( `civicrm_group_contact_cache_11`.group_id = 11 ) ) )
       AND  (  ( `civicrm_group_contact-11`.group_id IN ( 11 ) AND `civicrm_group_contact-11`.status IN ("Added") )  OR  ( `civicrm_group_contact-23`.group_id IN ( 23 ) AND `civicrm_group_contact-23`.status IN ("Added") )  OR  ( `civicrm_group_contact-32`.group_id IN ( 32 ) AND `civicrm_group_contact-32`.status IN ("Added") )  OR  ( `civicrm_group_contact-33`.group_id IN ( 33 ) AND `civicrm_group_contact-33`.status IN ("Added") )  OR  ( `civicrm_group_contact-34`.group_id IN ( 34 ) AND `civicrm_group_contact-34`.status IN ("Added") )  OR  ( `civicrm_group_contact-35`.group_id IN ( 35 ) AND `civicrm_group_contact-35`.status IN ("Added") )  OR  ( `civicrm_group_contact-36`.group_id IN ( 36 ) AND `civicrm_group_contact-36`.status IN ("Added") )  OR  ( `civicrm_group_contact-37`.group_id IN ( 37 ) AND `civicrm_group_contact-37`.status IN ("Added") )  OR  ( `civicrm_group_contact-38`.group_id IN ( 38 ) AND `civicrm_group_contact-38`.status IN ("Added") )  OR  ( `civicrm_group_contact-40`.group_id IN ( 40 ) AND `civicrm_group_contact-40`.status IN ("Added") )  OR  ( `civicrm_group_contact-42`.group_id IN ( 42 ) AND `civicrm_group_contact-42`.status IN ("Added") )  OR  ( `civicrm_group_contact-44`.group_id IN ( 44 ) AND `civicrm_group_contact-44`.status IN ("Added") )  OR  ( `civicrm_group_contact-45`.group_id IN ( 45 ) AND `civicrm_group_contact-45`.status IN ("Added") )  )

SELECT DISTINCT(contact_a.id) as contact_id, contact_a.contact_type  as `contact_type`, contact_a.contact_sub_type  as `contact_sub_type`, contact_a.sort_name  as `sort_name`, contact_a.display_name  as `display_name`, contact_a.do_not_email  as `do_not_email`, contact_a.do_not_phone  as `do_not_phone`, contact_a.do_not_mail  as `do_not_mail`, contact_a.do_not_sms  as `do_not_sms`, contact_a.do_not_trade  as `do_not_trade`, contact_a.is_opt_out  as `is_opt_out`, contact_a.legal_identifier  as `legal_identifier`, contact_a.external_identifier  as `external_identifier`, contact_a.nick_name  as `nick_name`, contact_a.legal_name  as `legal_name`, contact_a.home_URL  as `home_URL`, contact_a.image_URL  as `image_URL`, contact_a.preferred_mail_format  as `preferred_mail_format`, contact_a.first_name  as `first_name`, contact_a.middle_name  as `middle_name`, contact_a.last_name  as `last_name`, contact_a.job_title  as `job_title`, contact_a.birth_date  as `birth_date`, contact_a.is_deceased  as `is_deceased`, contact_a.deceased_date  as `deceased_date`, contact_a.household_name  as `household_name`, IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) AS organization_name, contact_a.sic_code  as `sic_code`, gender.value as gender_id, gender.label as gender, IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) AS current_employer, civicrm_address.id as address_id, civicrm_address.street_address as `street_address`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.city as `city`, civicrm_address.postal_code_suffix as `postal_code_suffix`, civicrm_address.postal_code as `postal_code`, civicrm_address.geo_code_1 as `geo_code_1`, civicrm_address.geo_code_2 as `geo_code_2`, civicrm_state_province.id as state_province_id, civicrm_state_province.abbreviation as `state_province`, civicrm_state_province.name as state_province_name, civicrm_country.id as country_id, civicrm_country.name as `country`, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as phone_type_id, civicrm_phone.phone as `phone`, civicrm_email.id as email_id, civicrm_email.email as `email`, civicrm_email.on_hold as `on_hold`, civicrm_im.id as im_id, civicrm_im.provider_id as provider_id, civicrm_im.name as `im`, civicrm_worldregion.id as worldregion_id, civicrm_worldregion.name as `world_region`, `civicrm_group_contact-13`.id as group_contact_id, `civicrm_group_contact-13`.status as status
  FROM civicrm_contact contact_a
 LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )
 LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id
  LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id
  LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)
  LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)
  LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1)
  LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id
   LEFT JOIN civicrm_group_contact `civicrm_group_contact-11` ON contact_a.id = `civicrm_group_contact-11`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-12` ON contact_a.id = `civicrm_group_contact-12`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-13` ON contact_a.id = `civicrm_group_contact-13`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-2` ON contact_a.id = `civicrm_group_contact-2`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-3` ON contact_a.id = `civicrm_group_contact-3`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-40` ON contact_a.id = `civicrm_group_contact-40`.contact_id
    LEFT JOIN civicrm_group_contact `civicrm_group_contact-42` ON contact_a.id = `civicrm_group_contact-42`.contact_id
    LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_13` ON contact_a.id = `civicrm_group_contact_cache_13`.contact_id
    LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_2` ON contact_a.id = `civicrm_group_contact_cache_2`.contact_id    LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_3` ON contact_a.id = `civicrm_group_contact_cache_3`.contact_id
   LEFT JOIN civicrm_option_group option_group_gender ON (option_group_gender.name = 'gender')
 LEFT JOIN civicrm_option_value gender ON (contact_a.gender_id = gender.value AND option_group_gender.id = gender.option_group_id)
  WHERE  ( ( ( `civicrm_group_contact-13`.group_id IN ( 13 ) AND `civicrm_group_contact-13`.status IN ("Added") ) OR ( `civicrm_group_contact_cache_13`.group_id = 13 ) ) )  AND  (  ( ( ( `civicrm_group_contact-2`.group_id IN ( 2 ) AND `civicrm_group_contact-2`.status IN ("Added") ) OR ( `civicrm_group_contact_cache_2`.group_id = 2 ) ) )  OR  ( ( ( `civicrm_group_contact-3`.group_id IN ( 3 ) AND `civicrm_group_contact-3`.status IN ("Added") ) OR ( `civicrm_group_contact_cache_3`.group_id = 3 ) ) )  OR  ( `civicrm_group_contact-11`.group_id IN ( 11 ) AND `civicrm_group_contact-11`.status IN ("Added") )  OR  ( `civicrm_group_contact-12`.group_id IN ( 12 ) AND `civicrm_group_contact-12`.status IN ("Added") )  OR  ( ( ( `civicrm_group_contact-13`.group_id IN ( 13 ) AND `civicrm_group_contact-13`.status IN ("Added") ) OR ( `civicrm_group_contact_cache_13`.group_id = 13 ) ) )  OR  ( `civicrm_group_contact-40`.group_id IN ( 40 ) AND `civicrm_group_contact-40`.status IN ("Added") )  OR  ( `civicrm_group_contact-42`.group_id IN ( 42 ) AND `civicrm_group_contact-42`.status IN ("Added") )  )
    ORDER BY contact_a.sort_name asc  LIMIT 0, 50

SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as civicrm_contact_source_contact_id, contact_civireport.display_name as civicrm_contact_contact_source, civicrm_contact_assignee.display_name as civicrm_contact_contact_assignee, civicrm_contact_target.display_name as civicrm_contact_contact_target, activity_civireport.id as civicrm_activity_id, activity_civireport.activity_type_id as civicrm_activity_activity_type_id, activity_civireport.source_contact_id as civicrm_activity_source_contact_id, activity_civireport.activity_date_time as civicrm_activity_activity_date_time, activity_civireport.status_id as civicrm_activity_status_id, activity_assignment_civireport.assignee_contact_id as civicrm_activity_assignment_assignee_contact_id, activity_target_civireport.target_contact_id as civicrm_activity_target_target_contact_id, case_activity_civireport.case_id as civicrm_case_activity_case_id
       FROM civicrm_activity activity_civireport

            LEFT JOIN civicrm_activity_target  activity_target_civireport
                   ON activity_civireport.id = activity_target_civireport.activity_id
            LEFT JOIN civicrm_activity_assignment activity_assignment_civireport
                   ON activity_civireport.id = activity_assignment_civireport.activity_id
            LEFT JOIN civicrm_contact contact_civireport
                   ON activity_civireport.source_contact_id = contact_civireport.id
            LEFT JOIN civicrm_contact civicrm_contact_target
                   ON activity_target_civireport.target_contact_id = civicrm_contact_target.id
            LEFT JOIN civicrm_contact civicrm_contact_assignee
                   ON activity_assignment_civireport.assignee_contact_id = civicrm_contact_assignee.id

             INNER JOIN civicrm_acl_contact_cache aclContactCache_0 ON ( contact_civireport.id = aclContactCache_0.contact_id OR contact_civireport.id IS NULL ) AND aclContactCache_0.user_id = 50977 INNER JOIN civicrm_acl_contact_cache aclContactCache_1 ON ( civicrm_contact_target.id = aclContactCache_1.contact_id OR civicrm_contact_target.id IS NULL ) AND aclContactCache_1.user_id = 50977   INNER JOIN civicrm_acl_contact_cache aclContactCache_2 ON ( civicrm_contact_assignee.id = aclContactCache_2.contact_id OR civicrm_contact_assignee.id IS NULL ) AND aclContactCache_2.user_id = 50977
            LEFT JOIN civicrm_option_value
                   ON ( activity_civireport.activity_type_id = civicrm_option_value.value )
            LEFT JOIN civicrm_option_group
                   ON civicrm_option_group.id = civicrm_option_value.option_group_id
            LEFT JOIN civicrm_case_activity case_activity_civireport
                   ON case_activity_civireport.activity_id = activity_civireport.id
            LEFT JOIN civicrm_case
                   ON case_activity_civireport.case_id = civicrm_case.id
            LEFT JOIN civicrm_case_contact
                   ON civicrm_case_contact.case_id = civicrm_case.id  WHERE civicrm_option_group.name = 'activity_type' AND
                               activity_civireport.is_test = 0 AND
                               activity_civireport.is_deleted = 0 AND
                               activity_civireport.is_current_revision = 1 AND ( activity_date_time >= 20090101000000 ) AND ( activity_date_time <= 20091231235959 ) GROUP BY activity_civireport.activity_date_time, activity_civireport.id     LIMIT 0, 50

I found this long and inconclusive thread in the MySQL forums; some people got around the problem by optimising queries.

Dave J

Hi Lobo,

The first two queries are from a single-site civi. The first I'm guessing is a query for an A-Z pager. The second, again guessing based on LIMIT 0,50 and the fields, is perhaps a standard contact search through the UI. Just had a reply about what they were doing: logged in as non-superuser, with several ACL rules in place, they say that searches in general are extremely slow and they give the particular example /civicrm/group/search?reset=1&force=1&context=smog&gid=13 . Client says: "Browsing group memberships or simple searches for contacts can take upto five minutes to complete.  It could have something to do with the way I set up the ACL’s as disabling the majority of them seemed to dramatically speed things up." However currently, even with most of their ACL rules disabled, we're still seeing stuck queries.

The third query is from a multi-site and occurred while running one of the standard reports in CiviReport, looks from the query to be an activities report. This installation has the standard ACL-based multi-site siloing plus a few ACL rules to restrict particular custom groups to particular sites.

Dave J

A quick update on this. The first two queries in the original post, plus the one in the 4th post, are slow due to the query having a very large number of joins added by the ACL checking code. Following some pointers from Lobo I've come up with a patch to make Civi generate these queries more efficiently. This has brought dramatic speed improvements but needs proper review & testing, I sent it to Lobo & Deepak last week.

The third query in the original post is completely different. It's from CiviReport, CRM_Report_Form_Activity, so mostly a hard-coded query but with the addition of an ACL clause:

INNER JOIN civicrm_acl_contact_cache aclContactCache_0 ON ( contact_civireport.id = aclContactCache_0.contact_id OR contact_civireport.id IS NULL ) AND aclContactCache_0.user_id = 50977
INNER JOIN civicrm_acl_contact_cache aclContactCache_1 ON ( civicrm_contact_target.id = aclContactCache_1.contact_id OR civicrm_contact_target.id IS NULL ) AND aclContactCache_1.user_id = 50977
INNER JOIN civicrm_acl_contact_cache aclContactCache_2 ON ( civicrm_contact_assignee.id = aclContactCache_2.contact_id OR civicrm_contact_assignee.id IS NULL ) AND aclContactCache_2.user_id = 50977

We've seen very slow queries (over 10 minutes) from activity reports on several sites, both multi-site and single-site. Here's another example, which seems to be from the ActivitySummary report:

SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as civicrm_contact_id, contact_civireport.display_name as civicrm_contact_display_name, email_civireport.email as civicrm_email_email, activity_civireport.activity_type_id as civicrm_activity_activity_type_id, COUNT(DISTINCT(activity_civireport.id)) as civicrm_activity_id_count
       FROM civicrm_activity activity_civireport

            LEFT JOIN civicrm_activity_target target_activity
                   ON activity_civireport.id = target_activity.activity_id
            LEFT JOIN civicrm_activity_assignment assignment_activity
                   ON activity_civireport.id = assignment_activity.activity_id
            LEFT JOIN civicrm_contact contact_civireport
                   ON (activity_civireport.source_contact_id = contact_civireport.id OR
                        target_activity.target_contact_id = contact_civireport.id                      OR
                        assignment_activity.assignee_contact_id = contact_civireport.id )

            LEFT JOIN civicrm_option_value
                   ON ( activity_civireport.activity_type_id = civicrm_option_value.value )
            LEFT JOIN civicrm_option_group
                   ON civicrm_option_group.id = civicrm_option_value.option_group_id
            LEFT JOIN civicrm_case_activity
                   ON civicrm_case_activity.activity_id = activity_civireport.id
            LEFT JOIN civicrm_case
                   ON civicrm_case_activity.case_id = civicrm_case.id
            LEFT JOIN civicrm_case_contact
                   ON civicrm_case_contact.case_id = civicrm_case.id
           LEFT JOIN civicrm_email  email_civireport
                  ON contact_civireport.id = email_civireport.contact_id AND
                    email_civireport.is_primary = 1   WHERE civicrm_option_group.name = 'activity_type' AND
                               activity_civireport.is_test = 0 AND
                               activity_civireport.is_deleted = 0 AND
                               activity_civireport.is_current_revision = 1  GROUP BY contact_civireport.id, activity_civireport.activity_type_id      LIMIT 0, 50

I've tried this query on a light-to-moderately loaded live server running MySQL 5.0.90 & a quiet dev server on 5.0.77 (current CentOS version). It fails to complete within 10 minutes on both (55 mins on dev). Here's the output of EXPLAIN on the above query:

+----+-------------+-----------------------+--------+------------------------------------------------------------------------------------------------+--------------------------------------------+---------+------------------------------------------------+-------+----------------------------------------------+
| id | select_type | table                 | type   | possible_keys                                                                                  | key                                        | key_len | ref                                            | rows  | Extra                                        |
+----+-------------+-----------------------+--------+------------------------------------------------------------------------------------------------+--------------------------------------------+---------+------------------------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | civicrm_option_group  | const  | PRIMARY,UI_name                                                                                | UI_name                                    | 194     | const                                          |     1 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | civicrm_option_value  | ref    | index_option_group_id_value,FK_civicrm_option_value_option_group_id,index_option_group_id_name | FK_civicrm_option_value_option_group_id    | 4       | const                                          |    40 | Using where                                  |
|  1 | SIMPLE      | activity_civireport   | ref    | UI_activity_type_id                                                                            | UI_activity_type_id                        | 4       | somedb_civicrm31.civicrm_option_value.value    |  4616 | Using where                                  |
|  1 | SIMPLE      | civicrm_case_activity | ref    | FK_civicrm_case_activity_activity_id                                                           | FK_civicrm_case_activity_activity_id       | 4       | somedb_civicrm31.activity_civireport.id        |     1 |                                              |
|  1 | SIMPLE      | civicrm_case          | eq_ref | PRIMARY                                                                                        | PRIMARY                                    | 4       | somedb_civicrm31.civicrm_case_activity.case_id |     1 | Using index                                  |
|  1 | SIMPLE      | civicrm_case_contact  | ref    | UI_case_contact_id                                                                             | UI_case_contact_id                         | 4       | somedb_civicrm31.civicrm_case.id               |     1 | Using index                                  |
|  1 | SIMPLE      | target_activity       | ref    | FK_civicrm_activity_target_activity_id                                                         | FK_civicrm_activity_target_activity_id     | 4       | somedb_civicrm31.activity_civireport.id        |     1 |                                              |
|  1 | SIMPLE      | assignment_activity   | ref    | FK_civicrm_activity_assignment_activity_id                                                     | FK_civicrm_activity_assignment_activity_id | 4       | somedb_civicrm31.activity_civireport.id        |     1 |                                              |
|  1 | SIMPLE      | contact_civireport    | ALL    | PRIMARY                                                                                        | NULL                                       | NULL    | NULL                                           | 13211 |                                              |
|  1 | SIMPLE      | email_civireport      | ref    | index_is_primary,FK_civicrm_email_contact_id                                                   | FK_civicrm_email_contact_id                | 5       | somedb_civicrm31.contact_civireport.id         |     1 |                                              |
+----+-------------+-----------------------+--------+------------------------------------------------------------------------------------------------+--------------------------------------------+---------+------------------------------------------------+-------+----------------------------------------------+
10 rows in set (0.03 sec)

Can any SQL gurus out there suggest any optimisations?

Dave J

Hi,

dlobo just send me the patch (since we had that problem with hanging queries) and I added it to our CiviCRM-System. As there was no side-effect on the testsystem, I just put it on the production site and the users are quite happy. So far there are no indications of bad behaviour.

attached is the patch, let us know if this work for you matt

lobo

Hi - per discussions last week - this does seem to be OK - will open an issue & commit it.

Hmm - I didn't spot that when I tested. Yep, please post it. I can see Lobo has pushed out issue to 4.1 so looks like I might have to patch all our customer sites (the ones that use groups anyway).

http://issues.civicrm.org/jira/browse/CRM-8812

Lobo has said can go ahead & commit - do you wanna commit it?