When there are a large number of supporters in the system, and the system is trying to parse large amounts of data to figure out how many supporters have signed a particular campaign... the system hangs.

There is an inefficiency with getting stats info.

Currently, all stats getting functions are in the file:
includes/info.inc

I can see a few steps which might help with this:
First, it might be best to move this functionality to a separate module? This would allow for the optional inclusion of this functionality, this module could create it's own indexing tables, and if disabled could greatly improve the load on the system.

Second, look into the possibility of integrating with drupal core tracking module? Or, a 3rd party tracking module?

Or, if no integration is possible, at the very least it should be worth a look to see how drupal core tracking handles itself.

Regardless of whether we can integrate with another stats module, we need to optimize how results are tracked.

Comments

damien_vancouver’s picture

damien_vancouver commented
Assigned: Unassigned » damien_vancouver
Priority: Major » Normal

I've committed several performance fixes to the supporter count tokens that should mostly resolve this problem - both for the text (supporters) and graph (graphs) tokens.

There is still a bunch more to do on all the other tokens though. Usually the change needed is a refactor of the token's code as well as a new optimized count function in info.inc. For example, I created an optimized citcon_get_campaign_supporters_count() function and had the token code call that, instead of them calling get_all_supporters() and then counting the result of that.

So... we need to optimize all the count functions with these four things:

  • Always using ->countquery() (or ->count() for EFQs) instead of running a full select * and then counting the array in PHP. For tokens requiring values (like first or most recent supporter) then use a SQL LIMIT clause to limit the query to just one or a few rows.
  • Log the underlying SQL produced and ensure there are proper indexes on columns in the WHERE clause, and that MySQL is using these indexes in its query plan
  • Caching those counts using drupal_cache (keyed by campaign nid). For the supporter counts I've hardcoded the cache timeout value to 5 seconds, but I will be adding a configurable option to the module settings that allow for timeouts as low as 1 second.
  • Again caching the above value in a Drupal static variable (cached just for the current page load). This allows all retrieved values to be re-used if a token is used more than once, for example. Or another example is if two different tokens that count the supporters to a campaign are used. Both will use the same static PHP cached value and drupal_get_cache() (and possibly the query) will only get called once.

Each token and graph token needs to be checked and refactored as required so that it's following the above guidelines. That will result in more specialized helper functions in includes/info.inc and hopefully we will end up not really ever using citcon_get_all_supporters() in any of the tokens.

I will evalulate the other tokens along these lines ASAP. Meanwhile, the worst of the perormance issues (supporter count tokens) should be fixed in 7.x-1.x-dev. Setting priority down to normal and assigning this issue to me to finish up.

I've been implementing counts that count translations of the current petition too, and that feature will get to all the tokens as I optimize each one.