MySQL – Is It Unwise to Run Replication on the Same Physical Server?

myisamMySQLreplication

I'm contemplating setting up a Master-Slave replication for my database. The slave server will be used for redundancy and possibly a reports server. However, one of the biggest issues I'm running into is that we're already maxed out on power at our datacenter. So adding another physical server is not an option.

Our existing database server is fairly under-utilized as far as cpu (load averages never really get above 1 on a quad-core). So the leading idea is to toss in some new drives and double the memory (from 8GB to 16) and run a second mysql instance on the same physical machine. Each instance would have separate disks for the database.

Is there anything wrong with this idea?

Edit (more info):
I've (luckily) never had anything bad enough happen to take down the server, but am trying to plan ahead. We of course have nightly backups that we could recover from. But I figured having the redundant data on separate disks would provide a quicker solution if the master server's drives failed (obviously not if the entire machine goes out).

As for the reporting aspect, any tables we would report off of are MyIsam. So doing expensive reads on the same tables that are being written to can bog down the server. My assumption was having a slave server to report off of wouldn't affect the main server as long as we threw enough RAM at it (since cpu load hasn't been an issue yet).

Best Answer

For redundancy in terms of system reliability and data safety running a slave on the same machine as the master offers you nothing (or close to). If something bad enough to bring down the master happens it will probably bring down the slave too.

For purely segregating users for access rights reasons, a good RDBMS will offer more effective ways of doing that.

Running the two databases on the same machine will require more RAM to run at the same efficiency as the two databases will compete for space to keep they various buffers and caches. There might be a performance benefit though via IO-load segregation, if the datafiles for the slave are on different physical drives than the master. In this case you can run complex reports that required many disk reads against the slave without competing for the master for drive IO bandwidth.

Edit: as DTest mentions in his comment below, one other possible benefit of a slave DB (even if on the same drives as the master) is that complex long-running queries in the slave that might otherwise cause locking issues for the day-to-day-running queries on the master are safer. Though you are still better off having the slave on different drives as such significant queries are likely to cause IO contention issues.

Related Question