Details
-
Type: Bug
-
Status: Done/Fixed
-
Priority: Major
-
Resolution: Fixed/Completed
-
Affects Version/s: 4.7
-
Fix Version/s: 4.7.14
-
Component/s: None
-
Labels:
-
Documentation Required?:None
-
Funding Source:Contributed Code
Description
I have been doing some digging into deadlocks and find that this query
{{INSERT INTO civicrm_acl_contact_cacheINSERT INTO civicrm_acl_contact_cache ( user_id, contact_id, operation )
SELECT 67005 as user_id, contact_a.id as contact_id, 'View' as operation
FROM civicrm_contact contact_a
....
WHERE (contact_a.id = 67005 OR (.... ) AND (contact_a.is_deleted = 0)))
GROUP BY contact_a.id
ON DUPLICATE KEY UPDATE
user_id=VALUES,
contact_id=VALUES,
operation=VALUES;}}
Is much worse than this query
{{
INSERT INTO civicrm_acl_contact_cache
(user_id, contact_id, operation)
SELECT SQL_NO_CACHE
DISTINCT s.user_id, s.contact_id, s.operation FROM
(SELECT
67005 as user_id, contact_a.id as contact_id, 'View' as operation
FROM civicrm_contact contact_a
...
WHERE ....
AND (contact_a.is_deleted = 0)
UNION SELECT 67005 as user_id, 67005 as contact_id, 'View' as operation
) as s
LEFT JOIN civicrm_acl_contact_cache ac ON ac.user_id = s.user_id AND ac.contact_id = s.contact_id AND ac.operation = s.operation
WHERE ac.id IS NULL}}
Note the ... in the above refers to the extra joins and clauses added by the ACL and / or ACL hook. I've left them out as they are not the key issue.
There are 2 changes above.
Most importantly INSERT ON DUPLICATE UPDATE takes 12 times as long as prefiltering the INSERT rows against the ones already there (12 seconds vs 0.5 seconds). In addition it locks all the tables involved, whereas I believe the INSERT may only lock one table on a for-insert basis.
The second change is removing contact_a.id = 67005 OR (.... )
I only got a 0.25 second improvement from this - ie. down from 0.5 to 0.25. I think this benefit could be greater under some ACL situations and it might reduce locking too - but it's not the low-hanging fruit here.
Attachments
Issue Links
1.
|
Remove call to fill cache right before emptying it | Done/Fixed | Unassigned |
|