5

I have a site that has heavy traffic and my database server (mysql) is getting heavy traffic at peek hours. Instead of upgrading to a better server, I am thinking of having 2 database servers working together so the heavy traffic can be "split".

My question is how this can happen? How 1 database can be handled by 2 different machines? What is the common practice of achieving something like this and what you suggest for me to do?

My web server how will communicate with the database servers? Web server should communicate only with one of them or with both of them?

iPaulo
  • 417
  • 3
  • 12
mr didodido
  • 51
  • 1
  • 2

6 Answers6

6

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.

uesp
  • 3,414
  • 1
  • 18
  • 16
4

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

Paul
  • 1,857
  • 1
  • 11
  • 15
2

Cluster them.

Chopper3
  • 101,299
  • 9
  • 108
  • 239
  • Not sure why this was down-voted. It's a valid response. This is active clustering, all nodes responding, not just failover. – mfinni Feb 16 '11 at 21:57
  • thanks dude - could give a crap about the rep, just want people to be informed so thanks. – Chopper3 Feb 16 '11 at 22:31
2

What you normally want to happen here is that all your write operations always go to the same (master), and you use some form of replication to sync the new data from the master to the slaves. You can then use a load balancer to apportion read queries among the various physical servers available, with the understanding that for certain things these servers might be slightly out of date. The load balancer needs to also be smart enough to know that certain kinds of queries need to fulfilled from the master server, so that for example when I add this post to Stack Overflow I don't have to wait for replication to catch up to see my new post if I end up load balanced to a secondary server. In short, the application needs to be written to be aware of this as well. You can't just turn an option on in the database and have it all just work.

Joel Coel
  • 12,932
  • 14
  • 62
  • 100
2

The bottom line will probably look better if you just upgrade the server. Running two live databases adds a lot of complexity and isn't worth the relatively small cost of the hardware. Really large systems use things like Oracle RAC to run the same database across multiple machines but it doesn't sound like your in the million dollar server cluster zone yet. Having a warm standby is always a good idea and you can use replication to keep it ready to go.

JOTN
  • 1,747
  • 1
  • 10
  • 12
1
  1. You can split the read and write SQLs in the application level. Setup a replication with two servers you have in the application (I assume that you are using a class to handle read, write, connection to DB server) anywhere you have write, delete, replace, update SQL have them connect to your master server and all you read SQLs connect to slave server. This was you can take the load of reads off of your master server.
  2. With applying replication structure you also need to optimize your tables (index,..) and you queries as well so get the max performance. Also dont forget to tune your replication parameters and monitor the servers traffic and if you see more read SQLs coming in than writes then in future you can add second slave server to your architect.
  3. If you are using replication solution don't forget that your slave MySQL version should be either same version as master or higher, otherwise replication will break at some point.
  4. Replication is a nice and common structure but, with that comes challenges also, Like if you replication breaks then you should know how to start the slave from the point of break so both servers be in sync with each other.
  5. If it was me I would try to optimize my SQLs and Queries and tables first, monitor my traffic and see if I have more of read or write SQLs and base on that I would take the nest step.
Kourosh Samia
  • 321
  • 1
  • 5
  • 12