When hosting thousands (or potentially millions) of iterations of Wordpress (e.g wordpress.com), is it better to to have one database per install, or assign a prefix to each install and use only one database?
Obviously, it would be easier (from an administration prospective) to create a new database for every install, but how does it affect server resources?
Maybe there is compromise between functionality and resource saving, for instance; grouping 100 installs per 1 MySQL database (using unique table prefixes for each install).
What is MySQL best practice when scaling multiple installs of any script that uses a database?
Any help would be greatly appreciated.
Many thanks, S.>
If you have "potentially millions" of mysql installs, you might end up needing multiple mysql servers.
I think sharing the databases and having multiple prefixes makes no sense, given you definitely need to be able to "move clients around" and to "protect clients from each other", this is achieved by having one db per wp install.
HTH.
In MySQL, databases are little more than a namespace for tables, which is basically the same thing as you would get if you used a prefix for the tables. Why not use the database for that?
Separating Wordpress instances by databases allows you to have easier operations for things like access privileges, backups, point in time recovery, query log analysis, and moving instances from one server to another. Many database administration tools can perform operations easily against all tables in a database, but not as easily against all tables with a specific prefix in their names.
I once spoke with the database administrator for WordPress.com, which hosts millions of WordPress instances. He said when they started that company, they stored all the WP instances together. Very quickly, they realized that operations would be much easier if they created a separate database per WP instance, and there was virtually no performance difference.