MySQL Diary > Cool MySQL Queries > As restoring a dump table into the MySQL master – you better get some sleep

As restoring a dump table into the MySQL master – you better get some sleep

Posted on April 4, 2011, 2:09 am, by Hazan Ilan, under Cool MySQL Queries, MySQL Script.

Restoring a dump table into the MySQL master server can lead to serious replication delay. The massive inserts commands cause the Master and slaves to use most of their resources for replication. As a result, replication lag may increase dramatically (linear to the table size).
To overcome the replication delay, caused by restoring the dump table on the master, there is a need to widespread the massive inserts. This can be done by the MySQL SLEEP command: 

Mysql> SELECT SLEEP(1);

Each MySQL INSERT command (actually, each INSERT command consist of many values), should be follow by a SLEEP command, which will give the replication, time to recover (releasing the resources taken by the replication).
The question that can be asked here is how much SLEEP time should be written. There is no absolute answer for this question and it depends on the system properties and the table size.
In my experiments, one second was enough.

Let’s see how easily it can be done:

Bash> mysqldump  -h xxx -P xxx –u xxx -pxxx my_db my_table > /tmp/my_dump.mysql_dump
Bash> cat /tmp/ my_dump.mysql_dump | sed "s/^(INSERT INTO.*)$/1nSELECT SLEEP(1);/" > /tmp/my_dump.mysql_dump_with_sleep

You can detect the sleep time inserted by:

Bash> grep SLEEP /tmp/my_dump.mysql_dump_with_sleep | wc –l

The new dump file should look like:

Bash>  cat /tmp/my_dump.mysql_dump_with_sleep
DROP TABLE IF EXISTS `testsleep`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `testsleep` (
  `col1` int(10) unsigned NOT NULL auto_increment,
  `col2` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`col1`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
 
--
-- Dumping data for table `testsleep`
--
 
LOCK TABLES `testsleep` WRITE;
/*!40000 ALTER TABLE `testsleep` DISABLE KEYS */;
INSERT INTO `testsleep` VALUES (1,1),(2,3),(3,65),(4,388),(5,408),(6,412),(7,413),(8,420),(9,431),(10,432);
SELECT SLEEP(1);
INSERT INTO `testsleep` VALUES (11,1),(12,3),(13,65),(14,388),(15,408),(16,412),(17,413),(18,420),(19,431),(20,432);
SELECT SLEEP(1);
INSERT INTO `testsleep` VALUES (21,1),(22,3),(23,65),(24,388),(25,408),(26,412),(27,413),(28,420),(29,431),(30,432);
SELECT SLEEP(1);
/*!40000 ALTER TABLE `testsleep` ENABLE KEYS */;
UNLOCK TABLES;

The output of applying the dump file with the sleeps will look like:

[user@master]$  nohup mysql --h xxx -P xxx –u xxx –pxxx  my_db  < /tmp/my_dump.mysql_dump_with_sleep
SLEEP(1)
0
SLEEP(1)
0
SLEEP(1)
0

The SLEEP effect can be seen at the master binlog:

[user@master]$  sudo mysqlbinlog mysql-binlog.009127 | less
SET TIMESTAMP=1298561696/*!*/;
INSERT INTO `testsleep` VALUES (1,1),(2,3),(3,65),(4,388),(5,408),(6,412),(7,413
# at 730
#110224 15:34:57 server id 1012  end_log_pos 908        Query   thread_id=394402
SET TIMESTAMP=1298561697/*!*/;
INSERT INTO `testsleep` VALUES (11,1),(12,3),(13,65),(14,388),(15,408),(16,412),
# at 908
#110224 15:34:58 server id 1012  end_log_pos 1086       Query   thread_id=394402
SET TIMESTAMP=1298561698/*!*/;
INSERT INTO `testsleep` VALUES (21,1),(22,3),(23,65),(24,388),(25,408),(26,412),
# at 1086
#110224 15:34:59 server id 1012  end_log_pos 1196       Query   thread_id=394402
SET TIMESTAMP=1298561699/*!*/;
/*!40000 ALTER TABLE `testsleep` ENABLE KEYS *//*!*/;

The SLEEP effect can be seen at the slave binlog:

[user@slave]$  sudo mysqlbinlog mysqld-relay-bin.022076 | less
SET TIMESTAMP=1298561696/*!*/;
INSERT INTO `testsleep` VALUES (1,1),(2,3),(3,65),(4,388),(5,408),(6,412),(7,413),(8,420),(9,431),(10,432)/*!*/;
# at 870
#110224 15:34:57 server id 1012  end_log_pos 908        Query   thread_id=3944028       exec_time=0     error_code=0
SET TIMESTAMP=1298561697/*!*/;
INSERT INTO `testsleep` VALUES (11,1),(12,3),(13,65),(14,388),(15,408),(16,412),(17,413),(18,420),(19,431),(20,432)/*!*/;
# at 1048
#110224 15:34:58 server id 1012  end_log_pos 1086       Query   thread_id=3944028       exec_time=0     error_code=0
SET TIMESTAMP=1298561698/*!*/;
INSERT INTO `testsleep` VALUES (21,1),(22,3),(23,65),(24,388),(25,408),(26,412),(27,413),(28,420),(29,431),(30,432)/*!*/;
# at 1226
#110224 15:34:59 server id 1012  end_log_pos 1196       Query   thread_id=3944028       exec_time=0     error_code=0
SET TIMESTAMP=1298561699/*!*/;

As always, I will be happy to receive comments

Tags: ,
Comment (RSS)  |  Trackback
MySQL Quiz
4 Comments
  1. Daniël van Eeden says:
    April 4, 2011 at 3:48 am

    You might want to turn -extended-insert off otherwise it will use multirow inserts. SLEEP() allows smaller values that 1 second like 0.5.

  2. Alexander Snisar says:
    April 6, 2011 at 3:44 am

    As per my logic restoring tables into master host (in most cases) required after some demage of that table. So by adding select sleep(); will lead to increasing restoration time. And when you need restore service taking care about replication delay is not a case.

    As for me it’s easier to stop replication, restore table without writing to binary log at the same time on master and slave, and that start replication from same position. But this applicable only in my case where slave mostly used for backup and failover scenario. If slave used to service read only queries this is not a case.

  3. Justin Swanhart says:
    April 7, 2011 at 10:24 am

    Since the data doesn’t exist on the master, it is safe to run the script on both the slave and the master.

    Disable binary logging in the slave which imports data on the master:
    SET SESSION SQL_LOG_BIN=0;
    . import_file.sql

    On the slave do the same thing.

    This will get your parallel import into the master and slave. Don’t allow any other sessions to write to the table until it finishes importing on the slave.

  4. max says:
    April 28, 2011 at 9:37 pm

    Hi,
    if you have multi row insert query , how do you apply this solution on multi row query insert case ?
    From your example :
    INSERT INTO `testsleep` VALUES (1,1),(2,3),(3,65),(4,388),(5,408),(6,412),(7,413),(8,420),(9,431),(10,432);
    SELECT SLEEP(1);
    INSERT INTO `testsleep` VALUES (11,1),(12,3),(13,65),(14,388),(15,408),(16,412),(17,413),(18,420),(19,431),(20,432);
    SELECT SLEEP(1);
    INSERT INTO `testsleep` VALUES (21,1),(22,3),(23,65),(24,388),(25,408),(26,412),(27,413),(28,420),(29,431),(30,432);
    SELECT SLEEP(1);

    to this :
    INSERT INTO testsleep (someval,someval2)
    SELECT someval,someval2 from testsleep

    how do you invoke sleep in multi insertion ?

    Thanks.

Leave a Reply

 

«
»