Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-18153

Innodb speed improvement building group cache (autocommit=0)

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 4.7.3
    • Fix Version/s: None
    • Component/s: None
    • Labels:
    • Versioning Impact:
      Patch (backwards-compatible bug fixes)
    • Documentation Required?:
      None
    • Funding Source:
      Contributed Code

      Description

      As seen here:
      https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

      There is a quick fix for improving the speed of bulk insert statements (when using mysql/innodb).

      SET autocommit=0;
      ... SQL import statements ...
      COMMIT;
      

      In our tests, wrapping this around the insert statements for loading contacts into groups reduces the time it takes to rebuild a ~300'000 contact smart group by about half.

      Before change::
      Time taken is: 10.650987148285

      After change::
      Time taken is:: 5.0638861656189
      Time taken is: 4.3147821426392
      Time taken is: 4.2649991512299

      CRM_Contact_BAO_GroupContactCache.php ~Line 53

      CRM_Core_DAO::executeQuery("SET autocommit=0");
      CRM_Core_DAO::executeQuery(
              "INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id) SELECT DISTINCT $idName, group_id FROM $tempTable");
      CRM_Core_DAO::executeQuery("COMMIT");
      

      This approach could be used for other areas large inserts are performed?

        Attachments

          Activity

            People

            • Assignee:
              eileen Eileen McNaughton
              Reporter:
              rocxa Alex C
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated: