Ideas

Improved CiviMember Roles Synchronization

In the process of improving Drupal/CiviCRM performance for a client, we discovered that CiviMember Roles Sync was killing the database every 15 minutes. Digging into the LAMP stack and analyzing the workflow for the organization, we learned they set the sync to Drupal cron out of necessity. Every 15 minutes, a series of CiviCRM queries would take ~130 seconds to complete with query cache prunes/day in the millions. Watching the server logs and monitoring processes, memory usage, disk I/O we were able to tune the database for a day or two but eventually it would push into swap space, corrupt tables, etc. Bad, bad, bad.

The real problem is not necessarily the queries to synchronize membership and roles, but how it's being executed. Cron should be used as an absolute last resort for anything but the most simple membership and users. The other option of "on login/logout" is not desirable because of workflow considerations. If a member renews his/her membership, they should be able to take immediate advantage of that membership. Forcing the user to login/logout is a nuisance.

The best solution is to synchronize on membership change. When a user creates/updates their membership, their Drupal role is immediately updated. We chatted with lobo on IRC about our plan for improving civicrm_member_roles module. With a couple of basic code modifications and leaning on hook_civicrm_post, we were able to hack our feature in and get it accepted into the next CiviCRM release.