I know this sounds like a programming question which means it should be on stackoverflow but i believe server admins worry about this more and i am not looking for just the programming answer to this question.
How do i create a database for scalability? I am in the middle of http://www.slideshare.net/vishnu/livejournals-backend-a-history-of-scaling which i cant read ATM and need to leave. But i would like to know more about creating a database that scales well. Somethings that it mentioned and occur in my mind are
- Separate handles for reads and writes?
- What happens when one server is busy (IO or CPU bound) and i need two servers to write to?
- Do i create multiple database? have a clusterId on users?
- Will it be a problem when moving users to one cluster to another?
- Might i code this so user ABC in DB A on cluster A and DEF in DB B in cluster B have the same PRIMARY KEY?
- When i move the above to cluster C? Does this mean i need to write much code to move them to another cluster/database?
- To make the above not an issue would i NOT use PRIMARY KEY and set the ID by hand by reading the other DBs on other clusters?