How can I have 2 database servers for my website?
Solution 1:
You have a few options to consider for improving your database performance not just including scaling out.
- Optimize MySQL Settings -- There are dozens of configuration parameters that can have a drastic impact on the performance of MySQL. Make sure you investigate this option first before deciding if you actually need to scale at all.
- Optimize Application -- Like Paul mentioned make sure your application is behaving reasonably. If you don't use the proper indexes and queries you're going to have bad performance regardless of the number of servers and waste money/time in the long run.
- Scaling Up -- Getting a bigger machine may be a simpler solution depending on what level of hardware you're currently on. For each application, though, there is some point where it will be cheaper/better to start scaling out instead (replication/sharding).
- Replication -- The usual configuration is to have one master server and multiple slave servers. The Master gets all the write requests and any of the slaves can serve read requests. This is good for cases where you expect many more reads than writes.
- Sharding -- Have multiple MySQL servers each handling a portion of the overall database. For example, users a-d are on db1, e-i on db2, etc.... I believe this is handled in the application layer and not MySQL itself.
The above items are in the order I would investigate them in although it depends on the application and your requirements. For example, if you were also looking to add redundancy for a high availability setup then replication would likely be the obvious choice.
Solution 2:
Someone posted a link to a livejournal mysql setup. They said that is better to stick to one big server rather than having it clustered.
Analyze the slides from one of the livejournal guys here: http://www.danga.com/words/2004_mysqlcon/
My suggestion is to start optimize your queries and you indexes. This reduced my load from average 4 to 0.5. Amazing, huh? By only adding extra indexes.
HTH