Your software
My Mautic version is: 4.1.0
My PHP version is: 7.4.27
My Database type and version is: 8.0.27
Your problem
My problem is:
The contact management is extremely slow to work with. I have 220k contacts on my database.
Mautic runs on a dedicated bare-metal server (32 cores, nVME drive and 128GB of ram) so this cannot be a hardware issue.
Loading the contact page, searching for a contact or anything related to contacts take up to several minutes to return results.
These errors are showing in the log:
This is related to MySQL. Slow query log reports queries like this:
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 ‘%poutsakos@%’) OR (l.lastname LIKE '%poutsakos@%’) OR (l.email LIKE ‘%poutsakos@%’) OR (l.company LIKE '%poutsakos@%’) OR (l.city LIKE ‘%poutsakos@%’) OR (l.state LIKE '%poutsakos@%’) OR (l.zipcode LIKE ‘%poutsakos@%’) OR (l.country LIKE '%poutsakos@%’)) ORDER BY l.last_active DESC LIMIT 100;
There are a few things off the bat you can try and do to speed things up a bit.
Are you using nginx or apache server ?
If you are using apache enable http2
sudo a2enmod http2
Then open the SSL virtual host file.
sudo nano /etc/apache2/sites-enabled/mautic-le-ssl.conf
Put the following directive after the opening <VirtualHost *:443> tag.
Protocols h2 http/1.1
Save and close and restart apache2 service
If you are nginx go into /etc/nginx/confd/mautic.conf and find the line
listen 443 ssl; # managed by Certbot
and add in http2 after the ssl
listen 443 ssl http2; # managed by Certbot
Restart your nginx server
2. Go into your /etc/php/7.4/fpm/php.ini file and change the memory_limit = 128M to something much higher, especially if you have such a strong server running. don’t forget to reload php fpm after this: systemctl restart php7.4-fpm
Check out your cronjob and how they are configured.
Run top command to see what is eating your CPU & Memory
There are some sql tricks to be done as well, however I have not got these down to a T to recommend yet.
The speed issue is related to SQL queries. this is not a server related issue. The performance meters are all very healthy; there’s no bottleneck on the server. All CPU, Memory and I/O look healthy.
The problem is on the application side and more specifically on the database. When we load the dashboard page of Mautic, it takes up to 10 seconds to load and we can see the sql queries taking waiting to respond on the mysql monitoring.
Mind you however that we have a very busy website running, with 500k page visits per day. Our page_hits table is 20GB.
The only workaround that we’ve found is to do:
mautic:maintenance:cleanup --days-old=60
That fixes things a bit. If we keep 360 days, the speed is very bad.
Are there updates to optimize the database structure?
When I first got involved with enterprise level Mautic instances the most frustrating lesson I learned is high performance takes careful consideration and a significant amount of tuning. It can become a mission critical issue if its not a part of your server management plans. I am not an expert in servers, Mautic, or databases but If you will permit me, I can share some observations:
Having a hot rod server is great, but as you said, it’s not using many resources, so you probably have some unrealized performance gains writing to be discovered. I would recommend a dive into your database server configuration to ensure it is optimized for your particular use. Secondly I would examine how your segmentation and campaign actions are setup. Having more, but less complicated segments and campaigns perform better than a few complex ones no matter how much hardware you throw at it. Cron timing is key as well. Running overlapping crons will create issues with locked tables, timeouts, and incomplete executions as indicated in your log. Consider running your crons sequentially using the “&” between crons to ensure one finishes before another starts. I also delete non essential dashboard widgets.
As an example of what is possible, I run a dozen mautic instances with an average of 2+ million contacts per instance with the largest install approaching 15 million contacts.
This particular VPS is 10 vCPU Cores, 60 GB RAM, 400 GB NVMe running Centos 8, a plesk panel, Apache/Nginx, Mariadb 10.4 and it flawlessly handles 70 Wordpress sites on the same server alongside some hefty Mautic installs running dozens of segments and campaigns. The biggest has 169 segments.
Test and tune until you find a performance level you are happy with. We are all here to help if we can. If you don’t have the time or in-house resources, consider reaching out to @Yosu_Cadilla who is great with this kind of problem.
Thanks for the detailed reply.
I’m actually in the process of trying to use Redis with Mautic, but It doesn’t seem to work for whatever reason.
Other than that, our Mautic installation is not very complex. It’s actually pretty simple, having very few and simple segments. We also have good PHP setup and always use optimized Opcache.
Our cron setup is also well optimized.
I agree that no matter how much hardware you through at it, it will never get’s better unless you find the true cause of the bottleneck. In our case it’s the large number of pageviews we have in our websites.
This however can be solved with caching. I can tell that the first time that I login to Mautic (after a few hours of the last visit) it’s taking a long time to load. When I load the same pages after that however, it load’s instantly. (caching).
Here’s an example:
Searching for a specific contact by email literally takes several minutes to end.
Queries:
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 '%a*******%') OR (l.lastname LIKE '%a*******%') OR (l.email LIKE '%a*******%') OR (l.company LIKE '%a*******%') OR (l.city LIKE '%a*******%') OR (l.state LIKE '%a*******%') OR (l.zipcode LIKE '%a*******%') OR (l.country LIKE '%a*******%')) ORDER BY l.id DESC LIMIT 50;
# Time: 2022-01-11T21:42:57.007136Z
# User@Host: m*****[m*****] @ db2.******.com [10.1.1.10] Id: 13822874
# Query_time: 41.181070 Lock_time: 0.000127 Rows_sent: 1 Rows_examined: 13307683
SET timestamp=1641937335;
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 '%a*******@gmai%') OR (l.lastname LIKE '%a*******@gmai%') OR (l.email LIKE '%a*******@gmai%') OR (l.company LIKE '%a*******@gmai%') OR (l.city LIKE '%a*******@gmai%') OR (l.state LIKE '%a*******@gmai%') OR (l.zipcode LIKE '%a*******@gmai%') OR (l.country LIKE '%a*******@gmai%')) ORDER BY l.id DESC LIMIT 50;
# Time: 2022-01-11T21:43:40.280753Z
# User@Host: m*****[m*****] @ db2.******.com [10.1.1.10] Id: 13822888
# Query_time: 42.344135 Lock_time: 0.000168 Rows_sent: 1 Rows_examined: 13307691
SET timestamp=1641937377;
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 '%a*******@gmail.c%') OR (l.lastname LIKE '%a*******@gmail.c%') OR (l.email LIKE '%a*******@gmail.c%') OR (l.company LIKE '%a*******@gmail.c%') OR (l.city LIKE '%a*******@gmail.c%') OR (l.state LIKE '%a*******@gmail.c%') OR (l.zipcode LIKE '%a*******@gmail.c%') OR (l.country LIKE '%a*******@gmail.c%')) ORDER BY l.id DESC LIMIT 50;
# Time: 2022-01-11T21:44:21.105427Z
# User@Host: m*****[m*****] @ db2.******.com [10.1.1.10] Id: 13822891
# Query_time: 39.897106 Lock_time: 0.000122 Rows_sent: 1 Rows_examined: 13307706
SET timestamp=1641937421;
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 '%a*******@gmail.com%') OR (l.lastname LIKE '%a*******@gmail.com%') OR (l.email LIKE '%a*******@gmail.com%') OR (l.company LIKE '%a*******@gmail.com%') OR (l.city LIKE '%a*******@gmail.com%') OR (l.state LIKE '%a*******@gmail.com%') OR (l.zipcode LIKE '%a*******@gmail.com%') OR (l.country LIKE '%a*******@gmail.com%')) ORDER BY l.id DESC LIMIT 50;
