Mysql – How to view when thesql mixed mode replication is logging a statement using row or statement format

MySQLreplication

I have a couple of servers running in MIXED mode replication and I do maintenance regularly on them deleting a decent sized set of data. There is one table that had no primary key and is just used for logging. When we delete old data out of this table it runs fast on either the slave or the master but when it's part of a script that runs over night it blocks and runs very slowly on the slave. This was working fine when we were using statement based replication everywhere on an older version of MySQL.

The table has a structure something like this:

CREATE TABLE `user_events` (
  `event_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `event_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `type` tinyint(3) unsigned DEFAULT '0',
  `ipnum` int(10) unsigned NOT NULL,
  INDEX(`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The delete statement is a simple.

 DELETE FROM `user_events` WHERE `event_time` < CURDATE()

Doing research I discovered that it is possible that this is because the system is switching over to row-based replication for this particular statement. In particular it looks a lot similar to this bug: http://bugs.mysql.com/bug.php?id=53375

Only this delete does not use RAND() or LIMIT so it should not be nondeterministic. So I'm not sure why it is using row based. It might be because the script it is being run in is using temporary tables. I could confirm this by running the delete inside and outside a script that uses temporary tables easily enough if I could see whether the statement was logged using row or statement based replication.

If I can confirm when a particular statement is run whether it was written row or statement based I can quickly solve the problem. I would also like to know this in general so I can determine which if any statements we are using are being pushed via row based replication.

I know the ultimate solution is to change to partitioning or adding a primary key and breaking the deletes into chunks and I'm also aware I can force the replication mode to statement for a session or even tell the sql statement not to write to the bin log and run an equivalent delete on the slave, but I'm right now just looking for a better way to understand and diagnose the problem. Thanks.

Best Answer

The table has no index on event_time. Your DELETE must therefore perform a full table scan every time. This has to be a big contributor to the slowness.

SUGGESTION #1

Add an index

ALTER TABLE user_events ADD INDEX (event_time);

SUGGESTION #2

Instead of doing 

DELETE FROM `user_events` WHERE `event_time` < CURDATE();

Try loading a temp table and switching it into place

ALTER TABLE user_events RENAME user_events_old;
CREATE TABLE user_events_new LIKE user_events_old;
INSERT INTO user_events_new
    SELECT * FROM user_events_old
    WHERE event_time >= CURDATE() + INTERVAL 0 SECOND
;
ANALYZE TABLE user_events_new;
ALTER TABLE user_events_new RENAME user_events;
DROP TABLE user_events_old;

EPILOGOUE

Suggestion #1 may be all you need. Suggestion #2 is an alternative means of deleting old rows.

YOUR QUESTION

If you have binary logging on the Slave and the Slave is not a Master, disable it.

If you want to see which queries are expanding to ROW rather than STATEMENT, use mysqlbinlog against the binary logs on the Master to see a text representation of all queries. The row-based ones will be a little obfuscated. The statement-based queries will appear as it was executed.

Related Question