The problems with multi-tenant MySQL

Just about every web host in the world gives people “mysql databases”. Usually one. Why? because that’s how permissions work in MySQL and it’s a relatively simple way to set it up (each of your web host clients is a user with access to their one database).

This has a lot of limitations for the end user:

  • You also only get one user (that can do anything). This means you can forget enhancing security of your web app by not letting the front end DROP TABLE (or DROP SCHEMA). This is probably one of the main reasons that so few web apps use any of the access controls available in MySQL.
  • You only get one database. Typically this means you get to run one application. Or, you start to get applications that allow you to specify a table name prefix as a lovely hack around this kind of situation (ick).
  • If you get more than one database, it’s going to be all prefixed with your username – and it’s going to be via a web UI to create/drop them – not via the normal way.
  • You’ll never get replication.
  • If you need to scale – it’s migrating hosting solutions, there isn’t an easy to use “now i need read-only slaves” button.
  • Backups are only ever going to be SQL dumps from the master (if you don’t completely trust your hosting provider)
  • If your provider does do replication, one other bad user in the system could introduce EPIC amounts of replication latency.

One solution is to give everybody their own MySQL instance. Ick. Why ick? Well… you now have a lot more MySQL servers to administer. That’s not the problem however: you’ve just screwed yourself on IOPs – each mysqld gets to compete to sync its data to disk.

The next step people go to is running MySQL inside a virtual machine. You are again screwing yourself on IOPs. Every VM on the box can now fight each other for a limited number of sync operations to make the data safe on disk. Forget if group_commit works for your MySQL version, having many VMs running MySQL on the same physical box will screw you much more than lack of group_commit ever will. You can probably kiss consistent performance and latency goodbye too (this will largely depend on how VMs are being run by your hosting provider).

The best way to get screwed is to get “free” extra CPU cycles and IOPs that are excess on the physical machine and then to suddenly switch to not getting any “free” ones and instead only the ones you pay for… wonder why your site is suddenly slow to respond where the number of visitors is the same and you’ve changed NOTHING?

Even running MySQL inside a VM that is the only VM on the box has a performance impact. You want to be using each physical machine to its fullest. If you’ve got a bunch of MySQL servers running inside VMs – you are not doing that.

(you can substitute just about any other database server for “MySQL” in all of the above… interestingly enough I have been told that a certain proprietary database server has a very low performance drop when run inside a VM)

6 thoughts on “The problems with multi-tenant MySQL

  1. Yes this really asks for a vMySQL to be created. Quota is another issue for hosting providers. Someone can fillup the entire datadir. A solution might be to use innodb_file_per_table and use filesystem quotas on the database dirs.

  2. The multi-tenant database management problem is a big issue. Most SaaS multi-tenant apps I have seen use a single login and put user data in separate schemas as you mention. They don’t use or care about separate logins because the security model is in the app itself. It would be interesting if drizzle could put in some minimal features to support this most common multi-tenant use case, for example to ensure (a) that transactions always have the default schema correctly marked and (b) to prevent apps from jumping across schema boundaries for a single transaction. From there it seems you could grow to the harder use case of creating user partitions within the DBMS, which is the only model that appears scalable.

    XtraBackup is incidentally putting in support for restoring individual schemas as well as tables. This can get you away from mysqldump for individual tenants.

  3. Pingback: Parallelizing MySQL replication slave - a dream come true? | OpenLife.cc

  4. I trust exactly zero pieces of software to work correctly when out of disk space/quota… and I’d be terrified of having MySQL rely on it. I’d make a bet on it breaking, but that would be a totally unfair way to take your money :)

  5. Pingback: Multi-tenancy Drizzle | Ramblings

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.