Performance: reconsider storing each email as an Activity

[xurizaemon]

Storing each email as an Activity (again?) seems to be done in order to connect the sent Mandrill email back to CiviCRM. But this approach seems quite heavyweight in the DB (our client had a similar situation to #26, where otherwise normal CiviMail usage led to several GB of DB storage being consumed in a few months).

Various options present themselves:

• Use a more lightweight approach than creating Activities. Activities are useful and flexible, but generating a lot of them will incur penalties across various locations in CiviCRM (eg search, smartgroups, contact display) when the benefit may only relate to connecting CiviCRM and Mandrill.
• Append to existing data rather than duplicate. It seems with MTE extension that each email (Transactional or CiviMail) will lead to two Activities per send (and additional Activities per open, bounce etc), because CiviCRM already stores an Activity for each CiviMail or Transactional email. Storing the Mandrill ID in that record would be less cost.
• Reduce the data stored by the existing approach. CiviMail tables are known to be quite heavyweight due to their data profile (many records!), and replicating their normalised storage layout would probably be more lightweight than repurposing Activities for this.

This extension is labelled discontinued, so it's up to the user community to engage this. Opening this issue to get our experience & thoughts out in the open.

[JoeMurray]

@Pradeep could you investigate and discuss with me tomorrow, including options and scope. Thx

[xurizaemon]

Branch @ https://github.com/fuzionnz/biz.jmaconsulting.mte/tree/configure_activity_mailbody (source of this batch of commits) is WIP making the fix proposed in #26 a configurable setting - I am not making this a PR yet, just sharing our work.

[JoeMurray]

Thanks!!

[artfulrobot]

@xurizaemon do you use that patch in production? My client's use of this extension generates 5GB/day after a big mailing! Is there any need to store the whole email body? Can't see advantage?

[xurizaemon]

Yikes! @artfulrobot IDK if this is used in production as I no longer work on the affected site, but no I don't think there's any reason to keep the body of each generated mail in the DB, hence the config option. (Took me a while rereading this to realise I appear to have been making it possible to NOT store that!)

@petednz might be able to answer. (Pete: RM-13550, RM-13554, RM-13562.)

It's been a few years and I presume the extension has had updates since!

[JoeMurray]

I think two activities per email recipient is a problem and likely wasn't in the original behaviour. I would be happy to consider a PR. If it will involve a significant tradeoff in reporting to avoid db bloat then maybe use a setting to allow end users to choose.

[artfulrobot]

@JoeMurray I just wasn't clear why it was creating any activities? Do you know why it does that? It seems to write opens/clicks to the expected tables already, so why the activities?

[petednz]

I won't be able to answer till next week when jitendra is back from leave.

[artfulrobot]

Thanks @petednz . Running this extension on a site that does a lot of mailing has caused MySQL to crash and brought the site down twice now due to its massive data consumption in the civicrm_activity table, so I'm keen to find a solution.

[artfulrobot]

@petednz be great to have a brief chat about this with someone who understands the code, if poss, either here or on mattermost. Do you think anyone might be up for that?

[petednz]

afaik we do not have this patch in use on the only site we still have mandrill running on

[artfulrobot]

FYI Today I released this alternative extension https://github.com/artfulrobot/mandrill

Please see the README for reasons why.