Mautic Community Forums

Extremely slow contact management

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;

Time: 2021-12-19T00:12:37.502620Z

User@Host: user****[user******] @ redis.*******.com [10.1.1.10] Id: 1494835

Query_time: 55.222637 Lock_time: 0.000245 Rows_sent: 1 Rows_examined: 11818083

SET timestamp=1639872702;

Steps I have tried to fix the problem:
I do not know what to do.

Hey there,

There are a few things off the bat you can try and do to speed things up a bit.

  1. 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.

Hi there,

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?

Thanks
Nick

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.

Check out

2 Likes

Hi,

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).

Redis however doesnt want to work with me:

Redis? - Support / Mautic 4 Install/Upgrade Support - Mautic Community Forums

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;

I have ****** sensitive info for obvious reasons.

Just of curiosity what version of MySQL are you running and have you done any optimizations like dedicating more memory etc?

I’m using MySQL 8.0.27
Yes, there are optimizations in place:

innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=96G
innodb_log_file_size=8G
skip_log_bin

And some other minor ones.

This is not a server config issue.