[Commits] Rev 4502: MDEV-6676: Speculative parallel replication: Intermediate patch. in http://bazaar.launchpad.net/~maria-captains/maria/10.0

knielsen at knielsen-hq.org knielsen at knielsen-hq.org
Thu Nov 13 16:09:45 EET 2014


At http://bazaar.launchpad.net/~maria-captains/maria/10.0

------------------------------------------------------------
revno: 4502
revision-id: knielsen at knielsen-hq.org-20140924192510-u078tru81vo3nglx
parent: knielsen at knielsen-hq.org-20140924110900-zwifltu91y212vxk
committer: Kristian Nielsen <knielsen at knielsen-hq.org>
branch nick: work-10.0-mdev6676
timestamp: Wed 2014-09-24 21:25:10 +0200
message:
  MDEV-6676: Speculative parallel replication: Intermediate patch.
  
  Run parallel slave worker threads in REPEATABLE READ isolation level.
  
  In READ COMMITTED, speculative parallel replication could result in
  incorrect data, since that isolation level is not high enough to
  force the parallel transactions to run in the right order.
=== modified file 'mysql-test/suite/rpl/r/rpl_parallel_optimistic.result'
--- a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result	2014-09-20 10:33:54 +0000
+++ b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result	2014-09-24 19:25:10 +0000
@@ -207,6 +207,86 @@ a	b
 5       1007
 status
 Ok, no retry
+*** Test that we replicate correctly when using READ COMMITTED and binlog_format=MIXED on the slave ***
+include/stop_slave.inc
+SET @old_format= @@GLOBAL.binlog_format;
+SET GLOBAL binlog_format= MIXED;
+SET @old_isolation= @@GLOBAL.tx_isolation;
+SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SET GLOBAL slave_parallel_threads=0;
+SET GLOBAL slave_parallel_threads=10;
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a int PRIMARY KEY, b INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,0), (2,0), (3,0);
+INSERT INTO t2 VALUES (1,0), (2,0);
+INSERT INTO t1 SELECT 4, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 4, COUNT(*) FROM t1;
+INSERT INTO t1 SELECT 5, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 5, COUNT(*) FROM t1;
+INSERT INTO t2 SELECT 6, COUNT(*) FROM t1;
+INSERT INTO t1 SELECT 6, COUNT(*) FROM t2;
+INSERT INTO t1 SELECT 7, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 7, COUNT(*) FROM t1;
+INSERT INTO t2 SELECT 8, COUNT(*) FROM t1;
+INSERT INTO t1 SELECT 8, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 9, COUNT(*) FROM t1;
+INSERT INTO t1 SELECT 9, COUNT(*) FROM t2;
+INSERT INTO t1 SELECT 10, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 10, COUNT(*) FROM t1;
+SELECT * FROM t1 ORDER BY a;
+a       b
+1       0
+2       0
+3       0
+4       2
+5       3
+6       5
+7       5
+8       7
+9       8
+10      8
+SELECT * FROM t2 ORDER BY a;
+a       b
+1       0
+2       0
+4       4
+5       5
+6       5
+7       7
+8       7
+9       8
+10      10
+include/save_master_gtid.inc
+include/start_slave.inc
+include/sync_with_master_gtid.inc
+SELECT * FROM t1 ORDER BY a;
+a       b
+1       0
+2       0
+3       0
+4       2
+5       3
+6       5
+7       5
+8       7
+9       8
+10      8
+SELECT * FROM t2 ORDER BY a;
+a       b
+1       0
+2       0
+4       4
+5       5
+6       5
+7       7
+8       7
+9       8
+10      10
+include/stop_slave.inc
+SET GLOBAL binlog_format= @old_format;
+SET GLOBAL tx_isolation= @old_isolation;
+include/start_slave.inc
 include/stop_slave.inc
 SET GLOBAL slave_parallel_threads=@old_parallel_threads;
 SET GLOBAL slave_parallel_mode=@old_mode;

=== added file 'mysql-test/suite/rpl/r/rpl_parallel_optimistic_nobinlog.result'
--- a/mysql-test/suite/rpl/r/rpl_parallel_optimistic_nobinlog.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_parallel_optimistic_nobinlog.result	2014-09-24 19:25:10 +0000
@@ -0,0 +1,85 @@
+include/rpl_init.inc [topology=1->2]
+ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
+CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a int PRIMARY KEY, b INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,0), (2,0), (3,0);
+INSERT INTO t2 VALUES (1,0), (2,0);
+SET @old_isolation= @@GLOBAL.tx_isolation;
+SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads;
+SET @old_mode=@@GLOBAL.slave_parallel_mode;
+include/stop_slave.inc
+SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SET GLOBAL slave_parallel_threads=10;
+SET GLOBAL slave_parallel_mode="domain,transactional,waiting";
+CHANGE MASTER TO master_use_gtid=slave_pos;
+*** Test that we replicate correctly when using READ COMMITTED and --log-slave-updates=0 on the slave ***
+INSERT INTO t1 SELECT 4, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 4, COUNT(*) FROM t1;
+INSERT INTO t1 SELECT 5, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 5, COUNT(*) FROM t1;
+INSERT INTO t2 SELECT 6, COUNT(*) FROM t1;
+INSERT INTO t1 SELECT 6, COUNT(*) FROM t2;
+INSERT INTO t1 SELECT 7, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 7, COUNT(*) FROM t1;
+INSERT INTO t2 SELECT 8, COUNT(*) FROM t1;
+INSERT INTO t1 SELECT 8, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 9, COUNT(*) FROM t1;
+INSERT INTO t1 SELECT 9, COUNT(*) FROM t2;
+INSERT INTO t1 SELECT 10, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 10, COUNT(*) FROM t1;
+SELECT * FROM t1 ORDER BY a;
+a       b
+1       0
+2       0
+3       0
+4       2
+5       3
+6       5
+7       5
+8       7
+9       8
+10      8
+SELECT * FROM t2 ORDER BY a;
+a       b
+1       0
+2       0
+4       4
+5       5
+6       5
+7       7
+8       7
+9       8
+10      10
+include/save_master_gtid.inc
+include/start_slave.inc
+include/sync_with_master_gtid.inc
+SELECT * FROM t1 ORDER BY a;
+a       b
+1       0
+2       0
+3       0
+4       2
+5       3
+6       5
+7       5
+8       7
+9       8
+10      8
+SELECT * FROM t2 ORDER BY a;
+a       b
+1       0
+2       0
+4       4
+5       5
+6       5
+7       7
+8       7
+9       8
+10      10
+include/stop_slave.inc
+SET GLOBAL tx_isolation= @old_isolation;
+SET GLOBAL slave_parallel_threads=@old_parallel_threads;
+SET GLOBAL slave_parallel_mode=@old_mode;
+include/start_slave.inc
+DROP TABLE t1, t2;
+include/rpl_end.inc

=== modified file 'mysql-test/suite/rpl/t/rpl_parallel_optimistic.test'
--- a/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test	2014-09-20 10:33:54 +0000
+++ b/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test	2014-09-24 19:25:10 +0000
@@ -251,6 +251,62 @@ eval SELECT IF($retry1=$retry2, "Ok, no
 --enable_query_log
 
 
+--echo *** Test that we replicate correctly when using READ COMMITTED and binlog_format=MIXED on the slave ***
+
+--connection server_2
+--source include/stop_slave.inc
+SET @old_format= @@GLOBAL.binlog_format;
+# Use MIXED format; we cannot binlog ROW events on slave in STATEMENT format.
+SET GLOBAL binlog_format= MIXED;
+SET @old_isolation= @@GLOBAL.tx_isolation;
+SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
+# Reset the worker threads to make the new settings take effect.
+SET GLOBAL slave_parallel_threads=0;
+SET GLOBAL slave_parallel_threads=10;
+
+--connection server_1
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a int PRIMARY KEY, b INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,0), (2,0), (3,0);
+INSERT INTO t2 VALUES (1,0), (2,0);
+INSERT INTO t1 SELECT 4, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 4, COUNT(*) FROM t1;
+
+INSERT INTO t1 SELECT 5, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 5, COUNT(*) FROM t1;
+
+INSERT INTO t2 SELECT 6, COUNT(*) FROM t1;
+INSERT INTO t1 SELECT 6, COUNT(*) FROM t2;
+
+INSERT INTO t1 SELECT 7, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 7, COUNT(*) FROM t1;
+
+INSERT INTO t2 SELECT 8, COUNT(*) FROM t1;
+INSERT INTO t1 SELECT 8, COUNT(*) FROM t2;
+
+INSERT INTO t2 SELECT 9, COUNT(*) FROM t1;
+INSERT INTO t1 SELECT 9, COUNT(*) FROM t2;
+
+INSERT INTO t1 SELECT 10, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 10, COUNT(*) FROM t1;
+
+SELECT * FROM t1 ORDER BY a;
+SELECT * FROM t2 ORDER BY a;
+--source include/save_master_gtid.inc
+
+--connection server_2
+--source include/start_slave.inc
+--source include/sync_with_master_gtid.inc
+SELECT * FROM t1 ORDER BY a;
+SELECT * FROM t2 ORDER BY a;
+
+--source include/stop_slave.inc
+SET GLOBAL binlog_format= @old_format;
+SET GLOBAL tx_isolation= @old_isolation;
+--source include/start_slave.inc
+
+
 --connection server_2
 --source include/stop_slave.inc
 SET GLOBAL slave_parallel_threads=@old_parallel_threads;

=== added file 'mysql-test/suite/rpl/t/rpl_parallel_optimistic_nobinlog.cnf'
--- a/mysql-test/suite/rpl/t/rpl_parallel_optimistic_nobinlog.cnf	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_parallel_optimistic_nobinlog.cnf	2014-09-24 19:25:10 +0000
@@ -0,0 +1,9 @@
+!include ../my.cnf
+
+[mysqld.1]
+log-slave-updates=0
+loose-innodb
+
+[mysqld.2]
+log-slave-updates=0
+loose-innodb

=== added file 'mysql-test/suite/rpl/t/rpl_parallel_optimistic_nobinlog.test'
--- a/mysql-test/suite/rpl/t/rpl_parallel_optimistic_nobinlog.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_parallel_optimistic_nobinlog.test	2014-09-24 19:25:10 +0000
@@ -0,0 +1,74 @@
+--source include/have_innodb.inc
+--source include/have_binlog_format_statement.inc
+--let $rpl_topology=1->2
+--source include/rpl_init.inc
+
+--connection server_1
+ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
+CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a int PRIMARY KEY, b INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,0), (2,0), (3,0);
+INSERT INTO t2 VALUES (1,0), (2,0);
+--save_master_pos
+
+
+--connection server_2
+--sync_with_master
+SET @old_isolation= @@GLOBAL.tx_isolation;
+SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads;
+SET @old_mode=@@GLOBAL.slave_parallel_mode;
+--source include/stop_slave.inc
+SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SET GLOBAL slave_parallel_threads=10;
+SET GLOBAL slave_parallel_mode="domain,transactional,waiting";
+CHANGE MASTER TO master_use_gtid=slave_pos;
+
+
+--echo *** Test that we replicate correctly when using READ COMMITTED and --log-slave-updates=0 on the slave ***
+
+--connection server_1
+
+INSERT INTO t1 SELECT 4, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 4, COUNT(*) FROM t1;
+
+INSERT INTO t1 SELECT 5, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 5, COUNT(*) FROM t1;
+
+INSERT INTO t2 SELECT 6, COUNT(*) FROM t1;
+INSERT INTO t1 SELECT 6, COUNT(*) FROM t2;
+
+INSERT INTO t1 SELECT 7, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 7, COUNT(*) FROM t1;
+
+INSERT INTO t2 SELECT 8, COUNT(*) FROM t1;
+INSERT INTO t1 SELECT 8, COUNT(*) FROM t2;
+
+INSERT INTO t2 SELECT 9, COUNT(*) FROM t1;
+INSERT INTO t1 SELECT 9, COUNT(*) FROM t2;
+
+INSERT INTO t1 SELECT 10, COUNT(*) FROM t2;
+INSERT INTO t2 SELECT 10, COUNT(*) FROM t1;
+
+SELECT * FROM t1 ORDER BY a;
+SELECT * FROM t2 ORDER BY a;
+--source include/save_master_gtid.inc
+
+--connection server_2
+--source include/start_slave.inc
+--source include/sync_with_master_gtid.inc
+SELECT * FROM t1 ORDER BY a;
+SELECT * FROM t2 ORDER BY a;
+
+
+--connection server_2
+--source include/stop_slave.inc
+SET GLOBAL tx_isolation= @old_isolation;
+SET GLOBAL slave_parallel_threads=@old_parallel_threads;
+SET GLOBAL slave_parallel_mode=@old_mode;
+--source include/start_slave.inc
+
+--connection server_1
+DROP TABLE t1, t2;
+
+--source include/rpl_end.inc
+

=== modified file 'sql/rpl_parallel.cc'
--- a/sql/rpl_parallel.cc	2014-09-19 13:25:37 +0000
+++ b/sql/rpl_parallel.cc	2014-09-24 19:25:10 +0000
@@ -592,6 +592,13 @@ handle_rpl_parallel_thread(void *arg)
   thd->set_time();
   thd->variables.lock_wait_timeout= LONG_TIMEOUT;
   thd->system_thread_info.rpl_sql_info= &sql_info;
+  /*
+    We need to use (at least) REPEATABLE READ isolation level. Otherwise
+    speculative parallel apply can run out-of-order and give wrong results
+    for statement-based replication.
+  */
+  thd->variables.tx_isolation= ISO_REPEATABLE_READ;
+
 
   mysql_mutex_lock(&rpt->LOCK_rpl_thread);
   rpt->thd= thd;
@@ -673,6 +680,7 @@ handle_rpl_parallel_thread(void *arg)
         PSI_stage_info old_stage;
         uint64 wait_count;
 
+        thd->tx_isolation= (enum_tx_isolation)thd->variables.tx_isolation;
         in_event_group= true;
         /*
           If the standalone flag is set, then this event group consists of a



More information about the commits mailing list