Mysql – Insert into table select – Replication lag – Percona Server 5.6

MySQLmysql-5.6perconapercona-serverreplication

I have two MySQL instances (Percona server 5.6.31) in Master-Slave replication setup.

I have set the below configuration:

ROW based replication is set.
Transaction Isolation is set to read-committed.

Today, there was a insert going on in my Master. It was in the format

INSERT INTO table1 SELECT * FROM table2

Table 2 has 200 million rows.

Though the number of insert records was only 5000 but the operation lasted for 30 mins. I observed replication lag during the insert operation.

I have load infile disabled due to security concerns. Hence I can't insert using that as well.

I went this article from Percona which says that this can be resolved if txn isolation is used as ROW and versions above 5.1 that this is resolved.

In what way I can make my slave to be in sync with Master in such conditions?
Why does the slave lag here?

Best Answer

The semantics of that statement is to do it all in a single transaction. This may well exceed innodb_lock_wait_timeout and/or cause other operations to exceed it.

With RBR, each of the 200M rows will be separately fed through the replication stream; this is a huge amount of data (more than the table size, itself).

The Slave cannot handle any other replication during the execution of the 200M individual inserts.

What to do? First, explain more about the situation.

Does table2 change during the operation?
Does table2 exist on the slave?
Let's see SHOW CREATE TABLE table. (Need to see indexes, FKs, etc)
Is the schema for table1 identical to table2?
Can you switch to SBR for this one statement? That will solve the replication stream bloat, but not the other comments.
Will it be OK to copy the rows over in chunks of, say, 1000 rows? Each chunk being one transaction? (That is, if there is a crash, the table may be partially copied.) If so, adapt the tips in http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks .

Related Solutions

Mysql – How to diagnose missing rows on a MySQL slave

The traditional way would be to use pt-table-checksum and pt-table-sync

I like doing things a little different. I immediately run pt-table-sync with the --sync-to-master --print options.

Here is the --sync-to-master option

--sync-to-master

Treat the DSN as a slave and sync it to its master.

Treat the server you specified as a slave. Inspect SHOW SLAVE STATUS, connect to the server’s master, and treat the master as the source and the slave as the destination. Causes changes to be made on the master. Sets --wait to 60 by default, sets --lock to 1 by default, and disables --[no]transaction by default. See also --replicate, which changes this option’s behavior.

Here is the --print option

--print

Print queries that will resolve differences.

If you don’t trust pt-table-sync, or just want to see what it will do, this is a good way to be safe. These queries are valid SQL and you can run them yourself if you want to sync the tables manually.

This, if you do something like this:

echo "SET SQL_LOG_BIN=0;" > /root/SQLChanges.sql
pt-table-sync --print --sync-to-master h=10.1.2.30,u=username,p=password >> /root/SQLChanges.sql

The file /root/SQLChanges.sql will contain every change you need to execute on the Slave. Once you are satisfied with its contents, just execute the script on the Slave.

With regard to using LOAD DATA INFILE in Replication, @DTest answered this question about that. I further explained how mysql replicates LOAD DATA INFILE.

MySQL Error: impossible to write to binary log

There are 3 methods that MySQL can use to write to the binary logs:

STATEMENT

This means that every SQL statement on the master is recorded in the log, and executed on the slave. This can cause problems if the SQL statement contains statements such as "NOW()", "RAND()" and anything non-deterministic. This also requires support from the storage engine in use.
ROW

This means that every row that is changed by a statement is individually recorded in the binary log. This gives larger binary logs (generally) than statement based logging, but is almost always guaranteed to give the exact replication needed.
MIXED

This allows MySQL to choose between binary and row based logging as required.

If you're getting this error, one suggestion is to change the BINLOG_FORMAT variable to MIXED. This allows MySQL to automatically switch between ROW and STATEMENT based logging as required.

Best Answer

Related Solutions

Mysql – How to diagnose missing rows on a MySQL slave

MySQL Error: impossible to write to binary log

Related Question