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

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
Derek Downey
  • 23,568
  • 11
  • 79
  • 104

5 Answers5

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.

David Spillett
  • 32,593
  • 3
  • 50
  • 92
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.

Gaius
  • 11,238
  • 3
  • 32
  • 64
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)

jcolebrand
  • 6,376
  • 4
  • 43
  • 67
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.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
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.

sabika
  • 1