| Posted by coume, 03-15-2010, 06:36 AM | | Hello,
I am looking at my next set up and I am considering having my MySQL DB replicated in another DC (with another backup) to maintain a safe backup...
MasterDB: Servint DC
SlaveDB: RackspaceCloud DC
By replicating the DB in a different DC, would it impact my forum speed (when users read or post new threads/posts)?
When using a DB replication, what kind of additional load will/should it add to my master server?
thanks in advance.
Ludo
|
| Posted by jweeb, 03-15-2010, 06:45 AM | | It would slow down if your database replication bandwidth together with your normal visit bandwidth usage is maxed out.
|
| Posted by ideamine, 03-18-2010, 06:07 AM | | It will definitely slow the server.
|
| Posted by coume, 03-18-2010, 06:51 AM | | Hello, could you please elaborate in which way? Server load, etc?
Thanks
|
| Posted by madaboutlinux, 03-18-2010, 07:04 AM | | In what way are you going to use the replicated data? If your forum is configured to use the database from MasterDB server (both server hosted in same DC), it won't impact the website performance much but it is always recommended to have a high configuration server and host website and database on it to achieve maximum performance.
If the forum is going to fetch the database records from the SlaveDB hosted in a different Data Center, it will definitely going to impact the performance. With performance I mean is, the forum will respond slowly to your clients. Since the requests has to travel from one DC to another, it depends on the intermittent connection between the two DC (the hops between the two and their response time) and will effect the website performance.
|
| Posted by coume, 03-18-2010, 07:34 AM | | The idea is to have the forum using the MasterDB in the Servint DC. The slaveDB in the RackspaceCloud DC would just be a live copy of the MasterDB as the forum would never read data from this SlaveDB.
The idea of having this live DB copy is to be able to switch to the RackspaceCould in case of critical failure at Servint to minimize downtime.
The initial idea was to rsync hourly the DB over but some people mentioned the live DB copy hence why I am trying to research if this could work or not (too resource intensive or not)
thanks
Ludo
|
| Posted by madaboutlinux, 03-18-2010, 08:18 AM | | If you are having a SlaveDB server just as a backup, rsync will not be a problem and easy to configure. You can set an hourly cron to just sync the database from Master to Slave. Though the rsync will be performed on the live DB, it won't create any problems.
And and hourly cron to sync the database won't impact the server performance as well. BTW, make sure the SlaveDB server is properly secured and only the ports required to access the server are open since the server is only going to act as a backup server.
|
| Posted by matt2kjones, 03-18-2010, 04:22 PM | | I have replicated Databases across two offices for a company i worked for in the past and saw no slow down what so ever, and that was with a 2GB database.
|
| Posted by coume, 03-18-2010, 05:09 PM | | Interesting Matt. I guess I will just have to test it out to see how it works for me
Thanks
Ludo
|
| Posted by kselva, 03-19-2010, 02:02 AM | | The Database replication on different DC may slow down server (for insert opertion),i would recommand to try the following things :
1)Mysql clustering
2)Sharing the data direcotry through NFS
|
| Posted by ianeeshps, 03-19-2010, 05:24 AM | | I would like to recommend the same mysql clustering option and it should be within one dc and internally networked servers.
If your servers are locally networked in a DC then it will be really nice to setup mysql clustering there. With local ip address. That would be safe and easy
|
| Posted by Scott.Mc, 03-19-2010, 07:02 AM | | That would best, especially given some of the replies in this thread which are incorrect.
mySQL replication is asynchronous so you can run in the setup you want with the "lag" providing you only use one of them unless it's failed over then you shouldn't have any issues. Just expect the slave to be a few seconds behind but for actually using the master all your operations (inserts/updates/selects) shouldn't have any impact.
|
| Posted by coume, 03-19-2010, 07:22 AM | | Thanks Scott. I should also add that the amount of insert operations is pretty low in comparison to the the others.
A newbie question, is the "lag" by default or is it something that you need to activate?
Thanks
Ludo
|
| Posted by Scott.Mc, 03-19-2010, 07:26 AM | | It's by default, essentially (in principle) all replication does is send the writes to a file which in turn is then played to the slaves which then executes the writes again. So distance+latency isn't a huge issue but don't expect your slave to be up to the minute, generally with such distances however it should perform reasonably well and you should only be a few seconds out should you have to failover.
|
| Posted by coume, 03-19-2010, 07:31 AM | | That's perfect then because even at 2-5min lag would be acceptable!
I will update here once it is done.
|
| Posted by fwaggle, 03-19-2010, 11:43 AM | | That's a worse idea than replication, to be honest.
|
| Posted by Toby H, 03-19-2010, 01:09 PM | | Done properly, you should not see a dip in performance with this set up. If it is done badly, there is a chance you will but it would depend very much on your configuration and the way the server talks to the database. Have you looked at cluster options?
|
| Posted by matt2kjones, 03-19-2010, 03:46 PM | | Out of all of the idea's here..... MySQL replication is by far the best. Using rsync in unsafe, because unless you lock the tables, flush the data to disc, copy the files somewhere, then unlock the tables, then rsync the files from the copies location... you will get data corruption on your slave as data will be cached in memory, or half writting to disc as it transfers, etc.
Plus, if you did lock your tables, flush the data, copy the files, then rsync from there.... the whole duration the tables are locked, all your INSERTS & UPDATES will be waiting for the table to unlock... which will make your site look like its timing out, especially if it takes a while to copy your tables.
MySQL replication wont lag at all..... no matter how slow your connection is... the slave will be behind, but thats not really an issue. It will add a bit of load to your server, but unless your pushing it close to the limits as it is, you should be ok, as most of your queries are probably SELECTS which wont cause replication anyway.
I mentioned that i used MySQL replication between two offices before. The setup was basically a company, with two sites, 60 miles apart, both with 8mb dsl, with 768k upload. We used a VPN to interconnect the site, one on 10.0.0.x the other on 10.0.1.x.
For 2 years we used replication, and the slave wasn't even ever that far behind.... probably no more than 30 seconds.
|
| Posted by UnixCabin, 03-20-2010, 11:50 AM | | Have 2 VPSs with elite data hosting... one in webnx and one in fdc. Using master-to-master mysql replication. Abid I don't have a busy site but the replication is almost instant.
Few tips to keep in mind: 1) set both slave replicator not to start after system reboot in case corruption don't get copied over. 2) use encryption, either vpn or make sure your mysql server is compiled with it 3) enable compression with replication, this should help with busy site.
|
|
 Add to Favourites
 Print this Article
|
