Answers.Body: <p>We have seen this on large instances. Things to do:</p>
  
  <ul>
  <li>upgrade to 5.13.5 (this also puts you close to the next ESR - a paid for service - which will be released on Nov20).</li>
  </ul>
  
  <p>There have been performance improvements - this will help.</p>
  
  <ul>
  <li><p>cull your smart groups; do you really still need all of them.</p></li>
  <li><p>reschedule the cron job -> ask yourself how up to date your smart groups need to be.</p></li>
  </ul>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-17T20:29:05.957Z
Answers.Text: I discovered about 500+ smart groups on their install, and am going to recommend culling. We are also looking towards an upgrade anyway in the next few months, so that is good to know. I will also looking at manually scheduling when the cron job is executed. Thanks for your feedback and quick response!
Answers.UserDisplayName: joegl
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-17T20:31:16.720Z
Answers.Text: Glad I was able to help you out!
Answers.UserDisplayName: KarinG - Semper IT
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-17T20:08:41.513Z
Answers.LastActivityDate: 2019-10-17T20:08:41.513Z
Answers.UserDisplayName: KarinG - Semper IT
Answers.Body: <p>Karin's answer is a good one.  However, not all smart groups are created equal!  Culling the <em>right</em> smart groups is what will fix your deadlocks.</p>
  
  <ul>
  <li>Identify your slow smart groups.  Here's a script (rough, could use improvement) that you can run that will show you how long it takes to build each smart group.  The latest version is available <a href="https://gist.github.com/MegaphoneJon/59089c1e155645a438af41051bca7ce7" rel="nofollow noreferrer">on my Github</a>:</li>
  </ul>
  
  
  
  <pre><code>#!/usr/bin/php
  &lt;?php
  // Use this code to see how long it takes to generate all of your smart groups to find slow ones.
  eval(`cv php:boot`);
  //Force a refresh of the cache 
  $sql = 'UPDATE civicrm_group SET cache_date = NULL, refresh_date = NULL';
  CRM_Core_DAO::executeQuery($sql);
  // Get a list of smart group IDs.
  $smartGroups = civicrm_api3('Group', 'get', [
    'return' =&gt; ["id", "title"],
    'options' =&gt; ['limit' =&gt; 0],
    'saved_search_id.id' =&gt; ['IS NOT NULL' =&gt; 1],
    'is_active' =&gt; 1,
    'is_hidden' =&gt; 0,
  ])['values'];
  
  foreach ($smartGroups as $group) {
    $time_start = microtime(true);
    echo "{$group['title']} ({$group['id']}): ";
    CRM_Contact_BAO_GroupContactCache::loadAll($group['id']);
    $time_end = microtime(true);
    $executionTime = ($time_end - $time_start);
    echo "$executionTime seconds\n";
  }
  </code></pre>
  
  <p>Running this on a site of mine, the vast majority of smart groups run in under .3 seconds.  However, some run for as long as 211 seconds.  Those are the ones causing deadlocks!</p>
  
  <p>If you can't cull a particular smart group, here are some strategies you can use instead:</p>
  
  <ul>
  <li>Don't nest your smart groups - that is, don't make a smart group "Vermont residents", a smart group "New Hampshire residents", then a smart group "New England Residents" whose members are the folks in the previous 2 (and more) smart groups.  Instead, rewrite the "New England residents" group to include Vermont and New Hampshire residents directly, instead of relying on the nested smart groups.</li>
  <li><em>Especially</em> don't make a smart group made up of members of at least one smart group and one static group!  For reasons too esoteric to discuss, this will completely kill performance.</li>
  <li>Consider whether you can use an extension like <a href="https://civicrm.org/extensions/civirules" rel="nofollow noreferrer">CiviRules</a> or <a href="https://civicrm.org/extensions/sql-tasks-extension-configurable-recurring-tasks" rel="nofollow noreferrer">SQL Tasks</a> to replace the smart group.  For instance, if you have a "Major Donors" smart group, defined as "Everyone who ever gave $500+ in a single donation", consider replacing it with a static group, and a CiviRule that adds donors to it when their donation exceeds your threshold.</li>
  <li>The SQL command CiviCRM comes up with to determine a smart group is sometimes inefficient.  Previously I wrote custom search extensions to optimize the SQL, but I've largely deprecated this approach in favor of SQL Tasks.  Custom searches can still be helpful if end users need to be able to make variations on a particular query on their own.</li>
  </ul>
  
  <p>Hopefully this will give you tools to best find and fix your slow smart groups and kill deadlocks for good!</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-18T16:37:33.937Z
Answers.Text: Nice script John! Thank you for sharing.
Answers.UserDisplayName: KarinG - Semper IT
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-22T17:39:13.790Z
Answers.Text: Wow Jon this is excellent! I will review these guidelines with the CRM administrators and also try the script! Thanks so much.
Answers.UserDisplayName: joegl
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-11-19T20:49:39.737Z
Answers.Text: Hey Jon G! I am not sure where exactly I should be running the smart group profiler script from -- it uses a CiviCRM class that's not included and I am not sure how it knows what database to run the SQL on. Thanks!
Answers.UserDisplayName: joegl
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-11-19T22:52:42.913Z
Answers.Text: Jon G -- you can ignore my last comment -- I was able to build your script in a Drupal module that I could run and output the results. It was really helpful! If you're interested in the module code let me know!
Answers.UserDisplayName: joegl
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-11-20T01:31:07.877Z
Answers.Text: @joegl - Glad you got that to work, but I would usually run this from the command line: `php smartgroupprofile.php`.
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-18T14:11:31.487Z
Answers.LastActivityDate: 2019-10-18T14:11:31.487Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Body: <p>The exact query and issue is below. </p>
  
  <pre><code>UPDATE  civicrm_contact  SET contact_type = 'Individual' , contact_sub_type 
  = NULL , sort_name = 'XXXXXX XXXXXX' , display_name = 'XXXXXX XXXXXX' 
  , first_name = 'XXXXXX' , last_name = 'XXXXXX' , email_greeting_id = 6 , 
  postal_greeting_id = 1 , addressee_id = 1   WHERE (  civicrm_contact.id = 
  27636 )   [nativecode=1205 ** Lock wait timeout exceeded; try restarting 
  transaction]
  </code></pre>
  
  <p>There are also a number of other "database deadlock" errors cropping up in the CiviCRM log.</p>
  
  <p>Essentially, the person using the CRM is complaining that randomly they'll be editing a contact and it will fail to save, instead simply timing out (I haven't reproduced the behavior but I trust it).</p>
  
  <p>After some research/reading, it appears CiviCRM has a contact_cache table in regards to Smart Groups it refreshes periodically via CRON. This specific CiviCRM install has thousands of contacts, and many large Smart Groups. So what I assume is happening is:</p>
  
  <ol>
  <li>The site issues a "job" for refreshing the smart groups and data in the contact_cache table.</li>
  <li>While the "job" is running, every single contact record affected by the smart group is "locked" in the database, and cannot be altered until the "job" finishes.</li>
  <li>The user does not know the job is happening in the background, saves a contact, and produces an UPDATE query for the contact.</li>
  <li>If the contact record is locked, it waits a certain specified amount of time (innodb_wait_timeout I believe) for the row to be unlocked, before failing the query altogether.</li>
  </ol>
  
  <p>My problem is, if the above is true, I don't know how I can resolve the problem. Upping the timeout from 50 seconds to 120 seconds for example, would only increase the amount of time they wait for the request to fail, because the Smart Groups take over 120 seconds to be refreshed.</p>
  
  <p>Other relevant info: Drupal 7, CiviCRM 5.9.1, PHP 7.0.33</p>
  
  <p>Thanks!</p>
  
Tags: <mysql><deadlocks>
Title: Update DB Query fails sporadically when saving contact; nativecode=1205 ** Lock wait timeout exceeded
ThreadText: # Update DB Query fails sporadically when saving contact; nativecode=1205 ** Lock wait timeout exceeded
  
  (Asked by: username_0 on 2019-10-17)
  
  The exact query and issue is below. 
  UPDATE  civicrm_contact  SET contact_type = 'Individual' , contact_sub_type 
  = NULL , sort_name = 'XXXXXX XXXXXX' , display_name = 'XXXXXX XXXXXX' 
  , first_name = 'XXXXXX' , last_name = 'XXXXXX' , email_greeting_id = 6 , 
  postal_greeting_id = 1 , addressee_id = 1   WHERE (  civicrm_contact.id = 
  27636 )   [nativecode=1205 ** Lock wait timeout exceeded; try restarting 
  transaction]
  
  There are also a number of other "database deadlock" errors cropping up in the CiviCRM log.
  Essentially, the person using the CRM is complaining that randomly they'll be editing a contact and it will fail to save, instead simply timing out (I haven't reproduced the behavior but I trust it).
  After some research/reading, it appears CiviCRM has a contact_cache table in regards to Smart Groups it refreshes periodically via CRON. This specific CiviCRM install has thousands of contacts, and many large Smart Groups. So what I assume is happening is:
  
  The site issues a "job" for refreshing the smart groups and data in the contact_cache table.
  While the "job" is running, every single contact record affected by the smart group is "locked" in the database, and cannot be altered until the "job" finishes.
  The user does not know the job is happening in the background, saves a contact, and produces an UPDATE query for the contact.
  If the contact record is locked, it waits a certain specified amount of time (innodb_wait_timeout I believe) for the row to be unlocked, before failing the query altogether.
  
  My problem is, if the above is true, I don't know how I can resolve the problem. Upping the timeout from 50 seconds to 120 seconds for example, would only increase the amount of time they wait for the request to fail, because the Smart Groups take over 120 seconds to be refreshed.
  Other relevant info: Drupal 7, CiviCRM 5.9.1, PHP 7.0.33
  Thanks!
  
  **Tags:** mysql, deadlocks
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2019-10-17. Score: 5
  
  We have seen this on large instances. Things to do:
  
  * upgrade to 5.13.5 (this also puts you close to the next ESR - a paid for service - which will be released on Nov20).
  
  There have been performance improvements - this will help.
  
  * cull your smart groups; do you really still need all of them.
  * reschedule the cron job -> ask yourself how up to date your smart groups need to be.
  
  > **Comments:**
  > **username_0**: I discovered about 500+ smart groups on their install, and am going to recommend culling. We are also looking towards an upgrade anyway in the next few months, so that is good to know. I will also looking at manually scheduling when the cron job is executed. Thanks for your feedback and quick response!  
  > **username_1**: Glad I was able to help you out!  
  
  ---
  
  ### Answer by username_2 on 2019-10-18. Score: 4
  
  Karin's answer is a good one.  However, not all smart groups are created equal!  Culling the right smart groups is what will fix your deadlocks.
  
  Identify your slow smart groups.  Here's a script (rough, could use improvement) that you can run that will show you how long it takes to build each smart group.  The latest version is available on my Github:
  
  #!/usr/bin/php
   ["id", "title"],
    'options' => ['limit' => 0],
    'saved_search_id.id' => ['IS NOT NULL' => 1],
    'is_active' => 1,
    'is_hidden' => 0,
  ])['values'];
  
  foreach ($smartGroups as $group) {
    $time_start = microtime(true);
    echo "{$group['title']} ({$group['id']}): ";
    CRM_Contact_BAO_GroupContactCache::loadAll($group['id']);
    $time_end = microtime(true);
    $executionTime = ($time_end - $time_start);
    echo "$executionTime seconds\n";
  }
  
  Running this on a site of mine, the vast majority of smart groups run in under .3 seconds.  However, some run for as long as 211 seconds.  Those are the ones causing deadlocks!
  If you can't cull a particular smart group, here are some strategies you can use instead:
  
  Don't nest your smart groups - that is, don't make a smart group "Vermont residents", a smart group "New Hampshire residents", then a smart group "New England Residents" whose members are the folks in the previous 2 (and more) smart groups.  Instead, rewrite the "New England residents" group to include Vermont and New Hampshire residents directly, instead of relying on the nested smart groups.
  Especially don't make a smart group made up of members of at least one smart group and one static group!  For reasons too esoteric to discuss, this will completely kill performance.
  Consider whether you can use an extension like CiviRules or SQL Tasks to replace the smart group.  For instance, if you have a "Major Donors" smart group, defined as "Everyone who ever gave $500+ in a single donation", consider replacing it with a static group, and a CiviRule that adds donors to it when their donation exceeds your threshold.
  The SQL command CiviCRM comes up with to determine a smart group is sometimes inefficient.  Previously I wrote custom search extensions to optimize the SQL, but I've largely deprecated this approach in favor of SQL Tasks.  Custom searches can still be helpful if end users need to be able to make variations on a particular query on their own.
  
  Hopefully this will give you tools to best find and fix your slow smart groups and kill deadlocks for good!
  
  > **Comments:**
  > **username_1**: Nice script John! Thank you for sharing.  
  > **username_0**: Wow Jon this is excellent! I will review these guidelines with the CRM administrators and also try the script! Thanks so much.  
  > **username_0**: Hey Jon G! I am not sure where exactly I should be running the smart group profiler script from -- it uses a CiviCRM class that's not included and I am not sure how it knows what database to run the SQL on. Thanks!  
  > **username_0**: Jon G -- you can ignore my last comment -- I was able to build your script in a Drupal module that I could run and output the results. It was really helpful! If you're interested in the module code let me know!  
  > **username_2**: @username_0 - Glad you got that to work, but I would usually run this from the command line: `php smartgroupprofile.php`.  
  
  ---
  
  