MDEV-8696: Adding indexes on empty table is slow with large innodb_sort_buffer_size.

Analysis: Current implementation will write and read at least one block
(sort_buffer_size bytes) from disk / index even if that block does not
contain any records.

Fix: Avoid writing / reading empty blocks to temporary files (disk).

6 files changed, 94 insertions, 21 deletions

diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc
index 4ad637d57d3..c55a260be9a 100644
--- a/storage/xtradb/handler/ha_innodb.cc
+++ b/storage/xtradb/handler/ha_innodb.cc
@@ -4,7 +4,7 @@ Copyright (c) 2000, 2015, Oracle and/or its affiliates. All Rights Reserved.
 Copyright (c) 2008, 2009 Google Inc.
 Copyright (c) 2009, Percona Inc.
 Copyright (c) 2012, Facebook Inc.
-Copyright (c) 2013, 2014 SkySQL Ab. All Rights Reserved.
+Copyright (c) 2013, 2015, MariaDB Corporation.
 
 Portions of this file contain modifications contributed and copyrighted by
 Google, Inc. Those modifications are gratefully acknowledged and are described
@@ -824,6 +824,12 @@ static SHOW_VAR innodb_status_variables[]= {
   (char*) &export_vars.innodb_os_log_pending_writes,	  SHOW_LONG},
   {"os_log_written",
   (char*) &export_vars.innodb_os_log_written,		  SHOW_LONGLONG},
+  {"os_merge_buffers_written",
+  (char*) &export_vars.innodb_merge_buffers_written,	  SHOW_LONGLONG},
+  {"os_merge_buffers_read",
+  (char*) &export_vars.innodb_merge_buffers_read,	  SHOW_LONGLONG},
+  {"os_merge_buffers_merged",
+  (char*) &export_vars.innodb_merge_buffers_merged,	  SHOW_LONGLONG},
   {"page_size",
   (char*) &export_vars.innodb_page_size,		  SHOW_LONG},
   {"pages_created",
diff --git a/storage/xtradb/include/srv0mon.h b/storage/xtradb/include/srv0mon.h
index 2d90f47eefe..d2b65721e1a 100644
--- a/storage/xtradb/include/srv0mon.h
+++ b/storage/xtradb/include/srv0mon.h
@@ -2,6 +2,7 @@
 
 Copyright (c) 2010, 2013, Oracle and/or its affiliates. All Rights Reserved.
 Copyright (c) 2012, Facebook Inc.
+Copyright (c) 2014, 2015, MariaDB Corporation.
 
 This program is free software; you can redistribute it and/or modify it
 under the terms of the GNU General Public License as published by the
@@ -255,6 +256,9 @@ enum monitor_id_t {
 	MONITOR_OVLD_OS_LOG_FSYNC,
 	MONITOR_OVLD_OS_LOG_PENDING_FSYNC,
 	MONITOR_OVLD_OS_LOG_PENDING_WRITES,
+	MONITOR_MERGE_BLOCKS_WRITTEN,
+	MONITOR_MERGE_BLOCKS_READ,
+	MONITOR_MERGE_BLOCKS_MERGED,
 
 	/* Transaction related counters */
 	MONITOR_MODULE_TRX,
diff --git a/storage/xtradb/include/srv0srv.h b/storage/xtradb/include/srv0srv.h
index 6deb8dc5584..14c9cd4de26 100644
--- a/storage/xtradb/include/srv0srv.h
+++ b/storage/xtradb/include/srv0srv.h
@@ -3,7 +3,7 @@
 Copyright (c) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
 Copyright (c) 2008, 2009, Google Inc.
 Copyright (c) 2009, Percona Inc.
-Copyright (c) 2013, 2014, SkySQL Ab. All Rights Reserved.
+Copyright (c) 2013, 2015, MariaDB Corporation.
 
 Portions of this file contain modifications contributed and copyrighted by
 Google, Inc. Those modifications are gratefully acknowledged and are described
@@ -145,6 +145,13 @@ struct srv_stats_t {
 	/** Number of lock waits that have been up to max time (i.e.) lock
 	wait timeout */
 	ulint_ctr_1_t		n_lock_max_wait_time;
+
+	/** Number of merge buffers written */
+	ulint_ctr_64_t		merge_buffers_written;
+	/** Number of merge buffers read */
+	ulint_ctr_64_t		merge_buffers_read;
+	/** Number of merge buffers merged */
+	ulint_ctr_64_t		merge_buffers_merged;
 };
 
 extern const char*	srv_main_thread_op_info;
@@ -1088,6 +1095,9 @@ struct export_var_t{
 	ulint innodb_purge_view_trx_id_age;	/*!< rw_max_trx_id
 						- purged view's min trx_id */
 #endif /* UNIV_DEBUG */
+	ib_int64_t innodb_merge_buffers_written;
+	ib_int64_t innodb_merge_buffers_read;
+	ib_int64_t innodb_merge_buffers_merged;
 };
 
 /** Thread slot in the thread table.  */
diff --git a/storage/xtradb/row/row0merge.cc b/storage/xtradb/row/row0merge.cc
index 0a5eb4374f1..3447d15ed1c 100644
--- a/storage/xtradb/row/row0merge.cc
+++ b/storage/xtradb/row/row0merge.cc
@@ -1,6 +1,7 @@
 /*****************************************************************************
 
 Copyright (c) 2005, 2015, Oracle and/or its affiliates. All Rights Reserved.
+Copyright (c) 2014, 2015, MariaDB Corporation.
 
 This program is free software; you can redistribute it and/or modify it under
 the terms of the GNU General Public License as published by the Free Software
@@ -873,6 +874,8 @@ row_merge_read(
 
 	success = os_file_read_no_error_handling(OS_FILE_FROM_FD(fd), buf,
 						 ofs, srv_sort_buf_size);
+	srv_stats.merge_buffers_read.inc();
+
 #ifdef POSIX_FADV_DONTNEED
 	/* Each block is read exactly once.  Free up the file cache. */
 	posix_fadvise(fd, ofs, srv_sort_buf_size, POSIX_FADV_DONTNEED);
@@ -907,6 +910,7 @@ row_merge_write(
 	DBUG_EXECUTE_IF("row_merge_write_failure", return(FALSE););
 
 	ret = os_file_write("(merge)", OS_FILE_FROM_FD(fd), buf, ofs, buf_len);
+	srv_stats.merge_buffers_written.inc();
 
 #ifdef UNIV_DEBUG
 	if (row_merge_print_block_write) {
@@ -1744,7 +1748,7 @@ write_buffers:
 			/* We have enough data tuples to form a block.
 			Sort them and write to disk. */
 
-			if (buf->n_tuples) {
+			if (UNIV_LIKELY(buf->n_tuples)) {
 				if (dict_index_is_unique(buf->index)) {
 					row_merge_dup_t	dup = {
 						buf->index, table, col_map, 0};
@@ -1785,13 +1789,17 @@ write_buffers:
 					dict_index_get_lock(buf->index));
 			}
 
-			row_merge_buf_write(buf, file, block);
+			/* Do not write empty buffers to temporary file */
+			if (buf->n_tuples) {
+
+				row_merge_buf_write(buf, file, block);
 
-			if (!row_merge_write(file->fd, file->offset++,
-					     block)) {
-				err = DB_TEMP_FILE_WRITE_FAILURE;
-				trx->error_key_num = i;
-				break;
+				if (!row_merge_write(file->fd, file->offset++,
+						block)) {
+					err = DB_TEMP_FILE_WRITE_FAILURE;
+					trx->error_key_num = i;
+					break;
+				}
 			}
 
 			UNIV_MEM_INVALID(&block[0], srv_sort_buf_size);
@@ -2076,6 +2084,9 @@ done1:
 	mem_heap_free(heap);
 	b2 = row_merge_write_eof(&block[2 * srv_sort_buf_size],
 				 b2, of->fd, &of->offset);
+
+	srv_stats.merge_buffers_merged.inc();
+
 	return(b2 ? DB_SUCCESS : DB_CORRUPTION);
 }
 
@@ -3755,17 +3766,21 @@ wait_again:
 			DEBUG_FTS_SORT_PRINT("FTS_SORT: Complete Insert\n");
 #endif
 		} else {
-			row_merge_dup_t	dup = {
-				sort_idx, table, col_map, 0};
-
-			error = row_merge_sort(
-				trx, &dup, &merge_files[i],
-				block, &tmpfd);
-
-			if (error == DB_SUCCESS) {
-				error = row_merge_insert_index_tuples(
-					trx->id, sort_idx, old_table,
-					merge_files[i].fd, block);
+			/* Sorting and inserting is required only if
+			there really is records */
+			if (UNIV_LIKELY(merge_files[i].n_rec)) {
+				row_merge_dup_t	dup = {
+					sort_idx, table, col_map, 0};
+
+				error = row_merge_sort(
+					trx, &dup, &merge_files[i],
+					block, &tmpfd);
+
+				if (error == DB_SUCCESS) {
+					error = row_merge_insert_index_tuples(
+						trx->id, sort_idx, old_table,
+						merge_files[i].fd, block);
+				}
 			}
 		}
 
diff --git a/storage/xtradb/srv/srv0mon.cc b/storage/xtradb/srv/srv0mon.cc
index a0dd32c203f..960c889f871 100644
--- a/storage/xtradb/srv/srv0mon.cc
+++ b/storage/xtradb/srv/srv0mon.cc
@@ -2,6 +2,7 @@
 
 Copyright (c) 2010, 2014, Oracle and/or its affiliates. All Rights Reserved.
 Copyright (c) 2012, Facebook Inc.
+Copyright (c) 2014, 2015, MariaDB Corporation.
 
 This program is free software; you can redistribute it and/or modify it under
 the terms of the GNU General Public License as published by the Free Software
@@ -674,6 +675,24 @@ static monitor_info_t	innodb_counter_info[] =
 	 MONITOR_EXISTING | MONITOR_DEFAULT_ON),
 	 MONITOR_DEFAULT_START, MONITOR_OVLD_OS_LOG_PENDING_WRITES},
 
+	{"os_merge_blocks_written", "os",
+	 "Number of merge blocks written (innodb_os_merge_blocks_written)",
+	 static_cast<monitor_type_t>(
+	 MONITOR_EXISTING | MONITOR_DEFAULT_ON),
+	 MONITOR_DEFAULT_START, MONITOR_MERGE_BLOCKS_WRITTEN},
+
+	{"os_merge_blocks_read", "os",
+	 "Number of merge blocks read (innodb_os_merge_blocks_read)",
+	 static_cast<monitor_type_t>(
+	 MONITOR_EXISTING | MONITOR_DEFAULT_ON),
+	 MONITOR_DEFAULT_START, MONITOR_MERGE_BLOCKS_READ},
+
+	{"os_merge_blocks_merged", "os",
+	 "Number of merge blocks merged (innodb_os_merge_blocks_merged)",
+	 static_cast<monitor_type_t>(
+	 MONITOR_EXISTING | MONITOR_DEFAULT_ON),
+	 MONITOR_DEFAULT_START, MONITOR_MERGE_BLOCKS_MERGED},
+
 	/* ========== Counters for Transaction Module ========== */
 	{"module_trx", "transaction", "Transaction Manager",
 	 MONITOR_MODULE,
@@ -1626,6 +1645,21 @@ srv_mon_process_existing_counter(
 		update_min = TRUE;
 		break;
 
+        /* innodb_os_merge_blocks_written */
+	case MONITOR_MERGE_BLOCKS_WRITTEN:
+		value = srv_stats.merge_buffers_written;
+		break;
+
+        /* innodb_os_merge_blocks_read */
+	case MONITOR_MERGE_BLOCKS_READ:
+		value = srv_stats.merge_buffers_read;
+		break;
+
+	/* innodb_os_merge_blocks_merged */
+	case MONITOR_MERGE_BLOCKS_MERGED:
+		value = srv_stats.merge_buffers_merged;
+		break;
+
 	/* innodb_log_waits */
 	case MONITOR_OVLD_LOG_WAITS:
 		value = srv_stats.log_waits;
diff --git a/storage/xtradb/srv/srv0srv.cc b/storage/xtradb/srv/srv0srv.cc
index e679e180d45..da293a4dd3f 100644
--- a/storage/xtradb/srv/srv0srv.cc
+++ b/storage/xtradb/srv/srv0srv.cc
@@ -3,7 +3,7 @@
 Copyright (c) 1995, 2014, Oracle and/or its affiliates. All Rights Reserved.
 Copyright (c) 2008, 2009 Google Inc.
 Copyright (c) 2009, Percona Inc.
-Copyright (c) 2013, 2014, SkySQL Ab. All Rights Reserved.
+Copyright (c) 2013, 2015, MariaDB Corporation.
 
 Portions of this file contain modifications contributed and copyrighted by
 Google, Inc. Those modifications are gratefully acknowledged and are described
@@ -1927,6 +1927,10 @@ srv_export_innodb_status(void)
 	}
 #endif /* UNIV_DEBUG */
 
+	export_vars.innodb_merge_buffers_written = srv_stats.merge_buffers_written;
+	export_vars.innodb_merge_buffers_read = srv_stats.merge_buffers_read;
+	export_vars.innodb_merge_buffers_merged = srv_stats.merge_buffers_merged;
+
 	mutex_exit(&srv_innodb_monitor_mutex);
 }