If you are running LOAD DATA LOCAL INFILE
on a Master every 5 minutes, how does that replicate ?
Back on Jan 22, 2012, I wrote an answer to this post : MySql shell command not replicated to slave
In my answer, I explained how LOAD DATA LOCAL INFILE
gets replicated:
- Master
- Executes
LOAD DATA LOCAL INFILE
- Copies the contents of entire text file used into the binary logs
- The SQL command
LOAD DATA LOCAL INFILE
is appended to the latest binary log
- Replication ships all of this from the Master's Binary Logs to the Slave's Relay Logs
- Slave
- Sees the Text File in the Relay Logs
- Reads all the blocks from multiple Relay Logs
- The text file get materialized in the
/tmp
folder
- Reads
LOAD DATA LOCAL INFILE
command from Relay Log
- Executes
LOAD DATA LOCAL INFILE
in the SQL Thread
During Steps 1-4 on the Slave, the IO Thread would have to be in the Reading event from the relay log
state, constantly extracting the CSV needed for the next LOAD DATA LOCAL INFILE
command. Sometimes, this causes Seconds_Behind_Master
to leap hundreds or even thousands of seconds at a time without warning after staying relatively idle.
Running STOP SLAVE;
does not help at all. The reason STOP SLAVE;
hangs? Steps 1-4 of the Slave will lock the IO Thread until the complete extraction of the CSV file. Even when the extraction has completed, there is the LOAD DATA LOCAL INFILE
itself. Running STOP SLAVE;
will lock on the SQL Thread running LOAD DATA LOCAL INFILE
.
In this crazy paradigm, Replication Lag has to increase steadily. Just look at your Relay_Log_Space
. It is 5708184440 (like 5.3 GB). There are multiple LOAD DATA LOCAL INFILE
commands just waiting to execute.
Look at the how Seconds_Behind Master
increases
LOAD DATA LOCAL INFILE
is executed on the Master
- The CSV needed in loaded into Binary Logs
- The CSV needed in unloaded from Relay Logs
LOAD DATA LOCAL INFILE
is executed on the Slave
If LOAD DATA LOCAL INFILE
takes 2 minutes, double that number and add the taken to ship the CSV file through the MySQL Replication process. You may need to come up with a different method of loading data that does not use LOAD DATA LOCAL INFILE
.
UPDATE 2013-04-05 15:00 EDT
If MySQL Replication continues falling behind (Seconds_Behind_Master keeps increasing) while the Master keeps getting log-jammed with small LOAD DATA LOCAL INFILE
commands, there is only one more thing I could suggest: Most people never touch sync_binlog, which is normally zero. What effect can this have on replicating LOAD DATA LOCAL INFILE
?
According to the Documentation on sync_binlog
, this setting can be used to flush binlog changes to disk. Since it is 0 by default, your Master DB Server is at the mercy of the OS because OS is responsible for flusihng binlog changes. When you set sync_binlog to 1, everything may actually change for better. How?
Here is what is probably happening when sync_binlog
is 0 on a Master:
- You run
LOAD DATA LOCAL INFILE
- mysqld on Master writes the command to the binlog
- mysqld on Master writes the entire CSV file into the binlogs
- mysqld on Master leaves it to the OS to flush binlogs changes
- Slave reads all binlogs info from Master except the last binlog that the Master's OS did not flush
- Slave status shows it is trying to retrieve the remaining info
This is how sync_binlog
can hopefully improve things:
- You run
SET GLOBAL sync_binlog = 1;
- You run
LOAD DATA LOCAL INFILE
- You run
SET GLOBAL sync_binlog = 0;
- mysqld on Master writes the command to the binlog
- mysqld on Master writes the entire CSV file into the binlogs
- mysqld on Master flushes every write to the binlogs because
sync_binlog = 1
- Slave reads all binlogs info from Master
- Slave status should show it has read every needed binlog
Give it a Try !!!
UPDATE 2013-04-09 11:23 EDT
If you have a low-to-moderate amount of writes (INSERT, UPDATE, DELETE, and ALTER TABLE) in the Master DB Server, leaving sync_binlog
at 1 may not be a bad idea. You would then need to do the following:
STEP 01) On the Slave, run STOP SLAVE;
STEP 02) On the Master, add sync_binlog=1
to /etc/my.cnf
:
[mysqld]
sync-binlog=1
STEP 03) Run one of the following on the Master:
SET GLOBAL sync_binlog = 1;
or
service mysql restart
STEP 04) On the Slave, run START SLAVE;
Give it a Try !!!
Munin's formula for "device utilization" is (milliseconds spent doing I/O)/second, which assumes you can't do any I/O in parallel, so I'm not sure that this is a meaningful metric. However, you do clearly have a genuine performance issue here since replication can't stay caught up.
Is there a significant difference in the I/O subsystems between the servers?
The way I would approach this is to compare the I/O subsystems and then if there isn't a glaring difference in architecture, run some I/O benchmarking at a quiet time with a tool like bonnie++ or fio to narrow down the difference in performance.
Note that the slave likely has an equal write workload to the master because it has replay all the writes, plus it may have a considerable read workload (for reading any parts of the database that have to be updated) and might be less efficient for reads than the master because it has less RAM for caching. In this situation I would not necessarily expect the slave to be able to catch up because it's entirely possible for the master to generate work at a rate faster than the slave can consume it.
Best Answer
TL;DR: This is probably caused by poor table design combined with ROW-based replication.
I just ran into this problem. I was asked to move an old database to a new server and set up replication.
I found that it's not actually the statement in the subject that causes the slave to hang (SET @@SESSION.GTID_NEXT= 'ANONYMOUS'). This statement is issued at the beginning of a transaction.
This table has 66 million rows. I found that it has no primary key or unique key. The query responsible for this uses an index scan on the master.
For the slave to replicate this with ROW-based replication, it needs to perform approximately 1200 full table scans on the slave. 1200 is probably a fairly small number here. It could be in the hundreds of thousands. The replication does actually work, but with this design, 'seconds_behind_master' will grow indefinitely.
I will add a primary key and partitioning to this table. I will also ask my colleagues to rewrite their code so bulk deletes are no longer necessary. This probably requires adding an additional column.
EDIT: I don't have enough points to comment on other posts, so I will add my comments here for now. I believe that issuing 'SET GLOBAL sql_slave_skip_counter = 1', as mentioned by others, will skip the entire transaction and lead to data inconsistencies. Correct me if I'm wrong.
A quick fix would be to change the binlog format to QUERY or MIXED. These formats can also lead to data inconsistencies, so I would recommend finding and fixing the root cause instead of changing the binlog format.