CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • Upgrade to 4.3.1 - Groups not working
Pages: [1]

Author Topic: Upgrade to 4.3.1 - Groups not working  (Read 1422 times)

jcasharpe

  • I post occasionally
  • **
  • Posts: 57
  • Karma: 5
    • Woodlands Church
  • CiviCRM version: 4.4.6
  • CMS version: Drupal 7
  • MySQL version: MariaDB 10.0.13
  • PHP version: 5.5
Upgrade to 4.3.1 - Groups not working
April 29, 2013, 02:41:46 pm
I've been testing upgrading our site from CiviCRM 4.1 to 4.3.0/1 and have found that the groups pages no longer are working.

The database seems to be looping inserting entries into civicrm_group_contact_cache. It seems to be the construction of the smart groups that is taking a particularly long time (so long that the contacts pages for the groups are timing out and also the groups tab on a contact). We currently have around 570 groups and around 25000 entries in civicrm_group_contact. civicrm_group_contact_cache seems to be growing to around 50000+ entries.
In particular it seems to be a smart group that has a relationship criteria that selects parents of members of another group that is taking a long time to be inserted into the cache and seems to get refreshed when viewing a contact's groups that isn't a member of that group so it appears something odd is happening there.

Any ideas?

jcasharpe

  • I post occasionally
  • **
  • Posts: 57
  • Karma: 5
    • Woodlands Church
  • CiviCRM version: 4.4.6
  • CMS version: Drupal 7
  • MySQL version: MariaDB 10.0.13
  • PHP version: 5.5
Re: Upgrade to 4.3.1 - Groups not working
April 29, 2013, 03:08:13 pm
This is the kind of query that is being executed that is taking a long time to complete:

INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (SELECT 523 as group_id,  contact_a.id as id  FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact `civicrm_group_contact-291` ON contact_a.id = `civicrm_group_contact-291`.contact_id    LEFT JOIN civicrm_group_contact `civicrm_group_contact-292` ON contact_a.id = `civicrm_group_contact-292`.contact_id    LEFT JOIN civicrm_group_contact `civicrm_group_contact-293` ON contact_a.id = `civicrm_group_contact-293`.contact_id    LEFT JOIN civicrm_group_contact `civicrm_group_contact-294` ON contact_a.id = `civicrm_group_contact-294`.contact_id    LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_519` ON contact_a.id = `civicrm_group_contact_cache_519`.contact_id   WHERE  (  ( `civicrm_group_contact_cache_519`.group_id = 519 AND `civicrm_group_contact-291`.group_id != ( 291 ) AND `civicrm_group_contact-291`.status IN ("Added") AND `civicrm_group_contact-292`.group_id != ( 292 ) AND `civicrm_group_contact-292`.status IN ("Added") AND `civicrm_group_contact-293`.group_id != ( 293 ) AND `civicrm_group_contact-293`.status IN ("Added") AND `civicrm_group_contact-294`.group_id != ( 294 ) AND `civicrm_group_contact-294`.status IN ("Added") AND contact_a.contact_type IN ('Individual') )  )  AND (contact_a.is_deleted = 0)   AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.status = 'Removed'
                              AND   civicrm_group_contact.group_id = 523 ) )

jcasharpe

  • I post occasionally
  • **
  • Posts: 57
  • Karma: 5
    • Woodlands Church
  • CiviCRM version: 4.4.6
  • CMS version: Drupal 7
  • MySQL version: MariaDB 10.0.13
  • PHP version: 5.5
Re: Upgrade to 4.3.1 - Groups not working
April 29, 2013, 03:38:10 pm
http://forum.civicrm.org/index.php/topic,21775.msg91193.html#msg91193 is likely related.

jamie

  • I post occasionally
  • **
  • Posts: 95
  • Karma: 6
Re: Upgrade to 4.3.1 - Groups not working
May 08, 2013, 01:34:07 pm
See also http://forum.civicrm.org/index.php/topic,28653.msg122540.html#msg122540

The underlying cause, I suspect, is http://issues.civicrm.org/jira/browse/CRM-11068 which is a feature that causes the Groups tab to display not only regular group membership but smart group membership. It seems this is achieved by re-generating the group cache for all groups whenever you hit that tab. That doesn't seem to scale very well.

I wonder if this feature should be configurable so it can be turned off? Other suggestions?

jcasharpe

  • I post occasionally
  • **
  • Posts: 57
  • Karma: 5
    • Woodlands Church
  • CiviCRM version: 4.4.6
  • CMS version: Drupal 7
  • MySQL version: MariaDB 10.0.13
  • PHP version: 5.5
Re: Upgrade to 4.3.1 - Groups not working
May 08, 2013, 03:20:16 pm
I did a little digging into this and have put my findings here: http://issues.civicrm.org/jira/browse/CRM-12466

Basically the groups view page tries to do a full refresh of the GroupContactCache which if it takes too long causes the page to fail to load. I found that commenting out the call to loadAll() at least lets me access the page. It still has some issues noted on the ticket.

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Upgrade to 4.3.1 - Groups not working
May 08, 2013, 03:44:48 pm

a couple of thoughts and comments:

1. civi can generate queries which are quite bad (we've been tracking and fixing some of them, but a few more exist out there). This will cause a long delay whether its in the group tab or when the group is included in the mailing or not

2. our premise was that if folks have got the scheduled job running (and you limit the number of groups to evaluate per run), this will reduce the load on the interactive system

3. i think our results should be determinisitic (or at least as close to that as we can make it). If we are not going to refresh the cache when the group tab is loaded, then i dont think we should show the smart group contacts

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

jamie

  • I post occasionally
  • **
  • Posts: 95
  • Karma: 6
Re: Upgrade to 4.3.1 - Groups not working
May 10, 2013, 08:00:20 am
We discovered we had some "sleeper" groups among our over 100 smart groups. Three such groups caused our MySQL server to hang and are probably smart groups we are no longer using. I disabled those groups - but realized that loadAll() was still re-building., That bug is fixed with:

https://git.mayfirst.org/?p=ptp/civicrm-core.git;a=commitdiff;h=5e5593a037659e54ae9c0b66715b12d401d21c01

When those smart groups are not included in loadAll, we get reasonable performance.

If you want to check for poorly performing smart groups on a linux-like machine, using Drupal and with drush installed:

In one terminal, login as root and run: watch mysqladmin -v processlist

That will show all active queries, including a column that shows the number of seconds the query has been running.

In another terminal, run:

drush sql-query "UPDATE civicrm_group SET refresh_date = NULL"
drush civicrm-api Job.group_rebuild

sdragnev

  • I post occasionally
  • **
  • Posts: 40
  • Karma: 0
  • CiviCRM version: 4.6.2
  • CMS version: Drupal 7
  • PHP version: 5.4
Re: Upgrade to 4.3.1 - Groups not working
May 29, 2013, 07:08:29 am
Having the same problem here. Clicking on the groups tab guarantees some fun times and timeouts on mysql locks.
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • Upgrade to 4.3.1 - Groups not working

This forum was archived on 2017-11-26.