Post by Axel Wers » Wed Dec 02, 2015 6:00 am

Hello, we need help. Our eshop loads slow, here is a slow query log.

Code: Select all

# Query_time: 7.146166 Lock_time: 0.065719 Rows_sent: 20 Rows_examined: 15454
SET timestamp=1449006931;
SELECT p.product_id, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM oc_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) AS discount, (SELECT price FROM oc_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) AS special FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '2' AND p.status = '1' AND ( p.mod_customer_group_restrict IS NULL OR FIND_IN_SET( 1, p.mod_customer_group_restrict )) AND p.date_available <= NOW() AND p2s.store_id = '0' GROUP BY p.product_id ORDER BY p.date_added DESC, LCASE(pd.name) DESC LIMIT 0,20;
What is issue with this? Thanks.

OpenCart 1.5.6.4

OC 2.3.0.2


New member

Posts

Joined
Fri Aug 22, 2014 3:59 pm

Post by IP_CAM » Wed Dec 02, 2015 8:57 am

I will not be of much help, to just publish a rather strange looking piece of code.
containing Variables, not commonly used in a default OC, and expect assistance.
:-\
Ernie
hitline.info/shop/

My Github OC Site: https://github.com/IP-CAM
5'200 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by Axel Wers » Wed Dec 02, 2015 9:02 am

That's MySQL slow query log, not code.

OC 2.3.0.2


New member

Posts

Joined
Fri Aug 22, 2014 3:59 pm

Post by IP_CAM » Wed Dec 02, 2015 10:23 am

I am aware of this, still, it's insufficient info, to assist.
Ernie

My Github OC Site: https://github.com/IP-CAM
5'200 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by victorj » Thu Dec 03, 2015 6:43 am

Make sure you totally disable product and categorie count in your shop (look for extensions that will remove this grom opencart)

next optimze your database for faster acces and queries

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


User avatar
Expert Member

Posts

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

Post by Axel Wers » Thu Dec 03, 2015 6:50 am

I have disabled product count in categories long time ago. Database is optimized (has about 20 MB).

We need probably something for better cache. On main page we have lot of products, so there is probably issue.

OC 2.3.0.2


New member

Posts

Joined
Fri Aug 22, 2014 3:59 pm

Post by victorj » Thu Dec 03, 2015 7:01 am

You can alter your database so all tables are altered from myisam to innodb and add tables indexes this will dramitically reduce databse stress and make your site much faster

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


User avatar
Expert Member

Posts

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

Post by Axel Wers » Thu Dec 03, 2015 7:06 am

I found some tips how and where add some indexes but unfortunatelly I didn't see eshop faster. But I can try alter tables to innoDB. Thanks for tip.

OC 2.3.0.2


New member

Posts

Joined
Fri Aug 22, 2014 3:59 pm

Post by IP_CAM » Fri Dec 04, 2015 8:39 am

>> I have disabled product count in categories long time ago. <<
Does not help very much, it's been counting anyway, every time someone asks for, because it has to know, to make the Pagebreak System work.

Much better, CACHE those Values, then, you even can display the Numbers, without experience any performance-loss, and serve your Customers better, by telling 'em, what you have on Display in real!
!!! OC v.1.5.6.x ONLY !!!
Good Luck ;)
Ernie

Sample improved Test Site, with other Speed-Up Tips on the font Page mentioned as well, such as the link to the upmost finest Atomix TURBO change to INNODB + Full DB Indexing Tool!
http://www.hitline.info/shop/

Code: Select all

	// http://octurbo.com/caching-opencarts-category-counts/
	// $query = $this->db->query($sql);
	// return $query->row['total'];
	// http://octurbo.com/caching-opencarts-category-counts/

	$cacheid='product.gettotalproducts.'.md5($sql).(int)$customer_group_id;
	$total=$this->cache->get($cacheid);
   if ($total === null ) {
       $query = $this->db->query($sql);
       $total = $query->row['total'];
       $this->cache->set($cacheid,$total);
   }
   return $total;   
	}
	
	// END opencarts-category-counts

My Github OC Site: https://github.com/IP-CAM
5'200 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by Axel Wers » Sun Dec 06, 2015 11:21 pm

Thanks, I use some tips soon. I already made some improvements, eshop is faster, but I think there is still way how to make ik better. I'll test some tips what I got here or via PM's and then write results. Thanks.

OC 2.3.0.2


New member

Posts

Joined
Fri Aug 22, 2014 3:59 pm

Post by IP_CAM » Mon Dec 07, 2015 5:49 am

possibly, you find some other interesting information here:
http://forum.opencart.com/viewtopic.php ... 37#p592237
Good Luck
Ernie

My Github OC Site: https://github.com/IP-CAM
5'200 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by Ljubo » Mon Jul 08, 2019 4:59 am

I desperately need this mod for OC 1.5.3.1...
please?

Active Member

Posts

Joined
Mon Jan 31, 2011 10:07 pm
Location - Serbia

Post by EvolveWebHosting » Mon Jul 08, 2019 8:29 am

Ljubo wrote:
Mon Jul 08, 2019 4:59 am
I desperately need this mod for OC 1.5.3.1...
please?
I think you may struggle to find this for such an old version of OC. You may want to try a commercial posting to see if any developer can assist you.

2 Week FREE Trial of our Shared Hosting plans (DIrectAdmin or cPanel) for new customers
2 Week FREE Trial of Astra Firewall and Malware Scanner
Visit our website for full details and to start your trial today - www.evolvewebhost.com


User avatar
Active Member

Posts

Joined
Fri Mar 27, 2015 11:13 pm
Location - Denver, Colorado, USA

Post by IP_CAM » Mon Jul 08, 2019 10:07 am

Ljubo wrote:
Mon Jul 08, 2019 4:59 am
I desperately need this mod for OC 1.5.3.1...
---
Well, there are several ways, to speed up your OC. :D
1. Use this, to cache the Category Product-Number Values. It will
dramatically improve overall Listing Page Loading:
Cache category data
Caches category data for Opencart 1.5 to make it faster.
Opencart installs with alot of categories are really slow.
https://gist.github.com/weismannweb/a16 ... 89ca1d1209
---
2. Cache your Database Stuff, it also has a remarkable effect on Loading. But
only after changing the echo $ouput; variable, obviously written
wrongly :crazy: in your OC Version system/library/response.php file , also
in the system_db_cache_pnsol.xml VqMod File too, to match the variable:

Code: Select all

FROM:
<search position="after" offset="1"><![CDATA[echo $output;]]></search>
TO:
<search position="after" offset="1"><![CDATA[echo $ouput;]]></search>
to make it work with your Version Code, if the wrongly written Variable really exists. :laugh:
DB Cache
OpenCart 1.5.6.x DB Cache module
Caches database sql queries to improve performance to 10 times.
Automatically drop affected cache entries after any modification operation: insert/update/delete
https://www.opencart.com/index.php?rout ... n_id=25388
---
3. Make sure, to use MySqli, it dramatically improved Page Load,
and check your BOTH config.php (ROOT +Admin) Driver
settings, to find out:

Code: Select all

define('DB_DRIVER', 'mysqli');
MySQLiz - MySQLi support
MySQLiz module adds MySQLi database driver support to OpenCart for old
versions of Opencart moved to modern hosting.
https://www.opencart.com/index.php?rout ... n_id=13041
---
Mysqli w/ SSL Certs
MySQLi with SSL support. This will allow for secure database communication via SSL.
This is the best way to add a layer of protection to your cart.
https://www.opencart.com/index.php?rout ... n_id=18572
---
Mysqli for Opencart 1.5.x
Add Mysqli to your older opencart versions BEFORE 1.5.6.1!
https://www.opencart.com/index.php?rout ... n_id=30233
---
4. Use the enclosed .htaccess file, just RENAME the:
RewriteBase /bright/
to match your Site Subdirectory, if you use an URL like www. yoursite/shop/
otherwise, if your Shop is located in the SITE ROOT Section, make it look like:
RewriteBase /

Code: Select all

## If your opencart installation does not run on the main web folder,
## make sure you folder it does run in ie. / becomes /cart/ or /shop/ 
## it's now set to match the SubDirectory: /shop/
RewriteBase /bright/
in Line 79 of the .htaccess File.
The .HTACCESS Zip File has now been removed again, it's available on personal request!
---
5. LINK your Styles and Scripts in the Theme header.tpl File
In your THEME header.tpl File, change the Links to Stylesheets and Scripts
to an absolute http Value instead of having them linked 'internally'. And if
your Shop runs by a http(s):// www. yourshop/ ... setting, make the Style + Script
links looking the opposite way, like http(s):// yourshop/ ..., to make all those
Styles and Scripts Cookie-Free, as this is called. :D
A sample header part, containing such ABSOLUTE URL Values, since my Sites are called by http(s)://WWW. ....

Code: Select all

<link rel="stylesheet" type="text/css" href="http://openshop.li/shop/catalog/view/theme/default/stylesheet/stylesheet.css" />
<?php foreach ($styles as $style) { ?>
<link rel="<?php echo $style['rel']; ?>" type="text/css" href="http://openshop.li/shop/<?php echo $style['href']; ?>" media="<?php echo $style['media']; ?>" />
<?php } ?>
<!--[if IE 7]> 
<link rel="stylesheet" type="text/css" href="http://openshop.li/shop/catalog/view/theme/default/stylesheet/ie7.css" />
<![endif]-->
<!--[if lt IE 7]>
<link rel="stylesheet" type="text/css" href="http://openshop.li/shop/catalog/view/theme/default/stylesheet/ie6.css" />
<link rel="stylesheet" type="text/css" href="http://openshop.li/shop/catalog/view/javascript/jquery/ui/themes/ui-lightness/jquery-ui-1.8.16.custom.css" />
<script type="text/javascript" src="http://openshop.li/shop/catalog/view/javascript/DD_belatedPNG_0.0.8a-min.js"></script>
<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
<script src="//ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.min.js"></script>
<script type="text/javascript" src="http://openshop.li/shop/catalog/view/javascript/common.js"></script>
<?php foreach ($scripts as $script) { ?>
<script type="text/javascript" src="http://openshop.li/shop/<?php echo $script; ?>"></script>
<?php } ?>
---
6. Make your Images cookiefree, your Version is easy able to.
Change in your ROOT config.php File the URL Value, by
example, if your Shop runs under http(s):// www. Yourshop,
make the image URL like http(s):// Yourshop,

Code: Select all

define('HTTP_SERVER', 'http://www.opencart.li/bright/');
define('HTTP_IMAGE', 'http://opencart.li/bright/image/');
// HTTPS
define('HTTPS_SERVER', 'http://www.opencart.li/bright/');
define('HTTPS_IMAGE', 'http://opencart.li/bright/image/');
---
7. ATOMIX Turbo Database Indexer.
You already told me, to have used this fine Tool, but for others,
I mention it also, since it's a MUST, to have the DB fully indexed:
https://github.com/IP-CAM/opencart-turbo
---
All of those extensions and infos above should technically function, if you use a stock OC. 1.5.3.1, I assume.
And then, come back an tell us, what gtmetrix.com means about it. Make a Test, before you change
anything, and one after, then, you'll know in detail, and we do too ;)

Good Luck ! ;)
Ernie

My Github OC Site: https://github.com/IP-CAM
5'200 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by Ljubo » Thu Jul 11, 2019 5:23 am

Thank you very much Ernie!
In the meantime, I have tried to move to another host and it is even worse situation:
502 Bad Gateway , so even GTmetrix could not work.
I will have to move to some Cloud VPS hosting in order to try to make the things faster (and to have some time to rebuild it in OC 3).
Will keep you posted.

Active Member

Posts

Joined
Mon Jan 31, 2011 10:07 pm
Location - Serbia

Post by IP_CAM » Thu Jul 11, 2019 9:21 am

Good for you ! My test Servers are in a shared IP Environment, among
about ~1'000 others, as I found out, but it does not keep a 3k product OC
from producing 100/92 in ~3s D/~5s M on the Vancouver GTMETRIX Site
in mobile and desktop tests on a 18pr. category Page, which is 8'300 KM away. ;)
So, just make sure, to select the TOP LEVEL Server Owner, to rent your
Server Space, but not some Sub/Sub/Sub/Hoster, only getting a small
Portion of Server Power for their Clients. On my way, I came trough
cheaper Sub-Hosters, to end up, where those in charge really are in
full charge of the Servers used. It may not always be the cheapest
solution, but for sure the best.
Good Luck! ;)
Ernie

Image

My Github OC Site: https://github.com/IP-CAM
5'200 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland
Who is online

Users browsing this forum: mhglobal and 67 guests