Improving ElasticEmail API
- Thread starter Lakjin
- Start date
frm.mwz
Well-Known Member
Great, just to confirm, after the defined amount of re-tries within mailwizz, the previously failed ones will still be marked with any (last) error (if still not sent-out)?
twisted1919
Administrator
Staff memberYes, that is correct.
frm.mwz
Well-Known Member
twisted1919
Administrator
Staff memberOne can hope
Any details on how this resending feature works? Does it search for delivery_log for failed emails or are you creating some sort of temp table to hold failed deliveries?One can hope
twisted1919
Administrator
Staff member@Lakjin
For regular sending, right before the campaign should be marked as sent, we do a lookup in the campaign_delivery_log table and we remove all the emails that have the GIVEUP status for that campaign. This automatically forces mailwizz to try resending those emails.
It does so 3 times then it gives up and marks the campaign as sent.
For temp queue tables campaigns, the workflow is the same but a bit heavy since we scan the campaign_delivery_log table, we select the giveup emails and we reinsert them again the temporary table, then we remove them from the campaign_delivery_log table.
For regular sending, right before the campaign should be marked as sent, we do a lookup in the campaign_delivery_log table and we remove all the emails that have the GIVEUP status for that campaign. This automatically forces mailwizz to try resending those emails.
It does so 3 times then it gives up and marks the campaign as sent.
For temp queue tables campaigns, the workflow is the same but a bit heavy since we scan the campaign_delivery_log table, we select the giveup emails and we reinsert them again the temporary table, then we remove them from the campaign_delivery_log table.
Have you considered creating a temporary table for GIVEUP emails, since it can be quite performance heavy to scan campaign_delivery_log? Also, earlier I mentioned a bug where failed emails are not being put in campaign_delivery_log, were you able to identify that? Cause otherwise I'm thinking resending won't work if GIVEUP emails aren't put into campaign_delivery_log
twisted1919
Administrator
Staff memberI did yes, but for now i don't think we need it, my tests show up good, until this becomes an issue we will leave it like so, we don't do pre-optimisations.
There's no way by default for emails not to be added in campaign_delivery_log, unless the code is somehow altered or maybe the insert into the campaign_delivery log fails for whatever reason, but if that happens, we issue an exception and halt the execution and the subscriber will receive the email once again and the delivery_log table populated.
How large was delivery_log in your tests? I'd imagine it becomes a issue when delivery_log is in the GBs.
I'll have to look into it more, but I'm pretty sure failed emails (when using temp queue tables) are not being recorded in delivery_log for me.
Okay just ran some tests. My delivery_log is roughly 20GB. It took 62 secs to search for any email with GIVEUP status. However, it took 0.0167 secs to search for any email with GIVEUP status and a specific campaign_id, so it doesn't take too long if you include a campaign_id (depending on if you consider 0.0167 to be long). This can probably be optimized by adding an index on the status field or an index for campaign_id and status.
I confirm you are right. I now see failed emails in delivery_log, not sure what happened before.
twisted1919
Administrator
Staff memberWe use campaign_id so we're on the fast lane
Also repetead hits in that table increases access speed, so overall we're good with this for now, plus, not all people have such big tables.
OptiBiz1
Active Member
...and this thread, my dear "MW colleagues", is the ultimate proof that the creator of Mailwizz (@twisted1919 ) is super adaptive and supportive. It also proves how important the community vs creator communication is. Thanks @Lakjin !
True but I only care about myself xDWe use campaign_id so we're on the fast lane
Also repetead hits in that table increases access speed, so overall we're good with this for now, plus, not all people have such big tables.
What about adding that campaign_id + status index? That should be an easy thing to do, no?
twisted1919
Administrator
Staff memberYes sure, but here's the catch, i am so afraid of altering that table during upgrades... it can take hours or even days to add that index on considerably large tables, just out of curiosity, try to add your own index on those two columns and see how long it takes.
That's a fair point. I can add the index on my own table and let you know. However, one request. I've added indexes in the past and then future MailWizz updates add the same index. However, upgrade fails because I already have the index. So in future updates of MailWizz, can you add a SQL check to add index only if index does not exist?
By the way, is there a reason why I would want to keep more than 14 days of delivery_log?
twisted1919
Administrator
Staff memberJust name them with a unique prefix, i,e: lakjin_whatever_columna_columnb_index
Yeah, if you need campaign stats for older campaigns, you need this.
You can also look into table partitioning in mysql to improve the access to this table.
Actually, I just noticed there is already a campaign_id and status index, cid_status... whoops.
twisted1919
Administrator
Staff memberSometimes it;s good to anticipate the usage of indexes