Open Discussion and Suggestions to Address Mailwizz Speed and Scalability Issues

[pradeep sharma]

Hi @twisted1919,

I'd like to initiate a detailed discussion on potential changes aimed at enhancing speed and scalability in future releases of Mailwizz. Having dedicated over a decade to developing a specialized email setup based on Mailwizz V 1.3, I've achieved impressive scalability, capable of processing up to 300 million emails per hour. This setup continues to serve us well, and I am eager to share insights that could benefit upcoming versions, possibly Mailwizz 3.

1. Optimizing MYSQL Usage in Send-Campaign Processes:The current cron job for sending campaigns, while well-crafted, is not optimized from a MYSQL perspective. It operates by processing emails in batches, issuing a MYSQL query with a limit parameter for each batch. For example, if a campaign involves sending 100k emails in batches of 1k, it results in 100 MYSQL queries, each gathering and then limiting data. This repetitive querying is inefficient.

A more efficient approach would involve a MYSQL-based queue system, where only subscriber ids/uids are queued in a table and processed according to a scheduled timeline. This system would benefit from MYSQL 8.0's capability for row-level locking, which is ideal for managing queues and improving processing throughput.

2. Addressing Inefficiencies in Campaign Delivery Logs:We currently use a single table to store all campaign delivery logs, which is manageable for a small volume of daily emails but becomes problematic with larger volumes due to the need to periodically delete old logs. MYSQL operations for deleting data are resource-intensive, affecting CPU and memory usage significantly.

A practical solution would involve segmenting the log storage into multiple tables—potentially 365 tables corresponding to each day of the year, or 12 tables for each month. This setup would allow us to direct logs to the appropriate table based on the campaign's send date and significantly streamline the deletion process by simply truncating whole tables when logs expire.

3. Streamlining Tracking Tables for Open and URL Clicks:Similarly to delivery logs, tracking tables for email opens and URL clicks should be segmented to improve management and facilitate easier deletions. This method would allow for automatic truncation of these tables post-expiration, thereby reducing the overhead on database operations.

4. Consideration for a Separate Database Host for Log Storage:To further optimize performance and scalability, it could be beneficial to host log tables on a separate database server. This separation would help in isolating heavy log data operations from the main operational databases, thereby improving overall system performance.

These enhancements are designed not only to address current inefficiencies but also to scale up the capabilities of Mailwizz as demand and data volumes grow. I look forward to exploring these possibilities further and hope they can be considered for integration into future versions of Mailwizz.


I have few more suggestion which i ll post later on another post.

Looking forward

I have attached some screen shot for referene.


Regards

Pradeep Sharma
Skype: sexy_virus2

 

[twisted1919]

I am always open to discussions related to improving MailWizz's performance, so I do have some answers to the point above, but regardless of what I am about to say, you need to keep in mind that MailWizz is designed to work in many environments, some with more resources, some with less, so we need to find common ground for everyone. For those who seek more than the stock app can offer, we do have an extension targeted to High Volume Senders, which for now is a work in progress and under testing, but like 99% done and ok.

Anyway, back to your points:

1. Optimizing MYSQL Usage in Send-Campaign Processes:The current cron job for sending campaigns, while well-crafted, is not optimized from a MYSQL perspective. It operates by processing emails in batches, issuing a MYSQL query with a limit parameter for each batch. For example, if a campaign involves sending 100k emails in batches of 1k, it results in 100 MYSQL queries, each gathering and then limiting data. This repetitive querying is inefficient.

A more efficient approach would involve a MYSQL-based queue system, where only subscriber ids/uids are queued in a table and processed according to a scheduled timeline. This system would benefit from MYSQL 8.0's capability for row-level locking, which is ideal for managing queues and improving processing throughput.

Using limit and offset to move through records in mysql was indeed not our best choice. However, even with a queue, those records have to be copied in the queue in a way or another, which involves queries.
And speaking about queues, we already got this covered in queue tables.

2. Addressing Inefficiencies in Campaign Delivery Logs:We currently use a single table to store all campaign delivery logs, which is manageable for a small volume of daily emails but becomes problematic with larger volumes due to the need to periodically delete old logs. MYSQL operations for deleting data are resource-intensive, affecting CPU and memory usage significantly.

This is the real problem most of the time. This is why we've added, in app, the ability to remove logs, so you can free up this table.
Our High Volume Sender table will create separate delivery logs tables for each campaign, thus alleviating this problem.
Other than this, keeping this table as small as possible is the only way, i.e: remove logs and/or remove campaigns you don't need anymore.

3. Streamlining Tracking Tables for Open and URL Clicks:Similarly to delivery logs, tracking tables for email opens and URL clicks should be segmented to improve management and facilitate easier deletions. This method would allow for automatic truncation of these tables post-expiration, thereby reducing the overhead on database operations.

I don't see these tables to be problematic at all, the quantity of data in them is really negligible compared to the above.

4. Consideration for a Separate Database Host for Log Storage:To further optimize performance and scalability, it could be beneficial to host log tables on a separate database server. This separation would help in isolating heavy log data operations from the main operational databases, thereby improving overall system performance.

That's really a choice MailWizz users should do if they feel like so, MailWizz itself should not care about the database underneath. You could as well use TiDB instead of MySQL and get a few dozen if not hundred of times the performance. But always is about tradeoffs.


As it stands right now, MailWizz's performance is really good given the right settings and infrastructure.
We benchmarked it at https://www.mailwizz.com/blog/mailwizz-for-high-volume-senders/ with almost stock settings.
If you also add the High Volume Sender extension on top, you're getting mad results.

That being said, I am glad you were able to modify MailWizz as you think better, the whole point of the app is to be extensible.
Our point of view is that for now MailWizz is fast enough.

 

[pradeep sharma]

This is the real problem most of the time. This is why we've added, in app, the ability to remove logs, so you can free up this table.

Log table is not a problem at all, even though how big it is.. the Real Problem Starts .....when We start Deleting those logs when it gets expired@@

Our High Volume Sender table will create separate delivery logs tables for each campaign, thus alleviating this problem.

Is this Setting available in the current version of Mailwizz.?

That's really a choice MailWizz users should do if they feel like so, MailWizz itself should not care about the database underneath.

Is there any option to define a separate database for LOG tables? which i am not aware...

I don't see these tables to be problematic at all, the quantity of data in them is really negligible compared to the above.

Make Sense

e do have an extension targeted to High Volume Senders, which for now is a work in progress and under testing, but like 99% done and ok.

ANY ETA for this!!!

 

[twisted1919]

s this Setting available in the current version of Mailwizz.?

ANY ETA for this!!!

Not really, we keep it internally for now because we're not sure 100% on how to market and price it.

Is there any option to define a separate database for LOG tables? which i am not aware...

My point was that if you care to do this, you're free to do it on your own

 

[pradeep sharma]

Not really, we keep it internally for now because we're not sure 100% on how to market and price it.

Hi @twisted1919

Just dropping in with a thought or two on your project. When it comes to gathering audience feedback and figuring out your pricing strategy, it’s all about striking that sweet spot. Given the hard work you’ve put into this, I reckon you could offer it at a friendly price—maybe even sneak it in just under Mailwizz’s price tag to make it extra tempting!

On another note, would you consider letting me jump in as a tester for this extension? I’d love to be at the front of the line to snag it,

Pradeep

 

[pradeep sharma]

Our High Volume Sender table will create separate delivery logs tables for each campaign, thus alleviating this problem.

Any ETA for this, extremely waiting...!!!