Data clusters using mysql

I'd want to create multiple database and in each of them maintain a set number of tables (ie 100 database each holding 60 tables). My application will have the infrastructure to know which DB and Table to access to find the data it needs. The content of each table would not exceed beyond 200 records but I want to be able to spread the da开发者_C百科ta across many machines for scalability.

What are the important issues to keep in mind while developing a a distributed system like using mysql? Where can I read to learn more about setting up such a system?

---

This is a great book:

http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/0596101716/ref=sr_1_1?ie=UTF8&qid=1294150323&sr=8-1

Typically you are only as powerful as your weakest database. If the performance of one slows down, then it will typically lock up web requests. Make sure to have great monitoring in place for your DB health and for your webapp health

200 records per table (depending on the number of fields and indexes) is a very small table size. This suggests that you should go back to the drawing board with your design.

100 databases is a lot to keep up with. If you go this route, automate everything! That being said, unless you have billions of records, you don't need this.

Based on the information you have provided, I would suggest scrapping your design and looking for something simpler. If their are external constraints that require this, then hire an operations person with mysql dba skills; what you described is a 10 - 20 hour a week commitment.

---

Based on the number of tables and records I would also recommend to rethink your approach. Designing with horizontal scalability in mind is commendable, but given the data count, you'd probably be better off to have just one DB server, optimised to keep the whole dataset in RAM (plus another node w/ master-slave replication for failover) and you'll be able to keep up with a LOT of traffic.

That aside, based on the little facts I know about your application, I would not recommend splitting your data in that many databases and tables. Sure, you can code whatever logic you want in your application to ensure that it knows where to find stuff but you are going to lose a lot of the power of SQL as you won't be able to combine data from different databases directly using pure SQL.