Slow segment updates? Try this

Product Support
1

Your software
My Mautic version is: 3.3.4
My PHP version is: 7.4
My Database type and version is: MariaDB 10.5.12

Your problem
My problem was: certain queries were running for VERY long times (10 minutes to several hours), eventually stacking up and taking the MariaDB server down.

How I fixed the problem:
I added a couple of lines to my.cnf (specifically /etc/mysql/mariadb.conf.d/50-server.cnf) to do two things:

  1. max_statement_time = 600 was added to prevent the super long queries from stacking up. Instead any query taking 10 minutes or more just fails and an error appears on the log. This kept the server from crashing, but segments stopped rebuilding because the cron job failed every time it hit the evil segment(s) that the server couldn’t handle.
  2. optimizer_switch=rowid_filter=off was added because after some digging, I found that MariaDB was inappropriately optimizing certain queries for segment building, especially those with filters referencing other segments and with filters on the contact and company level. Once I added this, the cron jobs rebuilding segments started finishing again.

These two fixes have brought the server back to life, and now I get errors in the log when scheduled reports or segment queries want to take down my server instead of the whole server crashing, and the queries that previously were timing out no longer do so.

This is far from an elegant solution. Ideally, there would be some fix for Mautic to make these queries more efficient, but since they are built by Doctrine and users, I wasn’t sure if the could be optimized, and it was clear that they’d at least run without rowid_filter optimization.

Some of this was over my head, but here’s the documentation on rowid filter optimization: Rowid Filtering Optimization - MariaDB Knowledge Base

6 Likes
2

Did these slow queries have OR statements by chance? I noticed that OR queries can be very slow and would suggest they be updated to be UNIONs instead to properly handle indexes on the columns. However, doctrine querybuilder doesn’t support UNIONs so it would actually be a lot of work to make this function as desired.

Also, it seems like segments that include or exclude other segments can be slow as it doesn’t actually just include/exclude users from the lead_lists_leads table it does a JOIN which seems incorrect to me as the users returned by that join might not match those on the segment depending on build order and if something were to fail on the actual include/exclude segment building.