My Mautic version is: 3.3.4
My PHP version is: 7.4
My Database type and version is: MariaDB 10.5.12
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:
max_statement_time = 600was 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.
optimizer_switch=rowid_filter=offwas 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