There are similar questions on this site, but they mostly cover server failure and data backup aspects. I'm concerned only as far as query performance. If master and slave share the same resources, Master table is used only for writes and Slave only for reads, will this help db to handle growing number of queries more efficiently, given that queries are properly optimized?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
It's very unlikely that running master and slave on the same machine would be beneficial, because the two instances would not actually be sharing resources in any positive or helpful way.
Running two instances with the same data means you have to divide up the available resources in some combination of implicit (cpu, disk, memory including OS cache) and explicit (buffer pool/key cache configuration) ways...
Significantly, the fact that one is a replica of the other means that database writes are contending for double resources at almost the same time: for each write done by the master, a comparable write has to be done by the slave, which means -- approximately -- twice the disk utilization, plus the fact that every binlog entry is written at least twice, first to the master binlog, then to the slave's relay log, then to the slave's binlog if enabled.
The fact that each instance now has less memory available means that less data can be cached in memory, meaning that you will further increase the need for disk access, as less cached data means more disk I/O, almost by definition.
There might be specific scenarios where splitting uo the workload like this could serve a useful purpose, but situations where such a setup would degrade performance seem to significantly outnumber any scenarios where you'd see an improvement... and any such cases would likely be very workload-specific.