This worklog has been replaced with mariadb.org/jira

This site is here for historical purposes only. Do not add or edit tasks here!

 
 
 

WorkLog Frontpage Log in / Register
High-Level Description | Task Dependencies | High-Level Specification | Low-Level Design | File Attachments | User Comments | Time Estimates | Funding and Votes | Progress Reports

 Add an option to mysqlbinlog to produce SQL script with fewer roundtrips
Title
Task ID37
Queue
Version N/A
Status
PriorityN/A
Copies to
Created byPsergey07 Aug 2009Done
Supervisor N/A  
Lead Architect    
Architecture Review  
Implementor  
Code Review  
QA  
Documentation  
 High-Level Description
SQL scripts generated by mysqlbinlog can be slow to load because they have many
small queries, hence applying the script against a remote server requires a lot
of roundtrips, and the network roundtrips become the bottleneck.

This bottleneck can be addressed by having mysqlbinlog combine multiple
statements into one:

+delimiter //
 binlog statement1;
 binlog statement2;
 binlog statement3;
+//
 binlog statement4;

loading such sql script will require fewer roundtrips. 

The behaviour can be controlled using a command line option

mysqlbinlog --combine-statements=#

Where the # is maximum allowed packet length.
 Task Dependencies
Others waiting for Task 37Task 37 is waiting forGraph
 
 High-Level Specification
Implementation overview:

- At start, print "--delimiter=;;"
- Modify the start of each print functions as follows

  if (my_b_tell(&cache) - my_start_of_combine_statement) + 
      estimiated_size_of_log_event) > combine_statement_size)
    my_b_write(&cache,";;",2);

- And we should end mysqlbinlog with;
  if (my_b_tell(&cache) != 0)
    my_b_write(&cache,";;",2);

Note: mysqlbinlog already uses 
 
  DELIMITER /*!*/;

so that it can process "multi-statements" like 

  CREATE PROCEDURE ... BEGIN stmt1; stmt2; ... END 

what remains to be done is to print the /*!*/; only when we're about to exceed
$args[combine-statements] bytes. In all other cases, delimit statements with
regular semicolon.

 Low-Level Design
 File Attachments
 NameTypeSizeByDate
 User Comments
 Time Estimates
NameHours WorkedLast Updated
Total0 
 Hrs WorkedProgressCurrentOriginal
Total000
 
 Funding and Votes
Votes: 0: 0%
 Make vote: Useless    Nice to have    Important    Very important    

Funding: 0 offers, total 0 Euro
 Progress Reports
(Sergei - Thu, 01 Jul 2010, 06:12
    
Category updated.
--- /tmp/wklog.37.old.29020	2010-07-01 06:12:44.000000000 +0000
+++ /tmp/wklog.37.new.29020	2010-07-01 06:12:44.000000000 +0000
@@ -1 +1 @@
-Server-RawIdeaBin
+Client-RawIdeaBin

(Sergei - Thu, 01 Jul 2010, 06:12
    
Version updated.
--- /tmp/wklog.37.old.29020	2010-07-01 06:12:44.000000000 +0000
+++ /tmp/wklog.37.new.29020	2010-07-01 06:12:44.000000000 +0000
@@ -1 +1 @@
-Server-9.x
+9.x

(Guest - Thu, 17 Jun 2010, 00:39
    
Dependency deleted: WL#39 no longer depends on WL#37

(Psergey - Sun, 09 Aug 2009, 12:56
    
High-Level Specification modified.
--- /tmp/wklog.37.old.22083	2009-08-09 12:56:36.000000000 +0300
+++ /tmp/wklog.37.new.22083	2009-08-09 12:56:36.000000000 +0300
@@ -11,3 +11,16 @@
   if (my_b_tell(&cache) != 0)
     my_b_write(&cache,";;",2);
 
+Note: mysqlbinlog already uses 
+ 
+  DELIMITER /*!*/;
+
+so that it can process "multi-statements" like 
+
+  CREATE PROCEDURE ... BEGIN stmt1; stmt2; ... END 
+
+what remains to be done is to print the /*!*/; only when we're about to exceed
+$args[combine-statements] bytes. In all other cases, delimit statements with
+regular semicolon.
+
+

(Psergey - Sun, 09 Aug 2009, 12:30
    
High Level Description modified.
--- /tmp/wklog.37.old.21090	2009-08-09 12:30:26.000000000 +0300
+++ /tmp/wklog.37.new.21090	2009-08-09 12:30:26.000000000 +0300
@@ -1,6 +1,6 @@
 SQL scripts generated by mysqlbinlog can be slow to load because they have many
 small queries, hence applying the script against a remote server requires a lot
-of roundtrips, and they become a bottleneck.
+of roundtrips, and the network roundtrips become the bottleneck.
 
 This bottleneck can be addressed by having mysqlbinlog combine multiple
 statements into one:
@@ -14,7 +14,7 @@
 
 loading such sql script will require fewer roundtrips. 
 
-The behavior can be controlled using a command line option
+The behaviour can be controlled using a command line option
 
 mysqlbinlog --combine-statements=#
 

(Psergey - Sun, 09 Aug 2009, 12:24
    
Dependency created: WL#39 now depends on WL#37

(Psergey - Fri, 07 Aug 2009, 17:16
    
High-Level Specification modified.
--- /tmp/wklog.37.old.20454	2009-08-07 17:16:54.000000000 +0300
+++ /tmp/wklog.37.new.20454	2009-08-07 17:16:54.000000000 +0300
@@ -1 +1,13 @@
+Implementation overview:
+
+- At start, print "--delimiter=;;"
+- Modify the start of each print functions as follows
+
+  if (my_b_tell(&cache) - my_start_of_combine_statement) + 
+      estimiated_size_of_log_event) > combine_statement_size)
+    my_b_write(&cache,";;",2);
+
+- And we should end mysqlbinlog with;
+  if (my_b_tell(&cache) != 0)
+    my_b_write(&cache,";;",2);
 


Report Generator:
 
Saved Reports:

WorkLog v4.0.0
  © 2010  Sergei Golubchik and Monty Program AB
  © 2004  Andrew Sweger <yDNA@perlocity.org> and Addnorya
  © 2003  Matt Wagner <matt@mysql.com> and MySQL AB