Merge 10.6bb-10.6-MDEV-21452

13 files changed, 106 insertions, 88 deletions

diff --git a/extra/mariabackup/xtrabackup.cc b/extra/mariabackup/xtrabackup.cc
index 3eb80a29aeb..1b1091280f1 100644
--- a/extra/mariabackup/xtrabackup.cc
+++ b/extra/mariabackup/xtrabackup.cc
@@ -2113,9 +2113,9 @@ static bool innodb_init_param()
 	srv_buf_pool_size = (ulint) xtrabackup_use_memory;
 	srv_buf_pool_chunk_unit = (ulong)srv_buf_pool_size;
 
-	srv_n_file_io_threads = (ulint) innobase_file_io_threads;
-	srv_n_read_io_threads = innobase_read_io_threads;
-	srv_n_write_io_threads = innobase_write_io_threads;
+	srv_n_file_io_threads = (uint) innobase_file_io_threads;
+	srv_n_read_io_threads = (uint) innobase_read_io_threads;
+	srv_n_write_io_threads = (uint) innobase_write_io_threads;
 
 	srv_max_n_open_files = ULINT_UNDEFINED - 5;
 
@@ -4254,8 +4254,10 @@ fail:
 	xb_fil_io_init();
 	srv_n_file_io_threads = srv_n_read_io_threads;
 
-	os_aio_init(srv_n_read_io_threads, srv_n_write_io_threads,
-		    SRV_MAX_N_PENDING_SYNC_IOS);
+	if (os_aio_init()) {
+		msg("Error: cannot initialize AIO subsystem");
+		goto fail;
+	}
 
 	log_sys.create();
 	log_sys.log.create();
diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result
index 4f32db780fd..5f51be715fe 100644
--- a/mysql-test/main/group_min_max.result
+++ b/mysql-test/main/group_min_max.result
@@ -2664,7 +2664,7 @@ a	b
 3	13
 explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ref	a,index	a	5	const	15	20.00	Using index; Using temporary
+1	SIMPLE	t1	range	a,index	a	5	NULL	3	100.00	Using where; Using index for group-by; Using temporary
 Warnings:
 Note	1003	select sql_buffer_result `test`.`t1`.`a` AS `a`,max(`test`.`t1`.`b`) + 1 AS `max(b)+1` from `test`.`t1` where `test`.`t1`.`a` = 0 group by `test`.`t1`.`a`
 drop table t1;
@@ -4027,3 +4027,24 @@ drop table t1;
 #
 # End of 10.1 tests
 #
+#
+# MDEV-24353: Adding GROUP BY slows down a query
+#
+CREATE TABLE t1 (p int NOT NULL, a int NOT NULL, PRIMARY KEY (p,a));
+insert into t1 select 2,seq from seq_0_to_1000;
+EXPLAIN select MIN(a) from t1 where p = 2 group by p;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	10	Using where; Using index for group-by
+SELECT MIN(a) from t1 where p = 2 group by p;
+MIN(a)
+0
+EXPLAIN select MIN(a) from t1 group by p;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	NULL	PRIMARY	4	NULL	10	Using index for group-by
+SELECT MIN(a) from t1 where p = 2;
+MIN(a)
+0
+drop table t1;
+#
+# End of 10.6 tests
+#
diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test
index 526552dda92..1db479b1c74 100644
--- a/mysql-test/main/group_min_max.test
+++ b/mysql-test/main/group_min_max.test
@@ -4,6 +4,7 @@
 #
 
 --source include/default_optimizer_switch.inc
+--source include/have_sequence.inc
 
 #
 # TODO:
@@ -1689,3 +1690,20 @@ drop table t1;
 --echo #
 --echo # End of 10.1 tests
 --echo #
+
+--echo #
+--echo # MDEV-24353: Adding GROUP BY slows down a query
+--echo #
+
+CREATE TABLE t1 (p int NOT NULL, a int NOT NULL, PRIMARY KEY (p,a));
+insert into t1 select 2,seq from seq_0_to_1000;
+
+EXPLAIN select MIN(a) from t1 where p = 2 group by p;
+SELECT MIN(a) from t1 where p = 2 group by p;
+EXPLAIN select MIN(a) from t1 group by p;
+SELECT MIN(a) from t1 where p = 2;
+drop table t1;
+
+--echo #
+--echo # End of 10.6 tests
+--echo #
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff b/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff
index ea9f47d8f3a..ee9a1eee717 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff
@@ -371,15 +371,6 @@
  VARIABLE_COMMENT	Number of pages that must be accessed sequentially for InnoDB to trigger a readahead.
  NUMERIC_MIN_VALUE	0
  NUMERIC_MAX_VALUE	64
-@@ -1609,7 +1609,7 @@
- SESSION_VALUE	NULL
- DEFAULT_VALUE	4
- VARIABLE_SCOPE	GLOBAL
--VARIABLE_TYPE	BIGINT UNSIGNED
-+VARIABLE_TYPE	INT UNSIGNED
- VARIABLE_COMMENT	Number of background read I/O threads in InnoDB.
- NUMERIC_MIN_VALUE	1
- NUMERIC_MAX_VALUE	64
 @@ -1705,7 +1705,7 @@
  SESSION_VALUE	NULL
  DEFAULT_VALUE	1048576
@@ -429,12 +420,3 @@
  VARIABLE_COMMENT	Number of undo tablespaces to use.
  NUMERIC_MIN_VALUE	0
  NUMERIC_MAX_VALUE	127
-@@ -2053,7 +2053,7 @@
- SESSION_VALUE	NULL
- DEFAULT_VALUE	4
- VARIABLE_SCOPE	GLOBAL
--VARIABLE_TYPE	BIGINT UNSIGNED
-+VARIABLE_TYPE	INT UNSIGNED
- VARIABLE_COMMENT	Number of background write I/O threads in InnoDB.
- NUMERIC_MIN_VALUE	1
- NUMERIC_MAX_VALUE	64
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index 36604f089a8..1c6e296422a 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -1429,7 +1429,7 @@ VARIABLE_NAME	INNODB_READ_IO_THREADS
 SESSION_VALUE	NULL
 DEFAULT_VALUE	4
 VARIABLE_SCOPE	GLOBAL
-VARIABLE_TYPE	BIGINT UNSIGNED
+VARIABLE_TYPE	INT UNSIGNED
 VARIABLE_COMMENT	Number of background read I/O threads in InnoDB.
 NUMERIC_MIN_VALUE	1
 NUMERIC_MAX_VALUE	64
@@ -1789,9 +1789,9 @@ VARIABLE_NAME	INNODB_WRITE_IO_THREADS
 SESSION_VALUE	NULL
 DEFAULT_VALUE	4
 VARIABLE_SCOPE	GLOBAL
-VARIABLE_TYPE	BIGINT UNSIGNED
+VARIABLE_TYPE	INT UNSIGNED
 VARIABLE_COMMENT	Number of background write I/O threads in InnoDB.
-NUMERIC_MIN_VALUE	1
+NUMERIC_MIN_VALUE	2
 NUMERIC_MAX_VALUE	64
 NUMERIC_BLOCK_SIZE	0
 ENUM_VALUE_LIST	NULL
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index bf3d4fd9bb1..caaf6d2f3e3 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7926,7 +7926,8 @@ best_access_path(JOIN      *join,
         access is to use the same index IDX, with the same or more key parts.
         (note: it is not clear how this rule is/should be extended to 
         index_merge quick selects). Also if we have a hash join we prefer that
-        over a table scan
+        over a table scan. This heuristic doesn't apply if the quick select
+        uses the group-by min-max optimization.
     (3) See above note about InnoDB.
     (4) NOT ("FORCE INDEX(...)" is used for table and there is 'ref' access
              path, but there is no quick select)
@@ -7944,7 +7945,9 @@ best_access_path(JOIN      *join,
   Json_writer_object trace_access_scan(thd);
   if ((records >= s->found_records || best > s->read_time) &&            // (1)
       !(best_key && best_key->key == MAX_KEY) &&                         // (2)
-      !(s->quick && best_key && s->quick->index == best_key->key &&      // (2)
+      !(s->quick &&
+        s->quick->get_type() != QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX && // (2)
+        best_key && s->quick->index == best_key->key &&      // (2)
         best_max_key_part >= s->table->opt_range[best_key->key].key_parts) &&// (2)
       !((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) &&   // (3)
         ! s->table->covering_keys.is_clear_all() && best_key && !s->quick) &&// (3)
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 4961b8af68f..c4083f8aeb9 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -3530,9 +3530,6 @@ static int innodb_init_params()
 	}
 
 #ifdef LINUX_NATIVE_AIO
-	if (srv_use_native_aio) {
-		ib::info() << "Using Linux native AIO";
-	}
 #elif !defined _WIN32
 	/* Currently native AIO is supported only on windows and linux
 	and that also when the support is compiled in. In all other
@@ -18675,15 +18672,15 @@ static MYSQL_SYSVAR_BOOL(optimize_fulltext_only, innodb_optimize_fulltext_only,
   "Only optimize the Fulltext index of the table",
   NULL, NULL, FALSE);
 
-static MYSQL_SYSVAR_ULONG(read_io_threads, srv_n_read_io_threads,
+static MYSQL_SYSVAR_UINT(read_io_threads, srv_n_read_io_threads,
   PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
   "Number of background read I/O threads in InnoDB.",
   NULL, NULL, 4, 1, 64, 0);
 
-static MYSQL_SYSVAR_ULONG(write_io_threads, srv_n_write_io_threads,
+static MYSQL_SYSVAR_UINT(write_io_threads, srv_n_write_io_threads,
   PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
   "Number of background write I/O threads in InnoDB.",
-  NULL, NULL, 4, 1, 64, 0);
+  NULL, NULL, 4, 2, 64, 0);
 
 static MYSQL_SYSVAR_ULONG(force_recovery, srv_force_recovery,
   PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
diff --git a/storage/innobase/include/os0file.h b/storage/innobase/include/os0file.h
index 149da54b63c..ab1d5bb1dea 100644
--- a/storage/innobase/include/os0file.h
+++ b/storage/innobase/include/os0file.h
@@ -1135,21 +1135,9 @@ void
 unit_test_os_file_get_parent_dir();
 #endif /* UNIV_ENABLE_UNIT_TEST_GET_PARENT_DIR */
 
-/** Initializes the asynchronous io system. Creates one array each for ibuf
-and log i/o. Also creates one array each for read and write where each
-array is divided logically into n_read_segs and n_write_segs
-respectively. The caller must create an i/o handler thread for each
-segment in these arrays. This function also creates the sync array.
-No i/o handler thread needs to be created for that
-@param[in]	n_read_segs	number of reader threads
-@param[in]	n_write_segs	number of writer threads
-@param[in]	n_slots_sync	number of slots in the sync aio array */
-
-bool
-os_aio_init(
-	ulint		n_read_segs,
-	ulint		n_write_segs,
-	ulint		n_slots_sync);
+/**
+Initializes the asynchronous io system. */
+int os_aio_init();
 
 /**
 Frees the asynchronous io system. */
diff --git a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h
index 913819ba69b..8b87cb9014c 100644
--- a/storage/innobase/include/srv0srv.h
+++ b/storage/innobase/include/srv0srv.h
@@ -361,11 +361,11 @@ extern ulong srv_buf_pool_load_pages_abort;
 /** Lock table size in bytes */
 extern ulint	srv_lock_table_size;
 
-extern ulint	srv_n_file_io_threads;
+extern uint	srv_n_file_io_threads;
 extern my_bool	srv_random_read_ahead;
 extern ulong	srv_read_ahead_threshold;
-extern ulong	srv_n_read_io_threads;
-extern ulong	srv_n_write_io_threads;
+extern uint	srv_n_read_io_threads;
+extern uint	srv_n_write_io_threads;
 
 /* Defragmentation, Origianlly facebook default value is 100, but it's too high */
 #define SRV_DEFRAGMENT_FREQUENCY_DEFAULT 40
diff --git a/storage/innobase/os/os0file.cc b/storage/innobase/os/os0file.cc
index 9e09ff9e482..a42006bf97e 100644
--- a/storage/innobase/os/os0file.cc
+++ b/storage/innobase/os/os0file.cc
@@ -3885,7 +3885,6 @@ versions where native aio is supported it won't work on tmpfs. In such
 cases we can't use native aio.
 
 @return: true if supported, false otherwise. */
-#include <libaio.h>
 static bool is_linux_native_aio_supported()
 {
 	File		fd;
@@ -4007,34 +4006,41 @@ static bool is_linux_native_aio_supported()
 }
 #endif
 
-
-
-bool os_aio_init(ulint n_reader_threads, ulint n_writer_threads, ulint)
+int os_aio_init()
 {
-  int max_write_events= int(n_writer_threads * OS_AIO_N_PENDING_IOS_PER_THREAD);
-  int max_read_events= int(n_reader_threads * OS_AIO_N_PENDING_IOS_PER_THREAD);
-  int max_events = max_read_events + max_write_events;
-	int ret;
-
+  int max_write_events= int(srv_n_write_io_threads *
+                            OS_AIO_N_PENDING_IOS_PER_THREAD);
+  int max_read_events= int(srv_n_read_io_threads *
+                           OS_AIO_N_PENDING_IOS_PER_THREAD);
+  int max_events= max_read_events + max_write_events;
+  int ret;
 #if LINUX_NATIVE_AIO
-	if (srv_use_native_aio && !is_linux_native_aio_supported())
-		srv_use_native_aio = false;
+  if (srv_use_native_aio && !is_linux_native_aio_supported())
+    goto disable;
 #endif
-	ret = srv_thread_pool->configure_aio(srv_use_native_aio, max_events);
-	if(ret) {
-		ut_a(srv_use_native_aio);
-		srv_use_native_aio = false;
+
+  ret= srv_thread_pool->configure_aio(srv_use_native_aio, max_events);
+
 #ifdef LINUX_NATIVE_AIO
-		ib::info() << "Linux native AIO disabled";
+  if (ret)
+  {
+    ut_ad(srv_use_native_aio);
+disable:
+    ib::warn() << "Linux Native AIO disabled.";
+    srv_use_native_aio= false;
+    ret= srv_thread_pool->configure_aio(false, max_events);
+  }
 #endif
-		ret = srv_thread_pool->configure_aio(srv_use_native_aio, max_events);
-		DBUG_ASSERT(!ret);
-	}
-	read_slots = new io_slots(max_read_events, (uint)n_reader_threads);
-	write_slots = new io_slots(max_write_events, (uint)n_writer_threads);
-	return true;
+
+  if (!ret)
+  {
+    read_slots= new io_slots(max_read_events, srv_n_read_io_threads);
+    write_slots= new io_slots(max_write_events, srv_n_write_io_threads);
+  }
+  return ret;
 }
 
+
 void os_aio_free()
 {
   srv_thread_pool->disable_aio();
@@ -4152,8 +4158,8 @@ os_aio_print(FILE*	file)
 	time_t		current_time;
 	double		time_elapsed;
 
-	for (ulint i = 0; i < srv_n_file_io_threads; ++i) {
-		fprintf(file, "I/O thread " ULINTPF " state: %s (%s)",
+	for (uint i = 0; i < srv_n_file_io_threads; ++i) {
+		fprintf(file, "I/O thread %u state: %s (%s)",
 			i,
 			srv_io_thread_op_info[i],
 			srv_io_thread_function[i]);
diff --git a/storage/innobase/srv/srv0srv.cc b/storage/innobase/srv/srv0srv.cc
index ba881bd3dc9..4fb2ffcc7bf 100644
--- a/storage/innobase/srv/srv0srv.cc
+++ b/storage/innobase/srv/srv0srv.cc
@@ -223,9 +223,9 @@ ulint	srv_lock_table_size	= ULINT_MAX;
 ulong	srv_idle_flush_pct;
 
 /** innodb_read_io_threads */
-ulong	srv_n_read_io_threads;
+uint	srv_n_read_io_threads;
 /** innodb_write_io_threads */
-ulong	srv_n_write_io_threads;
+uint	srv_n_write_io_threads;
 
 /** innodb_random_read_ahead */
 my_bool	srv_random_read_ahead;
diff --git a/storage/innobase/srv/srv0start.cc b/storage/innobase/srv/srv0start.cc
index bd32003590e..16e5b827397 100644
--- a/storage/innobase/srv/srv0start.cc
+++ b/storage/innobase/srv/srv0start.cc
@@ -110,7 +110,7 @@ lsn_t	srv_shutdown_lsn;
 ibool	srv_start_raw_disk_in_use;
 
 /** Number of IO threads to use */
-ulint	srv_n_file_io_threads;
+uint	srv_n_file_io_threads;
 
 /** UNDO tablespaces starts with space id. */
 ulint	srv_undo_space_id_start;
@@ -1171,9 +1171,7 @@ dberr_t srv_start(bool create_new_db)
 		return(srv_init_abort(err));
 	}
 
-	srv_n_file_io_threads = srv_n_read_io_threads;
-
-	srv_n_file_io_threads += srv_n_write_io_threads;
+	srv_n_file_io_threads = srv_n_read_io_threads + srv_n_write_io_threads;
 
 	if (!srv_read_only_mode) {
 		/* Add the log and ibuf IO threads. */
@@ -1185,15 +1183,18 @@ dberr_t srv_start(bool create_new_db)
 
 	ut_a(srv_n_file_io_threads <= SRV_MAX_N_IO_THREADS);
 
-	if (!os_aio_init(srv_n_read_io_threads,
-			 srv_n_write_io_threads,
-			 SRV_MAX_N_PENDING_SYNC_IOS)) {
-
+	if (os_aio_init()) {
 		ib::error() << "Cannot initialize AIO sub-system";
 
 		return(srv_init_abort(DB_ERROR));
 	}
 
+#ifdef LINUX_NATIVE_AIO
+	if (srv_use_native_aio) {
+		ib::info() << "Using Linux native AIO";
+	}
+#endif
+
 	fil_system.create(srv_file_per_table ? 50000 : 5000);
 
 	ib::info() << "Initializing buffer pool, total size = "
diff --git a/tpool/tpool.h b/tpool/tpool.h
index 0d83af5bd74..3a5658c0f36 100644
--- a/tpool/tpool.h
+++ b/tpool/tpool.h
@@ -220,7 +220,7 @@ public:
   {
     if (use_native_aio)
       m_aio.reset(create_native_aio(max_io));
-    if (!m_aio)
+    else
       m_aio.reset(create_simulated_aio(this));
     return !m_aio ? -1 : 0;
   }