Post by Onorio » Thu Sep 23, 2021 11:28 pm

Hi everyone,
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:

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.
The max redirects reached has been solved, but the #1 issue still eludes me.
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;
These queries are the only ones running for more than 2sec (usually from 4 to 8 seconds) and they get requested constantly, sometimes they get logged a minute apart sometimes 3 seconds( I assume depending on the traffic).

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.
Last edited by Onorio on Fri Sep 24, 2021 5:28 pm, edited 1 time in total.

New member

Posts

Joined
Thu Dec 15, 2016 5:48 pm

Post by straightlight » Thu Sep 23, 2021 11:35 pm

Onorio wrote:
Thu Sep 23, 2021 11:28 pm
Hi everyone,
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:

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.
The max redirects reached has been solved, but the #1 issue still eludes me.
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;
These queries are the only ones running for more than 2sec (usually from 4 to 8 seconds) and they get requested constantly, sometimes they get logged a minute apart sometimes 3 seconds( I assume depending on the traffic).

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.
Journal is unsupported on the forum.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by ADD Creative » Fri Sep 24, 2021 1:02 am

Looks like some sort of best seller query. It's not the default OpenCart one, so is probably added by your Journal theme.

www.add-creative.co.uk


Expert Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by Onorio » Fri Sep 24, 2021 1:15 am

Thank you for the quick reply,
I will contact them directly regarding this. Do you think this is the reason for the slowness?

New member

Posts

Joined
Thu Dec 15, 2016 5:48 pm

Post by straightlight » Fri Sep 24, 2021 1:19 am

Onorio wrote:
Fri Sep 24, 2021 1:15 am
Thank you for the quick reply,
I will contact them directly regarding this. Do you think this is the reason for the slowness?
Yes, it is. However, since the issue has been resolved on the topic, please add: [SOLVED] at the beginning of the subject line on your first post.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by JNeuhoff » Fri Sep 24, 2021 1:43 am

It's an issue with the Journal3 filter. The Journal framework in general is of poor quality and its framework is not compliant with OpenCart standards.
You'll have to contact the Journal3 support on this. Or use a proper OpenCart web theme and not Journal.

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by by mona » Fri Sep 24, 2021 2:03 am

the irony is the free advertising :laugh:

DISCLAIMER:
You should not modify core files .. if you would like to donate a cup of coffee I will write it in a modification for you.


https://www.youtube.com/watch?v=zXIxDoCRc84


User avatar
Expert Member

Posts

Joined
Mon Jun 10, 2019 9:31 am

Post by Onorio » Fri Sep 24, 2021 5:27 pm

Ok, i have opend a ticket with them, will see how it goes. Thank you for now I will mark this post as "solved"

New member

Posts

Joined
Thu Dec 15, 2016 5:48 pm

Post by paulfeakins » Fri Sep 24, 2021 6:59 pm

Onorio wrote:
Fri Sep 24, 2021 1:15 am
Thank you for the quick reply,
I will contact them directly regarding this. Do you think this is the reason for the slowness?
It probably is.

You really should have avoided Journal and used a CSS-only theme such as one of ours: https://www.antropy.co.uk/services/ecommerce-business/

UK OpenCart Hosting | OpenCart Audits | OpenCart Support - please email info@antropy.co.uk


User avatar
Guru Member
Online

Posts

Joined
Mon Aug 22, 2011 11:01 pm
Location - London Gatwick, United Kingdom

Post by Onorio » Mon Oct 04, 2021 4:20 pm

At the end of the day I've solved the issue by removing very old orders from the database.
You correctly assumed it was a bestseller script running constantly and slowing down the whole process.
By removing older orders and products ordered, the script runs a whole lot faster by serching fewer lines inside the database tables.
Thank you all for the help.

New member

Posts

Joined
Thu Dec 15, 2016 5:48 pm
Who is online

Users browsing this forum: alanjones and 83 guests