13

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).

Improve this question
11

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.

Improve this answer
2
  • 1
    my thought was the slave would not compete with the table locks on myIsam when the master is being written to (for complex reports). – Derek Downey Jan 11 '11 at 20:34
  • @DTest: that would certainly be a possible bonus, yes (+1). For other table types too when a large lock (like a table lock) might get requested by a complex report query. I'll add a note to my answer so it is less likely to get cut form display if more comments turn up. – David Spillett Jan 11 '11 at 23:36
7

It's not clear to me how this solves your problem. There's no redundancy since it's on the same physical hardware, the same OS kernel, the same MySQL binaries, maybe different disks but on the same storage controller, etc. And the reason for a reporting DB is to offload queries from the OLTP DB and as it's all on the same kit, where's the extra power coming from? Or is there something else you are trying to get from this setup?

One conceivable use for this would be to segregate users somehow perhaps, but again, I would have thought that could have been done with GRANT.

Improve this answer
1
  • added some more notes to my question. segregating of the users isn't what I'm trying to accomplish, though – Derek Downey Jan 11 '11 at 20:31
2

It is indeed considered unwise, are you just trying to take advantage of more cores? What are the goals of the new design consideration?

(posted as an answer not a comment to keep conversational thread focused)

Improve this answer
4
  • offer a slight bit of protection against drive failure, and at the same time provide a server for complex reports that won't slow down production sites accessing the Master. – Derek Downey Jan 11 '11 at 20:37
  • Will they be using the same disk controller or will you be able to install a new disk controller in addition to new spindles? – jcolebrand♦ Jan 11 '11 at 20:59
  • 1
    I just came across this one. I noticed your rhetorical questions mentioning taking advantage of multiple cores. I actually discussed that in my answer two months AFTER you said this. +1 for your insight ahead of mine. BTW Here is a nice video on running MySQL multiple times : youtu.be/5uKBg9prA1A – RolandoMySQLDBA Aug 23 '11 at 3:54
  • BTW My employer has a client who I arranged this architecture for. It has three read slaves on one machine (all in MyISAM) while the master is all InnoDB. – RolandoMySQLDBA Aug 23 '11 at 3:56
1

This can be a double edged sword

You can run multiple instances of mysql as read-only slaves on the same server as the master, provided each instance of MySQL resides on a different disk. This is only desirable if you are running older versions of MySQL that do not take advantage of multiple cores (CPUs). The latest versions of MySQL can be tuned to actually use the Multiple CPUs making it unnecessary for accessing multiple cores by running multiple instances of MySQL.

At the same time, it is also a very bad idea. Many clients of mine have done this to save money on purchasing bare metal or VM servers. Any spikes in server load can affect all running MySQL instances because of bad queries, slow queries, too many connections, glutted memory usage, insufficient server memory, cache thrashing, and so forth, in any one MySQL instance. This also adds application complexity by having to access the different MySQL instances via their port number and also you would be at the mercy of TCP/IP.

Improve this answer
0

I would criss cross the replication on a different server ie A's slave on B and B's slave on A. We run multiple instances on our servers and have had no issues as our MySQL servers were not at capacity. Fail over to a running server is much faster than doing a restore from a backup.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.