We're using an OpenCart 1.5.5.1 custom theme for our site.
Well it hasn't been smooth sailing for us and we are facing problem after problem. So far the gist of it is we're currently on a business SSD host (still shared I believe) to test the performance of the site (uniqpcs.co.uk), it is on a trial period so that we can load test the site (using load impact). Every single instance, when running the test it seems that the entry process limit is reached, causing the site to become unresponsive and eventually crashing (the entry process limit is set to 20 on our cPanel - even tried 40 but still same issue).
Been in contact with host provider (NameCheap) back and forth through email and live chat without any real help unfortunately but they mentioned a few things to me that I hope you can help clarify:
From what I understand the Entry Process limit is reached when a process (most likely PHP scripts) hangs much longer than expected and the delay causes more and more process to pile up hence the site becoming unresponsive and eventually reaching its limit.
From their MySQL error log they determined that the database is 'apparently' not optimized very well with 126 tables and a total of 26791 rows and said there is a SELECT query in MySQL which processes too long because of a large amount of rows:
Please find MySQL server logs below:
# Time: 140501 8:37:23
# User@Host: auniqpcs_uniqpc[auniqpcs_uniqpc] @ localhost []
# Query_time: 12.059221 Lock_time: 0.004748 Rows_sent: 1 Rows_examined: 26731
SET timestamp=1398947843;
SELECT COUNT(DISTINCT p.product_id) AS total FROM category_path cp LEFT JOIN product_to_category p2c ON (cp.category_id = p2c.category_id) LEFT JOIN product p ON (p2c.product_id = p.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 pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND cp.path_id = '211';
# User@Host: auniqpcs_uniqpc[auniqpcs_uniqpc] @ localhost []
# Query_time: 12.081561 Lock_time: 0.004338 Rows_sent: 1 Rows_examined: 26746
SET timestamp=1398947843;
SELECT COUNT(DISTINCT p.product_id) AS total FROM category_path cp LEFT JOIN product_to_category p2c ON (cp.category_id = p2c.category_id) LEFT JOIN product p ON (p2c.product_id = p.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 pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND cp.path_id = '258';
# Time: 140501 8:37:26
# User@Host: auniqpcs_uniqpc[auniqpcs_uniqpc] @ localhost []
# Query_time: 13.821338 Lock_time: 0.005288 Rows_sent: 1 Rows_examined: 26746
SET timestamp=1398947846;
SELECT COUNT(DISTINCT p.product_id) AS total FROM category_path cp LEFT JOIN product_to_category p2c ON (cp.category_id = p2c.category_id) LEFT JOIN product p ON (p2c.product_id = p.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 pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND cp.path_id = '245';
# Time: 140501 8:37:32
# User@Host: auniqpcs_uniqpc[auniqpcs_uniqpc] @ localhost []
# Query_time: 16.426658 Lock_time: 0.010242 Rows_sent: 1 Rows_examined: 26746
SET timestamp=1398947852;
SELECT COUNT(DISTINCT p.product_id) AS total FROM category_path cp LEFT JOIN product_to_category p2c ON (cp.category_id = p2c.category_id) LEFT JOIN product p ON (p2c.product_id = p.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 pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND cp.path_id = '245';
While processing, load tool sends another PHP request which "passes" the request to MySQL (while the 1st SQL query is still being processed). It is also possible that PHP requests that are referred to MySQL are badly coded.
pingdom: http://tools.pingdom.com/fpt/#!/bzzDid/uniqpcs.co.uk
I would really like to know what is the exact cause of this problem, is it badly coded PHP script, is it the MySQL database queries, or is it the host?
Now I'm not too sure what to do.... any advice is appreciated.
c
to see process origins). You should still be able to SSH into see this on a shared server...but then again, its namecheap...