henkedk
Member
Hey @twisted1919
So, I recently migrated to a bigger server - hope was to get better performance, as the cron every minute would kill the old machine for 30-45 seconds at a time.
New server, same result, so I hired a SQL guy to look into it, as it is the DB draining all the power - he gave me some feedback to pass on to you - the range of the following query is way to big, so eg in my case, it selects 692k rows every time it runs.
SELECT `t`.`list_id` AS `t?_c?`, `t`.`subscriber_id` AS `t?_c?`, `t`.`subscriber_uid` AS `t?_c?`, `t`.`email` AS `t?_c?`, `t`.`ip_address` AS `t?_c?`, `t`.`source` AS `t?_c?`, `t`.`date_added` AS `t?_c?` FROM `mw_list_subscriber` `t` LEFT OUTER JOIN `mw_campaign_delivery_log` `deliveryLogs` ON (`deliveryLogs`.`subscriber_id`=`t`.`subscriber_id`) AND (deliveryLogs.campaign_id = :cid) WHERE (((((t.list_id=:ycp?) AND (t.status=:ycp?)) AND (t.source != :src)) AND (t.date_added >= :cdate)) AND (DATE_ADD(t.date_added, INTERVAL ? MINUTE) <= NOW())) AND (deliveryLogs.subscriber_id IS NULL) LIMIT ? OFFSET ?
I honestly think you guys should look into a bit of SQL optimization in your sendout method, as MW is currently not capable of handling lists with a big amount of subscribers (100k+) very well, even if I push it on a 32 threads machine with SSD and 128 GB ram that does nothing else than run MW and is highly optimized for DB usage.
Another solution (optimal, but ofc way more time consuming) would be (I already mentioned this earlier) to turn your job-building around to select all subscribers to send to at the start of a campaign and dump them into a tmp table and operate the campaign based on that - basically if structured well, it would give the same heavy impact on the system one time at the campaign start as it does now every minute the cron runs.
So, I recently migrated to a bigger server - hope was to get better performance, as the cron every minute would kill the old machine for 30-45 seconds at a time.
New server, same result, so I hired a SQL guy to look into it, as it is the DB draining all the power - he gave me some feedback to pass on to you - the range of the following query is way to big, so eg in my case, it selects 692k rows every time it runs.
SELECT `t`.`list_id` AS `t?_c?`, `t`.`subscriber_id` AS `t?_c?`, `t`.`subscriber_uid` AS `t?_c?`, `t`.`email` AS `t?_c?`, `t`.`ip_address` AS `t?_c?`, `t`.`source` AS `t?_c?`, `t`.`date_added` AS `t?_c?` FROM `mw_list_subscriber` `t` LEFT OUTER JOIN `mw_campaign_delivery_log` `deliveryLogs` ON (`deliveryLogs`.`subscriber_id`=`t`.`subscriber_id`) AND (deliveryLogs.campaign_id = :cid) WHERE (((((t.list_id=:ycp?) AND (t.status=:ycp?)) AND (t.source != :src)) AND (t.date_added >= :cdate)) AND (DATE_ADD(t.date_added, INTERVAL ? MINUTE) <= NOW())) AND (deliveryLogs.subscriber_id IS NULL) LIMIT ? OFFSET ?
I honestly think you guys should look into a bit of SQL optimization in your sendout method, as MW is currently not capable of handling lists with a big amount of subscribers (100k+) very well, even if I push it on a 32 threads machine with SSD and 128 GB ram that does nothing else than run MW and is highly optimized for DB usage.
Another solution (optimal, but ofc way more time consuming) would be (I already mentioned this earlier) to turn your job-building around to select all subscribers to send to at the start of a campaign and dump them into a tmp table and operate the campaign based on that - basically if structured well, it would give the same heavy impact on the system one time at the campaign start as it does now every minute the cron runs.
