Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

1 Answer
1

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.

I thought I'd benefit from query cache when reading slave table. There are a few the same SELECT queries run by many user simultaneously, so I though to make read-only table and update it once in two days, so the whole time between updates all queries would use results from cache rather than actually accessing database. While Master table is updated frequently so cache is invalidated, but it is not necessary in my case to have updated info available instantly for reading, so I could update slave every two days. Goal was to take load off with query cache.
–
dmitNov 26 '13 at 12:00

So you were thinking of not leaving the slave running in sync with the master, as usual, but rather leaving it stopped and periodically catching it up?
–
Michael - sqlbotNov 26 '13 at 14:58

Yes, exactly. So what do you think in this case?
–
dmitNov 26 '13 at 16:37

"maybe" -- but have you considered using the MySQL event scheduler to periodically populate a "snapshot" table on the master, which could be served more consistently from the query cache since its writes would be less frequent?
–
Michael - sqlbotNov 26 '13 at 20:07