1

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?

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.

  • Thank you, this is helpful. From this, it seems that that I did the right thing in turning off the cron job, as there is no point having it given that the cache is set to 0 . Is that correct? I will get back to you with the information you requested, in the next few days. I would love it if we could get to the bottom of this! – naomi Oct 10 '17 at 16:26
  • Amount of contacts: 7333 Amount of smart groups: 42 Amount of ACL groups: 9 – naomi Oct 16 '17 at 11:07
  • Sorry, amount of ACL groups is 22 – naomi Oct 16 '17 at 11:35
  • Amount of ACL roles: 30. Amount of ACLs : 175 (seems excessive, but that's the result of "select count(*) from civicrm_acl where is_active = 1"). Amount of said ACLs whose object is a smart group: 10. My next test will be to rejig things so that no ACLs rely on smart groups at all, and then see if this resolves the issue. – naomi Oct 16 '17 at 11:42
  • I think thats the right approach, smart groups are heavy on cache refreshes especially if constantly being required for ACLs – Parvez Saleh Oct 16 '17 at 20:30
1

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.

  • Thanks, I have put this in, and will see how it goes. All proper programmers have duct tape in their first aid kit :) – naomi Oct 10 '17 at 18:18
  • I think I must be going crazy, but this code is not catching exceptions for me! The exception is being thrown anyway, immediately rather than after 10 seconds. And I put a logging statement right at the top of the catch block, which is never reached. Most confusing – naomi Oct 10 '17 at 19:59
  • Is it definitely \Exception and not just Exception? I had the same issue till I added the backslash. – Andrew West Oct 11 '17 at 22:49
  • Yes, it's exactly as you have it. – naomi Oct 16 '17 at 10:48
  • What version of Civi are you on? I'm on 4.7.24 – naomi Oct 16 '17 at 10:54
1

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!

0

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.

  • Thanks but, if I understand you correctly, this would mean smart groups don't immediately update? I need them to update immediately (see my question) – naomi Dec 11 '17 at 14:01
  • It will cache the mailing group when you compose the mailing and add them to the recipients. – Christia Dec 11 '17 at 17:31

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.