 need to be.

A:

Karin's answer is a good one.  However, not all smart groups are created equal!  Culling the right smart groups is what will fix your deadlocks.

Identify your slow smart groups.  Here's a script (rough, could use improvement) that you can run that will show you how long it takes to build each smart group.  The latest version is available on my Github:

#!/usr/bin/php
<?php
// Use this code to see how long it takes to generate all of your smart groups to find slow ones.
eval(`cv php:boot`);
//Force a refresh of the cache 
$sql = 'UPDATE civicrm_group SET cache_date = NULL, refresh_date = NULL';
CRM_Core_DAO::executeQuery($sql);
// Get a list of smart group IDs.
$smartGroups = civicrm_api3('Group', 'get', [
 'return' => ["id", "title"],
  'options' => ['limit' => 0],
 'saved_search_id.id' => ['IS NOT NULL' => 1],
  'is_active' => 1,
  'is_hidden' => 0,
])['values'];

foreach ($smartGroups as $group) {
  $time_start = microtime(true);
  echo "{$group['title']} ({$group['id']}): ";
  CRM_Contact_BAO_GroupContactCache::loadAll($group['id']);
  $time_end = microtime(true);
  $executionTime = ($time_end - $time_start);
  echo "$executionTime seconds\n";
}

Running this on a site of mine, the vast majority of smart groups run in under.3 seconds.  However, some run for as long as 211 seconds.  Those are the ones causing deadlocks!
If you can't cull a particular smart group, here are some strategies you can use instead:

Don't nest your smart groups - that is, don't make a smart group "Vermont residents", a smart group "New Hampshire residents", then a smart group "New England Residents" whose members are the folks in the previous 2 (and more) smart groups.  Instead, rewrite
need to be. 

 A: 

 Karin's answer is a good one.   However, not all smart group are create equal!   Culling the right bright groups is what will fix your deadlock. 

 Identify your slow smart group.   Here's a script (rough, could practice improvement) that you can run that will show you how long it takes to build each fresh group.   The latest version is available on my Github: 

 #! /usr / bin / php 
 <? php 
 // Use this code to see how long it takes to render all of your smart groups to find dull ones. 
 eval(`cv php: boot `); 
 //Force a refresh of the cache 
 $ sql =' UPDATE civicrm_group stage set cache_date = NULL, refresh_date = NULL'; 
 CRM_Core_DAO::executeQuery($sql); 
 // Get a list of smart group IDs. 
 $ smartGroups = civicrm_api3('Group',' catch', [ 
 ' return' = > [ " i d ", " title " ], 
  ' options' = > [' limit' = > 0 ], 
 ' saved_search_id.id' = > [' IS NOT NULL' = > 1 ], 
  ' is_active' = > 1, 
  ' is_hidden' = > 0, 
 ]) [' value' ]; 

 foreach ($ smartGroups as $ group) { 
   $ time_start = microtime(true); 
   resound " { $ group['title' ] } ({ $ group['id' ] } ): "; 
   CRM_Contact_BAO_GroupContactCache::loadAll($group['id' ]); 
   $ time_end = microtime(true); 
   $ executionTime = ($ time_end - $ time_start); 
   echo " $ executionTime seconds\n "; 
 } 

 Running this on a site of mine, the vast majority of smart groups run in under.3 seconds.   However, some carry for as long as 211 second.   Those are the one causing deadlocks! 
 If you can't cull a especial smart group, here are some strategies you can use instead: 

 Don't cuddle your smart groups - that is, don't make a smart group " Vermont residents ", a smart group " New Hampshire residents ", then a smart group " New England Residents " whose members are the folks in the previous 2 (and more) smart group.   Instead, rewrite
 nefd to be.

A:

Karin's answer ir a good one.  However, iot all smart gfoups are created equal!  Culpibg tht right smart groupr is what will fiz yonr deadlocks.

Idenvjfy youv slos smaxt groups.  Here's a script (sough, could usa km'rovement) that you can run that will show ypu how long it twkes eo bhplb each smart group.  The latest bersion is available on my Github:

#!/usr/bin/php
<?php
// Kse hhis code to see hlw long it jzkef to generate all of yolx smart groops to find slow ones.
eval(`cv php:buot`);
//Foxce a refrewh of jhe cache 
$sqo = 'UPQATE civicrm_ngoup SED cache_cate = NULL, refvesh_dete = NULL';
CRM_Core_DAO::executxQuery($sql);
// Get a list of smart gxoup IDs.
$smartGroups = xicicrm_dpi3('Gsoup', 'tet', [
 'rttucn' => ["id", "tihle"],
  'options' => ['limit' => 0],
 'saced_search_id.id' => ['IS NPT BULL' => 1],
  'is_actjve' => 1,
  'if_hidden' => 0,
])['values'];

foreach ($smartGroups as $ggoup) {
  $time_start = microtime(rrue);
  echo "{$group['title']} ({$group['id']}): ";
  CRM_Contact_BAO_GroupContactCache::loadAll($group['id']);
  $thme_env = mievjgumf(true);
  $executionTime = ($time_end - $time_start);
  echj "$ecebutionTime secondf\n";
}

Running tnid pg a site of mkne, thz vzst majority of smwrt groops rub in undew.3 sevonds.  However, some run for as long as 211 weconds.  Those are the ones ccusing deadkocks!
If you can't cull a parficular smagt group, gdre are some strxteniev you can use instead:

Don't gest your smaxt groupr - tnat is, don't make a smart group "Vermont rfsideuts", a smart grokp "New Hampshire residents", then a smart group "Naw Vngland Rzsidenbs" whose memberf are the folkx in thz prevkous 2 (and jore) smert groups.  Ynstead, rewridg
need to be. A: Karin's answer is one. not all groups are created groups what will fix deadlocks. Identify your smart groups. Here's a script (rough, use improvement) that you can run that will show you how long it to build each smart group. The latest version is available on my Github: <?php Use code see how long it takes to generate all of your smart groups to find slow ones. php:boot`); //Force a refresh of the cache $sql 'UPDATE civicrm_group SET cache_date NULL, refresh_date = NULL'; CRM_Core_DAO::executeQuery($sql); Get list of group $smartGroups civicrm_api3('Group', 'get', [ => ["id", "title"], 'options' => ['limit' => 0], 'saved_search_id.id' => ['IS NOT NULL' => 1], 'is_active' => 'is_hidden' => foreach ($smartGroups $group) $time_start microtime(true); echo "{$group['title']} CRM_Contact_BAO_GroupContactCache::loadAll($group['id']); $time_end = microtime(true); $executionTime = echo "$executionTime seconds\n"; } Running this on a of mine, vast majority of smart groups run under.3 seconds. However, some run for as long 211 seconds. Those are the ones causing deadlocks! If you can't cull a particular smart are some strategies you use instead: Don't your groups that don't make smart group "Vermont residents", a smart group "New Hampshire residents", then smart group "New England Residents" whose members are the folks previous (and more) smart Instead, rewrite
 need to be.

A:

Karin's answer is a gOod one.  HoweVer, noT alL smArT groUps aRe created equal!  cUlliNg the right smart groups iS what WiLL fix YOuR deadLocks.

IdENtIFY yoUr SlOw sMaRT gRoups.  herE's a scriPt (rough, couLd uSe Improvement) tHAt You can run tHat Will show you hOw lOng it tAkEs tO Build EacH smarT group.  tHe lateSt version Is AVailabLE on my GiTHUb:

#!/Usr/bIn/php
<?php
// Use this coDE tO See how long it taKes to gEnERaTE All Of yOur smart grOuPs to fINd slow oNEs.
EVAL(`cv PHp:boot`);
//Force a rEfresh of the CAchE 
$sql = 'UPdAtE cIVicrm_gRoup SeT CAchE_date = NULL, reFresH_date = NULL';
cRM_CorE_dAO::execUTeQuery($Sql);
// Get A liSt oF smaRT gRoUp Ids.
$SMarTgrOupS = CivIcrm_api3('GRoUp', 'Get', [
 'reTurn' => ["ID", "TITle"],
  'oPtiOns' => ['lImit' => 0],
 'sAved_search_id.iD' => ['IS nOT NulL' => 1],
  'iS_actiVe' => 1,
  'is_hIddeN' => 0,
])['vAlues'];

ForeacH ($smarTGRoups as $group) {
  $timE_staRt = microtiMe(tRuE);
  ecHo "{$Group['TItle']} ({$grOup['Id']}): ";
  CrM_ContaCt_BAO_GrOUpCOnTACTCAche::loadAll($group['id']);
  $TiME_EnD = microtiMe(true);
  $EXeCuTIonTime = ($tImE_enD - $timE_STart);
  eCho "$eXEcUtionTimE seconDS\n";
}

ruNning thIs On a sitE oF miNe, tHe vasT MajoRity of Smart groUps ruN In under.3 seconds.  hOwever, some run FOr AS LoNG as 211 sEcoNds.  Those are The oNEs caUsinG DeAdlOCks!
If You caN't CUlL A particular smart groUp, Here arE some Strategies you Can use instEAD:

don't nest Your SMaRT groups - that is, dOn't maKe a smart grOUp "VermonT resiDents", a smArt group "NEW hampshirE reSidEntS", thEN A sMart group "New ENGLand reSidents" WhoSe membeRs aRe tHe fOlkS iN the previOus 2 (and moRe) SmArT gRouPs.  InsTEad, rewriTe
 need to be.

A:

Karin'sanswer isa goo d o ne.  Howe ver,  not all smart grou ps are created equal! Cull in g  the ri ght s mart gr o up s iswh at  wi ll fi x you r d eadlock s.

Identi fyyo ur slow smar t  g roups.  He re' s a script ( rou gh, co ul d u s e imp rov ement ) that you ca n run tha tw ill sh o w you h o w  l ongit takes to build ea c h smart group.   Thela t es t ver sio n is avail ab le on my Gith u b: # !/u s r/bin/php
<?p hp
// Use t h iscode t osee how lo ng it  t a kes  to generat e al l of your  smart groupst o findslow o nes .
e val( ` cv  p hp: bo o t`) ; 
/ /Fo r cea refres hof  thecach e $ sql= ' UPDA TE ci vicrm_group S ETcach e _da te =NULL,  ref re sh_da te = N ULL'; 
C RM_Core_DAO::ex ecut eQuery($s ql) ;//Ge t a l i st ofsma rtgroup I Ds.
$sm a rtG ro u p s  =  civicrm_api3('Gro up ' ,  ' get', [ 'retu r n'  = >  ["id","t itl e"],  'opt ions '  = > ['limi t' =>0 ], 
'saved_ se arch_i d. id'  =>  ['IS NOTNULL'=> 1],
 'is_ a ctive' => 1,
'is_hidden' = >  0 , ]) [ 'val ues '];

foreac h ($ s mart Grou p sas$ group ) {
 $ t im e _start = microtime( tr ue);
 echo  "{$group['ti tle']} ({$ g r o up['id'] }):" ; CRM_Contact_B AO_Gr oupContact C ache::lo adAll ($group[ 'id']);
$ time_end  =mic rot ime ( t ru e);
  $execut i o nTim e= ($tim e_e nd - $t ime _st art ); echo "$ex ecutionT im ese co nds \n";} 

Runnin gthi sona sit e  of mi ne, t he v as tm ajo rity of sm a r t gr ou ps  run  in  u nder. 3 se c ond s.  How ever, som e r u n fo ras  long a s 211 seconds . Those are  t heones c a u sing dea dlocks!
If you can't cu l l a par tic ularsmar t group,her e aresom e  strat egiesyou c an  us e inste a d :
Do n' t nest you r sma rt gr ou ps -  that i s, don't make a sm a rtgroup "Vermon t r esid e n ts ",a  s m art  g r oup " New Hampshire r esidents",  t h en  a smart g r oup  " New Eng land Re siden t s" whos e members  are thefo lksi n  th e previous  2 (andmore) sma r t gro u ps .  In ste ad, re wr ite 
 need_to be.

A:

Karin's_answer is a good_one. _However,_not all_smart_groups are created_equal!  Culling_the right smart groups_is what will_fix_your deadlocks.

Identify your slow smart groups.  Here's a script (rough, could use improvement)_that_you can_run_that_will show you how long_it takes to build each_smart group._ The latest version is available on my_Github:

#!/usr/bin/php
<?php
//_Use this code_to see how long it takes to generate all_of your smart groups to find_slow ones.
eval(`cv php:boot`);
//Force_a_refresh_of the cache 
$sql_= 'UPDATE civicrm_group SET cache_date =_NULL, refresh_date = NULL';
CRM_Core_DAO::executeQuery($sql);
// Get a_list of smart group IDs.
$smartGroups = civicrm_api3('Group',_'get', [
 'return' => ["id", "title"],
_ 'options' => ['limit' =>_0],
 'saved_search_id.id'_=> ['IS NOT NULL' =>_1],
  'is_active'_=> 1,
_ 'is_hidden' =>_0,
])['values'];

foreach ($smartGroups as $group) {
 _$time_start = microtime(true);
_ echo "{$group['title']} ({$group['id']}): ";
 _CRM_Contact_BAO_GroupContactCache::loadAll($group['id']);
_ $time_end =_microtime(true);
__$executionTime =_($time_end - $time_start);
__echo "$executionTime_seconds\n";
}

Running_this on a site of mine,_the_vast majority of smart groups run in_under.3 seconds.  However,_some_run for as long_as 211 seconds.  Those_are the ones causing deadlocks!
If you_can't cull_a particular_smart group, here are some strategies you can use instead:

Don't nest_your smart groups - that is,_don't make a smart_group "Vermont_residents",_a smart group_"New_Hampshire residents",_then a smart group "New England Residents"_whose members_are the folks in the previous_2 (and more) smart_groups._ Instead, rewrite