Post by Xsecrets » Mon Dec 20, 2010 12:58 pm

Ok so I finally got around to putting a huge database of products I have access to into opencart so I could do some performance testing. When I started the pages wouldn't load at all they always hit the 90 second timeout. After tweaking I get a page load of 10-15 sec not optimal for a production site, but this database has almost 2 million products and 1200 categories with a couple thousand manufacturers.

I was wondering if anyone had an opencart install on a test server with lots of products/categories that wouldn't mind trying out the tweaks to see if everything works well on their install.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by Qphoria » Mon Dec 20, 2010 1:02 pm

if you could zip over the database that you have I can try it on my local and hosted sites.. I can also run it through the debugger and maybe see where the contention is

Image
Donate!|OpenCart Basics|GeoZones
Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by Xsecrets » Mon Dec 20, 2010 1:16 pm

well I can't give away the database. It's one that one of my clients paid a lot of money for. I think I've figured out some issues with the current queries. With the tweaks I've made I've seen at least a 5X improvement here.

If you want to take a look I can shoot you the changed model files. Basically I simply rearranged the queries a bit.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by d7a7z7e7d » Thu Dec 23, 2010 6:20 pm

Mind showing some examples of your tweaks? I'd be interested in applying them on my store even though it's relatively small in comparison. Every millisecond counts! :)

Image
OpenCart Extensions, Technical Support & Custom Development | Have I helped you?


User avatar
Active Member

Posts

Joined
Fri Sep 17, 2010 5:51 am
Location - USA

Post by SapporoGuy » Thu Dec 23, 2010 6:23 pm

I read somewhere that amazon says that a 100milisecond loss is a loss of 1% of revenue.
Problem was that they set a base line of where to start from in the article.

930sc ... because it is fun!


User avatar
Active Member

Posts

Joined
Mon Nov 01, 2010 7:29 pm

Post by Xsecrets » Thu Dec 23, 2010 11:03 pm

well the biggest gain I got was moving where the pd.language_id was. instead of having it in the where clause I put it in the on clause of the join which seemed to allow the db to utilize the combo index better. I did this for many of the other tables that had combo indexes as well like category_description etc.

so for instance

Code: Select all

public function getProducts() {
        $query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, wcd.unit AS weight_class 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) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) LEFT JOIN " . DB_PREFIX . "weight_class_description wcd ON (p.weight_class_id = wcd.weight_class_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.date_available <= NOW() AND p.status = '1'");
    
        return $query->rows;
    } 
becomes

Code: Select all

public function getProducts() {
        $query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, wcd.unit AS weight_class FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id AND pd.language_id = '" . (int)$this->config->get('config_language_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) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) LEFT JOIN " . DB_PREFIX . "weight_class_description wcd ON (p.weight_class_id = wcd.weight_class_id) WHERE p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.date_available <= NOW() AND p.status = '1'");
    
        return $query->rows;
    } 
on my test db that took me from around 8-9 seconds to run down to about 0.5 seconds of course like I said that's on a db with just shy of 2million products, so it's an extreme case.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by SapporoGuy » Thu Dec 23, 2010 11:13 pm

HUH?

Did I read that right?

You got a 8-9 sec query down to .5 sec? If so, woooooot!!
that will put opencart pretty high on the speedy scale.

930sc ... because it is fun!


User avatar
Active Member

Posts

Joined
Mon Nov 01, 2010 7:29 pm

Post by Xsecrets » Mon Dec 27, 2010 2:36 am

SapporoGuy wrote:HUH?

Did I read that right?

You got a 8-9 sec query down to .5 sec? If so, woooooot!!
that will put opencart pretty high on the speedy scale.
yes you read that correctly. of course as I said this is on a db with almost 2 million products in it. A normal db like most small businesses run would probably only ever get to maybe the .5 second range, but in theory the changes I've made might bring them back down to the two digit millisecond range or lower where they should be.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by SapporoGuy » Mon Dec 27, 2010 2:59 am

This is hot baby! Hot!

I'm getting: 0.002(4) at times 0.02.
Something with the cache or ??? makes it jump but on consecutive refreshes I'm getting double naughts again. Then every so often back to the 0.04 ...

Either way this really has to get into the core!

Great job X !!!

930sc ... because it is fun!


User avatar
Active Member

Posts

Joined
Mon Nov 01, 2010 7:29 pm

Post by Xsecrets » Mon Dec 27, 2010 3:38 am

SapporoGuy wrote:This is hot baby! Hot!

I'm getting: 0.002(4) at times 0.02.
Something with the cache or ??? makes it jump but on consecutive refreshes I'm getting double naughts again. Then every so often back to the 0.04 ...

Either way this really has to get into the core!

Great job X !!!
well it all depends on how many categories/products you have. If you are in the range of the demo data you should get .00? or better, but if you load up a couple hundred categories and and 5-10k products that will jump to more like .2-.5 and if you load up over a million well it jumps way up like on mine over 8 sec. When I first started with the database I could not get a page to load completely it would always time out. Now most pages load in under 10sec. I've put the tweaks on one other site for someone and it helped quite a bit, but no where near as much as I saw on my gigantic database. I suppose at some point the time it takes to query the db really becomes inconsequential compared to the rest of the page load time (images/scripts/latency/etc)

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by SapporoGuy » Mon Dec 27, 2010 3:43 am

OOps, forgot to mention I am running 2 mods on that query:
1 for the keywords and description and the other for multi lingual manufacturers. (gonna use the a bit different).

Code: Select all

$query = $this->db->query("SELECT DISTINCT p.*, pd.description AS description, pd.meta_description AS meta_description, 
pd.meta_keywords AS meta_keywords, pd.name AS name, p.image, md.name AS manufacturer, ss.name AS stock, wcd.unit AS weight_class 
FROM " . DB_PREFIX . "product p 
LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) 
AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' 
LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) 
LEFT JOIN " . DB_PREFIX . "manufacturer_description md ON (p.manufacturer_id = md.manufacturer_id) 
LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) 
LEFT JOIN " . DB_PREFIX . "weight_class_description wcd ON (p.weight_class_id = wcd.weight_class_id) 
WHERE p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' 
AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "' 
AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' 
AND p.date_available <= NOW() AND p.status = '1'");
So, it should take a tad longer than the standard default data.

However, your change brought back the times to something realistic again!

I was wondering, for the category display areas.
Wouldn't it make more sense to have everything cached?
And then on the product view do a full query to make sure everything is still current with regards to stock?

930sc ... because it is fun!


User avatar
Active Member

Posts

Joined
Mon Nov 01, 2010 7:29 pm

Post by jcgadgets » Fri Jan 14, 2011 2:28 pm

Hey everyone,

It seems as if the performance gains here are massive. Of course it is with a massive database, but if your site loads approximately 20x faster...it loads 20x faster. Of course that depends on overhead and things, but I want this in my site! Unfortunately, I'm sort of a noob at this stuff and don't want to be breaking anything. Would anyone mind coming down to my level for a minute and explaining what steps must be done to make this happen?


Thank you,
Jared

Active Member

Posts

Joined
Sun Oct 31, 2010 4:49 pm

Post by d7a7z7e7d » Sat Jan 15, 2011 1:48 am

Open up your Product model: /catalog/model/catalog/product.php and search for "pd.language_id" in each of the database queries. Anywhere you find that comparison, move it out of the WHERE clause and instead place it earlier in the query where the product_description table is being joined.

Do this for each query so that something like this:

Code: Select all

SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, m.image AS manufacturer_image, ss.name AS stock 
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) 
LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) 
WHERE p.product_id = '" . (int)$product_id . "' 
AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' 
AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' 
AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' 
AND p.date_available <= NOW() AND p.status = '1'"
Becomes:

Code: Select all

SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, m.image AS manufacturer_image, ss.name AS stock 
FROM " . DB_PREFIX . "product p 
LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id AND pd.language_id = '" . (int)$this->config->get('config_language_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) 
LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) 
WHERE p.product_id = '" . (int)$product_id . "' 
AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' 
AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' 
AND p.date_available <= NOW() AND p.status = '1'"
See how the pd.language_id check was moved?

You could probably do the same with the p2s.store_id and ss.language_id.

Image
OpenCart Extensions, Technical Support & Custom Development | Have I helped you?


User avatar
Active Member

Posts

Joined
Fri Sep 17, 2010 5:51 am
Location - USA

Post by Xsecrets » Sat Jan 15, 2011 2:15 am

d7a7z7e7d wrote: You could probably do the same with the p2s.store_id and ss.language_id.
actually it appears you can do it with all the language id's, however if you do it with the store id you get unexpected results. You can also do it in most of the other model files too. Basically every table that has a language id uses it as part of a combination primary key, and would benefit from this.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by Qphoria » Sat Jan 15, 2011 3:06 am

Ok I've made this into a vQmod script.

It includes changes for the pd.language_id that Xsecrets mentioned
and the additional ones for ss.language_id, and p2s.store_id that d7a7z7e7d mentioned
It only applies to the catalog/model/catalog/product.php file at this time

In my testing with this and the Log & Analyze DB Queries mod, on a default store the difference was negligible:
(testing the getLatestProducts query with limit of 8)

Default way:
Query Time: 0.0057
Query Time: 0.0052
Query Time: 0.006
Query Time: 0.0061

With the shift change:
Query Time: 0.006
Query Time: 0.0052
Query Time: 0.0054
Query Time: 0.0059

But word on the street is larger stores will benefit from this. This should hopefully make it easy for you to test. Keep in mind that if you want to test this yourself with Log & Analyze DB Queries mod, you will need to delete the system/cache files for each time you refresh your site to ensure the db query gets called with the new format.

Attachments


Image
Donate!|OpenCart Basics|GeoZones
Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by jcgadgets » Sat Jan 15, 2011 7:46 am

Thank you!

Looks like it's time for me to learn how to use vQmod (nervous :S)


Thank you,
Jared

Active Member

Posts

Joined
Sun Oct 31, 2010 4:49 pm

Post by Xsecrets » Sat Jan 15, 2011 7:51 am

jcgadgets wrote:Thank you!

Looks like it's time for me to learn how to use vQmod (nervous :S)


Thank you,
Jared
well it's not too hard to install vqmod, and once you've done that the nice thing is that any changes you make with it you can completely undo by simply renaming the xml file.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by POS » Wed Mar 23, 2011 5:24 am

Hi,
I get an error after trying the XML vQmod:
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND (LCASE(pd.name) LIKE '%ford%') AND p.status = '1' AND p.date_available <= NO' at line 1
Error No: 1064
SELECT COUNT(*) AS total FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id AND pd.language_id = '2') LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id AND p2s.store_id = '0') WHERE AND (LCASE(pd.name) LIKE '%ford%') AND p.status = '1' AND p.date_available <= NOW()
Any ideas?

PS: I am not sure what version of MySQL I have.

POS
Newbie

Posts

Joined
Tue Mar 22, 2011 11:34 pm

Post by Demon5 » Sun Apr 24, 2011 11:35 pm

Same here mysql on hostgator mysql Ver 14.14 Distrib 5.1.52, for pc-linux-gnu (i686) using readline 5.1

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND wcd.language_id = '1' AND p.date_available <= NOW() AND p.status = '1'' at line 1
Error No: 1064
SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, wcd.unit AS weight_class FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id AND pd.language_id = '1') LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id AND p2s.store_id = '0') LEFT JOIN manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN stock_status ss ON (p.stock_status_id = ss.stock_status_id AND ss.language_id = '1') LEFT JOIN weight_class_description wcd ON (p.weight_class_id = wcd.weight_class_id) WHERE AND wcd.language_id = '1' AND p.date_available <= NOW() AND p.status = '1'

Fatal error: Out of memory (allocated 17563648) (tried to allocate 16 bytes) in /home/demon5/public_html/core-router/store/system/database/mysql.php on line 29 when just trying to use google site feed

Fatal error: Out of memory (allocated 28573696) (tried to allocate 2 bytes) in /home/demon5/public_html/core-router/store/system/database/mysql.php on line 29 when clicking sales/mail.

This is with 47603 products.

https://www.lotnllc.com is your one stop shop for all your computer needs!


User avatar
Active Member

Posts

Joined
Sat Jun 19, 2010 4:12 am
Location - Sacramento, CA

Post by Qphoria » Mon Apr 25, 2011 12:11 am

Hmm the "WHERE AND" to "WHERE" bit in the last block of vqmod doesn't seem to be matching for some reason. Let me try it again

If you are trying to load 47000 in google base that will never work unless your host timeout is set really high. RSS feeds weren't ever meant to be that large and even with this db optimization, it won't change the fact that trying to load data for 47000 products on one page will still run out of memory. Perhaps an export to googlebase xml file on the server is an option, but still loading that much data from database will cause most hosts to timeout

Image
Donate!|OpenCart Basics|GeoZones
Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am
Who is online

Users browsing this forum: No registered users and 3 guests