[Fixed] Frequent database deadlocks

[sdragnev]

Hi all,

We've been getting a lot of deadlocks around operations on civicrm_group_contact_cache.

Here's an example output from pt-deadlock-logger:

server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
localhost 2014-10-21T15:41:19 56089 0 11 live_civicrm localhost  live_civicrm civicrm_contact PRIMARY RECORD S w 0 INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id) SELECT DISTINCT id, group_id FROM civicrm_temp_group_contact_cache815
localhost 2014-10-21T15:41:19 56148 0 11 live_civicrm localhost  live_civicrm civicrm_group_contact_cache PRIMARY RECORD X w 1 DELETE gc FROM civicrm_group_contact_cache gc INNER JOIN civicrm_group g ON g.id = gc.group_id WHERE TIMESTAMPDIFF(MINUTE, g.cache_date, 20141021194108) >= 5
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
localhost 2014-10-21T15:42:09 56188 0 22 live_civicrm localhost  live_civicrm civicrm_contact PRIMARY RECORD S w 0 INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id) SELECT DISTINCT id, group_id FROM civicrm_temp_group_contact_cache1080
localhost 2014-10-21T15:42:09 56252 0 19 live_civicrm localhost  live_civicrm civicrm_group_contact_cache PRIMARY RECORD X w 1 DELETE gc FROM civicrm_group_contact_cache gc INNER JOIN civicrm_group g ON g.id = gc.group_id WHERE TIMESTAMPDIFF(MINUTE, g.cache_date, 20141021194150) >= 5
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
localhost 2014-10-21T15:45:57 56730 0 10 live_civicrm localhost  live_civicrm civicrm_group_contact_cache PRIMARY RECORD X w 0 DELETE gc FROM civicrm_group_contact_cache gc INNER JOIN civicrm_group g ON g.id = gc.group_id WHERE TIMESTAMPDIFF(MINUTE, g.cache_date, 20141021194547) >= 5
localhost 2014-10-21T15:45:57 56745 0 0 live_civicrm localhost  live_civicrm civicrm_acl_cache PRIMARY RECORD X w 1 DELETE FROM civicrm_acl_cache WHERE modified_date IS NULL OR (TIMESTAMPDIFF(MINUTE, modified_date, 20141021194557) >= 5)
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
localhost 2014-10-21T16:02:24 58683 0 14 live_civicrm localhost  live_civicrm civicrm_contact PRIMARY RECORD S w 0 INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id) SELECT DISTINCT id, group_id FROM civicrm_temp_group_contact_cache1325
localhost 2014-10-21T16:02:24 58754 0 5 live_civicrm localhost  live_civicrm civicrm_group_contact_cache PRIMARY RECORD X w 1 DELETE gc FROM civicrm_group_contact_cache gc INNER JOIN civicrm_group g ON g.id = gc.group_id WHERE TIMESTAMPDIFF(MINUTE, g.cache_date, 20141021200219) >= 5

One possible culprit is the civicrm_group_roles module. We're syncing 8 roles, one of which is a smart group (the latter will probably change soon in an attempt to fix this).

Just wondering if there's any other ideas on how to troubleshoot/fix this.

Edit: I should say the db has had various performance options tweaked such as having a large memory buffer and is working fairly well otherwise.

[Erik Hommel]

There is possibly a difficult or time-consuming SMART group in your CiviCRM installation?

[sdragnev]

There is possibly a difficult or time-consuming SMART group in your CiviCRM installation?

There certainly is, but there's no way around it at present.
I'm guessing the group sync module is causing at least part of this issue so we'll be replacing it with a workaround to avoid hitting those group cache tables.

[sdragnev]

It was indeed a smart group used by the group sync module. It was too much work every time someone logged in.