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
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.
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
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! 
OpenCart Extensions, Technical Support & Custom Development | Have I helped you?
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.
Problem was that they set a base line of where to start from in the article.
930sc ... because it is fun!
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
becomes
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.
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;
} 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;
} OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
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.
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!
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.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.
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
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 !!!
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!
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)SapporoGuy wrote:This is hot baby! Hot!
I'm getting: 0.002(4) at times 0.02.
Something with the cache or
Either way this really has to get into the core!
Great job X !!!
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
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).
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?
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'");
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!
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
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
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:
Becomes:
See how the pd.language_id check was moved?
You could probably do the same with the p2s.store_id and ss.language_id.
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'"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'"You could probably do the same with the p2s.store_id and ss.language_id.
OpenCart Extensions, Technical Support & Custom Development | Have I helped you?
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.d7a7z7e7d wrote: You could probably do the same with the p2s.store_id and ss.language_id.
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
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.
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.
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.jcgadgets wrote:Thank you!
Looks like it's time for me to learn how to use vQmod (nervous :S)
Thank you,
Jared
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
Hi,
I get an error after trying the XML vQmod:
PS: I am not sure what version of MySQL I have.
I get an error after trying the XML vQmod:
Any ideas?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()
PS: I am not sure what version of MySQL I have.
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.
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!
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
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
Who is online
Users browsing this forum: No registered users and 7 guests
