Merge lp:~akopytov/percona-xtrabackup/parallel-table-rebuild into lp:percona-xtrabackup/2.1

[Alexey Kopytov]

    Implementation of
    https://blueprints.launchpad.net/percona-xtrabackup/+spec/parallel-table-rebuild

    Add a new option to innobackupex/xtrabackup called --rebuild-threads
    with the default value 1. The option specifies the number of threads
    started by XtraBackup when rebuilding secondary indexes on --apply-log
    --rebuild-indexes. Each thread rebuilds indexes for a single .ibd
    tablespace at a time.

    UNIV_LIST_DEBUG had to be removed from debug flags for non-5.6 build
    configurations, as the UNIV_LIST_DEBUG implementation of
    UT_LIST_REMOVE_CLEAR in 5.5- is not C++ compatible.

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

[Stewart Smith (stewart)]

Alexey Kopytov <email address hidden> writes:
> Add a new option to innobackupex/xtrabackup called --rebuild-threads
> with the default value 1. The option specifies the number of threads
> started by XtraBackup when rebuilding secondary indexes on --apply-log
> --rebuild-indexes. Each thread rebuilds indexes for a single .ibd
> tablespace at a time.

Why not default to sysconf(_SC_NPROCESSORS_ONLN) ?

I'm trying to think if this would ever be a really bad idea...

--
Stewart Smith

[Alexey Kopytov (akopytov)]

I was planning to implement that originally, but then changed my mind. Table rebuild is mostly I/O bound, so defaulting to _SC_NPROCESSORS_ONLN on say a 16-threads CPU will unlikely make things any faster

It should rather depend on storage specs, but I'm not aware of any magic to get reasonable defaults for I/O bound threads.

[Laurynas Biveinis (laurynas-biveinis)]

review: Approve

=== modified file 'doc/source/xtrabackup_bin/compact_backups.rst'
--- doc/source/xtrabackup_bin/compact_backups.rst	2013-02-04 14:09:53 +0000
+++ doc/source/xtrabackup_bin/compact_backups.rst	2013-05-03 07:35:34 +0000
@@ -47,18 +47,32 @@
 
 Output, beside the standard |innobackupex| output, should contain the information about indexes being rebuilt, like: ::
 
-  130201 10:40:20  InnoDB: Waiting for the background threads to start
-  Rebuilding indexes for table sbtest/sbtest1 (space id: 10)
-    Found index k_1
-    Dropping 1 index(es).
-    Rebuilding 1 index(es).
-  Rebuilding indexes for table sbtest/sbtest2 (space id: 11)
-    Found index k_1
-    Found index c
-    Found index k
-    Found index c_2
-    Dropping 4 index(es).
-    Rebuilding 4 index(es).
+  [01] Checking if there are indexes to rebuild in table sakila/city (space id: 9)
+  [01]   Found index idx_fk_country_id
+  [01]   Rebuilding 1 index(es).
+  [01] Checking if there are indexes to rebuild in table sakila/country (space id: 10)
+  [01] Checking if there are indexes to rebuild in table sakila/customer (space id: 11)
+  [01]   Found index idx_fk_store_id
+  [01]   Found index idx_fk_address_id
+  [01]   Found index idx_last_name
+  [01]   Rebuilding 3 index(es).
+
+Additionally, you can use the :option:`--rebuild-threads` option to process tables in multiple threads when rebuilding indexes, e.g.: ::
+
+  $ xtrabackup --prepare --rebuild-indexes --rebuild-threads=16 /data/backups/
+
+In this case |XtraBackup| will create 16 worker threads with each thread rebuilding indexes for one table at a time. It will also show thread IDs for each message ::
+
+  Starting 16 threads to rebuild indexes.
+
+  [09] Checking if there are indexes to rebuild in table sakila/city (space id: 9)
+  [09]   Found index idx_fk_country_id
+  [10] Checking if there are indexes to rebuild in table sakila/country (space id: 10)
+  [11] Checking if there are indexes to rebuild in table sakila/customer (space id: 11)
+  [11]   Found index idx_fk_store_id
+  [11]   Found index idx_fk_address_id
+  [11]   Found index idx_last_name
+  [11]   Rebuilding 3 index(es).
 
 Since |XtraBackup| has no information when applying an incremental backup to a compact full one, on whether there will be more incremental backups applied to it later or not, rebuilding indexes needs to be explicitly requested by a user whenever a full backup with some incremental backups merged is ready to be restored. Rebuilding indexes unconditionally on every incremental backup merge is not an option, since it is an expensive operation.
 

=== modified file 'innobackupex'
--- innobackupex	2013-04-28 18:35:33 +0000
+++ innobackupex	2013-05-03 07:35:34 +0000
@@ -137,6 +137,7 @@
 
 my $option_compact = '';
 my $option_rebuild_indexes = '';
+my $option_rebuild_threads = 0;
 
 my %mysql;
 my $option_backup = '';
@@ -987,6 +988,9 @@
     if ($option_rebuild_indexes) {
         $cmdline = $cmdline . " --rebuild-indexes"
     }
+    if ($option_rebuild_threads) {
+        $cmdline = $cmdline . " --rebuild-threads=$option_rebuild_threads"
+    }
 
     $now = current_time();
     print STDERR "\n$now  $prefix Starting ibbackup with command: $cmdline\n\n";
@@ -1225,9 +1229,6 @@
     if ($option_compact) {
 	$options = $options . " --compact";
     }
-    if ($option_rebuild_indexes) {
-        $options = $options . " --rebuild-indexes";
-    }
 
     $cmdline = "$option_ibbackup_binary $options";
 
@@ -1965,7 +1966,8 @@
                         'safe-slave-backup' => \$option_safe_slave_backup,
                         'safe-slave-backup-timeout=i' => \$option_safe_slave_backup_timeout,
                         'compact' => \$option_compact,
-                        'rebuild-indexes' => \$option_rebuild_indexes
+                        'rebuild-indexes' => \$option_rebuild_indexes,
+                        'rebuild-threads=i' => \$option_rebuild_threads
     );
                         
     if (!$rcode) {
@@ -3146,6 +3148,10 @@
 
 This option only has effect when used together with the --apply-log option and is passed directly to xtrabackup. When used, makes xtrabackup rebuild all secondary indexes after applying the log. This option is normally used to prepare compact backups. See the XtraBackup manual for more information.
 
+=item --rebuild-threads
+
+This option only has effect when used together with the --apply-log and --rebuild-indexes option and is passed directly to xtrabackup. When used, xtrabackup processes tablespaces in parallel with the specified number of threads when rebuilding indexes. See the XtraBackup manual for more information.
+
 =item --redo-only
 
 This option should be used when preparing the base full backup and when merging all incrementals except the last one. This option is passed directly to xtrabackup's --apply-log-only option. This forces xtrabackup to skip the "rollback" phase and do a "redo" only. This is necessary if the backup will have incremental changes applied to it later. See the xtrabackup documentation for details. 

=== modified file 'src/compact.cc'
--- src/compact.cc	2013-04-23 07:44:24 +0000
+++ src/compact.cc	2013-05-03 07:35:34 +0000
@@ -42,16 +42,29 @@
 #define XB_TMPFILE_SUFFIX ".tmp"
 
 /* Page range */
-typedef struct {
+struct page_range_t {
 	ulint	from;			/*!< range start */
 	ulint	to;			/*!< range end */
-} page_range_t;
+};
 
 /* Cursor in a page map file */
-typedef struct {
+struct page_map_cursor_t {
 	File		fd;	/*!< file descriptor */
 	IO_CACHE	cache;	/*!< IO_CACHE associated with fd */
-} page_map_cursor_t;
+};
+
+/* Table descriptor for the index rebuild operation */
+struct index_rebuild_table_t {
+	char*	name;					/* table name */
+	ulint	space_id;				/* space ID */
+	UT_LIST_NODE_T(index_rebuild_table_t)	list;	/* list node */
+};
+
+/* Thread descriptor for the index rebuild operation */
+struct index_rebuild_thread_t {
+	ulint		num;    /* thread number */
+	pthread_t	id;	/* thread ID */
+};
 
 /* Empty page use to replace skipped pages in the data files */
 static byte		empty_page[UNIV_PAGE_SIZE_MAX];
@@ -60,6 +73,12 @@
 	sizeof(compacted_page_magic) - 1;
 static const ulint	compacted_page_magic_offset = FIL_PAGE_DATA;
 
+/* Mutex protecting table_list */
+static pthread_mutex_t					table_list_mutex;
+/* List of tablespaces to process by the index rebuild operation */
+static UT_LIST_BASE_NODE_T(index_rebuild_table_t)	table_list;
+
+
 /************************************************************************
 Compact page filter. */
 static my_bool wf_compact_init(xb_write_filt_ctxt_t *ctxt, char *dst_name,
@@ -711,9 +730,10 @@
 static
 void
 xb_rebuild_indexes_for_table(
-/*=======================*/
-	dict_table_t*	table,	/*!< in: table */
-	trx_t*		trx)	/*!< in: transaction handle */
+/*=========================*/
+	dict_table_t*	table,		/*!< in: table */
+	trx_t*		trx,		/*!< in: transaction handle */
+	ulint		thread_n)	/*!< in: thread number */
 {
 	dict_index_t*	index;
 	dict_index_t**	indexes;
@@ -726,7 +746,7 @@
 	ulint*		add_key_nums;
 #endif
 
-	ut_ad(mutex_own(&(dict_sys->mutex)));
+	ut_ad(!mutex_own(&(dict_sys->mutex)));
 	ut_ad(table);
 
 	ut_a(UT_LIST_GET_LEN(table->indexes) > 0);
@@ -752,11 +772,11 @@
 	index = dict_table_get_first_index(table);
 	ut_a(dict_index_is_clust(index));
 
-	msg("  Dropping %lu index(es).\n", n_indexes);
+	row_mysql_lock_data_dictionary(trx);
 
 	for (i = 0; (index = dict_table_get_next_index(index)); i++) {
 
-		msg("  Found index %s\n", index->name);
+		msg("[%02lu]   Found index %s\n", thread_n, index->name);
 
 		/* Pretend that it's the current trx that created this index.
 		Required to avoid 5.6+ debug assertions. */
@@ -805,7 +825,7 @@
 	row_merge_drop_indexes(trx, table, indexes, n_indexes);
 #endif
 
-	msg("  Rebuilding %lu index(es).\n", n_indexes);
+	msg("[%02lu]   Rebuilding %lu index(es).\n", thread_n, n_indexes);
 
 	error = row_merge_lock_table(trx, table, LOCK_X);
 	xb_a(error == DB_SUCCESS);
@@ -818,6 +838,16 @@
 #endif
 	}
 
+	/* Commit trx to release latches on system tables */
+	trx_commit_for_mysql(trx);
+	trx_start_for_ddl(trx, TRX_DICT_OP_INDEX);
+
+	row_mysql_unlock_data_dictionary(trx);
+
+	/* Reacquire table lock for row_merge_build_indexes() */
+	error = row_merge_lock_table(trx, table, LOCK_X);
+	xb_a(error == DB_SUCCESS);
+
 #if MYSQL_VERSION_ID >= 50600
 	error = row_merge_build_indexes(trx, table, table, FALSE, indexes,
 					add_key_nums, n_indexes, &dummy_table,
@@ -835,6 +865,77 @@
 	trx_start_for_ddl(trx, TRX_DICT_OP_INDEX);
 }
 
+/**************************************************************************
+Worker thread function for index rebuild. */
+static
+void *
+xb_rebuild_indexes_thread_func(
+/*===========================*/
+	void*	arg)	/* thread context */
+{
+	dict_table_t*		table;
+	index_rebuild_table_t*	rebuild_table;
+	index_rebuild_thread_t*	thread;
+	trx_t*			trx;
+
+	thread = (index_rebuild_thread_t *) arg;
+
+	trx = trx_allocate_for_mysql();
+
+	/* Suppress foreign key checks, as we are going to drop and recreate all
+	secondary keys. */
+	trx->check_foreigns = FALSE;
+	trx_start_for_ddl(trx, TRX_DICT_OP_INDEX);
+
+	/* Loop until there are no more tables in tables list */
+	for (;;) {
+		pthread_mutex_lock(&table_list_mutex);
+
+		rebuild_table = UT_LIST_GET_FIRST(table_list);
+
+		if (rebuild_table == NULL) {
+
+			pthread_mutex_unlock(&table_list_mutex);
+			break;
+		}
+
+		UT_LIST_REMOVE(list, table_list, rebuild_table);
+
+		pthread_mutex_unlock(&table_list_mutex);
+
+		ut_ad(rebuild_table->name);
+		ut_ad(!trx_sys_sys_space(rebuild_table->space_id));
+
+		row_mysql_lock_data_dictionary(trx);
+
+		table = dict_table_get_low(rebuild_table->name);
+
+		row_mysql_unlock_data_dictionary(trx);
+
+		ut_a(table != NULL);
+		ut_a(table->space == rebuild_table->space_id);
+
+		/* Discard change buffer entries for this space */
+		ibuf_delete_for_discarded_space(rebuild_table->space_id);
+
+		msg("[%02lu] Checking if there are indexes to rebuild in table "
+		    "%s (space id: %lu)\n",
+		    thread->num,
+		    rebuild_table->name, rebuild_table->space_id);
+
+		xb_rebuild_indexes_for_table(table, trx, thread->num);
+
+		mem_free(rebuild_table->name);
+		mem_free(rebuild_table);
+	}
+
+	trx_commit_for_mysql(trx);
+
+	trx_free_for_mysql(trx);
+
+	return(NULL);
+}
+
 /******************************************************************************
 Rebuild all secondary indexes in all tables in separate spaces. Called from
 innobase_start_or_create_for_mysql(). */
@@ -842,17 +943,18 @@
 xb_compact_rebuild_indexes(void)
 /*=============================*/
 {
-	dict_table_t*	sys_tables;
-	dict_index_t*	sys_index;
-	btr_pcur_t	pcur;
-	const rec_t*	rec;
-	mtr_t		mtr;
-	const byte*	field;
-	ulint		len;
-	ulint		space_id;
-	char*		name;
-	dict_table_t*	table;
-	trx_t*		trx;
+	dict_table_t*		sys_tables;
+	dict_index_t*		sys_index;
+	btr_pcur_t		pcur;
+	const rec_t*		rec;
+	mtr_t			mtr;
+	const byte*		field;
+	ulint			len;
+	ulint			space_id;
+	trx_t*			trx;
+	index_rebuild_table_t*	rebuild_table;
+	index_rebuild_thread_t*	threads;
+	ulint			i;
 
 #if MYSQL_VERSION_ID >= 50600
 	/* Set up the dummy table for the index rebuild error reporting */
@@ -860,15 +962,12 @@
 	dummy_table.s = &dummy_table_share;
 #endif
 
-	/* Iterate all tables that are not in the system tablespace. */
+	/* Iterate all tables that are not in the system tablespace and add them
+	to the list of tables to be rebuilt later. */
 
 	trx = trx_allocate_for_mysql();
 	trx_start_for_ddl(trx, TRX_DICT_OP_INDEX);
 
-	/* Suppress foreign key checks, as we are going to drop and recreate all
-	secondary keys. */
-	trx->check_foreigns = FALSE;
-
 	row_mysql_lock_data_dictionary(trx);
 
 	/* Enlarge the fatal lock wait timeout during index rebuild
@@ -881,6 +980,9 @@
 	sys_index = UT_LIST_GET_FIRST(sys_tables->indexes);
 	ut_a(!dict_table_is_comp(sys_tables));
 
+	pthread_mutex_init(&table_list_mutex, NULL);
+	UT_LIST_INIT(table_list);
+
 	xb_btr_pcur_open_at_index_side(TRUE, sys_index, BTR_SEARCH_LEAF, &pcur,
 				       TRUE, 0, &mtr);
 	for (;;) {
@@ -910,28 +1012,13 @@
 		}
 
 		field = rec_get_nth_field_old(rec, 0, &len);
-		name = mem_strdupl((char*) field, len);
-
-		btr_pcur_store_position(&pcur, &mtr);
-
-		mtr_commit(&mtr);
-
-		table = dict_table_get_low(name);
-		ut_a(table != NULL);
-
-		/* Discard change buffer entries for this space */
-		ibuf_delete_for_discarded_space(space_id);
-
-		msg("Rebuilding indexes for table %s (space id: %lu)\n", name,
-		    space_id);
-
-		xb_rebuild_indexes_for_table(table, trx);
-
-		mem_free(name);
-
-		mtr_start(&mtr);
-
-		btr_pcur_restore_position(BTR_SEARCH_LEAF, &pcur, &mtr);
+
+		rebuild_table = static_cast<index_rebuild_table_t *>
+			(mem_alloc(sizeof(*rebuild_table)));
+		rebuild_table->name = mem_strdupl((char*) field, len);
+		rebuild_table->space_id = space_id;
+
+		UT_LIST_ADD_LAST(list, table_list, rebuild_table);
 	}
 
 	btr_pcur_close(&pcur);
@@ -942,4 +1029,36 @@
 	trx_commit_for_mysql(trx);
 
 	trx_free_for_mysql(trx);
+
+	/* Start worker threads for the index rebuild operation */
+	ut_ad(xtrabackup_rebuild_threads > 0);
+
+	if (xtrabackup_rebuild_threads > 1) {
+		msg("Starting %lu threads to rebuild indexes.\n",
+		    xtrabackup_rebuild_threads);
+	}
+
+	threads = (index_rebuild_thread_t *)
+		mem_alloc(sizeof(*threads) *
+			  xtrabackup_rebuild_threads);
+
+	for (i = 0; i < xtrabackup_rebuild_threads; i++) {
+
+		threads[i].num = i+1;
+		if (pthread_create(&threads[i].id, NULL,
+				   xb_rebuild_indexes_thread_func,
+				   &threads[i])) {
+
+			msg("error: pthread_create() failed: errno = %d\n",
+			    errno);
+			ut_a(0);
+		}
+	}
+
+	/* Wait for worker threads to finish */
+	for (i = 0; i < xtrabackup_rebuild_threads; i++) {
+		pthread_join(threads[i].id, NULL);
+	}
+
+	mem_free(threads);
 }

=== modified file 'src/xtrabackup.cc'
--- src/xtrabackup.cc	2013-04-29 05:46:15 +0000
+++ src/xtrabackup.cc	2013-05-03 07:35:34 +0000
@@ -163,7 +163,6 @@
 {array_elements(xtrabackup_encrypt_algo_names)-1,"",
 	xtrabackup_encrypt_algo_names, NULL};
 
-
 ibool xtrabackup_encrypt = FALSE;
 ulong xtrabackup_encrypt_algo;
 char *xtrabackup_encrypt_key = NULL;
@@ -171,6 +170,8 @@
 uint xtrabackup_encrypt_threads;
 ulonglong xtrabackup_encrypt_chunk_size = 0;
 
+ulint xtrabackup_rebuild_threads = 1;
+
 /* sleep interval beetween log copy iterations in log copying thread
 in milliseconds (default is 1 second) */
 int xtrabackup_log_copy_interval = 1000;
@@ -449,6 +450,7 @@
   OPT_XTRA_DEBUG_SYNC,
   OPT_XTRA_COMPACT,
   OPT_XTRA_REBUILD_INDEXES,
+  OPT_XTRA_REBUILD_THREADS,
 #if MYSQL_VERSION_ID >= 50600
   OPT_INNODB_CHECKSUM_ALGORITHM,
   OPT_INNODB_UNDO_DIRECTORY,
@@ -828,6 +830,12 @@
    (G_PTR*) &xtrabackup_rebuild_indexes, (G_PTR*) &xtrabackup_rebuild_indexes,
    0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
 
+  {"rebuild_threads", OPT_XTRA_REBUILD_INDEXES,
+   "Use this number of threads to rebuild indexes in a compact backup. "
+   "Only has effect with --prepare and --rebuild-indexes.",
+   (G_PTR*) &xtrabackup_rebuild_threads, (G_PTR*) &xtrabackup_rebuild_threads,
+   0, GET_UINT, REQUIRED_ARG, 1, 1, UINT_MAX, 0, 0, 0},
+
 #if MYSQL_VERSION_ID >= 50600
   {"innodb_checksum_algorithm", OPT_INNODB_CHECKSUM_ALGORITHM,
   "The algorithm InnoDB uses for page checksumming. [CRC32, STRICT_CRC32, "

=== modified file 'src/xtrabackup.h'
--- src/xtrabackup.h	2013-04-16 10:35:58 +0000
+++ src/xtrabackup.h	2013-05-03 07:35:34 +0000
@@ -52,6 +52,8 @@
 
 extern xb_page_bitmap *changed_page_bitmap;
 
+extern ulint	xtrabackup_rebuild_threads;
+
 void xtrabackup_io_throttling(void);
 my_bool xb_write_delta_metadata(const char *filename,
 				const xb_delta_info_t *info);

=== modified file 'test/t/compact.sh'
--- test/t/compact.sh	2013-01-14 11:02:59 +0000
+++ test/t/compact.sh	2013-05-03 07:35:34 +0000
@@ -32,3 +32,32 @@
 
 verify_db_state sakila
 
+##########################################################################
+# Test --rebuild-threads
+##########################################################################
+
+rm -rf $backup_dir
+
+innobackupex --no-timestamp --compact $backup_dir
+vlog "Backup created in directory $backup_dir"
+
+record_db_state sakila
+
+stop_server
+
+# Remove datadir
+rm -r $mysql_datadir
+
+# Restore sakila
+
+innobackupex --apply-log --rebuild-indexes --rebuild-threads=16 $backup_dir
+
+grep -q "Starting 16 threads to rebuild indexes" $OUTFILE
+
+vlog "Restoring MySQL datadir"
+mkdir -p $mysql_datadir
+innobackupex --copy-back $backup_dir
+
+start_server
+
+verify_db_state sakila

=== modified file 'utils/build.sh' (properties changed: -x to +x)
--- utils/build.sh	2013-04-30 07:47:49 +0000
+++ utils/build.sh	2013-05-03 07:35:34 +0000
@@ -43,7 +43,13 @@
 -DUNIV_DEBUG_THREAD_CREATION -DUNIV_DEBUG_LOCK_VALIDATE -DUNIV_DEBUG_PRINT \
 -DUNIV_DEBUG_FILE_ACCESS -DUNIV_SEARCH_DEBUG -DUNIV_LOG_LSN_DEBUG \
 -DUNIV_ZIP_DEBUG -DUNIV_AHI_DEBUG -DUNIV_SQL_DEBUG -DUNIV_AIO_DEBUG \
--DUNIV_LRU_DEBUG -DUNIV_BUF_DEBUG -DUNIV_HASH_DEBUG -DUNIV_LIST_DEBUG -DUNIV_IBUF_DEBUG"
+-DUNIV_LRU_DEBUG -DUNIV_BUF_DEBUG -DUNIV_HASH_DEBUG -DUNIV_IBUF_DEBUG"
+
+    if [ "$type" = "innodb56" ]
+    then
+	innodb_extra_debug="$innodb_extra_debug -DUNIV_LIST_DEBUG"
+    fi
+
     CFLAGS="$CFLAGS -g -O0 $innodb_extra_debug -DSAFE_MUTEX -DSAFEMALLOC"
     CXXFLAGS="$CXXFLAGS -g -O0 $innodb_extra_debug -DSAFE_MUTEX -DSAFEMALLOC"
     extra_config_51="--with-debug=full"
@@ -181,7 +187,12 @@
 	export LIBS="$LIBS -lrt"
     fi
     $MAKE_CMD MYSQL_ROOT_DIR=$server_dir clean
-    echo "$MAKE_CMD MYSQL_ROOT_DIR=$server_dir XTRABACKUP_VERSION=$XTRABACKUP_VERSION $xtrabackup_target" > build.sh
+    cat > build.sh <<EOF
+export CFLAGS="$CFLAGS"
+export CXXFLAGS="$CXXFLAGS"
+$MAKE_CMD MYSQL_ROOT_DIR=$server_dir \
+  XTRABACKUP_VERSION=$XTRABACKUP_VERSION $xtrabackup_target
+EOF
     chmod +x build.sh
     $MAKE_CMD MYSQL_ROOT_DIR=$server_dir XTRABACKUP_VERSION=$XTRABACKUP_VERSION $xtrabackup_target
     cd $top_dir