How to Handle 2 Million Products

Question

I work for a web company and we currently use a highly modified version of OSCommerce as our primary ecommerce application, but recently we have been approached by a number of companies with over 2 million individual product models they want to sell online.

Essentially my question is - are there any prebuilt PHP/MySQL shopping applications that are going to handle this many products gracefully or am I out of luck on that front? Am I going to need to create a custom app? What are my options here?

Would a nosql database be better than MySQL?

Noelnoelani

Don't over-architect. 2 million sounds like a lot, but any decent database/hardware combo out there can handle 2 million records in a table without breaking a sweat (assuming you have some decent indexes). See here re: inserting a million records a day: forums.mysql.com/read.php?61,44239,44251#msg-44251 – Manson

Answer 1

The answer to my own 10 year old question is basically what @jvenema was trying to tell me in his comment. Indexes. Good indexes.

10 years ago me had essentially no idea what he was doing, we largely only added indexes when there was a performance issue.

2 million rows in a database to me these days is basically nothing. Where I work now we have tables terabytes in size with billions and billions of rows.

Noelnoelani

Answer 2

Essentially my question is - are there any prebuilt PHP/MySQL shopping applications that are going to handle this many products gracefully

No. If there was these companies with the 2,000,000 products would be using it.

Am I going to need to create a custom app?

You already have. You started with OS Commerce as a base and built a custom application on top of it. You may not think of it as an application, but it is.

What are my options here?

Accept that you'll need a decent IT/Development team to chase after this work, and asses if that cost and R&D is worth going after this new business.

Would a nosql database be better than MySQL?

No. But a MySQL database wouldn't be better than a "nosql" database either.

Infanta

maybe a Oracle db would be better to manage this huge number of records – Masonmasonic

Answer 3

You definitely want to build a custom solution, and you definitely want to charge a lot more than you normally would because there's a lot of risk and due diligence needed.

As for your architecture, using NoSQL is possible and there are some compelling reasons behind using NoSQL for ecommerce - the main reason being that it is schemaless and if you have a ton of categories and a ton of products which all need to be sold differently (ie you sell computers differently than you sell watches) because the product attributes are different, managing database complexity becomes really important.

This video will show you what a really forward thinking start-up in NYC is doing. They're using MongoDB for their entire product database. This video should be a real eye opener as it outlines a lot of the pitfalls in MySQL for big ecommerce sites, and alot of the game-changing potential of NoSQL:

http://engineering.shopopensky.com/topics/mongodb

As for handling payments, you definitely do NOT want to store those in NoSQL. Keep your users, sessions, and payment data in MySQL and make sure it's highly secured. Here is a great (even though old) piece on securing sessions in PHP applications:

http://www.troubleshooters.com/codecorn/php/persist.htm

As a note, this last link should help you understand the theory better. Most PHP frameworks support this type of session handling out of the box. CodeIgniter, Yii, and ZendFramework are among the best.

Plucky

FWIW, 2 million entries isn't a big deal for MySQL, and I think generally keeping 1 database will make your app simpler to maintain in the long run. Personally, I don't think NoSQL is a good plan here. An interesting read (if a little old) can be found here, re: Facebook's use of MySQL: blog.facebook.com/blog.php?post=7899307130 – Manson

If all a shopping cart was was a single table full of products the world would be a much simpler place. – Infanta

+1 for acknowledging the risk inherent in storing payment data in a technology that doesn't support transactions. And in some cases, a bit immature. – Discordance

Answer 4

In response to the Magento Enterprise suggestion, we've recently implemented this application for our site which carries approximately 150,000+ SKU's. We too were migrating from an extensively customized osCommerce version. Our experience has been one of frustration and project delays because of the slow speed with which the enterprise system functions as well as lack of documentation for implementation. We've actually been unable to use much of the enterprise edition functionality because of this.

The application is notorious for its lack of documentation and slow response from the Varien support team, which we've experienced first hand. The templating system appears to be only partially complete, and not particularly well thought out. One of the responders to your question, Alan Storm, has actually been a savior to our team with his well written tutorials and generous stackoverflow.com answers.

My recommendation is to do extensive research beforehand of the Magento Enterprise platform - it is not the same as the community version. As Bob Brodie posted above, the server requirements and setup are not for the faint of heart, nor are they inexpensive. Investigate the speed enhancement options available - you will need them, the server overhead costs, consider the learning curve and additional time that it will add to the project timeline - Magento is significantly different from osCommerce - and above all find a reliable, experienced web host before you pay the $12,000 One Year licensing fee.

Maomaoism

Answer 5

You don't make it clear as to whether that's an inventory of 2 million products or 2 million individual items they'd like to sell. Either way a traditional SQL database should be able to handle it fine, although this is entirely dependent on how the schema is designed, etc. I'm not sure about pre-existing solutions though although I've heard good things about Magento.

Hilda

Answer 6

Depending on budget, Magento Enterprise may be a good solution for you. (I don't say that to sell it, I'm not a partner). You could either architect the solution or have a hosting company help you out. Rackspace is the premier partner hosting company and is great at putting these solutions together. There are a lot of numbers that come into play, such as peak simultaneous connections, pageviews per hour, transactions per hour, etc. You'd want to look into something that has at least 2 MySQL servers (master/slave replication) and multiple frontend servers behind a loadbalancer. Instead of Apache, take a look into nginx. You'll also want to check out apc & memcached for caching. A setup like that will go a long way. When set up correctly, Magento Enterprise could handle this many products with ease. The important thing to remember is that a custom solution wouldn't need to be developed - it would need to be engineered.

Adaxial

Answer 7

i'm just curious if anyone thinks that this set up could do it. magento community with a reverse proxy (probably varnish) and memcached as the app's cache. with no dynamic content on the product pages (since it could be rendered upon custom interaction with the cached response) to keep requests to app to an absolute minimum. using load balanced nginx servers.

also you could implement more structured database maintenance and optimisation procedures as a separate app.

maybe you could do something quite radical, but quite cheaply (i guess the guys with 2 millions products have a different idea of cheap to you and me), change the sales, tax and sales rule modules to something less flexible but more efficient.

i dunno seems like the best way to me. paying for magento EE every year vs a initial spend to boast performance.

Lorenzoloresz

Answer 8

One of the best answers to this sort of product load (and, incidentally, the one we're using) is https://magento.stackexchange.com/questions/459/running-magento-in-an-aws-environment

We're running a custom magento community (1.9) server on an Amazon Web Services beanstalk environment with RDS for products, redis for cache & S3 -> CDN for the media associated with Magento. It's early days, but we've found no real issues so far. Estimated development time... maybe a week or so to transfer from a VPS with local mysql / cache / apache / php?

Mordvin