| |
MySQL Replication
By Jay Fougere
Expert Author
Article Date: 2008-11-18
Replication is a way to run several SQL servers so that all of them have a current copy of a database or databases. Keep in mind that replication is not the same as clustering and should not be confused as such, even though replication can solve many of the same problems that clustering resolves; albeit in a different manner.
Replication can be handled in one of a few ways, however the most popular methods use a master-slave, master-master or a circular relationship. A master-slave relationship is where the master machine receives writes to the database which are then replicated to the slaves with very small delays. There will be no changes made to the database(s) on the slave(s) directly as the slave(s) will receive all changes from the master.
A master-master relationship is a little misleading by name. In this situation all of the machines in the group act as both master and slave machines. This can be a bit trickier to set up than the traditional master-slave scenario. Master-master replication works well with two machines, however if your needs exceed the capabilities of two machines you may consider circular replication.
Lastly, you can set up circular replication, which is similar in some ways to both master-master and master-slave. The best way to describe circular replication is with an example.
Let's suppose you have three machines; Moe, Larry, and Curly. Moe runs MySQL as a slave to Larry but as master to Curly. Larry, as mentioned, is master to Moe but is a slave to Curly. That leaves Curly as master to Larry but slave to Moe. This way a change on any one machine replicates across all machines. See below:
One thing to keep in mind when considering replication is the location of the individual servers. Servers should be in the same physical location and would ideally even be located on the same network switch. Due to the nature of the Iinternet I cannot recommend trying to replicate over the internet and I would not even consider it unless the database in question is a very low volume database with regard to write transactions.
Why would you want to run several instances of the same database on several different machines?
There is a multitude of reasons for doing this and the exact reason will have a huge bearing on the particular setup that you decide upon.
The first reason that comes to mind is so that you can have a hot-spare in event that the primary database machine goes down (and given enough time, it will go down!). With a little coding, automatic failover can even take place or you can try out MySQL Proxy to handle failover for you. Also, having a hot-spare is a great place to run heavy queries that might otherwise affect the performance of the "live" database. This is also a great place to make your backups being that when the tables are locked while backing up the database it will not affect the responsiveness of the live database.
Another popular use of replication is to scale database load. When one machine can no longer handle all of the requests it receives it is often times more economical to add more similar machines than it is to replace a single machine with a larger machine. With the cost of Intel/AMD based machines being as inexpensive as they are, moving to "Big Iron" can be a costly choice compared to using replication. This is particularly useful in less-volatile databases that have a huge number of reads but very few writes. One can set up replication so that all of the changes to the database are made to a single master which is then replicated across many machines.
General Database Hardware Considerations.
Database servers, unlike most other servers, can have hardware bottlenecks almost anywhere, whereas your other typcical servers are usally bound by one of either memory, disk speed or CPU. This is dependent entirely on your database design and the typical usage of your database.
Typically I would order hardware that I know will be used to serve as a database server with as much memory as I can afford, followed by the fastest disks I can afford and finishing up with the fastest CPUs I can afford. This is because MySQL (in my experience; your mileage may vary) is very rarely CPU bound. Generally when I see MySQL in a spinlock it is due to I/O. Memory is super important in this respect because it is so much faster than even the fastest disks and MySQL will cache a lot of your database in memory if it is available.
Also, in this day and age there is no reason to even consider 32 bit hardware and operating systems. Memory is so cheap it is easy to break the 4GB limit that 32 bit hardware/software imposes. Yes, I know about Physical Address Extension however this still limits you to 4GB per process which can result in a bottleneck.
Fast disks are also very important, especially if your machine only has a limited amount of memory. Regardless, the faster the disks, the more responsive your database is going to be. This is especially evident on databases with a lot of writes. While you can cache tables to memory so that your read queries are lightning quick, write transactions depend on the disk.
If you have a smallish database that is generally queried with large exotic (ie CPU intensive) queries, CPU speed might be more important than memory or disk speed. This is an extremely rare occurence in my experience.
Next time - Setting up MySQL Replication!
About the Author:
Jay Fougere is the IT manager for the iEntry network. He also writes occasional articles. If you have any IT questions, please direct them to Jay@ientry.com.
|
|
