MySQL 'going away' on small dataset

[JoeMurray]

We're currently having trouble with MySQL 'going away' on site that is loaded with a test set of 300 donors and about 5k organizational records. Here's what I found just now when top showed mysql taking up more than 99 percent of CPU for several minutes after I had tried to delete a record:

Code: [Select]

mysqladmin -uroot -p processlist | egrep -vw 'Sleep|processlist|Binlog Dump' | awk -F'|' '{print $6, $7, $8, $9}'
Enter password:
   
 Command   Time     State                          Info                                                                                                 
   
 Query     875737   Copying to tmp table on disk   SELECT SQL_CALC_FOUND_ROWS GROUP_CONCAT(DISTINCT civicrm_contact_assignee.sort_name  ORDER BY civicr
 Query     477760   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     477658   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     477568   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     476827   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     476557   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     475747   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     475566   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     474961   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     474371   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     474310   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     474170   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     474162   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     473694   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     473369   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     472899   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     472784   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     472691   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     472528   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     469851   Locked                         TRUNCATE TABLE civicrm_acl_contact_cache                                                             
 Query     433564   Locked                         SELECT SQL_CALC_FOUND_ROWS GROUP_CONCAT(DISTINCT civicrm_contact_assignee.sort_name  ORDER BY civicr
 Query     323      Locked                         TRUNCATE TABLE civicrm_acl_contact_cache   
On other installations I have worked around this problem by disabling 'Include order by clause' at Administer > Configure > Global Settings > Search Settings. In this case I need the functionality provided by ordering of results. The actual results being ordered are fairly small - a few hundred, though there will be tens of thousands of donors in the system initially.

Is there a place we should be looking in the code to help with optimization?

[xavier]

What ACL have you put in place?

Feels a bit like a deadlock (it's trying to truncate the cache table while something else it having it  open.

Are you having several users working on the table? If not, can you enable login all sql commands (using the devel drupal module if I recall) and see what's going on around the cache table?

X+

[JoeMurray]

We have left the smart group cache timeout at 0, so the cache is supposed to be truncated every time a contact is edited, added, or deleted. I am guessing that some other processes snuck a bit of time while the table was being written to disk but ended up blocked on that process. We are moving to enable slow query logging, and I'll consider turning on query display in Devel so that next time this intermittent problem occurs we can figure out more easily what is going on.