Question posted in 
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?
8
Answers
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.
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.
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.
No. If there was these companies with the 2,000,000 products would be using it.
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.
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.
No. But a MySQL database wouldn’t be better than a “nosql” database either.
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.
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.
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.
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?