I have recently upgraded my website (www.cattex.com) to a new version.
The new version uses opencart 3.0.3.7 with Journal 3.1.8, features LScache and memcache are also enabled.
The site looks and runs great and I'm very happy with the way it turned out, unfortunately out of nowhere, it will slow down incredibly making the whole experience a painful one, after a couple of seconds, it goes back to normal and repeats this whole process all day long.
I tried contacting my hosting (A2 hosting), they report that the issue is caused by a CPU overload.
In fact, by monitoring trough "phpmyadmin" I could observe my CPU going from a steady 2-3% to almost 80%, and then dropping down again.
After doing several things on the database tables and after running "turbo.php" I've seen some improvement and now the CPU never goes over 40% (except in rare cases).
I've tried many things: changing the php.ini settings, removing not necessary categories, inserting other indexes within DB tables, etc. basically things that I have found in past posts within the opencart forums that worked for other people.
The situation has improved, but since I was still experiencing slowness, I contacted my hosting for further information.
Their answer was:
The max redirects reached has been solved, but the #1 issue still eludes me.
To recap, the main issues affecting your site we have seen as:
1.) Long-running queries
2.) max redirects reached
In short, these two factors are what is causing your site slowness.
I've activated "long_query_log "on my MariaDB to see what was causing the issue, after 24h this is what I found:
Code: Select all
SELECT
p.product_id,
(
SELECT AVG(rating) total
FROM `review` r1
WHERE
r1.product_id = p.product_id
AND r1.status = '1'
GROUP BY r1.product_id
) rating,
(
SELECT price
FROM `product_discount` pd2
WHERE
pd2.product_id = p.product_id
AND pd2.customer_group_id = '1'
AND pd2.quantity = '1'
AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW())
AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW()))
ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1
) discount,
(
SELECT price
FROM `product_special` ps
WHERE
ps.product_id = p.product_id
AND ps.customer_group_id = '1'
AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW())
AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW()))
ORDER BY ps.priority ASC, ps.price ASC LIMIT 1
) special,
p.viewed
, SUM(op.quantity) AS sales
FROM `order_product` op
LEFT JOIN `order` o ON (o.order_id = op.order_id)
LEFT JOIN `product` p ON (p.product_id = op.product_id)
LEFT JOIN `product_description` pd ON (p.product_id = pd.product_id)
LEFT JOIN `product_to_store` p2s ON (p.product_id = p2s.product_id)
WHERE
p.status = '1'
AND p.date_available <= NOW()
AND p2s.store_id = '0'
AND pd.language_id = '2'
AND o.order_status_id > '0' GROUP BY p.product_id ORDER BY sales DESC, LCASE(pd.name) DESC LIMIT 0, 100;
So here's where my question finally comes in... What do I do next?
I'm pretty sure I have found the problem, but I don't know how to proceed.
Maybe this isn't even the problem, I'm basically stuck and don't know how to proceed and would really appreciate some help or guidance.
Thank you in advance for anyone willing to help.
Kind regards,
Onorio Torti.
PS. If I left out some important information please forgive me, I will obviously be willing to share anything you need, just let me know. And also, sorry for the really long question.