Post by smithhorn » Fri Jun 23, 2017 3:18 am

OC 2.1.0.1, Journal 2 Theme, Bluehost

We have just launched a site for a client and we have searched for ways to improve site speed. Using GTMetrix, we can see the majority of time is spent waiting:
https://gtmetrix.com/reports/g45papers.com/VHPdPqoo


We have no idea how to speed up the site. It started on a shared hosting service. We have then tried a cloud hosting and are now on a VPS, considering moving to a dedicated server.

We aren't sure how to improve the queries on page load so any help.suggestions would be appreciated!

Attachments

Page load log


New member

Posts

Joined
Sat Mar 25, 2017 2:07 am

Post by victorj » Fri Jun 23, 2017 3:46 am

First of all make sure your vps is configured propperly.
check latest pache is installed, and php 7.
latest maria database.
apache config should have cache enabled.

Journal is know to reduce site speed significat, even with journal cache enabled, optimizing database ie changing all tables from myisam to innodb and adding index tables speeds up a lot.

last to add is nitro cache, and you can get a reasonable fast site even with high visitor loads.

Koeltechnische deurrubbers eenvoudig online op maat bestellen.
Alle niet stekplichtige onderdelen zoals scharnieren, sloten, randverwarming en verlichting voor alle typen koelingen en vriezers.
https://koelcel-onderdelen.com

Koeltechnische deurrubbers eenvoudig online op maat bestellen.
Eigen productie en snelle levering.
https://123-deurrubbers.com


User avatar
Expert Member

Posts

Joined
Sat Jun 25, 2011 4:09 am
Location - Alkmaar Holland

Post by smithhorn » Fri Jun 23, 2017 4:31 am

Thanks for the info!

I just tried to force php7 by removing php5/56 and the site died. Are there specific items I need to ensure are activated to get the site to run php7+ only?

New member

Posts

Joined
Sat Mar 25, 2017 2:07 am

Post by victorj » Fri Jun 23, 2017 4:37 am

Not sure how your server is configured, and how you can set up php.
if you have access to whm with root access youre able to install all php 7 modules, just install php 7 complete and update to latest apache version.
in cpanel enable php 7 for your site.
be carefull with maxmem setting, just tweak till site runs fine.

Koeltechnische deurrubbers eenvoudig online op maat bestellen.
Alle niet stekplichtige onderdelen zoals scharnieren, sloten, randverwarming en verlichting voor alle typen koelingen en vriezers.
https://koelcel-onderdelen.com

Koeltechnische deurrubbers eenvoudig online op maat bestellen.
Eigen productie en snelle levering.
https://123-deurrubbers.com


User avatar
Expert Member

Posts

Joined
Sat Jun 25, 2011 4:09 am
Location - Alkmaar Holland

Post by sculptex » Fri Jun 23, 2017 5:19 am

Look for huge queries like 253 380. They take about 1.5 seconds each, basically a massive query individually excluding a list of hundreds of products from the query. Whatever is generating those terrible sql queries is the main problem.
I don't know what tools you have for searching but a multiple file search on part of those query strings may locate the culprit.

ImageImage


User avatar
Active Member

Posts

Joined
Tue Sep 13, 2011 3:07 am
Location - UK

Post by smithhorn » Fri Jun 23, 2017 5:23 am

Thanks for more replies!

In terms of searching, we have a simple grep search to find key words in files. Is that what you mean?

It looks like those two long queries are generated by the super_filter.php file from Journal2 and the standard product.php in catalog/model.
Last edited by smithhorn on Fri Jun 23, 2017 5:36 am, edited 1 time in total.

New member

Posts

Joined
Sat Mar 25, 2017 2:07 am

Post by victorj » Fri Jun 23, 2017 5:35 am

no he means search in opencart php files, just download all files and use something like agent ransack to search all files for certain words in the code.

these queries are part of journal theme.
Journal theme add loads off addons and functionality, and gives the possebility to give your site the looks and feel you want.
Downside is deu to all functions it slows down a site significant.
So in order to make a site fast, you have to optimize hosting to the max

Koeltechnische deurrubbers eenvoudig online op maat bestellen.
Alle niet stekplichtige onderdelen zoals scharnieren, sloten, randverwarming en verlichting voor alle typen koelingen en vriezers.
https://koelcel-onderdelen.com

Koeltechnische deurrubbers eenvoudig online op maat bestellen.
Eigen productie en snelle levering.
https://123-deurrubbers.com


User avatar
Expert Member

Posts

Joined
Sat Jun 25, 2011 4:09 am
Location - Alkmaar Holland

Post by smithhorn » Fri Jun 23, 2017 6:00 am

Gotcha. I think our grep search is performing the same task. I found that db query string in two different files. One was definitely a Journal file. It's for the Super Filter, but we are not using it on any of our pages. It looks like it is loaded even if the filter isn't visually used on a page. Any suggestions on how to eliminate this thing without breaking the site?

New member

Posts

Joined
Sat Mar 25, 2017 2:07 am

Post by victorj » Fri Jun 23, 2017 6:06 am

if you mean grep in ssh on your server, yes it does the same, although i like to do it local on files.
you should ask this at the journal theme crew, they are known to respond fast, and they know there theme best.

Recently optimize a heavy journal theme driven site on cloudhosting and its running amazing fast

Koeltechnische deurrubbers eenvoudig online op maat bestellen.
Alle niet stekplichtige onderdelen zoals scharnieren, sloten, randverwarming en verlichting voor alle typen koelingen en vriezers.
https://koelcel-onderdelen.com

Koeltechnische deurrubbers eenvoudig online op maat bestellen.
Eigen productie en snelle levering.
https://123-deurrubbers.com


User avatar
Expert Member

Posts

Joined
Sat Jun 25, 2011 4:09 am
Location - Alkmaar Holland

Post by smithhorn » Fri Jun 23, 2017 6:07 am

Do you mind sharing what you did to optimize the site?

New member

Posts

Joined
Sat Mar 25, 2017 2:07 am

Post by victorj » Fri Jun 23, 2017 6:12 am

no i dont mind, just contact me through pm or better through my sites, in my sig and use contact or check site, email is quiet visible..
dont expect a answer now, i ive in holland and time difference makes it here time to go to sleep :)

Koeltechnische deurrubbers eenvoudig online op maat bestellen.
Alle niet stekplichtige onderdelen zoals scharnieren, sloten, randverwarming en verlichting voor alle typen koelingen en vriezers.
https://koelcel-onderdelen.com

Koeltechnische deurrubbers eenvoudig online op maat bestellen.
Eigen productie en snelle levering.
https://123-deurrubbers.com


User avatar
Expert Member

Posts

Joined
Sat Jun 25, 2011 4:09 am
Location - Alkmaar Holland

Post by sculptex » Fri Jun 23, 2017 3:59 pm

Well there are 4 instances of that super_filter SQL query, totalling over 6 seconds so remove that and you will think your site is lightning fast again! These are just SQL timings and don't include extra wasted time taken to compile queries in the first place.
Sorry, I have always avoided Journal theme but they should support you how to disable that.

ImageImage


User avatar
Active Member

Posts

Joined
Tue Sep 13, 2011 3:07 am
Location - UK

Post by smithhorn » Fri Jun 23, 2017 11:43 pm

Thanks for the info! This was our first OC project, so it's been a big learning experience!

New member

Posts

Joined
Sat Mar 25, 2017 2:07 am

Post by smithhorn » Sat Jun 24, 2017 1:15 am

We just discovered those really long queries are actually generated by the product.php catalog model:

Code: Select all

	public function getProduct($product_id) {
		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' 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 " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' 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, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
and

Code: Select all

public function getProducts($data = array()) {
		$sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' 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 " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' 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";
This looks to be essential to loading products, so why are not more people having the same loading issues?

New member

Posts

Joined
Sat Mar 25, 2017 2:07 am

Post by sculptex » Sat Jun 24, 2017 8:29 am

There is nowhere in my version of that file that generates

Code: Select all

AND p.product_id !=
so that is what you want to be looking for as the culprit

ImageImage


User avatar
Active Member

Posts

Joined
Tue Sep 13, 2011 3:07 am
Location - UK

Post by smithhorn » Sun Jun 25, 2017 12:23 am

Which version of OC are you using? I looked into my original download for OC 2.1.0.1 and the above formatting is in the product.php file, but without the '!=', so I'm not sure where it's being generated.

New member

Posts

Joined
Sat Mar 25, 2017 2:07 am

Post by smithhorn » Sun Jun 25, 2017 1:17 am

Never mind. There is a modification that is including the '!=' bit. We are using an extension called Restrict Catalog (client's request), and that is the culprit. Still not sure why it fires off so many times per page load, but now we know :(

Thank you to all who tried to help!

New member

Posts

Joined
Sat Mar 25, 2017 2:07 am
Who is online

Users browsing this forum: Maxidays, thekrotek, tina86 and 40 guests