Hi,
I have a question, we have a relatively strong instance of Mautic running on a 4 Dedicated CPU and 8GB of Ram only for the application server, the DB server is 32 Dedicated CPU and 64GB of RAM.
My problem is when trying to open a big segment I am getting timeouts. Any thoughts?
We have tweaked the number of child workers accordingly and in general the system runs very fast
I am also seeing the same problem on a client’s instance. Have you managed to figure something out for this?
Likewise here…running on VPS, only application on the server, 8 cores, 16GB RAM, 320GB NVM storage, otherwise everything runs very well. When trying to open these segments, it pretty much renders the site unusable until the process stops. I’ve tried upping memory limit (512MB) but see no difference. Have also trimmed up database some, no help. Site has about 1 million contacts and we send about 250,000 emails per week. The offending segments still work fine. They just either take forever to open or timeout.
Chris
Is there any chance to post the top SQL queries from the slow query log?
Yes…here is last 24 hours, which should include quite a few entries since I was testing most of yesterday:
SELECT el.date_added - INTERVAL 10 SECOND FROM lead_event_log el WHERE (el.object = ‘segment’) AND (el.bundle = ‘lead’) AND (el.object_id = 259) ORDER BY el.date_added ASC LIMIT 1;
SELECT l.* FROM leads l LEFT JOIN users u ON u.id = l.owner_id WHERE (l.date_identified IS NOT NULL) AND ((l.firstname LIKE ‘%21missy25@gmail.com%’) OR (l.lastname LIKE ‘%21missy25@gmail.com%’) OR (l.email LIKE ‘%21missy25@gmail.com%’) OR (l.company LIKE ‘%21missy25@gmail.com%’) OR (l.city LIKE ‘%21missy25@gmail.com%’) OR (l.state LIKE ‘%21missy25@gmail.com%’) OR (l.zipcode LIKE ‘%21missy25@gmail.com%’) OR (l.country LIKE ‘%21missy25@gmail.com%’)) ORDER BY l.last_active DESC, l.id DESC LIMIT 100;
SELECT ll.event_id, ll.campaign_id, ll.trigger_date, ll.lead_id, e.name AS event_name, e.description AS event_description, c.name AS campaign_name, c.description AS campaign_description, ll.metadata, CONCAT(CONCAT(l.firstname, ’ '), l.lastname) AS lead_name FROM campaign_lead_event_log ll LEFT JOIN campaign_events e ON e.id = ll.event_id LEFT JOIN campaigns c ON c.id = e.campaign_id LEFT JOIN leads l ON l.id = ll.lead_id WHERE (ll.is_scheduled = 1) AND (e.type = ‘email.send’) ORDER BY ll.trigger_date ASC LIMIT 4;
SELECT DISTINCT l.*, l.id FROM leads l WHERE (l.email_tag = ‘Day1’) AND (l.segment = ‘Retargeting’) AND (l.id NOT IN (SELECT par20.lead_id FROM lead_lists_leads par20 WHERE par20.leadlist_id = ‘214’)) AND (l.id <= 1148368) AND ((l.date_added <= ‘2024-01-10 17:18:05’) OR (l.date_added IS NULL)) LIMIT 300;
SELECT DISTINCT l.*, l.id FROM leads l WHERE (l.email_tag = ‘Day1’) AND (l.segment = ‘Retargeting’) AND (l.id NOT IN (SELECT par20.lead_id FROM lead_lists_leads par20 WHERE par20.leadlist_id = ‘214’)) AND (l.id <= 1148365) AND ((l.date_added <= ‘2024-01-10 17:16:02’) OR (l.date_added IS NULL)) LIMIT 300;
SELECT DISTINCT l.*, l.id FROM leads l WHERE (l.email_tag = ‘Day1’) AND (l.segment = ‘Retargeting’) AND (l.id NOT IN (SELECT par20.lead_id FROM lead_lists_leads par20 WHERE par20.leadlist_id = ‘214’)) AND (l.id <= 1148372) AND ((l.date_added <= ‘2024-01-10 17:19:05’) OR (l.date_added IS NULL)) LIMIT 300;
SELECT DISTINCT l.*, l.id FROM leads l WHERE (l.email_tag = ‘Day1’) AND (l.segment = ‘Retargeting’) AND (l.id NOT IN (SELECT par20.lead_id FROM lead_lists_leads par20 WHERE par20.leadlist_id = ‘214’)) AND (l.id <= 1148395) AND ((l.date_added <= ‘2024-01-10 17:24:01’) OR (l.date_added IS NULL)) LIMIT 300;
SELECT DISTINCT l.*, l.id FROM leads l WHERE (l.email_tag = ‘Day1’) AND (l.segment = ‘Retargeting’) AND (l.id NOT IN (SELECT par20.lead_id FROM lead_lists_leads par20 WHERE par20.leadlist_id = ‘214’)) AND (l.id <= 1148375) AND ((l.date_added <= ‘2024-01-10 17:20:02’) OR (l.date_added IS NULL)) LIMIT 300;
SELECT DISTINCT l.*, l.id FROM leads l WHERE (l.email_tag = ‘Day1’) AND (l.segment = ‘Retargeting’) AND (l.id NOT IN (SELECT par20.lead_id FROM lead_lists_leads par20 WHERE par20.leadlist_id = ‘214’)) AND (l.id <= 1148349) AND ((l.date_added <= ‘2024-01-10 17:13:02’) OR (l.date_added IS NULL)) LIMIT 300;
Chris
Also here are slow pages from PHP log:
/index.php?action=lead:getLeadCount&id=241&_=1704905932282&mauticUserLastActive=2&mauticLastNotificationId=114944
/index.php?_=1704905932281&mauticUserLastActive=2&mauticLastNotificationId=114944
/index.php?action=lead:getLeadCount&id=242&_=1704905932283&mauticUserLastActive=362&mauticLastNotificationId=114944
/index.php?_=1704840316952&mauticUserLastActive=164&mauticLastNotificationId=113727
/index.php?action=lead:getLeadCount&id=243&_=1704905932284&mauticUserLastActive=722&mauticLastNotificationId=114944
/index.php?search=&tmpl=list&_=1704839815972&mauticUserLastActive=1047&mauticLastNotificationId=
/index.php?_=1704839854151&mauticUserLastActive=1020&mauticLastNotificationId=113727
/index.php?_=1704905932285&mauticUserLastActive=951&mauticLastNotificationId=114944
/index.php?mauticUserLastActive=135&mauticLastNotificationId=113734
/index.php?mauticUserLastActive=79&mauticLastNotificationId=113733
I turned on the logs for just a few seconds when trying to open one of the problematic segments to try and narrow down the cause.
Opening the segment added this to the general_log table:
SELECT h0_.is_published AS is_published_0, h0_.date_added AS date_added_1, h0_.created_by AS created_by_2, h0_.created_by_user AS created_by_user_3, h0_.date_modified AS date_modified_4, h0_.modified_by AS modified_by_5, h0_.modified_by_user AS modified_by_user_6, h0_.checked_out AS checked_out_7, h0_.checked_out_by AS checked_out_by_8, h0_.checked_out_by_user AS checked_out_by_user_9, h0_.id AS id_10, h0_.name AS name_11, h0_.description AS description_12, h0_.alias AS alias_13, h0_.public_name AS public_name_14, h0_.filters AS filters_15, h0_.is_global AS is_global_16, h0_.is_preference_center AS is_preference_center_17, h0_.last_built_date AS last_built_date_18, h0_.category_id AS category_id_19 FROM hatcoma_lead_lists h0_ WHERE h0_.id = ‘538’
Running that query returns the lead_lists entry for that segment. 1 row. It’s not the issue.
Nothing was added to the slow_log table as a result of trying to open the segment, even if I wait until the request times out.
When I first tested this, long_query_time was set at the default 10 seconds, I’ve since re-tested this with long_query_time set to .01 seconds, and still nothing shows up in the slow_log table.
The slow page log shows counts to be the slowest. Which is a known mysql issue. However, the sql log isn’t showing any count queries. So that’s probably just a normal query log, not a slow query log.
Was there anything in my logs that gave any clues to the issue??
Chris
When you open a segment, you can see a list of contacts belonging to that segment, separated into pages. Normally for a feature like this, you only query the items you intend to display on a given page, and you offset your query results accordingly when you change pages. I initially suspected that Mautic might actually be pulling back all of those records at once and then dividing them into pages afterwards. After looking at the requests Mautic sends back to the server when you change pages, though, it seems like that is not the case.
I turned on the slow query log in the database and tried out various time thresholds to try to catch something, but nothing I saw in there seemed to be the culprit. Things didn’t even get entered into the log until I set the threshold to <.1 seconds. If it was a query issue I should have seen something at a much higher threshold.
If Mautic is not pulling back the entire list of contacts like in case 1, and there are no obviously slow queries in case 2, then the problem is somewhere between getting the data from the database and sending the reply back to the front end, so whatever processing Mautic does with the results from the database must be time-consuming, like some sort of calculations are being run on them. My main suspect for this right now is the chart showing the changes to contacts. For the particular segment I was looking at with 39k contacts, it took ~100 seconds to open it. Just hitting save on the date range without changing it also produced the same delay.
That makes sense as we’ve seen slow login times based on the amount of data that various widgets are asked to display on the dashboard upon login.
Is there a way to limit data displayed in the chart like there is on the dashboard widgets?
I also have a database guy that said he’s seen this one other time with another application that uses SQL and changing the data type from text/longtext to blob fixed it on that application. I’m NO database expert so I’m not even sure I understand the difference between blob and text, but I’m going to try that on our staging site as it has a nice database interface for making that change.
Chris
In configurations > system settings > system defaults there is a date range filter default, which sounds like the relevant setting. However, changing it doesn’t seem to do anything.
Yeah I had already tried that to no avail. Another thing that we’ve never gotten to work is changing the default page that opens after login. Doesn’t seem to be a way to change it from the Dashboard.
Here’s more info on this issue. Some may be relevant, some may not.
Problem seems to occur primarily on segments that I’ve created by “cloning” an existing segment and updating/editing it.
This problem is fairly recent, with no reports from users about this prior to early Fall 2023.
Our server SHOULD be large enough. A VPS on Digital Ocean; only app on it, 8 cores, 16GB RAM 320GB nvm drive. All other parts of Mautic run very well.
Sometime around this starting I did add a cron job that clears the cache each day. Could THIS be why some of this is occurring? Mautic having to recreate lots of cached data? If so, seems like a potential bug?? I just removed that cron job from both staging and live to see if it helps to keep cache in place and only clear when issues arise.
(this is a strange one) Several users report not being able to open (or timing out) various forms and other elements. But I can login using these users’ account credentials and I can open, edit, save, clone them just fine with no issues. Literally 5 minutes after they report the issue!
Client’s users are all behind a VPN. Their IT folks swear nothing is in place to slow, block or otherwise limit Mautic. I’ve requested a VPN account so I can test in same environment.
Thanks so much for your help on this
Chris
Update on this: The company I maintain this Mautic instance for has hired a database guy to look into these issues and he’s found some things and has some proposed solutions we’re going to try. When I get results (worked or didn’t) I will post all here in effort to help anyone else with this problem. Note this didn’t start occurring until the database grew to over about 50GB.
Chris
Did your database guy make any progress?
I know this thread is dated, but he was working on making several changes to both PHP and MySQL settings on the server. I will check back and report what was done.
Hi there, just checking back on this.
None of the server changes made a noticeable difference in this issue on the 4.x version of Mautic. We’re in the process of upgrading our Mautic instance to 5.x to see if that might solve this issue. We’re also splitting our database into at least 2 different installations of Mautic, one for promotional emails and another for transactional emails. So don’t waste your time with messing with server settings to solve this issue on very large databases.
Hello everyone!
I’m having the same issue, trying to open a segment with 20k 30k contacts, take a while to load, while open a segment with 1k 2 3k or less, it’s super fast.
Initially I think I can resolve that adding some new index to the database, (I already speed up a lot the Contacts page by adding some indexes) but seems here with the segment that this is not the problem.
Any clue @ chrisb_hallcomm?
I’m also thinking to upgrading to Mautic 5 but in other thread I asked, some people are not recommending yet (for production).
Mauricio