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.
USE db_99;
do something with db_1 or 2 or 3;
will not replicate because of your binlog-do-db
and replicate-do-db
. (The latter is redundant in your case.)
This is because replication (except for "wild" versions) is based on the USE, not on any explicit db spelled out in the statement.
Is it a "bug"? Well, a lot of people get burned by it.
Best Answer
Anti-OPTIMIZE
If you are using InnoDB (which you should be using), then
OPTIMIZE TABLE
is almost totally useless. Stop running that.If you are
DELETEing
huge chunks of a table, and that led you to want toOPTIMIZE
, then let's discuss alternatives.Replication issues
It is possible to avoid replicating any command(s):
But it seems like you would want to
OPTIMIZE
on the Slaves, where you are doingSELECTs
?If you have a graceful way to take a Slave out of rotation, you could do a "rolling"
OPTIMIZE
across the slaves, one at a time.ANALYZE
If the goal is to refresh the "statistics", then
ANALYZE TABLE
is much faster, hence less intrusive.PARTITION
If "old" data is being purged, then Partitioning is much faster than
DELETE
, and has no need forOPTIMIZE
; should we discuss that?