We often import contacts into segments that we use to send bulk emails. The contacts are often already in Mautic but the data we use to do the segmenting is in another system so we import new segments several times a week or more often. We currently have several hundred segments in our Mautic instance.
I’ve noticed that our cron task for updating segments “mautic:segments:update” takes a very long time to complete. I’m actually not sure if it is successfully finishing.
The segments created by the imports for bulk emails should not be updated. I see these listed in the output when running the task directly. It skips through them quickly but should I be deleting these old segments as they really provide no value anymore. Would deleting the segments have any impact on reports or contacts themselves? It seems that this is just a good housekeeping task but I want to make sure I’m not going to regret deleting the older segments.
I do see that some of our dynamic filter based segments take a really long time. The filter for the segment is defined as a minimum number of page views during the last week as the criteria. Is this just an expensive query to run on an instance with a little over 200,000 identified contacts and an additional 166,000 anonymous contacts? Any advice on steps I can use to increase the speed of updating Mautic segments or other best practices.
Hello @jsieber, how much RAM do you have on your server?
Is it a dedicated server or VPS or shared with other Apps/sites?
Hi @Yosu_Cadilla,
This is a EC2 instance running only Mauitc with the database running on RDS. The instance is a t3.medium with 4GB of Ram. Currently, I have 1,512MB of ram allocated to PHP. I’m going to try increasing that to see if it helps. Is there a good way to determine how much RAM to allocate to PHP form Mautic? Thanks for your insight.
-John
When manually watching the update segment task it appears to hang on any dynamic segment using criteria similar to the following:
Page sessions greater than or equal to 5
Visted URL contains https://example.com/some-url-stubb/
Date Last Active greater than or equal to -1 months
We have numerous segments with similar setups and they just seem to hang when the task gets to them.
I’ve checked the log for memory errors or execution time outs but am not finding anything related to those issues.
This is the SQL statement that hangs when we try to run a segment update.
SELECT count(leadIdPrimary) count, max(leadIdPrimary) maxId, min(leadIdPrimary) minId FROM (SELECT DISTINCT l.id as leadIdPrimary, VUYJeSIc.lead_id AS VUYJeSIc_lead_id FROM ma_leads l LEFT JOIN ma_lead_lists_leads VUYJeSIc ON VUYJeSIc.lead_id = l.id and (VUYJeSIc.leadlist_id = 254) WHERE (EXISTS(SELECT count(id) FROM ma_page_hits NmPUHYDk WHERE (l.id = NmPUHYDk.lead_id) AND (NmPUHYDk.email_id IS NULL) AND (NmPUHYDk.redirect_id IS NULL) AND (NOT EXISTS(SELECT XlFYmfoH.id FROM ma_page_hits XlFYmfoH WHERE (l.id = XlFYmfoH.lead_id) AND (XlFYmfoH.date_hit > NmPUHYDk.date_hit - INTERVAL 30 MINUTE) AND (XlFYmfoH.date_hit < NmPUHYDk.date_hit))) HAVING count(id) >= '5')) AND (EXISTS(SELECT NULL FROM ma_page_hits PUXmYbsy WHERE (PUXmYbsy.lead_id = l.id) AND (PUXmYbsy.url LIKE '%https://www.example.com/guided-tours/%'))) AND (l.last_active >= '2019-12-08') AND (VUYJeSIc.lead_id IS NULL)) sss
These queries will just run indefinitely without completing and spike the CPU on the dedicated RDS database.
Outside of the segment updating issues our instance runs well.