how to speed up search by group

[daveatkins]

Our Civi is terribly slow when getting lists of contacts in groups. For example, a url like this:

https://oursite/civicrm/group/search?reset=1&force=1&context=smog&gid=1409

takes 30 seconds to load 34 contacts. A group of 134 contacts takes 90 seconds to load.

We have about 17,000 contacts organized into 221 groups. This is the primary way we access contacts other than by searching by name to find an individual. Most of the time, we need to pull up the list of contacts so we can export to a list or schedule a mailing. It's pretty awful and embarassing to have people expecting to wait 1-2 minutes for pages to load.

I have tried to tweak the search settings because I saw some comments here about that, but it had no impact. I have optimized the database tables a number of times and sometimes this improves performance a little, but we are nowhere near an acceptable level.

What can I do to improve performance?

The system is a bit out of date, but that's a major project to upgrade Drupal, mysql, linux, etc.

The Civi version is 4.1.6.

[Erik Hommel]

You might have a look at the civicrm_group_contact_cache table and make sure the scheduled job to process smart group cache is switched on and runs regularly?

[Fabian_SYSTOPIA]

In addition to Erik's post I would advice to set the time that groups are cached to a value longer than 5 minutes and adapt the cron job to update the groups accordingly.

If you leave it at the standard time (5 minutes I think) it could affect the performance of your database as the job runs every 5 minutes. We had a similar issue on an installation wit 120k contacts and many smart groups and set the cache time to one hour.

Obvious flipside: Changes in groups may not be shown for up to one hour.

Regards
Fabian

[daveatkins]

thanks...we don't use SmartGroups very often...but I can make this change and see if it helps.

[JonGold]

Could you share more about what you've already done, the environment, etc.?  In particular:
Do you have root access on the whole server? Can you tweak MySQL cache sizes?  Are there other services running on the server?

The speeds you're describing are so slow that I can't help but think the problem is beyond something that can be fixed within CiviCRM.

[daveatkins]

I am not finding these settings in Civi. There is no cron task related to groups at /civicrm/admin/job The search settings have a value for smart group cache; it was blank; I set it to 5, but these are not the groups that are slow for us...all groups are slow.

As another example, if you go to advanced search and choose a group, that takes forever too.

[daveatkins]

Jon,
I have root access to everything; the server is a linode virtual server. What I have done is simply a msqlcheck -o on all tables in the drupal database. I have dropped all the extraneous temporary import and task tables that Civi creates.

We are on Drupal 6.28, php 5.2, Mysql 5.075 ubuntu linux. Civi 4.1.6

The server is running apache, varnish, and pound to serve several Drupal sites.

The server has 16GB RAM. It is a very old ubuntu version; 9.04, I think. Just have not had time to rebuild. Lots of things need to be updated, but then again, this has always been a problem; it is not new.

[JonGold]

Hmm.  There's two settings relevant for smart group rebuilding - the cache timeout and the scheduled job that automatically rebuilds the groups.

I don't have a copy of 4.1 available, but on 4.5:
Check Administer menu > System Settings > Scheduled Jobs  there's a scheduled job to rebuild the smart groups.  The smart group cache timout is in Administer menu > Customize Data and Screens > Search Preferences.

Are your smart groups built on other smart groups?  That is, is some of the criteria for one smart group that it must calculate another smart group to continue?  I haven't checked the code, but I can theorize that it might be quicker to run a smart group if the search is built not to nest smart groups.

Rebuilding that server will certainly help - I believe the speed boost from PHP 5.3 to 5.4 alone was about 30% - but that won't give you the order of magnitude speed boost you'll need to get "normal" performance.  It's still probably worth testing on a dev server (even a local laptop) with more modern versions of the software to see how much it helps.

Oh - I remember that Civi 4.4 has much better smart group performance - but you'll definitely need to upgrade PHP and probably MySQL before you can take advantage of it.  Still - the more I think about your situation, the more I think the solution lies in software upgrades.  We see far better performance on much smaller Linodes with fewer contacts.

[JonGold]

Upon re-reading, I'm also seeing that you're not using many smart groups - I assumed your performance issues were tied to that!  If you're seeing that slow a speed on non-smart groups with only 17,000 contacts, something is definitely amiss.  Non-smart groups is a simple join of civicrm_contact to civicrm_contact_group, it's a very fast operation.

[daveatkins]

there is also organic groups integration; that is very complicated.

[daveatkins]

can you help me understand the exact database query being executed so I can try this from command line and see if that is the problem? Where, in the code, does it get called?

I have written my own database reports to extract the information and it is not as slow as Civi, so it would help to be able to break this down into exactly what is going on. It would be nice to be able to do some kind of trace to see what is taking so long. In situations like this, I expect the problem is not the query itself, but something else that is perhaps timing out.

I should also mention...I am in a situation here where I am trying to manage a custom system that was developed by others.

So I start by trying to figure out what code is executed by the url
https://oursite/civicrm/group/search?reset=1&force=1&context=smog&gid=1409

any tips are welcome!

[JonGold]

Hi Dave,

There's a lot of different ways to tackle that problem - personally, I like going into the PHP itself and having it output the one SQL query in question to a log or to the screen, but there's simpler options available.

All (or most) of your various options are detailed here: http://wiki.civicrm.org/confluence/display/CRMDOC/Debugging+for+developers

In particular, check out "Output all sql queries run by CiviCRM to a CiviCRM.*.log file".  If you want to dig deeper, there's the classic CiviCRM debug method - you "View Source" on a page, search for ".tpl", and then locate that template in the CiviCRM filesystem (along with its corresponding PHP page).  From there, start looking for variables called "$sql" or "$SQL", and print them to a log or screen using the techniques outlined on the page above.