Old 10k list uses high CPU, new 40k list uses low CPU

M

mwizz98

Member
I have an old list with 10k subscribers, when I send to it, CPU usage goes to 100%, and it sends 3k emails/hour, which is slower than the configuration settings.

I have a new list with 40k subscribers, when I send to it, CPU usage remains under 50%, and it sends at 10k emails/hour, and I can send even faster if I wanted.

Everything is on the same server, and is using the same settings and the same delivery server. The only difference is the list being sent to.

The process eating up the cpu time is mysqld.

I archived most campaigns of the old list, but it didn't seem to make a difference.

Before making any changes to the configuration of MySQL I wanted to post this here and get some thoughts on why this is happening. Thank you.
 
Last edited:
Hello,
Are you sending to the full list, or are you using some segments of the list?

Cosmin
 
Thank you for your answer. I will send out another campaign in a couple of days and I'll keep an eye on the stats and report back if there is any problem.

While we're on this subject, I'd like to get your thoughts on console.php. I have everything instrumented in New Relic and I noticed that console.php takes a very long time. See charts below (the first chart shows clock time, not cpu):

mw-nr1.JPG

mw-nr2.JPG

When digging deeper into the trace details of slow transactions, we can see the component causing this:

mw-nr3.JPG

There are no campaigns sending, very little traffic, very low cpu utilization, basically nothing going on in Mailwizz. Any thoughts on that? Thank you.
 
Hello,
That is the queue running. The queue is used for different tasks across the application, like exporting/importing subscribers, etc. It is running every minute and the fact that is not using any CPU is because maybe it has nothing to process at that time.

Cosmin
 
@twisted1919 as promised I sent out another campaign and tracked the issue mentioned in the OP while it as happening, 200% cpu usage by MySQL, load average jumps to 15, even though i am sending to a small list of 10k subscribers

Here's what I found:

mw-mysql1.JPG

when I click on the top entry:

mw-mysql2.JPG

all other entries are exactly the same and say the same thing



For comparison, when I send to a newer list with 40k subscribers, the same thing happens, but not as bad:

mw-mysql3.JPG

mw-mysql4.JPG



The campaigns are the same, everything is the same, no custom fields or anything else like that

Does that tell you anything?

Thanks
 
Well, something is different since we get very different timings. It's just what's different is not that obvious.
One possible problem would be that the smaller list is older, thus is more fragmented on disk and also has much more campaigns than the larger list, which means a different distribution of list/subscribers data in the campaign delivery log table.
This might also be a sign that the mysql's innodb pool buffer size is not sized properly, you might want to look at this article and see if you can adjust the mysql server.
Lastly, you can look into hiring a database enginner to help with your workload.
 
I think you are exactly right, because the smaller list is many years old and I did not have the logs set to be deleted, so the campaign delivery logs table for that list grew to many GBs, and I think when it does that outer join it struggles, or something like that.

I already followed the directions about optimizing MySQL settings, so the buffer size is pretty good.

So far a couple of GBs have been deleted from that table so I just ran

SQL: 
mysql> OPTIMIZE TABLE mw_campaign_delivery_log;
+-----------------------------------+----------+----------+-------------------------------------------------------------------+
| Table                             | Op       | Msg_type | Msg_text                                                          |
+-----------------------------------+----------+----------+-------------------------------------------------------------------+
| mailwizz.mw_campaign_delivery_log | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| mailwizz.mw_campaign_delivery_log | optimize | status   | OK                                                                |
+-----------------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (9 min 37.47 sec)

As far as I know, OPTIMIZE is usually safe to do. If the operation above is unsafe or breaks anything, please let me know.

(Also, please consider creating the option to disable campaign delivery logs altogether.)

Thanks!
 
mwizz98 said:
As far as I know, OPTIMIZE is usually safe to do. If the operation above is unsafe or breaks anything, please let me know.
Click to expand...
See the accepted answer from https://stackoverflow.com/questions/54850311/analyze-optimize-on-innodb-tables

mwizz98 said:
please consider creating the option to disable campaign delivery logs altogether.
Click to expand...
Not an option, they are there for a reason, they keep track of subscribers whom received your campaigns so we don't send them same campaign twice.
 
UPDATE: PROBLEM SOLVED

The campaign delivery logs table was originally 5.7 GB. I set Mailwizz to delete all logs except for the last 10 days and ran the daily cleaner, which took over 30 mins. Then I ran another OPTIMIZE operation on it. The table size finally shrunk to 50 MB.

I wanted to see if that would help solve the puzzling issue of CPU going to 100%.

Before, sending to my old list of 10k subscribers took about 4 hours and CPU was around 100% while it was sending. After deleting the logs, CPU is only around 30% and the campaign finishes sending in 1 hour. It can probably send even faster but I am capping it.

I wonder how large ESPs deal with this. They probably move delivery logs to a separate archive table when a campaign finishes, or something like that.

The mystery is solved. @twisted1919 as always thanks for your help and patience.
 
You must log in or register to reply here.
Back
Top