4

I was alerted to this problem when a contact export was performed that maxed out the 40GB of spare disk space and crashed the entire server.

I found that exporting even a small number of contacts from a smart group is extremely slow and causes MariaDB to use a huge amount of disk space for temporary files.

Specifically, my testing showed that exporting 70 contacts using the default output fields takes 9.4 minutes and creates an 11 GB temporary file.

CiviCRM operations other than exporting are running at reasonable speed.

The single query of interest (shown below) was run with various numbers of exported contacts to obtain the following measurements:

Contacts   Time (S)         Rows       Temp File
Exported                 Examined        (GB)
    1       0.24             ?             ?
    5        4.0             ?             ?
   10        4.4             ?             ?
   15         40          8,753,893       1.0
   20         92         18,432,164       2.4
   25        103         21,884,677       2.7
   30        136         25,507,988       3.1
   40        325         44,676,670       5.0
   50        342         52,419,343       5.8
   60        593         92,125,429      11.0*
   70        565         95,407,449      11.0*

* Yes, I confirmed the last two cases. It’s not clear why there was little difference between 60 and 70 contacts, but each added contact has its own unique effect based on its number of groups, tags, notes, etc.

Questions:

  • Does this seem normal for a database of this size (see row counts below)?
  • Might it be due to a poorly optimized query?
  • Does it suggest a MySQL configuration issue?
  • Any suggestions?

Slow query log results for the case of 70 contacts:

Query_time: 845.694403  Lock_time: 0.341576  Rows_sent: 70  Rows_examined: 95407449
Full_scan: No  Full_join: Yes  Tmp_table: Yes  Tmp_table_on_disk: Yes
Filesort: Yes  Filesort_on_disk: No  Merge_passes: 7  Priority_queue: No

Methodology:

  • Performed CiviCRM export with default output fields of all 70 contacts in a smart group.
  • MySQL slow query log was used to find the offending query.
  • That query was then executed repeatedly in MySQL workbench with various numbers of requested contact_id’s.
  • MySQL slow query log was used to determine “Rows Examined”
  • Temp file size was monitored with watch ls -alh /tmp/*.MA?
  • Times shown are for the query only (excluding fetch times of up to 340 seconds) as reported by MySQL Workbench

Server:

  • 2 core VPS, 8GB RAM, SSD
  • MariaDB 10.0
  • Ubuntu 16.04
  • Drupal 7
  • CiviCRM: 5.23.4

Row counts in critical CiviCRM tables:

  • civicrm_contact: 24,206
  • civicrm_group_contact_cache: 129,211
  • civicrm_group: 367
  • civicrm_group_contact: 93,572
  • civicrm_tag: 33
  • civicrm_entity_tag: 12,761
  • civicrm_note: 5,349

Critical MariaDB/MySQL parameters:

  • sort_buffer_size = 8M
  • join_buffer_size = 8M
  • innodb_buffer_pool_size = 4096M
  • key_buffer_size = 128M
  • table_open_cache = 512

Query:

SELECT 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.image_URL as `image_URL`, contact_a.preferred_communication_method as `preferred_communication_method`, contact_a.preferred_language as `preferred_language`, contact_a.preferred_mail_format as `preferred_mail_format`, contact_a.hash as `hash`, contact_a.source as `contact_source`, contact_a.first_name as `first_name`, contact_a.middle_name as `middle_name`, contact_a.last_name as `last_name`, contact_a.prefix_id as `prefix_id`, contact_a.suffix_id as `suffix_id`, contact_a.formal_title as `formal_title`, contact_a.communication_style_id as `communication_style_id`, contact_a.email_greeting_id as email_greeting_id, contact_a.postal_greeting_id as postal_greeting_id, contact_a.addressee_id as addressee_id, contact_a.job_title as `job_title`, contact_a.gender_id as `gender_id`, 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`, contact_a.user_unique_id as `user_unique_id`, contact_a.employer_id as `current_employer_id`, contact_a.is_deleted as `contact_is_deleted`, contact_a.created_date as `created_date`, contact_a.modified_date as `modified_date`, contact_a.addressee_display as addressee_display, contact_a.addressee_custom as addressee_custom, contact_a.email_greeting_display as email_greeting_display, contact_a.email_greeting_custom as email_greeting_custom, contact_a.postal_greeting_display as postal_greeting_display, contact_a.postal_greeting_custom as postal_greeting_custom, IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) as current_employer, civicrm_address.id as address_id, civicrm_location_type.id as location_type_id, civicrm_location_type.name as `location_type`, civicrm_address.street_address as `street_address`, civicrm_address.street_number as `street_number`, civicrm_address.street_number_suffix as `street_number_suffix`, civicrm_address.street_name as `street_name`, civicrm_address.street_unit as `street_unit`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.supplemental_address_3 as `supplemental_address_3`, 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_address.manual_geo_code as `manual_geo_code`, civicrm_address.name as `address_name`, civicrm_address.master_id as `master_id`, civicrm_address.county_id as county_id, civicrm_address.state_province_id as state_province_id, civicrm_address.country_id as country_id, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as `phone_type_id`, civicrm_phone.phone as `phone`, civicrm_phone.phone_ext as `phone_ext`, civicrm_email.id as email_id, civicrm_email.email as `email`, civicrm_email.on_hold as `on_hold`, civicrm_email.is_bulkmail as `is_bulkmail`, civicrm_email.signature_text as `signature_text`, civicrm_email.signature_html as `signature_html`, civicrm_im.id as im_id, civicrm_im.provider_id as `im_provider`, civicrm_im.provider_id as provider_id, civicrm_im.name as `im`, civicrm_openid.id as openid_id, civicrm_openid.openid as `openid`, civicrm_worldregion.id as worldregion_id, civicrm_worldregion.name as `world_region`, civicrm_website.id as website_id, civicrm_website.url as `url`, 
  CONCAT_WS(',',
    GROUP_CONCAT(DISTINCT IF(civicrm_group_contact.status = 'Added', civicrm_group_contact.group_id, '')),
    GROUP_CONCAT(DISTINCT civicrm_group_contact_cache.group_id)
  )
as `groups`, GROUP_CONCAT(DISTINCT(civicrm_tag.name)) as tags, GROUP_CONCAT(DISTINCT(civicrm_note.note)) as notes  FROM civicrm_contact contact_a   LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_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 )  LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id )  LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id )  LEFT JOIN civicrm_openid ON ( civicrm_openid.contact_id = contact_a.id  )  LEFT JOIN civicrm_location_type ON civicrm_address.location_type_id = civicrm_location_type.id  LEFT JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id  LEFT JOIN civicrm_group_contact_cache ON contact_a.id = civicrm_group_contact_cache.contact_id  LEFT JOIN civicrm_entity_tag ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND civicrm_entity_tag.entity_id = contact_a.id )  LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND contact_a.id = civicrm_note.entity_id )  LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id   LEFT JOIN civicrm_group_contact_cache civicrm_group_contact_cache_5e8691eef0e92 ON contact_a.id = civicrm_group_contact_cache_5e8691eef0e92.contact_id   LEFT  JOIN civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id  LEFT JOIN civicrm_website ON contact_a.id = civicrm_website.contact_id  WHERE  (  (  ( civicrm_group_contact_cache_5e8691eef0e92.group_id IN ("381") )  )  )  AND (contact_a.is_deleted = 0) AND contact_a.is_deleted != 1 AND  contact_a.id IN ( 6442,22905,990,17543,6317,614,21675,22232,543,5161,5532,19913,593,1016,25226,12381,3,2,4596,19253,574,8641,14216,757,8263,5219,22235,16554,14028,2276,17450,21002,555,21312,8076,5191,10562,12706,11653,760,8113,315,2293,1369,765,19581,1303,4893,21344,1907,1814,771,21299,25138,1819,22464,22818,8553,795,10568,925,1296,5557,17018,911,17932,750,21552,10364,11819 )    GROUP BY contact_a.id LIMIT 0, 100000;

Explain:

id  select_type  table                                      type     possible_keys                                             key                               key_len   ref                                                              rows  Extra
1   SIMPLE       civicrm_group_contact_cache_5e8691eef0e92  range    UI_contact_group,FK_civicrm_group_contact_cache_group_id  UI_contact_group                  8         NULL                                                             70    Using where; Using index; Using temporary; Using filesort
1   SIMPLE       contact_a                                  eq_ref   PRIMARY,index_is_deleted_sort_name                        PRIMARY                           4         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     Using where
1   SIMPLE       civicrm_address                            ref      FK_civicrm_address_contact_id                             FK_civicrm_address_contact_id     5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     
1   SIMPLE       civicrm_country                            eq_ref   PRIMARY                                                   PRIMARY                           4         sb1.civicrm_address.country_id                                   1     Using where
1   SIMPLE       civicrm_email                              ref      FK_civicrm_email_contact_id                               FK_civicrm_email_contact_id       5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     
1   SIMPLE       civicrm_phone                              ref      FK_civicrm_phone_contact_id                               FK_civicrm_phone_contact_id       5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     
1   SIMPLE       civicrm_im                                 ref      FK_civicrm_im_contact_id                                  FK_civicrm_im_contact_id          5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     
1   SIMPLE       civicrm_openid                             ALL      FK_civicrm_openid_contact_id                              NULL                              NULL      NULL                                                             1     Using where
1   SIMPLE       civicrm_location_type                      eq_ref   PRIMARY                                                   PRIMARY                           4         sb1.civicrm_address.location_type_id                             1     Using where
1   SIMPLE       civicrm_group_contact                      ref      UI_contact_group                                          UI_contact_group                  4         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         2     
1   SIMPLE       civicrm_group_contact_cache                ref      UI_contact_group                                          UI_contact_group                  4         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         2     Using index
1   SIMPLE       civicrm_entity_tag                         ref      UI_entity_id_entity_table_tag_id                          UI_entity_id_entity_table_tag_id  199       sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id,const   1     Using where; Using index
1   SIMPLE       civicrm_note                               ref      index_entity                                              index_entity                      198       const,sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id   1     Using where
1   SIMPLE       civicrm_worldregion                        eq_ref   PRIMARY                                                   PRIMARY                           4         sb1.civicrm_country.region_id                                    1     Using where
1   SIMPLE       civicrm_tag                                eq_ref   PRIMARY                                                   PRIMARY                           4         sb1.civicrm_entity_tag.tag_id                                    1     Using where
1   SIMPLE       civicrm_website                            ref      FK_civicrm_website_contact_id                             FK_civicrm_website_contact_id     5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     






EDIT 4/9/20 The following is based on the query that occurs during a regular (non-smart) group export of 2 contacts. It's very similar to the above smart-group query. The table below shows the results of that single query for contact #1 alone, contact #2 alone, and both contacts together, as each table is added to the SELECT and JOIN sections.

Exported Contact(s)                             | ---------------#1---------------| -------------#2--------------- | ------------------#1 & #2----------------- |
                                                | Count  Rows Examined   Time (S) | Count  Rows Examined  Time (S) | Rows Examined Sum  Rows Examined  Time (S) |
Full query (from Export from reg. group)        |        13,170,619      44       |        5,258,678      16       | 18,429,297         32,064,083     349      |
SELECT contact_id only (no joins)               |        1               0.1      |        1              0.1      | 2                  2              0.188    |
  + group_contact (regular groups)              | 81     89              0.1      | 36     38             0.1      | 127                129            0.1      |
    + group_tag (smart groups)                  | 32     2,937           0.18     | 21     836            0.1      | 3,773              3,775          0.1      |
      + tag, entity_tag                         | 18     105,465         0.29     | 6      10,412         0.14     | 115,877            115,879        0.39     |
        + notes                                 | 6      669,369         4.1      | 11     110,960        0.48     | 780,329            780,331        3        |
          + email                               | 4      2,677,480       11.5     | 3      332,883        1.2      | 3,010,363          3,010,365      10.8     |
            + phone                             | 2      5,354,964       19.5     | 4      1,331,535      4.6      | 6,686,499          6,686,501      23.8     |
              + address, country, location_type | 2      10,709,930      40.8     | 3      3,994,608      13.5     | 14,704,538         14,704,540     51.5     |
                + open_id                       | 0      10,709,946      38       | 0      3,994,644      13.2     | 14,704,590         28,339,376     136      |
                + im                            | 0      10,709,946      38       | 0      3,994,644      13.5     | 14,704,590         28,339,376     136      |
                + im, open_id                   | 0      10,709,946      37.8     | 0      3,994,644      13.5     | 14,704,590         28,339,376     103      |
                   + world_region               | 1      13,170,619      39.7     | 1      5,258,678      13.8     | 18,429,297         32,064,083     145.5    |

For example:

  • The "+ email" row indicates the query results including (in Select and Joins) email, notes, and all other tables above it.
  • The "+ im" row indicates the query results including address, phone, etc., but excludes the open_id table as that row has the same indentation.

Points of interest:

  • The column "Rows Examined Sum" is simply the sum of Rows Examined for Contacts 1 and 2. This is generally very close to the actual Rows Examined for the query run with both contacts, but in some cases the latter is much greater. Specifically, when the open_id or im tables are introduced, there is little effect on the Rows Examined for either contact #1 or contact #2, but for the case of #1 AND #2, there is a large difference! Oddly, neither contact has a row in either of those two tables, and the total row counts of these two tables is only 0 and 6, respectively.
  • The last row, "+ world_region" adds a considerable amount to Rows Examined, although each contact has only one unique country in all of their address records (and therefore only one world region).
  • The increases in execution time are disproportionally greater than the increases in Rows Examined. .e.g for "+ world_region", the Records Examined for the case of #1 AND #2 increased by 13%, while the time to execute increased by 41%! Perhaps this is due to greater reliance on temporary tables on disk.
  • Hi - Could you attach an explain plan? – Parvez Saleh Apr 3 at 15:04
  • Explain plan added. – BobS Apr 3 at 20:52
  • Hmm, strange, explain plan looks good and no wild joins going on. Could you provide a bit more information about the database itself, is it a clean import or something thats built up over time? Are you running the GDPR extension? – Parvez Saleh Apr 3 at 21:30
  • 1
    Check that the smart group is not corrupted. We have noted with recent upgrades that smart groups can get munged and need to be rebuilt. Be sure to refresh the smart group cache as well. (The Explain says that temporary was used for civicrm_group_contact_cache_5e8691eef0e92 so that's my guess.) – DaveD - BackOffice Thinking Apr 7 at 20:46
  • 1
    We had the same issue. As a workaround we disabled the ability to use the default set of fields in a form hook. More limited sets of fields seem to work OK. – lolcode Apr 22 at 16:02
2

MySQL is forced to generate temporary tables when the query contains GROUP_CONCAT (among other reasons), which this query does in order to capture all values of groups, tags and notes into single fields. The huge temporary tables are the predictable result of several exported fields each having many rows per exported contact, where the those numbers multiply rather than add to achieve the high row count in the temporary table.

Restructuring the query to use subqueries in the critical joins resulted in a speed improvement of 170X!

Additional information here.

Thanks all for your help!

| improve this answer | |
0

Ok, I'm all out of ideas on this I'm afraid. I would probably try a few different export formats to see if its a particular one thats doing it. Check for any extensions - turn them off etc etc. Haven't seen this on a standard install.

| improve this answer | |
  • I've broken down the query by table and contacts. See "EDIT 4/9/20" in the OP. – BobS Apr 9 at 18:45
0
  1. That's a lot of rows examined, a red flag. Perhaps you could try it on a non-smart group instead?

  2. I'll guess you're doing the default export. Especially with some older CiviCRM versions, that generated much larger exports that you'd expect or want. Try specifying some specific fields instead and see if that runs much faster.

  3. Do tell what version of CiviCRM you're using.

| improve this answer | |
  • The query for a non-smart group is almost identical. I've broken down that query by table and contacts. See "EDIT 4/9/20" in the OP. It's definitely dependent on the fields that are being exported, but no field stands out as causing the problem. Using Civi 5.24.3. – BobS Apr 9 at 18:48
  • Did you try picking a set of fields to export instead of the default? Also, what do you make of the 90M+ rows being examined? – Alan Dixon Apr 9 at 19:36
  • The "EDIT 4/9/20" section illustrates what happens as fields are added. If a contact has an exported field of X rows and another of field of Y rows, there is a Cartesian join which results in X*Y rows examined. Lots of such fields, each with lots of such rows, and it grows quickly. But for some fields it seems to grow more than it should, and for others the sum of each contact's rows examined is far less than the rows examined for all of the contacts together. Perhaps this is due to the limitations of available memory. For the case of 32,064,083 rows examined, there were 10 merge passes. – BobS Apr 10 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.