High CPU Usage

General Discussion
21

No as far as I know… Remember that this run but just taking CPU from the VPS with 2 cores.
But for me the most important thing is that with the same data in 3.3.4 I ma now sending mails again… I just have to reload the pics (I did not save the media files :frowning: )
And by the way the manual send is going faster than in 4.4.1

1 Like
23

I am interested to know if you ever moved back to 4.4.3 and experiencing the same issue ?

We are running the latest versions, and we are seeing very high CPU usage on the DB.

I am far from a technical person, from the reading that I have done many people say that this high CPU usage caused by mysql is due to heavy queries running or not well implemented queries.

It would ge great if there was an expert DBA here that could understand what are the queries Mautic is implementing on inserting new data or generating different reports like the email stats report graph.
Generally on reports and on opening a heavy email that was sent to many contacts we see the system hanging :frowning:

24

I have returned all the instances to 4.4.1 all Mautic instances and did not migrate anymore to the 4.4.3. Now the CPU is more or less ok.

For me is clear that 4.4.3 has changed some query that makes that mysql goes to very high consumption

25

Thanks for the quick response.

If I am running 4.4.3 on certain instances, could you let me know how to actually downgrade the instance to a previous version (if possible at all)

26

I think I made

1.- Backup of the DB and backup local.cfg

2.- Install a new instance of Mautic 4.4.1 from scratch with a new DB

3.- Restore the DB in the new Database

4.- Restore the local command editing it to point to the new DB name and new folders

5.- Executed doctrine comands

doctrine:migrations:migrate

doctrine:schema:update --force

and then delete de full cache folder

It was sometime ago so I am not 100% sure that I did not miss any step, but in this way I had the old instance as well to come back to it if something went wrong

After everything was ok… I have deleted the old instance

27

ok cool. that I can do.

I just noticed on the one instance, where there is high traffic it is running 4.4.1. CPU is around 100% all the time, 2 Dedicated CPU, 8GB RAM. External Volume holding MySQL.
Running Nginx, php8.0

28

Do you know if I can downgrade to the latest version of 3.x the same way

29

I did it as well… but I am not 100% sure that everything works… I am not using all the functionality

30

I just bumped into this issue after upgrading from 3.4.1 to 4.4.10.

The hanging query was

SELECT count(*) as count
FROM leads l
WHERE (l.id IN (SELECT ll.lead_id FROM lead_lists_leads ll WHERE (ll.leadlist_id IN (5)) AND (ll.manually_removed = 0)))
  AND (l.id NOT IN (SELECT dnc.lead_id FROM lead_donotcontact dnc WHERE dnc.channel = 'email'))
  AND (l.id NOT IN
       (SELECT stat.lead_id FROM email_stats stat WHERE (stat.lead_id IS NOT NULL) AND (stat.email_id IN (8))))
  AND (l.id NOT IN (SELECT mq.lead_id
                    FROM message_queue mq
                    WHERE (mq.status <> 'sent')
                      AND (mq.channel = 'email')
                      AND (mq.channel_id IN (8))))
  AND (l.id NOT IN (SELECT lc.lead_id
                    FROM lead_categories lc
                             INNER JOIN emails e ON e.category_id = lc.category_id
                    WHERE (e.id = 8)
                      AND (lc.manually_removed = 1)))
  AND ((l.email IS NOT NULL) AND (l.email <> ''))

I have a few large tables with a few hundred thousand entries, it seemed to be specifically this AND:

AND (l.id NOT IN
       (SELECT stat.lead_id FROM email_stats stat WHERE (stat.lead_id IS NOT NULL) AND (stat.email_id IN (8))))

I managed to fix it by adding a few indexes and optimizing the two largest tables:

-- Index on leads.email
ALTER TABLE leads ADD INDEX idx_leads_email (email);

-- Composite index on lead_lists_leads
ALTER TABLE lead_lists_leads ADD INDEX idx_lll_leadlist_manually (lead_id, leadlist_id, manually_removed);

-- Composite index on lead_donotcontact
ALTER TABLE lead_donotcontact ADD INDEX idx_dnc_lead_channel (lead_id, channel);

-- Composite index on email_stats
ALTER TABLE email_stats ADD INDEX idx_stats_lead_email (lead_id, email_id);

-- Composite index on message_queue
ALTER TABLE message_queue ADD INDEX idx_mq_lead_status_channel_channelid (lead_id, status, channel, channel_id);

-- Composite index on lead_categories
ALTER TABLE lead_categories ADD INDEX idx_lc_lead_category_manually (lead_id, category_id, manually_removed);

-- Index on emails for category_id
ALTER TABLE emails ADD INDEX idx_emails_categoryid (category_id);

and then

OPTIMIZE TABLE email_stats;
OPTIMIZE TABLE leads;