campaigns:trigger very slow

[Dcoutelle]

Q	A
Bug report?	N
Feature request?	N
Enhancement?	Y

Description:

We noticed that the campaign triggers are very slow when you start to have a "big" contact database, even for a simple contact.
It would be great to find a way that campaign CRON runs faster.

In our case, at the begining of a quite simple campaign with 1 million contacts, we're running the first decision (email open with 5 days on red path)

We needed nearly 2.5days to execute the command mautic:campaigns:update -i 1

I check in the SQL and i found one query that takes 85% of execution time:
SELECT e.lead_id, e.event_id, e.date_triggered, e.is_scheduled FROM campaign_lead_event_log e WHERE (e.campaign_id = 4) AND (EXISTS (SELECT null FROM campaign_lead_event_log include_leads WHERE (include_leads.lead_id = e.lead_id) AND (include_leads.lead_id IN (864912, 864913, 864914, 864915, 864916, 864917, 864918, 864919, 864920, 864921, 864922, 864923, 864924, 864925, 864926, 864928, 864929, 864930, 864931, 864932, 864933, 864934, 864935, 864936, 864937, 864938, 864939, 864940, 864941, 864942, 864943, 864944, 864945, 864946, 864947, 864948, 864949, 864950, 864951, 864952, 864953, 864955, 864957, 864958, 864959, 864960, 864961, 864962, 864963, 864964, 864965, 864966, 864967, 864968, 864970, 864971, 864972, 864973, 864974, 864975, 864977, 864978, 864979, 864980, 864981, 864982, 864983, 864984, 864985, 864986, 864987, 864988, 864989, 864990, 864991, 864992, 864993, 864994, 864995, 864997, 864998, 864999, 865000, 865001, 865002, 865003, 865004, 865005, 865006, 865007, 865008, 865009, 865010, 865011, 865012, 865013, 865014, 865015, 865017, 865018)))) GROUP BY e.lead_id, e.event_id, e.date_triggered, e.is_scheduled HAVING EXISTS (SELECT null FROM campaign_lead_event_log include_events WHERE (include_events.lead_id = e.lead_id) AND (include_events.event_id IN (24)) AND (include_events.is_scheduled = 0))

From what i've seen, index seems already optimized...

Any idea to optimize campaign performances ?

@alanhartless if you have any ideas that you'd like to share with us, we would be happy to help on that subject.

[Dcoutelle]

This request take 16s but allow to use batch mode :
SELECT e.lead_id, e.event_id, e.date_triggered, e.is_scheduled FROM campaign_lead_event_log e WHERE (e.campaign_id = 4) AND (EXISTS (SELECT null FROM campaign_lead_event_log include_leads WHERE (include_leads.lead_id = e.lead_id) AND (include_leads.lead_id IN (864913)))) GROUP BY e.lead_id, e.event_id, e.date_triggered, e.is_scheduled HAVING EXISTS (SELECT null FROM campaign_lead_event_log include_events WHERE (include_events.lead_id = e.lead_id) AND (include_events.event_id IN (24)) AND (include_events.is_scheduled = 0))

This one take 0.0019s but must be executed for each lead.
SELECT e.lead_id, e.event_id, e.date_triggered, e.is_scheduled FROM campaign_lead_event_log e WHERE (e.campaign_id = 4) AND (e.lead_id = 864913) GROUP BY e.lead_id, e.event_id, e.date_triggered, e.is_scheduled HAVING EXISTS ( SELECT null FROM campaign_lead_event_log include_events WHERE ( include_events.lead_id = e.lead_id ) AND ( include_events.event_id IN (24) ) AND (include_events.is_scheduled = 0) )

Do you think it's a good way to stop use batch mode for this part of campaign query builder ?

[MaxWebmecanik]

What would be great would be to list test cases where these request have to behave identically.
What would be even better would be to write unit tests for these cases.
If someone points me towards an example of a unit test in Mautic, I'd be willing to write them myself.

[escopecz]

@MaxWebmecanik every bundle can have Tests dir with unit tests. For example https://github.com/mautic/mautic/blob/staging/app/bundles/EmailBundle/Tests/Model/EmailModelTest.php

[MaxWebmecanik]

Thanks @escopecz
These tests will need some fixtures to be set up.
Are there any examples I could base myself on?

[lenineto]

@Dcoutelle
I know this is a somewhat old thread but I'm wondering if you found a solution for that. I'm running Mautic's latest version (2.8.2) and I'm running a campaign with less than 100k contacts and Mautic is taking over 4 hours to process it. That's pretty much useless... We will never be able to run mautic for serious business with this kind of performance.
I feel like there must be something wrong with our setups, otherwise Mautic.com would never be able to provide a decent service if this was the natural performance.

[npracht]

Hi @lenineto, i'm from @Dcoutelle team.
Unfortunately, we didn't have the opportunity to work on this on our side.

[npracht]

Hello @dbhurley i know this is not a bug but you may know that for big databases mautic is not performing well. Or maybe you can share some piece of the roadmap, i heard that a campaign refactorisation is planned ?