List:Commits« Previous MessageNext Message »
From:Andrei Elkin Date:April 2 2011 11:32am
Subject:bzr commit into mysql-next-mr-wl5569 branch (andrei.elkin:3272) WL#5569
View as plain text  
#At file:///home/andrei/MySQL/BZR/2a-23May/WL/mysql-next-mr-wl5569/ based on revid:andrei.elkin@stripped

 3272 Andrei Elkin	2011-04-02
      wl#5569 MTS
      
      a test file for benchmarking is added.
      Benchmarking results can be gained by extracting the master side generating 
      and the slave side applying times like in the following loop:
      workers=6; 
         for n in `seq 1 3`; 
           do echo; echo loop: $n; echo;
           my_mtr.sh --mysqld=--mts-slave-parallel-workers=$workers  \
                     rpl_parallel_benchmark --mysqld=--binlog-format=statement \
           && cat /dev/shm/var/mysqld.2/data/test/delta.out >> p${workers}_stmt.out 2>&1;
         done
     @ mysql-test/extra/rpl_tests/rpl_parallel_benchmark_load.test
        the load generator for a test file for benchmarking is added.
     @ mysql-test/suite/rpl/r/rpl_parallel_benchmark.result
        a new results file is added.
     @ mysql-test/suite/rpl/t/rpl_parallel_benchmark-slave.opt
        slave does to log into binary log. The number of workers is supposed to set via --mysqld at mtr invocation.
     @ mysql-test/suite/rpl/t/rpl_parallel_benchmark.test
        a test file for benchmarking is added.

    added:
      mysql-test/extra/rpl_tests/rpl_parallel_benchmark_load.test
      mysql-test/suite/rpl/r/rpl_parallel_benchmark.result
      mysql-test/suite/rpl/t/rpl_parallel_benchmark-master.opt
      mysql-test/suite/rpl/t/rpl_parallel_benchmark-slave.opt
      mysql-test/suite/rpl/t/rpl_parallel_benchmark.test
=== added file 'mysql-test/extra/rpl_tests/rpl_parallel_benchmark_load.test'
--- a/mysql-test/extra/rpl_tests/rpl_parallel_benchmark_load.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_parallel_benchmark_load.test	2011-04-02 11:32:02 +0000
@@ -0,0 +1,274 @@
+#
+# This is a load generator to call from rpl_parallel and rpl_sequential tests
+#
+
+#
+# load volume parameter
+#
+
+let $iter= 128;
+let $tables= 4;
+let $wk_queries= 16;
+let $nk_queries= 0;
+let $pre_inserted_rows= 400;
+
+connection slave;
+
+call mtr.add_suppression('Slave: Error dropping database'); ## todo: fix
+
+source include/stop_slave.inc;
+start slave;
+
+connection master;
+
+--disable_query_log
+--disable_result_log
+
+use test;
+delimiter |;
+create procedure one_session(k int)
+begin
+  while k > 0 do
+    insert into tm_nk values(k, 0);
+    insert into tm_wk values(null, 0);
+    insert into ti_nk values(k, 0);
+    insert into ti_wk values(null, 0);
+    set k = k - 1;
+  end while;
+end|
+delimiter ;|
+
+## let $i = $workers + 1;
+##eval 
+# delimiter |;
+# create procedure p1(i int)
+# begin
+#     while i > 0
+#       ##while ($i)
+#       ##{
+#       ## let $i1=$i;
+#       ## dec $i1;
+#       ## use test$i1;
+#       ## call on_session();
+#       ## dec $i;
+#       ##}
+#       use test0;
+#       call one_session();
+#       use test1;
+#       call one_session();
+#       use test2;
+#       call one_session();
+#       use test3;
+#       call one_session();
+#       i= i-1;
+#     end while;
+# end|
+# delimiter ;|
+
+
+let $i = $databases;
+while($i)
+{
+  eval create database test$i;
+
+  let $m= $tables;
+  while ($m)
+  {
+  eval create table test$i.tm_nk_$m (a int, b int, c text) engine=myisam;
+  eval create table test$i.tm_wk_$m (a int auto_increment primary key, b int, c text) engine=myisam;
+  eval create table test$i.ti_nk_$m (a int, b int, c text) engine=innodb;
+  eval create table test$i.ti_wk_$m (a int auto_increment primary key, b int, c text) engine=innodb;
+  let $k= $pre_inserted_rows;
+  while ($k)
+  {
+   eval insert into test$i.ti_wk_$m values(null,  $i, uuid());
+   eval insert into test$i.ti_nk_$m values(null,  $i, uuid());
+
+   dec $k;
+  }
+  dec $m;
+  }
+
+
+  # this table is special - just for timing. It's more special on test1 db
+  # where it contains master timing of the load as well.
+  eval create table test$i.benchmark (state text) engine=myisam; # timestamp keep on the slave side
+
+  dec $i;
+}
+
+--enable_result_log
+--enable_query_log
+
+
+sync_slave_with_master;
+#connection slave;
+
+--disable_query_log
+--disable_result_log
+
+let $i = $databases;
+while($i)
+{
+  eval alter table test$i.benchmark add ts timestamp not null default current_timestamp;
+
+  dec $i;
+}
+--enable_result_log
+--enable_query_log
+
+
+# not gather events into relay log w/o executing yet
+stop slave sql_thread;
+
+##call p1(1);
+
+connection master;
+
+--disable_query_log
+--disable_result_log
+
+#
+# Load producer
+#
+
+# initial timestamp to record
+
+# the extra ts col on slave is effective only with the STMT format (todo: bug-report)
+set @save.binlog_format= @@session.binlog_format;
+set @@session.binlog_format=STATEMENT;
+let $i = $databases;
+while($i)
+{
+  eval insert into test$i.benchmark set state='slave takes on load';
+
+  dec $i;
+}
+set @@session.binlog_format= @save.binlog_format;
+
+connection slave;
+
+insert into test1.benchmark set state='master started load';
+
+
+connection master;
+
+while ($iter)
+{
+    let $i = $databases;
+
+    while ($i)
+    {
+
+	begin;
+   	   ###eval insert into tm_nk values($iter, $i1, repeat('a', round(rand()*10)));
+
+    	   let $q= $wk_queries;
+	   while ($q)
+	   {
+	       let $m= `select 1 + floor(rand() * $tables)`;
+	       eval insert into test$i.ti_wk_$m values(null,  $i, uuid());
+
+	       dec $q;
+	   }
+
+    	   let $q= $nk_queries;
+	   while ($q)
+	   {
+	       let $m= `select 1 + floor(rand() * $tables)`;
+	       eval insert into test$i.ti_nk_$m values(null,  $i, uuid());
+
+	       dec $q;
+	   }
+	commit;
+
+	dec $i;
+    }
+
+    dec $iter;
+}
+
+connection slave;
+
+##use test1;
+insert into test1.benchmark set state='master ends load';
+
+connection master;
+
+# terminal timestamp to record
+
+let $i = $databases;
+set @save.binlog_format= @@session.binlog_format;
+set @@session.binlog_format=STATEMENT;
+while($i)
+{
+  eval insert into test$i.benchmark set state='slave ends load';
+
+  dec $i;
+}
+set @@session.binlog_format= @save.binlog_format;
+
+--enable_result_log
+--enable_query_log
+
+connection slave;
+
+## todo: record start and end time of appying to compare times of
+#  parallel and sequential execution.
+
+--disable_query_log
+--disable_result_log
+
+insert into test1.benchmark set state='slave is processing load';
+
+# To force filling timestamp cols with the slave local clock values
+# to implement benchmarking.
+
+set @save.mts_exp_slave_local_timestamp=@@global.mts_exp_slave_local_timestamp;
+set @@global.mts_exp_slave_local_timestamp=1;
+
+--sleep 1
+
+start slave sql_thread;
+
+let $wait_timeout= 600;
+let $wait_condition= SELECT count(*)+sleep(1) = 5 FROM test1.benchmark;
+source include/wait_condition.inc;
+
+use test;
+select * from test1.benchmark into outfile 'benchmark.out';
+select ts from test1.benchmark where state like 'master started load' into @m_0;
+select ts from test1.benchmark where state like 'master ends load' into @m_1;
+select ts from test1.benchmark where state like 'slave takes on load' into @s_0;
+select ts from test1.benchmark where state like 'slave ends load' into @s_1;
+select time_to_sec(@m_1) - time_to_sec(@m_0) as 'delta_m', 
+       time_to_sec(@s_1) - time_to_sec(@s_0) as 'delta_s' into outfile 'delta.out';
+
+--enable_result_log
+--enable_query_log
+
+
+connection master;
+
+--disable_query_log
+--disable_result_log
+
+let $i = $databases;
+while($i)
+{
+  eval drop database test$i;
+
+  dec $i;
+}
+
+use test;
+drop procedure one_session;
+
+--enable_result_log
+--enable_query_log
+
+sync_slave_with_master;
+#connection slave;
+set @@global.mts_exp_slave_local_timestamp= @save.mts_exp_slave_local_timestamp;
+
+# End of the tests

=== added file 'mysql-test/suite/rpl/r/rpl_parallel_benchmark.result'
--- a/mysql-test/suite/rpl/r/rpl_parallel_benchmark.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_parallel_benchmark.result	2011-04-02 11:32:02 +0000
@@ -0,0 +1,17 @@
+include/master-slave.inc
+[connection master]
+set @save.mts_slave_parallel_workers= @@global.mts_slave_parallel_workers;
+call mtr.add_suppression('Slave: Error dropping database');
+include/stop_slave.inc
+start slave;
+stop slave sql_thread;
+use test;
+select * from test1.benchmark into outfile 'benchmark.out';
+select ts from test1.benchmark where state like 'master started load' into @m_0;
+select ts from test1.benchmark where state like 'master ends load' into @m_1;
+select ts from test1.benchmark where state like 'slave takes on load' into @s_0;
+select ts from test1.benchmark where state like 'slave ends load' into @s_1;
+select time_to_sec(@m_1) - time_to_sec(@m_0) as 'delta_m', 
+time_to_sec(@s_1) - time_to_sec(@s_0) as 'delta_s' into outfile 'delta.out';
+set @@global.mts_exp_slave_local_timestamp= @save.mts_exp_slave_local_timestamp;
+include/rpl_end.inc

=== added file 'mysql-test/suite/rpl/t/rpl_parallel_benchmark-master.opt'
--- a/mysql-test/suite/rpl/t/rpl_parallel_benchmark-master.opt	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_parallel_benchmark-master.opt	2011-04-02 11:32:02 +0000
@@ -0,0 +1 @@
+--log-warnings=0

=== added file 'mysql-test/suite/rpl/t/rpl_parallel_benchmark-slave.opt'
--- a/mysql-test/suite/rpl/t/rpl_parallel_benchmark-slave.opt	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_parallel_benchmark-slave.opt	2011-04-02 11:32:02 +0000
@@ -0,0 +1,3 @@
+--skip-log-bin --skip-log-slave-updates --log-warnings=0 --slave-transaction-retries=0
+
+

=== added file 'mysql-test/suite/rpl/t/rpl_parallel_benchmark.test'
--- a/mysql-test/suite/rpl/t/rpl_parallel_benchmark.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_parallel_benchmark.test	2011-04-02 11:32:02 +0000
@@ -0,0 +1,57 @@
+#
+# WL#5563 Prototype for Parallel Slave with db name partitioning.
+#
+# The test checks correctness of replication and is designed for 
+# benchmarking and comparision with results of its sequential
+# counterpart rpl_sequential.test.
+# Both tests leave mysqld.2/data/test/delta.out file
+# that contains a row with two columns.
+#  1. the duration (in seconds) of execution on the master
+#  2. the duration of execution on the slave
+#  The 2nd column of the rpl_parallel can be compared with the 2nd of rpl_sequential.test.
+#
+#  The duration recorded in the file accounts the SQL thread/workers work.
+#  That is benchmarking on the slave side is effectively started with
+#  `start slave sql_thread'.
+#  NOTICE, there is set @@global.slave_local_timestamp=1;
+#  
+#
+# of load that rpl_parallel_load.test represents.
+# See there how to tune load and concurrency parameters.
+#
+# Example of usage.
+# To gather a collection of figures:
+#  mysql-test$ export slave;
+#  mysql-test$ slave=parallel; for n in `seq 1 10`; 
+#  do ./mtr  --vardir=/dev/shm/var1 --mtr-build-thread=765 rpl_$slave 
+#             --mysqld=--binlog-format=statement; 
+#     find /dev/shm/var1 -name delta.out -exec cat {} \; | cat >> delta.$slave.log; 
+#  done
+#
+# mysql-test$ slave=sequential; ...
+#
+# In the end there will be mysql-test/delta.{parallel,sequential}.log files.
+#
+
+let $rpl_skip_reset_master_and_slave= 1;
+
+--source include/master-slave.inc
+
+connection slave;
+set @save.mts_slave_parallel_workers= @@global.mts_slave_parallel_workers;
+###select @@global.mts_slave_parallel_workers as 'non-zero means parallel';
+
+let $workers = `select @@global.mts_slave_parallel_workers`;
+###let $databases= $workers;
+# workers vary db:s do not
+let $databases= 16;
+
+connection master;
+source extra/rpl_tests/rpl_parallel_benchmark_load.test;
+
+connection slave;
+--disable_query_log
+set @@global.mts_slave_parallel_workers= @save.mts_slave_parallel_workers;
+--enable_result_log
+
+--source include/rpl_end.inc


Attachment: [text/bzr-bundle] bzr/andrei.elkin@oracle.com-20110402113202-65e12tpteebi39g3.bundle
Thread
bzr commit into mysql-next-mr-wl5569 branch (andrei.elkin:3272) WL#5569Andrei Elkin2 Apr