1

I want to prepare my database for sharding in the future. But right now I only need 1 node.

What I'd like to do is to shard the database into virtual shards, say 12 shards, and put all of them onto the same server node for now.

And when I need to scale out, I could just then get a 2nd server and move 6 of the 12 virtual shard onto the new machines.

Are there any pitfalls with having 12 different databases on 1 machine?

  • Can I still run only 1 MySQL instance to support those 12 databases?
  • Does having 12 databases result in higher memory usage?
  • Does this require having 12X as many database connections?
  • Any other pitfalls?

Thanks.

Continuation
  • 3,080
  • 5
  • 30
  • 38

1 Answers1

2

Nothing wrong at all. There might be a slight overhead in memory usage, but if you are in a situation where you have to consider sharding, then memory should not be your main problem anyway.

Your applications can use a connection pool. Most MySQL connectors support this mode, and it let's the pool manager decide on the most efficient way to handle the requests. In theory, they could also use one and the same connection (and issue "USE db_name" to change databases), but that can get really confusing for the developers.

wolfgangsz
  • 8,847
  • 3
  • 30
  • 34
  • When you said "slight memory overhead" how much would that be? Does going from 1 database to 12 databases increase memory consumption by 12X? Or even just double memory consumption? – Continuation Aug 26 '10 at 11:59
  • It's hard to tell without actually doing it. MySQL keeps various caches for its databases, including some for the table descriptors. As the tables would now be spread over mutiple database, I would expect some additional cache requirements for these additional databases that have to be kept open. I don't think that anyone could answer this with any precision until you really try it out. – wolfgangsz Aug 26 '10 at 13:23