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)