MySql Replication Questions

2
votes

I am looking at switching from a standalone single MySql server to a Master->Slave Replication setup (using 2 servers). Currently there are a number of heavy write (insert/update/delete) jobs (for loading fresh data from external sources) that run for several hours each day. These jobs slow down "read" (selects) performance on corresponding tables. Hoping that the replication will help a little with this issue (among other challenges like backups).

questions:

1) Does the slave replication "SQL Thread" lock reads when executing the relay log? Trying to gain more insight on how the SQL is actually executed (on the slave). I am hoping that the execution on the slave is more "optimized" (as opposed to the original statements executed on the master), so any potential locking is minimal. Otherwise, the same kind of "read" performance bottleneck exhibited on the master will trickle down to the slave.

2) From reading the docs, sounds like by default SBR is used (Statement based replication). Not sure if to change this to RBR (row based)? Or "mixed". Which would be recommended? I am inclined to stay with default, but really uncertain.

3) When some of the jobs complete, we currently run ANALYZE & OPTIMIZE to recover file space for corresponding tables where heavy delets/updates were performed. My understansing is that these commands will be replicated to the slave unless we run them with "NO_WRITE_TO_BINLOG". Not sure how the slaves tables are impacted when doing lots of updates/deletes. Is it even necessary to replicate these commands to the slave? The main concern is that OPTIMIZE in particular locks the table, and on a live production site (and a large table), this is a very big problem.

Thanks in advance for any pointers/insight!

1
mysqlreplication

1 Answers

2
votes

From the look of it, the only benefit you will get from replicating the data is that the master will not need to process the reads, and if you have multiple slaves, the insertion on the slaves should be quicker as well, because the reads are distributed. How much depends on how many reads are being performed in any give time.

If this will speed up the insertion of the data into the master and slaves significantly then, you may end up with a win.

As far as locking goes, it doesn't propagate to the slave as far as I know (this is also stated in the faq below). I guess that you could optimise the slaves in rotation, so that only one is out of action at any given time.

Useful reading http://dev.mysql.com/doc/refman/5.0/en/replication-faq.html