Your software
My Mautic version is: 2.16.1
My PHP version is: 7.2
Your problem
My problem is: Loading large segments time out regularly, with mysql (mariadb) being the bottleneck.
These errors are showing in the log:
2020/04/20 14:58:23 [error] 12763#12763: *135 upstream timed out (110: Connection timed out) while reading response header from upstream, client: xx.xx.xx.xx, server: xx.xxxxxx.com, request: "GET /s/segments/view/21 HTTP/2.0", upstream: "fastcgi://unix:/run/php/php7.2-fpm.sock", host: "xx.xxxxxx.com"
Steps I have tried to fix the problem:
- Installed blackfire to analyze requests
- increased nginx timeout to 3 mins
Here is the query that is taking forever as reported by Blackfire:
select date_format(...) as date, ? from lead_event_log t where (t.object = ?) and (t.bundle = ?) and (t.action = ?) and (t.object_id = ?) and (t.date_added between ? and ?) and (not exists (select ? from lead_event_log el where (el.lead_id = t.lead_id) and (el.object = ?) and (el.bundle = ?) and (el.action = ?) and (el.object_id = ?) and (date_format(...) = date_format(...)) and (el.date_added > t.date_added))) group by date_format(...) order by date_format(...) asc limit ?
Any ideas?