Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two MySQL (MariaDB actually) servers, a master and a slave with standard replication. Two times already in less than a month I've had my slave become unusable.

This is what I experienced:

  • Notice a drop in query per second on the slave with my monitoring tool
  • Connect to the server with the mysql client, run SHOW SLAVE STATUS \G
  • Notice everything looks normal (Waiting for master to send event, seconds behind master = 0, etc)
  • Try and stop the replication with STOP SLAVE
  • STOP SLAVE hangs
  • Impossible to connect to the server with a client
  • Execute SHOW SLAVE STATUS \G on another already connected client, and it hangs too.

After that, I saw the MySQL process using CPU, and I saw a lot of disk writes with iotop, so I let it do its stuff for a while.

After 15 minutes I killed -9 the MySQL process because I got frustrated. I then restart MySQL, and I can connect and run queries, good.

I execute SHOW SLAVE STATUS \G which now works, and it looks normal: seconds behind master = 0, waiting for events. Then I go on the master and execute CREATE DATABASE foobar to see if the replication works, and it doesn't.

Again, at that point I see a lot of disk writes so I assume the replication is catching up. Two hours later, I finally decide to check SHOW PROCESSLIST and all I see being executed is a OPTIMIZE TABLE table1 ... and then I remember that I had that exact same problem last time.

I kill the process with that query, then I reexecute SHOW PROCESSLIST and sure enough it's catching up with the master. SHOW SLAVE STAUTS \G reported 15k seconds behind master, which was coherent.

Anyway, after that wall of text, my questions:

  • why does a OPTIMIZE query block the replication catching up ?
  • can I prevent those OPTIMIZE queries ? I didn't put any cron to do that, and as far as I can see, nothing in /etc/cron.{d,daily,hourly,weekly} does this.

Thanks.

share|improve this question
    
As a side note, in MariaDB as well as MySQL, if you STOP SLAVE during a long-running operation, STOP SLAVE will block and subsequent SHOW SLAVE STATUS will also block. That behavior is confusing but normal -- in the interest of data integrity, STOP SLAVE does not stop the slave until the current replication event group has finished executing. –  Michael - sqlbot Jun 18 '13 at 2:18

1 Answer 1

up vote 0 down vote accepted

Your First Question

why does a OPTIMIZE query block the replication catching up ?

The SQL thread in MySQL Replication processes SQL commands from the Relay Logs as a FIFO queue (i.e. first-come, first-server basis). Once the SQL thread see a command, it processes it to completion. Meanwhile, the Seconds_Behind_Master just keeps creeping up.

While the SQL thread on the Slave is running OPTIMIZE TABLE, once you run STOP SLAVE; it SQL thread has complete the running OPTIMIZE TABLE before the SQL thread can terminate. That's why STOP SLAVE; hangs. To verify this, open another mysql session and run SHOW PROCESSLIST;, You should see a line that has OPTIMIZE TABLE with the status Killing slave....

Your Second Question

can I prevent those OPTIMIZE queries ? I didn't put any cron to do that, and as far as I can see, nothing in /etc/cron.{d,daily,hourly,weekly} does this.

Someone or something is running the OPTIMIZE TABLE. You have to hunt it down in all your jobs or PHP apps. Use the mysqlbinlog utility to dumps all the binary logs into a text file. Then, grep the text file for OPTIMIZE TABLE or optimize table. The timestamp of when it was submitted into the binlogs will be a few lines about the command.

When you do find that OPTIMIZE TABLE command in your jobs or PHP code, simply run this:

SET SQL_LOG_BIN=0; OPTIMIZE TABLE tblname; SET SQL_LOG_BIN=1;

This will not register the OPTIMIZE TABLE in the binlogs, thus preventing a Slave from replicating it.

As was just pointed out in the comments below, You can also replace

OPTIMIZE TABLE tblname;

with one of the following:

OPTIMIZE NO_WRITE_TO_BINLOG TABLE tblname;
OPTIMIZE LOCAL TABLE tblname;

to restrict the OPTIMIZE TABLE command from landing in the binlogs and thus prevent replication of it.

share|improve this answer
    
OPTIMIZE LOCAL TABLE tblname and OPTIMIZE NO_WRITE_TO_BINLOG TABLE tblname are equivalent commands that accomplish the same thing -- optimizing the table on the server where the command is issued without writing the statement to the binary log -- without the need to manually set SQL_LOG_BIN off and on again. –  Michael - sqlbot Jun 17 '13 at 23:25
    
@Michael-sqlbot Thanks, it's been awhile since seeing that syntax. Taken for granted and overlooked too long. Thanks again. –  RolandoMySQLDBA Jun 18 '13 at 0:51
    
@RolandoMySQLDBA Thank you, the first part makes sense. For some reason I did not consider an OPTIMIZE query like a regular query. As for the second question, there was in fact someone who launched an OPTIMIZE, but killed it after. I guess it got replicated anyway. –  Vincent Rischmann Jun 18 '13 at 7:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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