Answers.Body: <p>Do you have some smart groups that are getting re-calculated on each submission perhaps?</p>
  
  <p>Here's my answer to a more general question about smart groups and performance:
  <a href="https://civicrm.stackexchange.com/questions/19772/how-smart-groups-work/19774">How Smart Groups Work</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-02-24T15:43:56.440Z
Answers.Text: I am afraid, that I understand too little about the processing of smart groups to answer this question. I can say, that I deleted as much smart groups as easy possible, but there are still 18 left. If this would help I could get rid of more with some effort. We use smart groups only as mailing lists for CiviMail. So I set smart group cache timeout to 0 and there is no scheduled job, that calculates smart group cache. I would only want a specific smart group calculated on mailing creation if it is used.
Answers.UserDisplayName: Harald Balluch
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-02-25T16:10:47.200Z
Answers.Text: Here's an easy experiment: try disabling all your smart groups (i.e. so they will be easy to re-enable). Smart group performance can be very counter-intuitive (I've added a link in my answer above to a previous spiel).
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-02-24T15:14:12.527Z
Answers.LastActivityDate: 2020-02-25T16:11:23.383Z
Answers.LastEditDate: 2020-02-25T16:11:23.383Z
Answers.UserDisplayName: Alan Dixon
Body: <p>We use CiviCRM 5.21.2 with Drupal 7.69 and Webform together with webform_civicrm to provide petitions. The petitions ask for address and email. So every petition signature creates a contact with email and address. Additionally every contact gets a tag and if the email is confirmed with webform_confirm_email in some cases a group.</p>
  
  <p>If the load on this webform submission gets higher we face deadlocks. Some of them seem to connected to group cache and acl cache and some to email or address inserts.</p>
  
  <p>I saw that the tables email and address have triggers defined for insert, update and delete that write into the table contact. For example:</p>
  
  <p>UPDATE civicrm_contact SET modified_date = CURRENT_TIMESTAMP WHERE id = NEW.contact_id;</p>
  
  <p>Is it possible that these triggers have something to do with at least some of the deadlocks. Or that they at least make the chance higher to run into a deadlock?</p>
  
  <p>Does anybody know, why they are there? Is it necessary to update the modified_date in the table contact immediately or would it be sufficient for the system if this is done by a cron job that runs once per day?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-02-23T20:11:43.657Z
Comments.Text: What version of MySQL/MariaDB are you using?  There is some new-ish code in CiviCRM that takes advantage of locking features not available in older versions of the database software, which reduces deadlocks.
Comments.UserDisplayName: Jon G - Megaphone Tech
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-02-24T10:32:39.180Z
Comments.Text: It is Mariadb 10.3.22
Comments.UserDisplayName: Harald Balluch
Tags: <drupal><contacts><webform-civicrm><email><database>
Title: Deadlocks on a high load of contact inserts
ThreadText: # Deadlocks on a high load of contact inserts
  
  (Asked by: username_0 on 2020-02-21)
  
  We use CiviCRM 5.21.2 with Drupal 7.69 and Webform together with webform\_civicrm to provide petitions. The petitions ask for address and email. So every petition signature creates a contact with email and address. Additionally every contact gets a tag and if the email is confirmed with webform\_confirm\_email in some cases a group.
  
  If the load on this webform submission gets higher we face deadlocks. Some of them seem to connected to group cache and acl cache and some to email or address inserts.
  
  I saw that the tables email and address have triggers defined for insert, update and delete that write into the table contact. For example:
  
  UPDATE civicrm\_contact SET modified\_date = CURRENT\_TIMESTAMP WHERE id = NEW.contact\_id;
  
  Is it possible that these triggers have something to do with at least some of the deadlocks. Or that they at least make the chance higher to run into a deadlock?
  
  Does anybody know, why they are there? Is it necessary to update the modified\_date in the table contact immediately or would it be sufficient for the system if this is done by a cron job that runs once per day?
  
  **Tags:** drupal, contacts, webform-civicrm, email, database
  
  > **Comments:**
  > **username_1**: What version of MySQL/MariaDB are you using?  There is some new-ish code in CiviCRM that takes advantage of locking features not available in older versions of the database software, which reduces deadlocks.  
  > **username_0**: It is Mariadb 10.3.22  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2020-02-24. Score: 1
  
  Do you have some smart groups that are getting re-calculated on each submission perhaps?
  
  Here's my answer to a more general question about smart groups and performance:
  [How Smart Groups Work](https://civicrm.stackexchange.com/questions/19772/how-smart-groups-work/19774)
  
  > **Comments:**
  > **username_0**: I am afraid, that I understand too little about the processing of smart groups to answer this question. I can say, that I deleted as much smart groups as easy possible, but there are still 18 left. If this would help I could get rid of more with some effort. We use smart groups only as mailing lists for CiviMail. So I set smart group cache timeout to 0 and there is no scheduled job, that calculates smart group cache. I would only want a specific smart group calculated on mailing creation if it is used.  
  > **username_2**: Here's an easy experiment: try disabling all your smart groups (i.e. so they will be easy to re-enable). Smart group performance can be very counter-intuitive (I've added a link in my answer above to a previous spiel).  
  
  ---
  
  