New to load balancing database, which idea is best?

[brentman]

So just to preface this, I have been part of two operations (one as developer, one with a 3rd party company developing) where the business was forced to cease due to difficulties in database load balancing and lots of people lost lots of money. I am talking about big data and high performance needed at the same time.

 

So for my new project, I am going to try and design it around having a forever expanding infrastructure of servers but that means setting it correctly from the beginning. I have put together some ideas for possible ways to split the database load across multiple servers. Any input to which idea(s) are best would be great so I know which to explore further. Also any relevant info on this type of thing would be helpful as this is the first time I am personally doing this.

 

Thanks!

[QuickOldCar]

http://cassandra.apache.org/

 

Not much more to say more, all the big players use it, originally developed by facebook.

[brentman]

Thanks that looks like it is the best solution. I am however nervous using something outside of apache/php/mysql for a production build as that is all I have ever worked with and I am on a time crunch to launch Jan 5th. Any more typical suggestions I can implement pre launch until I can migrate to Cassandra later on?

[kicken]

Option 3, except you use Replication to copy the data rather than a cron job. The master is usually treated as write-only also, all the reads are done via the slave servers.

 

So your scripts would have separate read and write connections. The read connection would go to one of the possible slave servers. The write connection goes to the master server. Whenever you need to make a change (UPDATE/INSERT/DELETE/etc) use the write connection. If you're just reading (SELECT/etc) use the read connection.

 

If you end up with multiple slave servers, then you can balance across them by either just choosing one at random, or use another separate balancing node that will cycle through them.