We have a serious problem after upgrading to 4.7.6 and now to 4.7.7 (without solving the problem) - it is a problem that seems to have been active already in previous version (other people posted about it in 2015), but i can´t find a clue to solve it. See here a post to this topic from somebody from 2015: https://issues.civicrm.org/jira/browse/CRM-16277
It is even impossible to access certain groups (the screen freezes and I manually need to kill the processes /on sleep / in the control panel)
It works when I create a new group with 1 person, but I need to work with groups with approx. 4000 people.
It also seems that sometimes it goes through, but most of the time it freezes and it´s impossible to work.
Please if you have the same problem or know a solution, please let us know.
Attached an excerpt from our slow log file
Query 1: 0.02 QPS, 0.15x concurrency, ID 0x59955E428CD5C6B0 at byte 18602444
Scores: V/M = 8.80
Time range: 2016-05-11 11:19:48 to 23:54:11
Attribute pct total min max avg 95% stddev median
============ === ======= ======= ======= ======= ======= ======= =======
Count 12 795
Exec time 42 6812s 188ms 49s 9s 26s 9s 5s
Lock time 17 5s 2ms 2s 6ms 8ms 63ms 3ms
Rows sent 0 0 0 0 0 0 0 0
Rows examine 67 2.30G 8.59k 28.24M 2.96M 27.20M 6.75M 270.35k
Rows affecte 2 7.43M 0 53.07k 9.57k 44.45k 13.14k 3.35k
Bytes sent 0 41.69k 0 79 53.70 54.21 8.93 54.21
Query size 19 782.95k 1006 1009 1008.48 964.41 0 964.41
String:
Databases gemeingut_civicrm
Hosts localhost
Last errno 0 (772/97%), 1160 (22/2%), 1213 (1/0%)
Users gemeingut
Query_time distribution
1us
10us
100us
1ms
10ms
100ms
1s
10s+
Tables
SHOW TABLE STATUS FROM gemeingut_civicrm LIKE 'civicrm_contact'
SHOW CREATE TABLE gemeingut_civicrm.civicrm_contact
SHOW TABLE STATUS FROM gemeingut_civicrm LIKE 'civicrm_group_contact_cache'
SHOW CREATE TABLE gemeingut_civicrm.civicrm_group_contact_cache
SHOW TABLE STATUS FROM gemeingut_civicrm LIKE 'civicrm_group_contact'
SHOW CREATE TABLE gemeingut_civicrm.civicrm_group_contact
CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache186 (SELECT 184 as group_id, contact_a.id as id FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact_cache civicrm_group_contact_cache_111 ON contact_a.id = civicrm_group_contact_cache_111.contact_id LEFT JOIN civicrm_group_contact_cache civicrm_group_contact_cache_30 ON contact_a.id = civicrm_group_contact_cache_30.contact_id LEFT JOIN civicrm_group_contact_cache civicrm_group_contact_cache_8 ON contact_a.id = civicrm_group_contact_cache_8.contact_id WHERE ( ( civicrm_group_contact_cache_30.group_id = 30 ) OR ( civicrm_group_contact_cache_111.group_id = 111 ) OR ( civicrm_group_contact_cache_8.group_id = 8 ) ) 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 = 184 ) )
Converted for EXPLAIN
EXPLAIN /!50100 PARTITIONS/
SELECT 184 as group_id, contact_a.id as id FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact_cache civicrm_group_contact_cache_111 ON contact_a.id = civicrm_group_contact_cache_111.contact_id LEFT JOIN civicrm_group_contact_cache civicrm_group_contact_cache_30 ON contact_a.id = civicrm_group_contact_cache_30.contact_id LEFT JOIN civicrm_group_contact_cache civicrm_group_contact_cache_8 ON contact_a.id = civicrm_group_contact_cache_8.contact_id WHERE ( ( civicrm_group_contact_cache_30.group_id = 30 ) OR ( civicrm_group_contact_cache_111.group_id = 111 ) OR ( civicrm_group_contact_cache_8.group_id = 8 ) ) 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 = 184 ) )
