I have process which executes query like below on Mysql(InnoDB) slave

INSERT ignore INTO user_profile_temp (some_fields) select some_fields from user_profile

user_profile is bigtable of 8GB which will take 1 to 2 hour with HDD.

Because of this query , replication(master to slave) is waiting in queue to get above query getting complete. There can be lag of 1 to 2 hours.

Lag is happening because of select query.As select query will acquire shared lock only at row level which mean replication records not impacting current read in process , should be able to update the record parallely.

My question is does replication process waits if any user process running at that instant or it can run in parallel if it is not impacting same rows/tables?

  • what mysql versions (master and slave)? By user process are you talking about a querying being run on a slave? Is the table user_profile_temp innodb? – danblack May 23 '19 at 1:57

It depends.

Older versions of MySQL run one replication query at a time. Newer versions can do some level of parallelism.

Meanwhile, queries against the Slave can run in parallel just the same as reads and writes can go in parallel on a single server. If the write (from replication) and the read (from a user on the slave) do not touch the same rows of the same tables, then they can go in parallel. But if a significant lock is taken out by one, the other must wait. Or get into a deadlock.

It's not clear where your INSERT is coming from. It should not be coming from the Slave, since Slaves should be readonly. If it is coming from the replication stream, then what version of MySQL is running and how is it configured?

| improve this answer | |
  • Insert is a statement which is executed on slave as part of scheduled cron. Insert happens on temporary table. On sql version, I will check and update – emilly May 25 '19 at 1:44
  • @emilly - The only connection (user or replication) that can see a TEMPORARY TABLE is the thread itself. So, it might run in a separate thread. (If other tables are involved in the cron job, it will might block or be blocked by other connections.) – Rick James May 25 '19 at 23:46

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.