Jump to content

Archived

This topic is now archived and is closed to further replies.

Noodleman

Performance of very large stores

Recommended Posts

I am currently looking into OsCommerce, and how it is likley to perform with a large volume of products and categories in the database.

 

I currently have a database with over 3.5 million products, across 15,000 categories. I know in product/shop terms this is a very large quantity, thus reaching out to the community for your own experiences with large stores.

 

I wanted to see if anybody has any experience of using oscommerce with large data sets, anything over 500,000 products really? If so, how was the application performance? Was the store reactive, or laggy? etc.

 

I am currently working on an assumption that providing the MySQL database is of the correct spec, and has been tuned correctly there would be no issues with a store of this size. Does anybody have any good, or bad experiences with large stores?

 

Thanks,

Share this post


Link to post
Share on other sites

@@Noodleman

 

Unfortunately, I have never worked on a shop with that many products. The largest for me was just over 200,000 and once it was optimized the store worked efficiently. This was on a dedicated server. Sorry I can't be of more help, perhaps someone has worked on stores with larger databases.

 

 

 

 

Chris


:|: Was this post helpful ? Click the LIKE THIS button :|:

 

See my Profile to learn more about add ons, templates, support plans and custom coding (click here)

Share this post


Link to post
Share on other sites

First, a dedicated server is a must. Having mysql on its own server would help. Beyond that, you'll have problems if you try to use the code as is with that many products. You would have to change the code in a way that it does not try to read in everything at once. For example, the stock shop will read in the categories on each page load. Even if you could load in 15,000 categories in a second, where would they be displayed? You would need to set it up similar to Amazon where only a small number of categories are shown and searching is controlled so as not to return all of the products. If setup correctly, I don't see any reason why the shop side couldn't function well with that many categories/products. The admin might be a different matter since the catalog section tries to load in everything at once and that just would not work well. You would need some new way to handle the products. So this is doable but a lot of modifications would be needed, in my opinion.

Share this post


Link to post
Share on other sites

mattice webmaster of oscommerce.nl is probably in a good position to help


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites

Thanks for the info :)

 

After some more thoughts on this, I decided the best way to answer the question was to simply just setup a demo store and see how it performs. It didn't take too long to produce a script to map the data between apps. I do have a dedicated SQL server, and a dedicated web-server. I've created a job today to populate OsCommerce database from my source data. It's currently running. I expect it will take another couple of hours before I can see how it performs without any modifications or tweeks. I will re-build all DB indexes after the import and see how it goes without any modifications at this stage. Will share my results.

 

I took on board the information about the categories, and how they are loaded each time in full. I can see that would be a pain. I've rolled up all my sub categories in my script, so that I only have 3 tiers. this has reduced the total category count to just under 400. I suspect this will be a little more managable.

 

@@bruyndoncx, thanks for the information. Will see how my test goes first. :), Also, respect to the best avatar ever. PATB was, and still is to date the best cartoon ever made (I collect the memorabilia).

 

@@Jack_mcs, luckily I do not need to worry about the product admin. I have designed various integration mappings between systems. These mappings take care of product updates etc for me from my other applications. As long as my source DB is up to date, everything else falls in line.

 

will report back with results when avaialble :)

Share this post


Link to post
Share on other sites

Initial results are in, and it isn't actually looking that good. I only imported 1.6m products in this test, a little under 50% of the total requirement.

 

After importing I rebuild all available indexes in the product tables. Using 400 categories, and 1.6m products I am finding it takes on average 3-5 mins to load any page, I suspect a little more work is required on the indexes. I will play about with it more today, although my initial finidings suggest perhaps oscommerce may not be the best choice for me.

 

@@Jack_mcs, I have a query related to your comment "You would have to change the code in a way that it does not try to read in everything at once". can you expand on that a little for me?

 

Thanks,

Share this post


Link to post
Share on other sites

nevermind, I found out what you meant. I created a new table and stored the category id & product count for each category, I then updated the queries to use this lookup table for the values rather than using the select count(*) queries in a loop for each category.

 

This bumped the loading time of the page from 3-5 mins to about 15 seconds. the 15 seconds was due to the length of time the query runs to find the new products. The biggest part of this time is taken up by the sorting of the data to find the newest items. Solution, put the new products into a new table, and read the data direct from there after doing a DB update. brings the time to load the page down to pritty much 0

 

A few more tweeks and I think there will be no issues.

Share this post


Link to post
Share on other sites

@@Noodleman

 

Category counts could be disabled at all, unless you need to show them ofcourse

 

You will find this of interest, maybe you could also update with your own results/changes

 

EDIT

 

Regarding the categories box, a thought would be not lo load the entire category tree on each page load, but to query each parent level category for child categories depending on what category user is watching on the menu, I think that is what @@Jack_mcs meant

Share this post


Link to post
Share on other sites

@@Noodleman

 

I can't imagine your logic in one online store perhaps in multidatabase enviroment. The threads maybe that you need. I wonder if a customer ask a search query with "pro" in advanced_search page in one database...

I really don't understand this issue. This is very impractical between related tables in only one database. :x

Sounds like a Mission Impossible


:blink:
osCommerce based shop owner with minimal design and focused on background works. When the less is more.
Email managment with tracking pixel, package managment for shipping, stock management, warehouse managment with bar code reader, parcel shops management on 3000 pickup points without local store.

Share this post


Link to post
Share on other sites

@@Noodleman

 

I can't imagine your logic in one online store perhaps in multidatabase enviroment. The threads maybe that you need. I wonder if a customer ask a search query with "pro" in advanced_search page in one database...

I really don't understand this issue. This is very impractical between related tables in only one database. :x

Sounds like a Mission Impossible

 

I'm not sure I fully follow your comments?

 

Update: I maanged to get the store working at a reasonable speed with 3.2m products in the DB. I re-wrote some of the box code that was slowing it down, and re-wrote some of the queries to improve their run time. Disabled category counts, I don't need them. I split out the latest products into a seperate table, and re-build the data on a schedule from the products DB. I found the selection query for new products / top products was taking a long time for such a small record count, due to the fact it selects all the data, then sorts it and only takes the top X products. The sort takes quite a while as expected. So, by pulling the data into a new table, and reducing the record count to only a few hundred across categories, it makes it lightning quick.

 

Several similar tweeks across other parts of the store, and its nice and quick now. Home page opens in about 1.5 seconds, and product categories around 2-3 seconds.

 

Still a little more tweeking to be done, but its working as i would like :)

Share this post


Link to post
Share on other sites

and reducing the record count to only a few hundred across categories, it makes it lightning quick.

 

Lol. :thumbsup: Yes you don't follow the origin mission.

Don't forget the cache functions in the store which can be usefull.


:blink:
osCommerce based shop owner with minimal design and focused on background works. When the less is more.
Email managment with tracking pixel, package managment for shipping, stock management, warehouse managment with bar code reader, parcel shops management on 3000 pickup points without local store.

Share this post


Link to post
Share on other sites

Thought I would follow up on this topic and share my tweeks, and the results of those tweeks.

 

With my store I am not using, and have no intention of ever using special offers. I've found that the query that uses special offers is really slow, specifically the IF statements used in the MySQL query. Here is an example.

 

IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price,

IF(s.status, s.specials_new_products_price, p.products_price) as final_price

 

with these statements in the query, the entire query was taking aroung 12 seconds to run. As I have no need to use this data, I've stripped it out of the query, using only the price from the products table. The resulting query time went from 12 seconds, to 0.012 seconds consistently (Even after flusing the cache). I noted that this statement is used frequently, so I've been tweeking it in various places. The result is a much faster product load time when loading a product info page.

 

The same check also exists in the search functions. Making the same changes here has reduced the search time also. I was getting around a 45 second search time to begin with. After tweeking the queries I have this down to between 0.75 and 6 seconds.

 

I also removed the code that does product counts of each category on page load.. that was very slow and will never scale up!

 

I will be adding one further enhancement, which is the product count query that runs when you load a category which is used to dispaly the total products in a category on the page. It is a seperate query to the one that grabs the actual data. I am going to put the record counts into a new table and modify this query so it just gets the value from the lookup table, rather than perform the count itself.

 

I've stopped tweeking the performance now, but I will be returning to it at some point after I do a full load of the entire database.

 

I find the site is using around 2.25GB of memory after indexes are loaded with 1.5m products and modified queries. I will be allocating around 6-8GB of memory in total once the full load has been done and monitoring for performance.

 

Cheers for your replies :)

Share this post


Link to post
Share on other sites

×