0

Let's suppose a system that has a MySQL master server and a slave.

A lot of read\write is happening on the master server and I choose to perform a heavy slow query on the slave.

What could happen? Could the master server show some slowdown?

XCore
  • 119
  • 3
  • 9
  • I'm not sure if you can configure it in a different way but at my job master waits slave confirmation so the answer would be yes, master would be affected – Olvathar Mar 20 '14 at 16:54
  • So, for instance, supposing that the slave at some point crashes, what would the Master do? – XCore Mar 20 '14 at 16:56
  • A few weeks ago we had a crash on a slave server, that left a remaining insert query halted on a MyISAM table which blocked any other petition and everything crashed. Sorry, I'm not a db admin and I'm just reporting what our admin told :D – Olvathar Mar 20 '14 at 17:03
  • @Olvathar lol I can bet you had wonderful moments there in the office ;) – XCore Mar 20 '14 at 17:10
  • Just to clear, are you asking about replication, and what version of MySQL server? – Marcus Adams Mar 20 '14 at 17:11
  • @MarcusAdams uhmmm about master\slave I'm talking about replication. But I'm actually talking about something like this: we have a master db which has a high read\write rate. Then there's the slave which serves as replica. That said I'm trying to perform mmm let's call it "data analysis". I don't want to block the master db, so I was planning to use the slave. But I'm not sure if that could bring any benefit. – XCore Mar 20 '14 at 17:12

1 Answers1

0

Using a replicated slave server for read only queries for such things as reporting is common practice.

Replication on the master reads from the log file and does not hit the actual database tables. Replication on the master side does not perform any queries, so no real database load.

Replication has its own thread on the master server, and while it's blocked, it should not stop normal database operations.

MySQL replication is able to fully recover after a crash and will automatically pick up where it left off.

There is one caveat. With MySQL 5.5+, MySQL supports (default is off) Semisynchronous Replication, where a database request on the master won't respond to the client until after the slave has confirmed receipt of the transaction. If it times out waiting for the slave to respond, MySQL will revert to asynchronous replication. When it's determined that the slave has caught up, it will attempt semisynchronous replication again.

This means that if you run a query that just completely bogs down the slave server, you may see a delay of up to 10 seconds on the master side, but after that, nothing should be impacted. You can configure the timeout.

Also, if all of these servers share the same network bandwidth, consider that returning large result sets from either server may cause issues if you reach the maximum capacity of your bandwidth.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143