Largest Database OS Commerce can effectively handle

May 19, 2003

Hello:

I have a huge database consisting of over 800,000 products that are sorted into approx 6,000 categories. Does anyone know if OS Commerce will handle this size database effectively, i.e. will page loads be quick (2 s or less) and are product searches quick? Are there any existing stores that are this big or bigger that I can visit to see an example? The existing shopping cart that I use (which shall remain nameless) has been brought to its knees with this amount of data and I need a new cart. Also, since we eventually will have over 2 million products, what is the largest number of products that OS Commerce can handle effectively?

TIA

Bill Ranney

Knowbotics Corp.

jon_l · May 20, 2003

I wouldn't of thought there would be any problems with osCommerce handling a database of this size. I'd of thought it would all come down to whether the database itself (mySQL) and the server can handle the load.

I also wouldn't of thought mySQL would have a problem with this amount of data, though you'd have to do some database tuning, especially adding some extra indexes.

I've been up to 6000 products in the database (ok, this is nowhere near your amount), but there was no noticeable difference compared to a couple of hundred products. Though I did add a few indexes.

It would be pretty easy for you to bring this up on a test site to check the performance. You would be able to test this on a standard osCommerce install, and there are a number of tools that would help load the data.

Jon.

Tecumseh · May 20, 2003

I don't think there would be a problem. You may need to re-evaluate the product and category ID numbers (size as in enough digits) in the database to be able to handle that number of items. Maybe it's already set up fo rthat amount .. not too sure.

Dave

petersj · May 20, 2003

You shouldnt have any problems - if your using a dedicated server server with a decent amount of RAM. If you dont require the reviews and bestsellers features then dropping these will speed things up as will turning off the display how many products are in each category option. You will also want to add some indexes to the products_to_categories table.

May 20, 2003

Do you know of a big e-store with over 500,000 products i can visit to check page load times, search times, etc?

Thanks. -Bill

petersj · May 20, 2003

Have a look at http://www.demusica.com/catalog/default.php I know the owner was having some problems with the speed to start with (search for "sheet music" in this forumn). It seems to be OK now.

May 20, 2003

I have read here about a few persons that had more than 1 million products with no problems and there is a few persons that posted tricks to optimize MySQL database

HTH

The_Bear

pjin · May 25, 2003

I loaded a database with 800,000 products on a dedicated server, and it was way too slow. Totally unacceptalbe. the dedicated server is 1.7G Hz and 512M ram. I can always upgrade Ram it does solve the problem.

Mr. "The_Bear" said there were a few persons that had more than 1 million products with no problem. Can you give me the reference site or contact info?

I would be happy to pay for someone to solve the problem. Thanks.

Daemonj · May 25, 2003

I loaded a database with 800,000 products on a dedicated server, and it was way too slow. Totally unacceptalbe. the dedicated server is 1.7G Hz and 512M ram. I can always upgrade Ram it does solve the problem.

First, use the load test (courtesy of The_Bear ;)) to see where your site is at in terms of performance. Copy the results or write them down for comparison after the modifications below.

Ensure that category counts are off.

Create a few extra indexes on some of the most used tables (see this topic) and optimize all of the tables.

Comment out calls to unused features (best sellers, reviews, manufacturers, etc.) from the column_*.php, default.php, and product_info.php

Enable GZip compression with a setting of 1.

Enable cacheing (be sure it is in your webspace and not the servers)

Aside from osCommerce, ensure that the mysql config file is setup for huge.

Ensure that Apache, PHP, and MySQL are all setup for performance (security is still a must and preceeds speed).

Then use the load test again to see the difference.

And that should do it for you.

After that, feel free to PM me and we will discuss that

I would be happy to pay for someone to solve the problem.

statement. ;)

Daemonj · May 25, 2003

Also, you did not mention the CPU type (Pentium, Xeon, Athlon, etc.) of your server.

That can make a big difference along with whether it is single or dual and what OS and version you are running. If you say Windows you should be shot. :lol:

May 25, 2003

Does type of CPU matter? What is the best to use from mysql standpoint?

Thanks.

Bill

Daemonj · May 25, 2003

Every piece of a server plays an important part in the performance of the system, especially when discussing a database driven one.

For a CPU, you do not want a Celeron or Duron (they do not have the necessary cache and pipe). You want at least a Pentium or Athlon and preferably a Xeon.

Your hard drives should be the fastest you can get (hopefully 10K SCSI's that are setup for RAID and are hot-swappable).

You should have at least 512K RAM and preferably 1GB with your size database.

May 25, 2003

Do you ever see the necessity to split web server from mysql server?

Bill

Daemonj · May 25, 2003

That really depends upon a lot of factors that are specific to each environment.

My general answer is no so long as the single server has the necessary resources to be both a web server and db server at the same time without impacting the other service.

When you split into a web server and db server you then have to take into account the lag of going across the network wire and connector (hub, switch, router) in additon to having 2 boxes with decent hardware.

In general, you will get more of a benefit from a single linux server that has dual-cpu's, adequate RAM, and good drives.

Keep in mind that your db server will need the same requirements so adding a web server on top of that is not that big of a deal.

pjin · May 30, 2003

I still do not understand why someone cannot load any dedicated server with a test database (I am willing to donate the database of about 800,000 products) and test it real time. And, all that matters is MAKE IT WORK. Discussions are really useless, or even non-sense, if no results come out. No, no, no, don't take it personal. I am speaking in general term.

Don't you think if someone come up with the real solution, he can sell a working solution that can handle one million products for $500/license and sell thousands of licenses? Why doesn't he do it? Is it so challenging?

If this is against the FREEware nature of oscommerce, then he can ask for donation, and get all donated money to support oscommerce and make it better and better.

I don't see anything wrong to sell license to reward the core developers of oscommerce, so they can do this full time. After all, everyone has a family to feed. It takes a full time endeavor to write killer app.

In fact, if you can make it, I can help you sell it (And I promise I would not take a penny from the sale. -- Solely a volunteer. We should pay the developers first). You bet there will be huge demand out there. I can bet a million for it.

May 30, 2003

Is there really a demand for a product that can handle 1 million products?

Really haven't seen any store carry that many. The only one that would be close is Amazon and they use a custom solution and hundreds of computers to power it. Even large retailers like Wal-Mart would be hard pressed to carry more than 100,000 products in real-time. I can't really see any small to medium online store handling that.

I will be opening a store next month with 39,000 products in the database and don't seem to have any problems. My machine is actually pretty pathetic compared to the behemoths on the net.

It has AMD Athlon 1700 XP+, 1 gigabyte of RAM and 100 gigabyte IDE hard drive. Nothing fancy or major there. Hopefully by the time I need that, I will be hosting it in-house on a dedicated line.

pjin · May 30, 2003

well you maybe right there are not a big demands there, but who says other merchants cannot go head to head competing with amazon?

With google and search engine technologies, amazon is nothing to fear.

trust me I believe quite many e-tailers out there will eventually challenge amazon in number of SKU's.

Of course, amazon is still the leader in registered customers. however, i bet you will agree you will be very happy if you can capture 0.1% of amazon's customer base.

There is NO loyalty in internet shopping. Whoever can cut prices to the lowest level and maintain an acceptable level of services will rule the world. Wal-Mart is several generations younger than Sears but, in the end, who rules?

dreamscape · May 30, 2003

You want at least a Pentium or Athlon and preferably a Xeon.

how about quad 3.6gHz Xeons?? :shock:

if someone can donate the 800,000 product database I can test the speed.

Daemonj · May 30, 2003

You want at least a Pentium or Athlon and preferably a Xeon.

how about quad 3.6gHz Xeons?? :shock:

if someone can donate the 800,000 product database I can test the speed.

All I can say is Holy Friggin Sh#t!. :shock: 8)

Obviously you already know that you are very lucky.

Can I ask what you are doing with that kind of system (trying to win RC5 or SETI all by yourself?)? :lol:

Daemonj · May 30, 2003

i posted relevant message before and you replied. I still do not understand why someone cannot load any dedicated server with a test database (I am willing to donate the database of about 800,000 products) and test it real time. And, all that matters is MAKE IT WORK. Discussions are really useless, or even non-sense, if no results come out. No, no, no, don't take it personal. I am speaking in general term.

The system already works and dedicated servers are too expensive to be sitting around with a test database that large. That would be too much of a waste of drive space and power for just a test area.

The person with the 800,000 records should already have an ample dedicated server. When they create their store it would make sense because then it would be functional and getting used.

When everything is tested and problems are solved, I am ready to pay $500 for a proven solution, or even more than that. However, I would like to pay for a working solution that is mature, well tested, and free of bug, NOT a simple promise. I do not mind paying even more for a reliable solution that works seriously, but I am quite wary of consulting charge based on hours. In the end, only results matter. Hours do not count.

You might want to take a look at product if you want something to pay for.

Don't you think you can sell a working solution that can handle one million products for $500/license and sell thousands of licenses?

In fact, if you can make it, I can help you sell it. You bet there will be huge demand out there. I can bet a million for it.

No, I do not think that you will make a lot of money on that type of system. Anyone with that number of records (and there are few) is already using a custom-built system that is likely tied into MSSQL Server or Oracle and is not just for web sales but everything else in their company.

Besides that, any well-written program with an adequate database and good server can handle a million records. How well the queries are optimized along with the server will be the determining factor of a site's speed, not the total number of records in the database.

dreamscape · May 30, 2003

You want at least a Pentium or Athlon and preferably a Xeon.

how about quad 3.6gHz Xeons?? :shock:

if someone can donate the 800,000 product database I can test the speed.

All I can say is Holy Friggin Sh#t!. :shock: 8)

Obviously you already know that you are very lucky.

Can I ask what you are doing with that kind of system (trying to win RC5 or SETI all by yourself?)? :lol:

It was only supposed to be dual Xeons but the datacenter messed up the order and got dual P4s... to correct it they gave us quad Xeons... OK by me :lol:

its not a dedicated server, but its not very full either... its a reseller server and I got an account on it before the host cut it ooff (he only sells a small number of accounts on each server)... I run madmacgames on my account on it (yeah I'm a hog, I took an entire reseller account for myself (4gig space, 60gig transfer)....)...

Daemonj · May 30, 2003

its not a dedicated server, but its not very full either... its a reseller server and I got an account on it before the host cut it ooff (he only sells a small number of accounts on each server)... I run madmacgames on my account on it (yeah I'm a hog, I took an entire reseller account for myself (4gig space, 60gig transfer)....)...

I was wondering who the owner of that fabulous store was. Awesome work you have done there. I will bet it took some time!

It must not be too full of a sever because MadMacGames loads like it is on a dedicated server.

yeah I'm a hog, I took an entire reseller account for myself (4gig space, 60gig transfer)....)...

I am a reseller myself but only have a dual Pentium system. :( I thought that I was doing well until your post. :( ;) :lol:

davidstummer · May 30, 2003

i have a numberplate site. when fully running it will have around 10 million + combos on. If you want to upload products to a remote host, you may want to think about using other ways to upload apart from things like ep. I use SSH, and a mysql client on my pc to connect. this works really well, and is so much faster than using your browser to do the work

davidstummer · May 30, 2003

AND....

i am on a shared server!!!!!!

(no p*ss taking please, i am a student in debt!)

Largest Database OS Commerce can effectively handle

Recommended Posts

Guest

jon_l

Tecumseh

petersj

Guest

petersj

Guest

pjin

Daemonj

Daemonj

Guest

Daemonj

Guest

Daemonj

pjin

Guest

pjin

dreamscape

Daemonj

Daemonj

dreamscape

Daemonj

davidstummer

davidstummer

Archived

Browse

Activity