Can someone help me understand smart group caching so I can prevent these deadlocks?

Question

I know that the Civi devs have been struggling with this issue so I am not complaining or asking for it to be fixed - I just want to understand how it works, so that I can work aroud it.

I am constantly getting complaints from users that data is missing from their dashboard, and when I look in the logs it is invariably due to deadlocks in civicrm_acl_contact_cache. The logs show that these deadlocks happen when the system is trying to INSERT into that table. Only rarely is it trying to delete. This is the opposite to what Eileen says here, i.e. that flushing the cache is more problematic than building: https://civicrm.org/blog/eileen/478-group-contact-cache-deadlocks-improvement

Users have reports on their dashboards that rely on smart groups for their filters. For this reason I set the smart group cache timeout to 0, so that when, for example, someone completes an activity (and the target contact goes into a different smart group as a result) the resulting change is immediately visible. Otherwise users will worry that the change they have made has "not saved" or "not worked".

I followed Eileen;s advice (in the blog post I link to above) and turned off opportunistic caching. But I found I did not have to run the cache flush as a scheduled job either - everything seems to work without it, presumably because the cache timeout is zero.

It has not helped with the deadlocks, though.

Is a timeout of 0 simply not supported?

I think I would be able to deal with this much better if I understood how the ACL cache relates to the smart group cache. Does the smart group timeout affect both? When does the ACL cache get flushed / filled? Do I have control over that? If someone could explain this paragraph from Eileen's blog it would help:

If your smart group cache time out is every 5 minutes and your cron only runs every 10 minutes your cache will be 15 minutes old most of the time. I would expect you would want to run the cron every minute (& possibly reduce your time out by a minute).

I am confused about two aspects of this: 1. She is talking about a cron job that flushes the caches, but that paragraph seems to relate to building them 2. It makes sense that the ACL cache build will depend on the smart group cache. So yes, if the smart group cache is old, and the ACL cache gets built, it will have old data in it. But that assumes that the cron setting is about the ACL cache, not the smart groups. Yet the setting that turns off the opportunistic caching pertains to smart groups, and it is that setting that she says we can replace with the cron.

How can there be a difference between when the cache is flushed and when it is built, anyway? Surely refilling the cache means first you flush it, then you fill it. They cannot be separate

And for one last extremely confusing thing: I turned off the cron job. Why is the ACL cache still trying to update?

Answer 1

I cant answer everything but I think I can help clarify a few things;

How can there be a difference between when the cache is flushed and when it is built, anyway? Surely refilling the cache means first you flush it, then you fill it. They cannot be separate

The answer to this lies in the state of the cache, there are in fact two states;

1. Current - does not need clearing or rebuilding
2. Invalid - this is either empty (as an operation flushed it or expired due to time/cron)

Important bit of information is that an action may chose to flush the cache without rebuilding it - as its not needed by that action but the action would affect it. A good example is a new contact being created - the ACL may be affected but in the create action the cache only needs to be flushed so that the ACL action can then build it.

And for one last extremely confusing thing: I turned off the cron job. Why is the ACL cache still trying to update?

All caching has a expiration time, regardless of the cron if that time expires the next action will try to rebuild the cache. The idea of the cron is to carry out that action offline therefore reducing the time taken on the user action.

I'd like to work through the issue with you so please do keep this conversation going - we have some large customers running ACL without major cache issues so it should be a good case to establish exactly whats going wrong.

Perhaps you could share some numbers i.e. How many ACL groups/rules, smart groups, size of contacts etc. All of these factors will play in important part of the diagnosis.

Answer 2

We have a very similar setup, with the smart group cache timeout set to 0. We also get a lot of deadlocks, but ours are with civicrm_group_contact_cache. They're a mixture of delete, insert, insert ignore, and create temporary table.

Like you I can't quite understand what's happening, or why it results in a deadlock (why doesn't it just queue?). And I'm very interested in seeing other answers here! But I've recently had some luck hacking around the problem by adding some try...catch loops around the SQL queries in question. On ours it now waits a second, then retries the query - and does this up to 10 times. I expect proper programmers will react in horror, but nonetheless this has solved 99% of our deadlocks.

So the original line in Contact/BAO/GroupContactCache.php was:

CRM_Core_DAO::executeQuery($query, $params);

and we made it into:

    $attempts = 0;
    while ($attempts < 10) {
        try {
            CRM_Core_DAO::executeQuery($query, $params);
        } catch ( \Exception $e ) {
            if ( $attempts < 10 ) {
                sleep( 1 );
                $attempts += 1;
                continue;
            } else {
                throw $e;
            }
        }
        break;
    }

I had to do this with 4 queries in Contact/BAO/GroupContactCache.php. You need at least PHP5.5, I think.

I put in some logging, and I can see that it almost always works on the second try.

It's not elegant, and obviously the actual problem is elsewhere. But at least it bypasses it while we figure out the underlying issue. Maybe something similar would work for the ACL cache.

Answer 3

For me, the only way to deal with this problem was to remove as many smart groups as I could from the system. In the process I also rewrote all the dashlet reports I could into Drupal views. There are a few dashlets left that are CiviCRM reports and still rely on smart group. I believe that people who use these reports still have problems with data "disappearing" which are the effect of deadlocks. But they are so used to it that they don't bother me about it any more!

Answer 4

We have over 60,000 contacts and dozens of smart groups and were seeing a MySQL deadlock with smart group cache processes. I changed the CiviCRM cronjob to run every 30 minutes (previously and for other reasons) and edited the scheduled job in CiviCRM for smart group caching so that the parameter of the job is limit=20 which allows only 20 smart groups to be cached per cron run. After killing the last batch of stuck processes in MySQL I haven't seen this problem pop up.