List:Replication« Previous MessageNext Message »
From:Jesse Date:July 23 2008 12:47am
Subject:Re: Best way to recover from Replication Failure
View as plain text  
>> Yes, I do use LOAD DATA INFILE periodically, but I don't use cron (
>> or Windows scheduler in this case) to execute it.  When I use it, it's
>> usually to import several hundred records into a certain table.
>
> That could be the problem right here since there were a bunch of bugs
> related to LOAD DATA INFILE and the binlog. Some were corrected

But, I hadn't used that before I recently initiated replication.  I've only
in the past week or two initiated replication on two of our master servers,
and the one I had problems with was not one that I would even use LOAD DATA
INFILE on.

> already, others are scheduled for upcoming releases. Please have a
> look at the output of the following commands on *both* the master and
> slave:
>
> SHOW VARIABLES LIKE "read%buffer%";
> SHOW VARIABLES LIKE "%packet%";

On the master, it returned the following:
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_buffer_size     | 61440  |
| read_rnd_buffer_size | 258048 |
+----------------------+--------+
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+

On the slave, it returned:
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_buffer_size     | 131072 |
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16776192 |
+--------------------+----------+

This is on one of my master/slave setup's

>
> IIRC your read_buffer_size (and possibly read_rnd_buffer_size) should
> be sane and should not be greater than max_allowed_packet. Also,
> max_allowed_packet should be pretty large and should be quite a bit
> larger on the slave than on the master. In theory, this should work
> around those bugs.

If it came to it, I could create a program to import the data for me.  That
would probably make it work faster anyway.  I've just never gotten around to
writing a program to do it.  That would use the "INSERT INTO..." phrase, and
skip LOAD DATA INFILE all together anyway.

Thanks again for your help.

Jesse

Thread
Best way to recover from Replication FailureJesse22 Jul
  • Re: Best way to recover from Replication FailureMichael Bunzel22 Jul
  • Re: Best way to recover from Replication FailureJesse22 Jul
    • Re: Best way to recover from Replication FailureMarcus Bointon22 Jul
    • Re: Best way to recover from Replication FailureMichael Bunzel22 Jul
  • Re: Best way to recover from Replication FailureJesse23 Jul