1

I'm working on a project where work in a queue requires a database read against heterogeneous table schemas (InnoDB/MyISAM). The queries constructed during the "read" process can result in 10-15 minute execution times, and they will be run in parallel, say 10-15 at a time, but they will each be mutually exclusive and unaware of any sibling processes.

QUESTIONS

  • Is it better to stop replication, issue the read query, then restart replication ? If so, what happens to a currently running read query if the replication is suddenly turned back on (by a sibling process)?
  • is it safe to allow simultaneous read queries and replication processing ?
2
+50

You need to be aware of query results and query behavior with replication running. While there are a minimum of two threads for MySQL Replication, it is the SQL thread that can get in the way of SELECT queries. Why?

MyISAM

Each time an INSERT, an UPDATE, or a DELETE is executed, a full table locked is issued. That can block SELECTs. The only exception is when you have concurrent_insert set to 2 and the MyISAM table experiences no UPDATEs or DELETEs.

Please see my earlier posts on MyISAM behavior.

AFFECTS ON REPLICATION : You should be a little concerned here because the SQL thread only processes INSERTs, UPDATEs, DELETEs, and various DDL commands. Should any of these command get issue by the SQL thread during MySQL Replication on a MyISAM table you are reading, there will be some latency due to the momentary table lock.

InnoDB

InnoDB can be a little more forgiving in allowing you to read from an InnoDB table that is being written, but it comes at a price. What is the price ???

InnoDB allows for MVCC (Multiversion Concurrency Control) and Transaction Isolation. This means InnoDB will write gobs of housecleaning and point-in-time information so that you can read rows from an InnoDB table without blocking incoming writes, and vice versa.

Here is a pictorial representation of InnoDB's Architecture

InnoDB Architecture

All that housecleaning and point-in-time information sits inside the Undo Space and Rollback Segments inside the system tablespace.

AFFECTS ON REPLICATION : When it comes to MySQL Replication, INSERTs, UPDATEs and DELETEs against an InnoDB from an active SQL thread will not block SELECTs from that same InnoDB tables. Of course, any DDL against an InnoDB table will block SELECTs just the same as a MyISAM table would.

MySQL REPLICATION

MySQL Replication can also be affected for the same reasons I mentioned. The shoe would be on the other foot: the SQL thread would get locked if it needs to do an INSERT, UPDATE, or DELETE against a MyISAM table. For InnoDB, MVCC info would be written up to permit SELECTs on the Slave.

EPILOGUE

If the Slave has hundreds of DB Connections, one more DB Connection (the SQL thread) may not be a lot to worry about

If you do know that the Slave may experience an onslaught of INSERTs, UPDATEs, and DELETEs coming from the Slave's SQL Thread, you could run STOP SLAVE; to disconnect the SQL thread and stop processing SQL commands and also stop downloading Master binlog events. As an alternative, you could run STOP SLAVE SQL_THREAD; to disconnect the SQL thread but allow incoming master binlog event to continue to be downloaded to its realy logs and await processing when the START SLAVE; is issued later. If the number of SQL commands coming from MySQL Replication are few and far between, then MySQL Replication can be left running.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.