MDEV-22456 Dropping the adaptive hash index may cause DDL to lock up InnoDB

If the InnoDB buffer pool contains many pages for a table or index
that is being dropped or rebuilt, and if many of such pages are
pointed to by the adaptive hash index, dropping the adaptive hash index
may consume a lot of time.

The time-consuming operation of dropping the adaptive hash index entries
is being executed while the InnoDB data dictionary cache dict_sys is
exclusively locked.

It is not actually necessary to drop all adaptive hash index entries
at the time a table or index is being dropped or rebuilt. We can let
the LRU replacement policy of the buffer pool take care of this gradually.
For this to work, we must detach the dict_table_t and dict_index_t
objects from the main dict_sys cache, and once the last
adaptive hash index entry for the detached table is removed
(when the garbage page is evicted from the buffer pool) we can free
the dict_table_t and dict_index_t object.

Related to this, in MDEV-16283, we made ALTER TABLE...DISCARD TABLESPACE
skip both the buffer pool eviction and the drop of the adaptive hash index.
We shifted the burden to ALTER TABLE...IMPORT TABLESPACE or DROP TABLE.
We can remove the eviction from DROP TABLE. We must retain the eviction
in the ALTER TABLE...IMPORT TABLESPACE code path, so that in case the
discarded table is being re-imported with the same tablespace identifier,
the fresh data from the imported tablespace will replace any stale pages
in the buffer pool.

rpl.rpl_failed_drop_tbl_binlog: Remove the test. DROP TABLE can
no longer be interrupted inside InnoDB.

fseg_free_page(), fseg_free_step(), fseg_free_step_not_header(),
fseg_free_page_low(), fseg_free_extent(): Remove the parameter
that specifies whether the adaptive hash index should be dropped.

btr_search_lazy_free(): Lazily free an index when the last
reference to it is dropped from the adaptive hash index.

buf_pool_clear_hash_index(): Declare static, and move to the
same compilation unit with the bulk of the adaptive hash index
code.

dict_index_t::clone(), dict_index_t::clone_if_needed():
Clone an index that is being rebuilt while adaptive hash index
entries exist. The original index will be inserted into
dict_table_t::freed_indexes and dict_index_t::set_freed()
will be called.

dict_index_t::set_freed(), dict_index_t::freed(): Note that
or check whether the index has been freed. We will use the
impossible page number 1 to denote this condition.

dict_index_t::n_ahi_pages(): Replaces btr_search_info_get_ref_count().

dict_index_t::detach_columns(): Move the assignment n_fields=0
to ha_innobase_inplace_ctx::clear_added_indexes().
We must have access to the columns when freeing the
adaptive hash index. Note: dict_table_t::v_cols[] will remain
valid. If virtual columns are dropped or added, the table
definition will be reloaded in ha_innobase::commit_inplace_alter_table().

buf_page_mtr_lock(): Drop a stale adaptive hash index if needed.

We will also reduce the number of btr_get_search_latch() calls
and enclose some more code inside #ifdef BTR_CUR_HASH_ADAPT
in order to benefit cmake -DWITH_INNODB_AHI=OFF.

9 files changed, 72 insertions, 142 deletions

diff --git a/storage/innobase/include/btr0cur.h b/storage/innobase/include/btr0cur.h
index 19d0fb397be..f70fe687182 100644
--- a/storage/innobase/include/btr0cur.h
+++ b/storage/innobase/include/btr0cur.h
@@ -154,8 +154,7 @@ Note that if mode is PAGE_CUR_LE, which is used in inserts, then
 cursor->up_match and cursor->low_match both will have sensible values.
 If mode is PAGE_CUR_GE, then up_match will a have a sensible value. */
 dberr_t
-btr_cur_search_to_nth_level(
-/*========================*/
+btr_cur_search_to_nth_level_func(
 	dict_index_t*	index,	/*!< in: index */
 	ulint		level,	/*!< in: the tree level of search */
 	const dtuple_t*	tuple,	/*!< in: data tuple; NOTE: n_fields_cmp in
@@ -181,16 +180,24 @@ btr_cur_search_to_nth_level(
 				to protect the record! */
 	btr_cur_t*	cursor, /*!< in/out: tree cursor; the cursor page is
 				s- or x-latched, but see also above! */
+#ifdef BTR_CUR_HASH_ADAPT
 	ulint		has_search_latch,
 				/*!< in: latch mode the caller
 				currently has on search system:
 				RW_S_LATCH, or 0 */
+#endif /* BTR_CUR_HASH_ADAPT */
 	const char*	file,	/*!< in: file name */
 	unsigned	line,	/*!< in: line where called */
 	mtr_t*		mtr,	/*!< in/out: mini-transaction */
-	ib_uint64_t	autoinc = 0);
+	ib_uint64_t	autoinc);
 				/*!< in: PAGE_ROOT_AUTO_INC to be written
 				(0 if none) */
+#ifdef BTR_CUR_HASH_ADAPT
+# define btr_cur_search_to_nth_level btr_cur_search_to_nth_level_func
+#else /* BTR_CUR_HASH_ADAPT */
+# define btr_cur_search_to_nth_level(ix,lv,t,md,l,cur,has,file,line,m,ai) \
+	btr_cur_search_to_nth_level_func(ix,lv,t,md,l,cur,file,line,m,ai)
+#endif /* BTR_CUR_HASH_ADAPT */
 
 /*****************************************************************//**
 Opens a cursor at either end of an index.
diff --git a/storage/innobase/include/btr0pcur.h b/storage/innobase/include/btr0pcur.h
index b79260e5ab6..26c7ebcee6d 100644
--- a/storage/innobase/include/btr0pcur.h
+++ b/storage/innobase/include/btr0pcur.h
@@ -1,7 +1,7 @@
 /*****************************************************************************
 
 Copyright (c) 1996, 2016, Oracle and/or its affiliates. All Rights Reserved.
-Copyright (c) 2017, 2019, MariaDB Corporation.
+Copyright (c) 2017, 2020, 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
@@ -136,15 +136,22 @@ btr_pcur_open_with_no_init_func(
 				page, but assume that the caller uses his
 				btr search latch to protect the record! */
 	btr_pcur_t*	cursor, /*!< in: memory buffer for persistent cursor */
+#ifdef BTR_CUR_HASH_ADAPT
 	ulint		has_search_latch,
 				/*!< in: latch mode the caller
 				currently has on search system:
 				RW_S_LATCH, or 0 */
+#endif /* BTR_CUR_HASH_ADAPT */
 	const char*	file,	/*!< in: file name */
 	unsigned	line,	/*!< in: line where called */
 	mtr_t*		mtr);	/*!< in: mtr */
-#define btr_pcur_open_with_no_init(ix,t,md,l,cur,has,m)			\
+#ifdef BTR_CUR_HASH_ADAPT
+# define btr_pcur_open_with_no_init(ix,t,md,l,cur,has,m)		\
 	btr_pcur_open_with_no_init_func(ix,t,md,l,cur,has,__FILE__,__LINE__,m)
+#else /* BTR_CUR_HASH_ADAPT */
+# define btr_pcur_open_with_no_init(ix,t,md,l,cur,has,m)		\
+	btr_pcur_open_with_no_init_func(ix,t,md,l,cur,__FILE__,__LINE__,m)
+#endif /* BTR_CUR_HASH_ADAPT */
 
 /*****************************************************************//**
 Opens a persistent cursor at either end of an index. */
diff --git a/storage/innobase/include/btr0pcur.ic b/storage/innobase/include/btr0pcur.ic
index 8b0da666250..b5b57050832 100644
--- a/storage/innobase/include/btr0pcur.ic
+++ b/storage/innobase/include/btr0pcur.ic
@@ -1,7 +1,7 @@
 /*****************************************************************************
 
 Copyright (c) 1996, 2015, Oracle and/or its affiliates. All Rights Reserved.
-Copyright (c) 2015, 2019, MariaDB Corporation.
+Copyright (c) 2015, 2020, 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
@@ -496,10 +496,12 @@ btr_pcur_open_with_no_init_func(
 				page, but assume that the caller uses his
 				btr search latch to protect the record! */
 	btr_pcur_t*	cursor, /*!< in: memory buffer for persistent cursor */
+#ifdef BTR_CUR_HASH_ADAPT
 	ulint		has_search_latch,
 				/*!< in: latch mode the caller
 				currently has on search system:
 				RW_S_LATCH, or 0 */
+#endif /* BTR_CUR_HASH_ADAPT */
 	const char*	file,	/*!< in: file name */
 	unsigned	line,	/*!< in: line where called */
 	mtr_t*		mtr)	/*!< in: mtr */
@@ -516,7 +518,7 @@ btr_pcur_open_with_no_init_func(
 
 	err = btr_cur_search_to_nth_level(
 		index, 0, tuple, mode, latch_mode, btr_cursor,
-		has_search_latch, file, line, mtr);
+		has_search_latch, file, line, mtr, 0);
 
 	cursor->pos_state = BTR_PCUR_IS_POSITIONED;
 
diff --git a/storage/innobase/include/btr0sea.h b/storage/innobase/include/btr0sea.h
index 645b3689ff6..b808ba036db 100644
--- a/storage/innobase/include/btr0sea.h
+++ b/storage/innobase/include/btr0sea.h
@@ -1,7 +1,7 @@
 /*****************************************************************************
 
 Copyright (c) 1996, 2016, Oracle and/or its affiliates. All Rights Reserved.
-Copyright (c) 2018, MariaDB Corporation.
+Copyright (c) 2018, 2020, 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
@@ -54,15 +54,6 @@ btr_search_disable(
 void
 btr_search_enable();
 
-/** Returns the value of ref_count. The value is protected by latch.
-@param[in]	info		search info
-@param[in]	index		index identifier
-@return ref_count value. */
-ulint
-btr_search_info_get_ref_count(
-	btr_search_t*	info,
-	dict_index_t*	index);
-
 /*********************************************************************//**
 Updates the search info. */
 UNIV_INLINE
@@ -156,18 +147,6 @@ btr_search_update_hash_on_delete(btr_cur_t* cursor);
 bool
 btr_search_validate();
 
-/** X-Lock the search latch (corresponding to given index)
-@param[in]	index	index handler */
-UNIV_INLINE
-void
-btr_search_x_lock(const dict_index_t* index);
-
-/** X-Unlock the search latch (corresponding to given index)
-@param[in]	index	index handler */
-UNIV_INLINE
-void
-btr_search_x_unlock(const dict_index_t* index);
-
 /** Lock all search latches in exclusive mode. */
 UNIV_INLINE
 void
@@ -178,18 +157,6 @@ UNIV_INLINE
 void
 btr_search_x_unlock_all();
 
-/** S-Lock the search latch (corresponding to given index)
-@param[in]	index	index handler */
-UNIV_INLINE
-void
-btr_search_s_lock(const dict_index_t* index);
-
-/** S-Unlock the search latch (corresponding to given index)
-@param[in]	index	index handler */
-UNIV_INLINE
-void
-btr_search_s_unlock(const dict_index_t* index);
-
 /** Lock all search latches in shared mode. */
 UNIV_INLINE
 void
@@ -236,12 +203,8 @@ btr_get_search_table(const dict_index_t* index);
 #else /* BTR_CUR_HASH_ADAPT */
 # define btr_search_sys_create(size)
 # define btr_search_drop_page_hash_index(block)
-# define btr_search_s_lock(index)
-# define btr_search_s_unlock(index)
 # define btr_search_s_lock_all(index)
 # define btr_search_s_unlock_all(index)
-# define btr_search_x_lock(index)
-# define btr_search_x_unlock(index)
 # define btr_search_info_update(index, cursor)
 # define btr_search_move_or_delete_hash_entries(new_block, block, index)
 # define btr_search_update_hash_on_insert(cursor)
@@ -327,6 +290,18 @@ struct btr_search_t{
 };
 
 #ifdef BTR_CUR_HASH_ADAPT
+/** @return number of leaf pages pointed to by the adaptive hash index */
+inline ulint dict_index_t::n_ahi_pages() const
+{
+  if (!btr_search_enabled)
+    return 0;
+  rw_lock_t *latch = btr_get_search_latch(this);
+  rw_lock_s_lock(latch);
+  ulint ref_count= search_info->ref_count;
+  rw_lock_s_unlock(latch);
+  return ref_count;
+}
+
 /** The hash index system */
 struct btr_search_sys_t{
 	hash_table_t**	hash_tables;	/*!< the adaptive hash tables,
diff --git a/storage/innobase/include/btr0sea.ic b/storage/innobase/include/btr0sea.ic
index 4972de16064..90877d23192 100644
--- a/storage/innobase/include/btr0sea.ic
+++ b/storage/innobase/include/btr0sea.ic
@@ -1,6 +1,7 @@
 /*****************************************************************************
 
 Copyright (c) 1996, 2015, Oracle and/or its affiliates. All Rights Reserved.
+Copyright (c) 2020, 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
@@ -62,8 +63,8 @@ btr_search_info_update(
 	dict_index_t*	index,	/*!< in: index of the cursor */
 	btr_cur_t*	cursor)	/*!< in: cursor which was just positioned */
 {
-	ut_ad(!rw_lock_own(btr_get_search_latch(index), RW_LOCK_S));
-	ut_ad(!rw_lock_own(btr_get_search_latch(index), RW_LOCK_X));
+	ut_ad(!rw_lock_own_flagged(btr_get_search_latch(index),
+				   RW_LOCK_FLAG_X | RW_LOCK_FLAG_S));
 
 	if (dict_index_is_spatial(index) || !btr_search_enabled) {
 		return;
@@ -87,24 +88,6 @@ btr_search_info_update(
 	btr_search_info_update_slow(info, cursor);
 }
 
-/** X-Lock the search latch (corresponding to given index)
-@param[in]	index	index handler */
-UNIV_INLINE
-void
-btr_search_x_lock(const dict_index_t* index)
-{
-	rw_lock_x_lock(btr_get_search_latch(index));
-}
-
-/** X-Unlock the search latch (corresponding to given index)
-@param[in]	index	index handler */
-UNIV_INLINE
-void
-btr_search_x_unlock(const dict_index_t* index)
-{
-	rw_lock_x_unlock(btr_get_search_latch(index));
-}
-
 /** Lock all search latches in exclusive mode. */
 UNIV_INLINE
 void
@@ -125,24 +108,6 @@ btr_search_x_unlock_all()
 	}
 }
 
-/** S-Lock the search latch (corresponding to given index)
-@param[in]	index	index handler */
-UNIV_INLINE
-void
-btr_search_s_lock(const dict_index_t* index)
-{
-	rw_lock_s_lock(btr_get_search_latch(index));
-}
-
-/** S-Unlock the search latch (corresponding to given index)
-@param[in]	index	index handler */
-UNIV_INLINE
-void
-btr_search_s_unlock(const dict_index_t* index)
-{
-	rw_lock_s_unlock(btr_get_search_latch(index));
-}
-
 /** Lock all search latches in shared mode. */
 UNIV_INLINE
 void
diff --git a/storage/innobase/include/buf0buf.h b/storage/innobase/include/buf0buf.h
index 0536e2f8ac6..a9b8ef94bab 100644
--- a/storage/innobase/include/buf0buf.h
+++ b/storage/innobase/include/buf0buf.h
@@ -71,11 +71,13 @@ struct fil_addr_t;
 /* @} */
 /** @name Modes for buf_page_get_known_nowait */
 /* @{ */
-#define BUF_MAKE_YOUNG	51		/*!< Move the block to the
+#ifdef BTR_CUR_HASH_ADAPT
+# define BUF_MAKE_YOUNG	51		/*!< Move the block to the
 					start of the LRU list if there
 					is a danger that the block
 					would drift out of the buffer
 					pool*/
+#endif /* BTR_CUR_HASH_ADAPT */
 #define BUF_KEEP_OLD	52		/*!< Preserve the current LRU
 					position of the block. */
 /* @} */
@@ -282,12 +284,6 @@ extern "C"
 os_thread_ret_t
 DECLARE_THREAD(buf_resize_thread)(void*);
 
-#ifdef BTR_CUR_HASH_ADAPT
-/** Clear the adaptive hash index on all pages in the buffer pool. */
-void
-buf_pool_clear_hash_index();
-#endif /* BTR_CUR_HASH_ADAPT */
-
 /*********************************************************************//**
 Gets the current size of buffer buf_pool in bytes.
 @return size in bytes */
diff --git a/storage/innobase/include/buf0lru.h b/storage/innobase/include/buf0lru.h
index 1efbb1f03ef..a72d98395af 100644
--- a/storage/innobase/include/buf0lru.h
+++ b/storage/innobase/include/buf0lru.h
@@ -49,17 +49,6 @@ These are low-level functions
 /** Minimum LRU list length for which the LRU_old pointer is defined */
 #define BUF_LRU_OLD_MIN_LEN	512	/* 8 megabytes of 16k pages */
 
-#ifdef BTR_CUR_HASH_ADAPT
-struct dict_table_t;
-/** Try to drop the adaptive hash index for a tablespace.
-@param[in,out]	table	table
-@return	whether anything was dropped */
-bool buf_LRU_drop_page_hash_for_tablespace(dict_table_t* table)
-	MY_ATTRIBUTE((warn_unused_result,nonnull));
-#else
-# define buf_LRU_drop_page_hash_for_tablespace(table)
-#endif /* BTR_CUR_HASH_ADAPT */
-
 /** Empty the flush list for all pages belonging to a tablespace.
 @param[in]	id		tablespace identifier
 @param[in,out]	observer	flush observer,
diff --git a/storage/innobase/include/dict0mem.h b/storage/innobase/include/dict0mem.h
index b259d2fb2ad..80d18dcbb2a 100644
--- a/storage/innobase/include/dict0mem.h
+++ b/storage/innobase/include/dict0mem.h
@@ -831,6 +831,9 @@ struct dict_index_t{
 	dict_table_t*	table;	/*!< back pointer to table */
 	unsigned	space:32;
 				/*!< space where the index tree is placed */
+	/** root page number, or FIL_NULL if the index has been detached
+	from storage (DISCARD TABLESPACE or similar),
+	or 1 if the index is in table->freed_indexes */
 	unsigned	page:32;/*!< index tree root page number */
 	unsigned	merge_threshold:6;
 				/*!< In the pessimistic delete, if the page
@@ -1021,11 +1024,23 @@ struct dict_index_t{
 			for (unsigned i = 0; i < n_fields; i++) {
 				fields[i].col->detach(*this);
 			}
-
-			n_fields = 0;
 		}
 	}
 
+#ifdef BTR_CUR_HASH_ADAPT
+  /** @return a clone of this */
+  dict_index_t* clone() const;
+  /** Clone this index for lazy dropping of the adaptive hash index.
+  @return this or a clone */
+  dict_index_t* clone_if_needed();
+  /** @return number of leaf pages pointed to by the adaptive hash index */
+  inline ulint n_ahi_pages() const;
+  /** @return whether mark_freed() had been invoked */
+  bool freed() const { return UNIV_UNLIKELY(page == 1); }
+  /** Note that the index is waiting for btr_search_lazy_free() */
+  void set_freed() { ut_ad(!freed()); page= 1; }
+#endif /* BTR_CUR_HASH_ADAPT */
+
 	/** This ad-hoc class is used by record_size_info only.	*/
 	class record_size_info_t {
 	public:
@@ -1605,6 +1620,11 @@ struct dict_table_t {
 
 	/** List of indexes of the table. */
 	UT_LIST_BASE_NODE_T(dict_index_t)	indexes;
+#ifdef BTR_CUR_HASH_ADAPT
+	/** List of detached indexes that are waiting to be freed along with
+	the last adaptive hash index entry */
+	UT_LIST_BASE_NODE_T(dict_index_t)	freed_indexes;
+#endif /* BTR_CUR_HASH_ADAPT */
 
 	/** List of foreign key constraints in the table. These refer to
 	columns in other tables. */
diff --git a/storage/innobase/include/fsp0fsp.h b/storage/innobase/include/fsp0fsp.h
index a4e3b84b55e..8debdb00013 100644
--- a/storage/innobase/include/fsp0fsp.h
+++ b/storage/innobase/include/fsp0fsp.h
@@ -1,7 +1,7 @@
 /*****************************************************************************
 
 Copyright (c) 1995, 2016, Oracle and/or its affiliates. All Rights Reserved.
-Copyright (c) 2013, 2019, MariaDB Corporation.
+Copyright (c) 2013, 2020, 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
@@ -568,22 +568,11 @@ fsp_get_available_space_in_free_extents(
 /**********************************************************************//**
 Frees a single page of a segment. */
 void
-fseg_free_page_func(
+fseg_free_page(
 	fseg_header_t*	seg_header, /*!< in: segment header */
 	ulint		space_id, /*!< in: space id */
 	ulint		page,	/*!< in: page offset */
-#ifdef BTR_CUR_HASH_ADAPT
-	bool		ahi,	/*!< in: whether we may need to drop
-				the adaptive hash index */
-#endif /* BTR_CUR_HASH_ADAPT */
 	mtr_t*		mtr);	/*!< in/out: mini-transaction */
-#ifdef BTR_CUR_HASH_ADAPT
-# define fseg_free_page(header, space_id, page, ahi, mtr)	\
-	fseg_free_page_func(header, space_id, page, ahi, mtr)
-#else /* BTR_CUR_HASH_ADAPT */
-# define fseg_free_page(header, space_id, page, ahi, mtr)	\
-	fseg_free_page_func(header, space_id, page, mtr)
-#endif /* BTR_CUR_HASH_ADAPT */
 /** Determine whether a page is free.
 @param[in,out]	space	tablespace
 @param[in]	page	page number
@@ -596,45 +585,25 @@ Frees part of a segment. This function can be used to free a segment
 by repeatedly calling this function in different mini-transactions.
 Doing the freeing in a single mini-transaction might result in
 too big a mini-transaction.
-@return TRUE if freeing completed */
-ibool
-fseg_free_step_func(
+@return whether the freeing was completed */
+bool
+fseg_free_step(
 	fseg_header_t*	header,	/*!< in, own: segment header; NOTE: if the header
 				resides on the first page of the frag list
 				of the segment, this pointer becomes obsolete
 				after the last freeing step */
-#ifdef BTR_CUR_HASH_ADAPT
-	bool		ahi,	/*!< in: whether we may need to drop
-				the adaptive hash index */
-#endif /* BTR_CUR_HASH_ADAPT */
 	mtr_t*		mtr)	/*!< in/out: mini-transaction */
 	MY_ATTRIBUTE((warn_unused_result));
-#ifdef BTR_CUR_HASH_ADAPT
-# define fseg_free_step(header, ahi, mtr) fseg_free_step_func(header, ahi, mtr)
-#else /* BTR_CUR_HASH_ADAPT */
-# define fseg_free_step(header, ahi, mtr) fseg_free_step_func(header, mtr)
-#endif /* BTR_CUR_HASH_ADAPT */
 /**********************************************************************//**
 Frees part of a segment. Differs from fseg_free_step because this function
 leaves the header page unfreed.
-@return TRUE if freeing completed, except the header page */
-ibool
-fseg_free_step_not_header_func(
+@return whether the freeing was completed, except for the header page */
+bool
+fseg_free_step_not_header(
 	fseg_header_t*	header,	/*!< in: segment header which must reside on
 				the first fragment page of the segment */
-#ifdef BTR_CUR_HASH_ADAPT
-	bool		ahi,	/*!< in: whether we may need to drop
-				the adaptive hash index */
-#endif /* BTR_CUR_HASH_ADAPT */
 	mtr_t*		mtr)	/*!< in/out: mini-transaction */
 	MY_ATTRIBUTE((warn_unused_result));
-#ifdef BTR_CUR_HASH_ADAPT
-# define fseg_free_step_not_header(header, ahi, mtr)	\
-	fseg_free_step_not_header_func(header, ahi, mtr)
-#else /* BTR_CUR_HASH_ADAPT */
-# define fseg_free_step_not_header(header, ahi, mtr)	\
-	fseg_free_step_not_header_func(header, mtr)
-#endif /* BTR_CUR_HASH_ADAPT */
 
 /** Reset the page type.
 Data files created before MySQL 5.1.48 may contain garbage in FIL_PAGE_TYPE.