Merge lp:~akopytov/percona-xtrabackup/bug1213036-2.1 into lp:percona-xtrabackup/2.1

[Alexey Kopytov]

    Bug #1213036: ib_incremental_* tests are too slow

    Changed ib_incremental_* tests and a few other ones to use multi-row
    INSERT statements instead of inserting multiple rows by calling mysql
    command line client for each row. Introduced a convenience function
    insert_rows() in common.sh to avoid code repetition in multiple tests.

http://jenkins.percona.com/view/XtraBackup/job/percona-xtrabackup-2.1-param/436/

[Stewart Smith (stewart)]

review: Approve

=== modified file 'test/inc/common.sh'
--- test/inc/common.sh	2013-07-25 15:04:49 +0000
+++ test/inc/common.sh	2013-08-19 04:08:39 +0000
@@ -721,5 +721,34 @@
     fi
 }
 
+##############################################################################
+# Execute a multi-row INSERT into a specified table.
+#
+# Arguments:
+#
+#   $1 -- table specification
+#
+#   all subsequent arguments represent tuples to insert in the form:
+#   (value1, ..., valueN)
+#
+# Notes:
+#
+#   1. Bash special characters in the arguments must be quoted to screen them
+#      from interpreting by Bash, i.e. \(1,...,\'a'\)
+#
+#   2. you can use Bash brace expansion to generate multiple tuples, e.g.:
+#      \({1..1000},\'a'\) will generate 1000 tuples (1,'a'), ..., (1000, 'a')
+##############################################################################
+function multi_row_insert()
+{
+    local table=$1
+    shift
+
+    vlog "Inserting $# rows into $table..."
+    (IFS=,; echo "INSERT INTO $table VALUES $*") | \
+        $MYSQL $MYSQL_ARGS
+    vlog "Done."
+}
+
 # To avoid unbound variable error when no server have been started
 SRV_MYSQLD_IDS=

=== modified file 'test/inc/ib_incremental_common.sh'
--- test/inc/ib_incremental_common.sh	2013-07-25 15:04:49 +0000
+++ test/inc/ib_incremental_common.sh	2013-08-19 04:08:39 +0000
@@ -15,50 +15,25 @@
 load_dbase_schema incremental_sample
 
 # Adding initial rows
-vlog "Adding initial rows to database..."
-numrow=100
-count=0
-while [ "$numrow" -gt "$count" ]
-do
-	${MYSQL} ${MYSQL_ARGS} -e "insert into test values ($count, $numrow);" incremental_sample
-	let "count=count+1"
-done
-vlog "Initial rows added"
-
-# Full backup
-# backup root directory
-mkdir -p $topdir/backup
+multi_row_insert incremental_sample.test \({1..100},100\)
 
 vlog "Starting backup"
-innobackupex  $topdir/backup
-full_backup_dir=`grep "innobackupex: Backup created in directory" $OUTFILE | awk -F\' '{print $2}'`
-vlog "Full backup done to directory $full_backup_dir"
+full_backup_dir=$topdir/full_backup
+innobackupex --no-timestamp $full_backup_dir
 
 # Changing data
 
 vlog "Making changes to database"
 ${MYSQL} ${MYSQL_ARGS} -e "create table t2 (a int(11) default null, number int(11) default null) engine=innodb" incremental_sample
-let "count=numrow+1"
-let "numrow=1000"
-while [ "$numrow" -gt "$count" ]
-do
-	${MYSQL} ${MYSQL_ARGS} -e "insert into test values ($count, $numrow);" incremental_sample
-	${MYSQL} ${MYSQL_ARGS} -e "insert into t2 values ($count, $numrow);" incremental_sample
-	let "count=count+1"
-done
+
+multi_row_insert incremental_sample.test \({101..1000},1000\)
+multi_row_insert incremental_sample.t2 \({101..1000},1000\)
 
 # Rotate bitmap file here and force checkpoint at the same time
 shutdown_server
 start_server
 
-i=1001
-while [ "$i" -lt "7500" ]
-do
-        ${MYSQL} ${MYSQL_ARGS} -e "insert into t2 values ($i, repeat(\"ab\", 32500));" incremental_sample
-        let "i=i+1"
-done
-
-vlog "Changes done"
+multi_row_insert incremental_sample.t2 \({1001..7500},REPEAT\(\'ab\',32500\)\)
 
 # Saving the checksum of original table
 checksum_test_a=`checksum_table incremental_sample test`
@@ -73,10 +48,9 @@
 vlog "###############"
 
 # Incremental backup
-innobackupex --incremental --incremental-basedir=$full_backup_dir \
-    $topdir/backup $ib_inc_extra_args
-inc_backup_dir=`grep "innobackupex: Backup created in directory" $OUTFILE | tail -n 1 | awk -F\' '{print $2}'`
-vlog "Incremental backup done to directory $inc_backup_dir"
+inc_backup_dir=$topdir/backup_incremental
+innobackupex --no-timestamp --incremental --incremental-basedir=$full_backup_dir \
+    $inc_backup_dir $ib_inc_extra_args
 
 vlog "Preparing backup"
 # Prepare backup

=== modified file 'test/t/bug1182726.sh'
--- test/t/bug1182726.sh	2013-07-08 09:28:05 +0000
+++ test/t/bug1182726.sh	2013-08-19 04:08:39 +0000
@@ -19,15 +19,7 @@
 load_dbase_schema incremental_sample
 
 # Adding initial rows
-vlog "Adding initial rows to database..."
-numrow=100
-count=0
-while [ "$numrow" -gt "$count" ]
-do
-	${MYSQL} ${MYSQL_ARGS} -e "insert into test values ($count, $numrow);" incremental_sample
-	let "count=count+1"
-done
-vlog "Initial rows added"
+multi_row_insert incremental_sample.test \({1..100},100\)
 
 # Full backup of the slave server
 switch_server $slave_id

=== modified file 'test/t/bug810269.sh'
--- test/t/bug810269.sh	2013-07-25 15:04:49 +0000
+++ test/t/bug810269.sh	2013-08-19 04:08:39 +0000
@@ -20,28 +20,7 @@
     "ALTER TABLE test ENGINE=InnoDB ROW_FORMAT=compressed \
 KEY_BLOCK_SIZE=4" incremental_sample
 
-vlog "Adding initial rows to table"
-
-numrow=10000
-count=0
-while [ "$numrow" -gt "$count" ]; do
-    sql="INSERT INTO test VALUES ($count, $numrow)"
-    let "count=count+1"
-    for ((i=0; $i<99; i++)); do
-	sql="$sql,($count, $numrow)"
-	let "count=count+1"
-    done
-    ${MYSQL} ${MYSQL_ARGS} -e "$sql" incremental_sample
-done
-
-rows=`${MYSQL} ${MYSQL_ARGS} -Ns -e "SELECT COUNT(*) FROM test" \
-    incremental_sample`
-if [ "$rows" != "10000" ]; then
-    vlog "Failed to add initial rows"
-    exit -1
-fi
-
-vlog "Initial rows added"
+multi_row_insert incremental_sample.test \({1..10000},10000\)
 
 checksum_a=`checksum_table incremental_sample test`
 

=== modified file 'test/t/ib_slave_info.sh'
--- test/t/ib_slave_info.sh	2013-04-27 18:46:54 +0000
+++ test/t/ib_slave_info.sh	2013-08-19 04:08:39 +0000
@@ -12,15 +12,7 @@
 load_dbase_schema incremental_sample
 
 # Adding initial rows
-vlog "Adding initial rows to database..."
-numrow=100
-count=0
-while [ "$numrow" -gt "$count" ]
-do
-	${MYSQL} ${MYSQL_ARGS} -e "insert into test values ($count, $numrow);" incremental_sample
-	let "count=count+1"
-done
-vlog "Initial rows added"
+multi_row_insert incremental_sample.test \({1..100},100\)
 
 # Full backup of the slave server
 switch_server $slave_id

=== modified file 'test/t/ib_stream_incremental.sh'
--- test/t/ib_stream_incremental.sh	2013-07-03 18:30:28 +0000
+++ test/t/ib_stream_incremental.sh	2013-08-19 04:08:39 +0000
@@ -22,15 +22,7 @@
     load_dbase_schema incremental_sample
 
 # Adding initial rows
-    vlog "Adding initial rows to database..."
-    numrow=100
-    count=0
-    while [ "$numrow" -gt "$count" ]
-    do
-	${MYSQL} ${MYSQL_ARGS} -e "insert into test values ($count, $numrow);" incremental_sample
-	let "count=count+1"
-    done
-    vlog "Initial rows added"
+    multi_row_insert incremental_sample.test \({1..100},100\)
 
     full_backup_dir=$topdir/full_backup
 
@@ -38,16 +30,7 @@
     innobackupex --no-timestamp $full_backup_dir
 
 # Changing data
-
-    vlog "Making changes to database"
-    let "count=numrow+1"
-    let "numrow=500"
-    while [ "$numrow" -gt "$count" ]
-    do
-	${MYSQL} ${MYSQL_ARGS} -e "insert into test values ($count, $numrow);" incremental_sample
-	let "count=count+1"
-    done
-    vlog "Changes done"
+    multi_row_insert incremental_sample.test \({101..500},500\)
 
 # Saving the checksum of original table
     checksum_a=`checksum_table incremental_sample test`

=== modified file 'test/t/xb_export.sh'
--- test/t/xb_export.sh	2013-07-25 15:04:49 +0000
+++ test/t/xb_export.sh	2013-08-19 04:08:39 +0000
@@ -35,15 +35,7 @@
 load_dbase_schema incremental_sample
 
 # Adding some data to database
-vlog "Adding initial rows to database..."
-numrow=100
-count=0
-while [ "$numrow" -gt "$count" ]
-do
-	${MYSQL} ${MYSQL_ARGS} -e "insert into test values ($count, $numrow);" incremental_sample
-	let "count=count+1"
-done
-vlog "Initial rows added"
+multi_row_insert incremental_sample.test \({1..100},100\)
 
 checksum_1=`checksum_table incremental_sample test`
 rowsnum_1=`${MYSQL} ${MYSQL_ARGS} -Ns -e "select count(*) from test" incremental_sample`

=== modified file 'test/t/xb_incremental_compressed.inc'
--- test/t/xb_incremental_compressed.inc	2013-07-25 15:04:49 +0000
+++ test/t/xb_incremental_compressed.inc	2013-08-19 04:08:39 +0000
@@ -12,23 +12,6 @@
 
 . inc/common.sh
 
-function add_rows()
-{
-  local table=$1
-  local start=$2
-  local limit=$3
-
-  while [ "$limit" -gt "$start" ]; do
-    sql="INSERT INTO $table VALUES ($start, $limit)"
-    let "start=start+1"
-    for ((i=0; $i<99; i++)); do
-      sql="${sql},($start, $limit)"
-      let "start=start+1"
-    done
-    ${MYSQL} ${MYSQL_ARGS} -e "$sql" incremental_sample
-  done
-}
-
 #
 # Test incremental backup of a compressed tablespace with a specific page size
 #
@@ -61,18 +44,7 @@
 
   # Adding 10k rows
 
-  vlog "Adding initial rows to database..."
-
-  add_rows test 0 10000
-
-  rows=`${MYSQL} ${MYSQL_ARGS} -Ns -e "SELECT COUNT(*) FROM test" \
-incremental_sample`
-  if [ "$rows" != "10000" ]; then
-    vlog "Failed to add initial rows"
-    exit -1
-  fi
-
-  vlog "Initial rows added"
+  multi_row_insert incremental_sample.test \({1..10000},10000\)
 
   # Full backup
 
@@ -97,15 +69,15 @@
 number INT(11) DEFAULT NULL) ENGINE=INNODB \
 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=$page_size" incremental_sample
 
-  add_rows test 10001 12500
-  add_rows t2 10001 12500
+  multi_row_insert incremental_sample.test \({10001..12500},12500\)
+  multi_row_insert incremental_sample.t2 \({10001..12500},12500\)
 
   # Rotate bitmap file here and force checkpoint at the same time
   shutdown_server
   start_server
 
-  add_rows test 12501 15000
-  add_rows t2 12501 15000
+  multi_row_insert incremental_sample.test \({12501..15000},15000\)
+  multi_row_insert incremental_sample.t2 \({12501..15000},15000\)
 
   rows=`${MYSQL} ${MYSQL_ARGS} -Ns -e "SELECT COUNT(*) FROM test" \
 incremental_sample`