Re: Are there any pitfalls with having multiple MySQL databases on 1 server node?
Posted by:
Rick James
Date: August 28, 2010 11:51AM
Sorry, I am a bit verbose on this topic. First, some simple answers to your simple questions. Then some probing questions about related, and important, issues.
* Can I still run only 1 MySQL instance to support those 12 databases?
Trivial.
* Does having 12 databases result in higher memory usage? How much higher?
The overhead of extra databases is trivial. Extra tables is very minor. 100 databases containing a total of 1000 tables is no problem. A million tables in a single instance is a problem -- and probably a design mistake.
* Does this require having 12X as many database connections?
So? On a moderate config, 100 connections is no problem. On a bigger system, 1000 is feasible. Beyond that -- need to rethink the architecture.
* Any other pitfalls?
You did not mention other configurations...
Another approach: Multiple instances of MySQL on a single machine. This can be done by using different "ports". The pitfall here is that you need to carve out RAM among the instances.
A 3rd party solution: Spider.
A big issue in Sharding: Ok, you have sharded the data (say, on users) for the main table among multiple machines. That is fine if all your queries include "WHERE userid = ...". But what about the queries that go across multiple users? In social networking it is often the "posts" that are the bulk, and thereby need sharding. But what about the list of "my posts"? Or the list of "my friends"? Or their posts? (But this is a different architectural discussion.)
How will you divvy things into 12 buckets? By a hash? What will need to happen if you grow to need 13? (But this is a different architectural discussion.)
A "connection" needs the triple (hostname (or IP), port, database name). Sharding involves calculating what triple to use. In the long run, you want to change the hostname. You proposed changing the database name in the sort run. I mentioned the third choice of changing the port.
Even better, is to have an "API layer" between the application and the databases. The front end does not present actual SQL statements, but an abstraction of that. Example: Instead of saying "SELECT * FROM foo WHERE userid=$userid", it asks "GetFooData($userid)". The layer is then free to hash $userid and map it to a different host / port / database. And when migrating a user, only the layer need be changed.
When you get to the point of needing sharding, you will have dozens, maybe hundreds, of front end servers and several api-layer servers. What kind of load balancer will you be using in front of the frontends?
How many Slaves will be hanging off each sharded Master? You realize that you can scale readonly operations arbitrarily via adding Slaves; this is relatively easy. Sharding is when you need to scale _writes_ beyond what a single server can handle.
My point is, in large scale deployment (which I do deal with), sharding is about the last thing that needs to be considered. Granted, it is among the toughest; it is good that you are thinking about it early. And your approach is reasonable.