Opencart expert required: Debug why queries are overloading server
Budget: $30 – $250 AUD
We are having issues with queries running on our database that is overloading the shared hosting. They have informed us we need to stop these queries running in the future. We require you to debug these errors, advise why they are happening, and propose a fix for the solution.
The host informed us that the queries run for over 10 minutes, and load the server up to 400%.
The queries are below.
[22-Nov-2023 04:46:11 UTC] PHP Fatal error: Uncaught Exception: Error: MySQL server has gone away<br />Error No: 2006<br />SELECT p.product_id, (SELECT AVG(rating) AS total FROM review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '6' 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) AS discount, (SELECT price FROM product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '6' 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) AS special FROM product p 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 pd.language_id = '1' AND p.status = '1' in /home/xx/public_html/system/library/db/mysqli.php on line 66
[22-Nov-2023 04:46:11 UTC] PHP Fatal error: Uncaught Exception: Error: MySQL server has gone away<br />Error No: 2006<br />SELECT COUNT(DISTINCT p.product_id) AS total FROM product p 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 pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p.manufacturer_id = '25' in /home/xxx/public_html/system/library/db/mysqli.php:66
Stack trace:
#0 /home/xxx/public_html/system/library/db.php(45): DB\MySQLi->query('SELECT COUNT(DI...')
#1 /home/xxx/public_html/catalog/model/catalog/product.php(517): DB->query('SELECT COUNT(DI...')
#2 /home/xxx/storage/modification/system/engine/loader.php(248): ModelCatalogProduct->getTotalProducts(Array)
#3 /home/xxx/public_html/system/engine/proxy.php(47): Loader->{closure}(Array, Array)
#4 /home/xxx/public_html/catalog/controller/product/manufacturer.php(152): Proxy->__call('getTotalProduct...', Array)
#5 /home/xxx/stor in /home/xx/public_html/system/library/db/mysqli.php on line 66
[22-Nov-2023 22:58:37 UTC] PHP Fatal error: Uncaught Exception: Error: <br />Error No: in /home/xx/public_html/system/library/db/mysqli.php:10
Stack trace:
#0 /home/xx/public_html/system/library/db.php(31): DB\MySQLi->__construct('localhost', 'xxxx', '3306')
#1 /home/xx/public_html/system/framework.php(80): DB->__construct('mysqli', 'localhost', xxxx', '3306')
#2 /home/xx/public_html/system/startup.php(104): require_once('/home/xx/...')
#3 /home/xx/public_html/index.php(19): start('catalog')
#4 {main}
thrown in /home/xx/public_html/system/library/db/mysqli.php on line 10
The host informed us that the queries run for over 10 minutes, and load the server up to 400%.
The queries are below.
[22-Nov-2023 04:46:11 UTC] PHP Fatal error: Uncaught Exception: Error: MySQL server has gone away<br />Error No: 2006<br />SELECT p.product_id, (SELECT AVG(rating) AS total FROM review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '6' 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) AS discount, (SELECT price FROM product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '6' 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) AS special FROM product p 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 pd.language_id = '1' AND p.status = '1' in /home/xx/public_html/system/library/db/mysqli.php on line 66
[22-Nov-2023 04:46:11 UTC] PHP Fatal error: Uncaught Exception: Error: MySQL server has gone away<br />Error No: 2006<br />SELECT COUNT(DISTINCT p.product_id) AS total FROM product p 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 pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p.manufacturer_id = '25' in /home/xxx/public_html/system/library/db/mysqli.php:66
Stack trace:
#0 /home/xxx/public_html/system/library/db.php(45): DB\MySQLi->query('SELECT COUNT(DI...')
#1 /home/xxx/public_html/catalog/model/catalog/product.php(517): DB->query('SELECT COUNT(DI...')
#2 /home/xxx/storage/modification/system/engine/loader.php(248): ModelCatalogProduct->getTotalProducts(Array)
#3 /home/xxx/public_html/system/engine/proxy.php(47): Loader->{closure}(Array, Array)
#4 /home/xxx/public_html/catalog/controller/product/manufacturer.php(152): Proxy->__call('getTotalProduct...', Array)
#5 /home/xxx/stor in /home/xx/public_html/system/library/db/mysqli.php on line 66
[22-Nov-2023 22:58:37 UTC] PHP Fatal error: Uncaught Exception: Error: <br />Error No: in /home/xx/public_html/system/library/db/mysqli.php:10
Stack trace:
#0 /home/xx/public_html/system/library/db.php(31): DB\MySQLi->__construct('localhost', 'xxxx', '3306')
#1 /home/xx/public_html/system/framework.php(80): DB->__construct('mysqli', 'localhost', xxxx', '3306')
#2 /home/xx/public_html/system/startup.php(104): require_once('/home/xx/...')
#3 /home/xx/public_html/index.php(19): start('catalog')
#4 {main}
thrown in /home/xx/public_html/system/library/db/mysqli.php on line 10