How to Make Drush sql-sync Faster

Drush SQL-Sync

I did a small research with drush sql-sync. I believe I have arrived at an interesting inference to make it a bit faster, especially for old system by passing an additional argument. 

I often use drush sql-sync to quickly move database from development instance to local and so. While I prefer to use features module that can capture most of the configurations in Drupal as a versionable module in git. But it leaves some of the things like taxonomy, node reference, etc. abandoned. Not because the module is immature but Drupal core is still evolving in this area. This is one of the biggest improvements going to be added to Drupal 8. Configuration management led by heyrocker is in full swing to make this happen. So it has been essential to get a copy of database in local to mimic the dev site environment.

Back to my inference, I use a relatively old computer at office, thought RAM, Graphics card, etc. are tweaked to meet the Web development requirements, the disk (I/O) speed is still slow. I always use sql-sync for the above mentioned purpose but with this old system I'm noticing an exceptionally strange delay.

I ran the command a couple of times in verbose mode. On further analysis I found that it is because of INSERT statements in SQL dump. Drush, by default sets an additional argument --skip-extended-insert which instructs mysqldump program to have a separate INSERT statement for each rows in MySQL table.

A couple of grep commands and quick code read reveled that using --no-ordered-dump argument with drush sql-sync instructs mysqldump to leverage the multiple values insert (AKA extended insert in MySQL parlance). 

Let's quickly take a look at the dump that is used along the way of database sync process,

1. MySQL dump without extended insert

1. --
2. -- Table structure for table `actions`
3. --
4.  
5. DROP TABLE IF EXISTS `actions`;
6. /*!40101 SET @saved_cs_client     = @@character_set_client */;
7. /*!40101 SET character_set_client = utf8 */;
8. CREATE TABLE `actions` (
9.   `aid` varchar(255) NOT NULL DEFAULT '0' COMMENT 'Primary Key: Unique actions ID.',
10.   `type` varchar(32) NOT NULL DEFAULT '' COMMENT 'The object that that action acts on (node, user, comment, system or custom types.)',
11.   `callback` varchar(255) NOT NULL DEFAULT '' COMMENT 'The callback function that executes when the action runs.',
12.   `parameters` longblob NOT NULL COMMENT 'Parameters to be passed to the callback function.',
13.   `label` varchar(255) NOT NULL DEFAULT '0' COMMENT 'Label of the action.',
14.   PRIMARY KEY (`aid`)
15. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Stores action information.';
16. /*!40101 SET character_set_client = @saved_cs_client */;
17.  
18. --
19. -- Dumping data for table `actions`
20. --
21. -- ORDER BY:  `aid`
22.  
23. LOCK TABLES `actions` WRITE;
24. /*!40000 ALTER TABLE `actions` DISABLE KEYS */;
25. set autocommit=0;
26. INSERT INTO `actions` VALUES ('comment_publish_action','comment','comment_publish_action','','Publish comment');
27. INSERT INTO `actions` VALUES ('comment_save_action','comment','comment_save_action','','Save comment');
28. INSERT INTO `actions` VALUES ('comment_unpublish_action','comment','comment_unpublish_action','','Unpublish comment');
29. INSERT INTO `actions` VALUES ('node_make_sticky_action','node','node_make_sticky_action','','Make content sticky');
30. INSERT INTO `actions` VALUES ('node_make_unsticky_action','node','node_make_unsticky_action','','Make content unsticky');
31. INSERT INTO `actions` VALUES ('node_promote_action','node','node_promote_action','','Promote content to front page');
32. INSERT INTO `actions` VALUES ('node_publish_action','node','node_publish_action','','Publish content');
33. INSERT INTO `actions` VALUES ('node_save_action','node','node_save_action','','Save content');
34. INSERT INTO `actions` VALUES ('node_unpromote_action','node','node_unpromote_action','','Remove content from front page');
35. INSERT INTO `actions` VALUES ('node_unpublish_action','node','node_unpublish_action','','Unpublish content');
36. INSERT INTO `actions` VALUES ('system_block_ip_action','user','system_block_ip_action','','Ban IP address of current user');
37. INSERT INTO `actions` VALUES ('user_block_user_action','user','user_block_user_action','','Block current user');
38. /*!40000 ALTER TABLE `actions` ENABLE KEYS */;
39. UNLOCK TABLES;
40. commit;

2. MySQL dump with extended insert

1. --
2. -- Table structure for table `actions`
3. --
4.  
5. DROP TABLE IF EXISTS `actions`;
6. /*!40101 SET @saved_cs_client     = @@character_set_client */;
7. /*!40101 SET character_set_client = utf8 */;
8. CREATE TABLE `actions` (
9.   `aid` varchar(255) NOT NULL DEFAULT '0' COMMENT 'Primary Key: Unique actions ID.',
10.   `type` varchar(32) NOT NULL DEFAULT '' COMMENT 'The object that that action acts on (node, user, comment, system or custom types.)',
11.   `callback` varchar(255) NOT NULL DEFAULT '' COMMENT 'The callback function that executes when the action runs.',
12.   `parameters` longblob NOT NULL COMMENT 'Parameters to be passed to the callback function.',
13.   `label` varchar(255) NOT NULL DEFAULT '0' COMMENT 'Label of the action.',
14.   PRIMARY KEY (`aid`)
15. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Stores action information.';
16. /*!40101 SET character_set_client = @saved_cs_client */;
17.  
18. --
19. -- Dumping data for table `actions`
20. --
21.  
22. LOCK TABLES `actions` WRITE;
23. /*!40000 ALTER TABLE `actions` DISABLE KEYS */;
24. set autocommit=0;
25. INSERT INTO `actions` VALUES ('comment_publish_action','comment','comment_publish_action','','Publish comment'),('comment_save_action','comment','comment_save_action','','Save comment'),('comment_unpublish_action','comment','comment_unpublish_action','','Unpublish comment'),('node_make_sticky_action','node','node_make_sticky_action','','Make content sticky'),('node_make_unsticky_action','node','node_make_unsticky_action','','Make content unsticky'),('node_promote_action','node','node_promote_action','','Promote content to front page'),('node_publish_action','node','node_publish_action','','Publish content'),('node_save_action','node','node_save_action','','Save content'),('node_unpromote_action','node','node_unpromote_action','','Remove content from front page'),('node_unpublish_action','node','node_unpublish_action','','Unpublish content'),('system_block_ip_action','user','system_block_ip_action','','Ban IP address of current user'),('user_block_user_action','user','user_block_user_action','','Block current user');
26. /*!40000 ALTER TABLE `actions` ENABLE KEYS */;
27. UNLOCK TABLES;
28. commit;

While #1 is good to play safe from hitting MySQL's max_allowed_packet size limit. Besides it might help a bit to revision the SQL dump. I strongly believe that it is not a right way and Drupal way of doing, however I have seen peoples believing in this approach and arguing to justify this.

I know that leveraging extended insert makes a lot of differences in performance visually. My curiosity didn't stop here. I wanted to run a benchmark and see the difference in terms of numerical figures.

Inference of benchmark is as below,

The site I used for this purpose is a Drupal 7 site. It is fairly a new site, started developing for a client project two weeks ago. It has been populated with 100 nodes, 50 users and 20+ fields. Hosted on Amazon EC2 Micro instance.

1. Drush sql-sync without --no-ordered-dump argument

With --no-ordered-dump argument it is clear that the sql-sync command is about 4 times faster in my case. I tried this experiment a few more times as there could be some problems with bandwidth availability or other programs might be consuming more I/O cycles but still the observation was almost similar. It certainly makes sense to use sql-sync with above mentioned argument. In fact I added an alias to my ~/.bash_aliases to ease myself from typing that lengthy argument again and again.