Vadim and I have just published a new technical white paper. It shows how Percona Server with XtraDB can make large-scale multi-tenant databases easier to build with MySQL. Our experiences working with SaaS and shared-hosting companies influenced the features we included in Percona Server and XtraDB, and I think this is the best explanation of what levers are available and how to use them.
Percona Server with XtraDB for Software-as-a-Service Application Databases is posted on the white-paper section of our website. No registration is required; it is just a PDF, freely downloadable with no marketing follow-up. Many thanks to Mark Callaghan and Dimitri Kravtchuk, who reviewed and gave valuable suggestions that made the white paper better.
Interesting writeup. Good to see clear benefits of using Percona Server and XtraDB to solve some problems from the normal MySQL/InnoDB. Also I never considered having separate DB users for each SaaS user for a little pet project. But how you highlighted the benefits of being able to track an individual’s usage for sharding planning could be very helpful.
Thank you for sharing it with us.
I’m not sure you do a good job of backing up your arguments against the EAV model. Salesforce, the larges SaaS provider, uses exactly that model (with a lot of customizations at the database level). Granted, most companies don’t have the engineering budget of Salesforce to develop that type of architecture, but the tradeoff is the number of DBAs needed to maintain the approach that you are recommending. This is one of the things Salesforce touts when talking about the database model. Of course, automating some of the maintenance is an option as well, but it is interesting to discuss the tradeoffs.
What about what I recommended requires more DBAs?
I’ve never personally seen EAV work well. In many cases it runs into a hard wall because MySQL is limited to 61 tables per join, and people want “tables” with more than 61 “columns”, which requires more than 61 self-joins of the EAV table. (We’ve even been hired to patch MySQL to raise this limit.) But there are exceptions to every rule, which is why I said “The EAV model *usually* causes serious and immediate performance problems, and simply will not work for *many* types of applications,” without using absolutes.
As for SalesForce, I know from experience that what companies say externally about their success is often intended to intimidate competitors, while their engineering staff boils in frustration at the cost and effort required to work with the systems the non-engineers are boasting about. So although I won’t argue with you, I take it with a grain of salt.