The simple answer is No.
In Mysql replication, Master copies the bin log files to slaves, and after that, it's work is over. Now the Slave will run the bin files and execute them, but there won't be any performance on Master.
There might be scenario where you are using full synchronous replication, in which master will wait for the slave to execute the query, but again it won't impact the performance in terms of memory or CPU, but the master will wait for the query to be executed.
Also, for your second question, Phil already answered it, that ssh sends data through encryption which uses a lot of CPU, hence if you want other ways, use the other methods which are described by Phil.
You could be suffering from what is known as data drift.
QUERIES
This can happen if there are queries that are unsafe for replication.
One of the more common types is running UPDATE
or DELETE
using LIMIT
. Using LIMIT
on DML can work just fine on a Master. On a Slave, the rows selected (and perhaps certain ORDER BY
choices) may not be the same set being updated or deleted as the set on the Master. See the MySQL Documentation for a Comprehensive Description of Unsafe Statements that can affect MySQL Replication.
Baron Schwartz once dealt with this and had to refactor his query to get around this
The following hypothetical scenario illustrates one way to introduce data drift:
BINLOGS
Master
- 20 DB Connections writing changes (INSERTs,UPDATEs,DELETEs)
- The I/O thread from a Slave has to serialize the SQL coming from the 20 DB Connections
- The serializing of the queries into the binary logs may be in an order different from when each DB Connection executed its change.
- sync_binlog set to 0 (default), which leaves the responsibility of flushing binlogs to disk in the hands of the OS
Slave
- I/O Thread reads binlog events in the order the Master wrote them
- SQL Thread executes binlog events in the order the Master wrote them
Observation
If binlogs are not flushed to Disk in a timely, predictable manner, any binlog events the Slave needs could easily be bypassed. This could cause data simply not exist on the Slave. Depending on the data recorded or not recorded, Replication's SQL Thread could break because of missing data or data that should be missing.
EPILOGUE
Not every Slave can be affected this way. Masters keep a list of all Slave I/O Threads and transmits binlog events to the Slave in order by ProcessID on the Master. I can see later slaves being victimized first.
If sync_binlog is indeed an issue, perhaps all Slaves have data drift and we just don't know of it yet.
The only way to tell is to download one of the following
or
and checksum everything on every Slave against the Master. You may find more data drift problems than you think. Just run the sync scripts to correct them.
CAVEAT
You suggested that network latency could be at issue. With binlogs not being flushed by the OS yet, any disconnect and reconnect of MySQL Replication due to latency or dropped packets is worth looking over as well. It could also be a major contributor to data drift.
Also to be noted is what network route the new Slave is communicating with back to the Master. If it is not the same route as the older Slaves (perhaps passing through a different switch, over public IP, etc.) needs to be investigated.
Best Answer
The amount of memory should be the same, the config should be the same (except config that pertain to Master / Slave only).
Reason is when you fail over, you don't want to spend time trying to find out what value should be there or not. I would even create 2 my.cnf files:
my.cnf
andmy_failover.cnf
(preparemy.cnf
on Slave in case master fails. File can containbinlog=1
(turn on bin log).All you should need to do is:
mv my.cnf mybk.cnf, my my_failover.cnf my.cnf
Then your slave will start login.
Regarding caching queries, well, all Select statement running on Master won't replicate to Slave. If your Master is 80% Select / 20% write, it should not be a problem.
If you want to cache your Select from Master, turn on Slow Query log for query > 1 sec then apply that to Slave. You could do that once a day (in morning).
Chances are, soon you'll be using your slave for reporting.
You could also use Percona Playback.