backport Bug#31060470 to 5.7

Yasufumi Kinoshita · Yasufumi Kinoshita · commit 294533723f22 · 2021-04-09T17:12:04.000+09:00
Bug#31060470 : InnoDB temp table could hurt InnoDB perf badly (Bug#98974)

Minimizes flush_list scanning to find the oldest page except for system temporary.
Because the huge number of system temporary pages cause too long scan.
- new FlushHp, buf_pool-&gt;oldest_hp is introduced
- minimizes scan also at page_cleaner_flush_pages_recommendation()

* Fixed also the one of the susupected causers of Bug#31073853
  buf_flush_relocate_on_flush_list() should move the hp not to skip the page,
  when conflict with hp.

RB: 26176
Reviewed-by: Debarun Banerjee &lt;debarun.banerjee@oracle.com&gt;
diff --git a/storage/innobase/buf/buf0buf.cc b/storage/innobase/buf/buf0buf.cc
@@ -424,8 +424,14 @@ buf_pool_get_oldest_modification(void)
 		/* We don't let log-checkpoint halt because pages from system
 		temporary are not yet flushed to the disk. Anyway, object
 		residing in system temporary doesn't generate REDO logging. */
-		for (bpage = UT_LIST_GET_LAST(buf_pool->flush_list);
-		     bpage != NULL
+		bpage = buf_pool->oldest_hp.get();
+		if (bpage != NULL) {
+			ut_ad(bpage->in_flush_list);
+		} else {
+			bpage = UT_LIST_GET_LAST(buf_pool->flush_list);
+		}
+
+		for (; bpage != NULL
 			&& fsp_is_system_temporary(bpage->id.space());
 		     bpage = UT_LIST_GET_PREV(list, bpage)) {
 			/* Do nothing. */
@@ -434,6 +440,12 @@ buf_pool_get_oldest_modification(void)
 		if (bpage != NULL) {
 			ut_ad(bpage->in_flush_list);
 			lsn = bpage->oldest_modification;
+			buf_pool->oldest_hp.set(bpage);
+		} else {
+			/* The last scanned page as entry point,
+			or nullptr. */
+			buf_pool->oldest_hp.set(
+				UT_LIST_GET_FIRST(buf_pool->flush_list));
 		}
 
 		buf_flush_list_mutex_exit(buf_pool);
@@ -1844,6 +1856,10 @@ buf_pool_init_instance(
 	new(&buf_pool->flush_hp)
 		FlushHp(buf_pool, &buf_pool->flush_list_mutex);
 
+	/* Initialize the hazard pointer for the oldest page scan */
+	new (&buf_pool->oldest_hp)
+		FlushHp(buf_pool, &buf_pool->flush_list_mutex);
+
 	/* Initialize the hazard pointer for LRU batches */
 	new(&buf_pool->lru_hp) LRUHp(buf_pool, &buf_pool->mutex);
 
@@ -3196,6 +3212,21 @@ HazardPointer::is_hp(const buf_page_t* bpage)
 	return(bpage == m_hp);
 }
 
+/** Adjust the value of hp for moving. This happens when some other thread
+working on the same list attempts to relocate the hp of the page.
+@param bpage	buffer block to be compared
+@param dpage	buffer block to be moved to */
+void
+HazardPointer::move(const buf_page_t *bpage, buf_page_t *dpage)
+{
+	ut_ad(bpage != NULL);
+	ut_ad(dpage != NULL);
+
+	if (is_hp(bpage)) {
+		m_hp = dpage;
+	}
+}
+
 /** Adjust the value of hp. This happens when some other thread working
 on the same list attempts to remove the hp from the list.
 @param bpage	buffer block to be compared */
diff --git a/storage/innobase/buf/buf0flu.cc b/storage/innobase/buf/buf0flu.cc
@@ -561,6 +561,11 @@ buf_flush_insert_sorted_into_flush_list(
 		UT_LIST_INSERT_AFTER(buf_pool->flush_list, prev_b, &block->page);
 	}
 
+	if (buf_pool->oldest_hp.get() != NULL) {
+		/* clear oldest_hp */
+		buf_pool->oldest_hp.set(NULL);
+	}
+
 	incr_flush_list_size_in_bytes(block, buf_pool);
 
 #if defined UNIV_DEBUG || defined UNIV_BUF_DEBUG
@@ -658,6 +663,7 @@ buf_flush_remove(
 	/* Important that we adjust the hazard pointer before removing
 	the bpage from flush list. */
 	buf_pool->flush_hp.adjust(bpage);
+	buf_pool->oldest_hp.adjust(bpage);
 
 	switch (buf_page_get_state(bpage)) {
 	case BUF_BLOCK_POOL_WATCH:
@@ -757,7 +763,8 @@ buf_flush_relocate_on_flush_list(
 
 	/* Important that we adjust the hazard pointer before removing
 	the bpage from the flush list. */
-	buf_pool->flush_hp.adjust(bpage);
+	buf_pool->flush_hp.move(bpage, dpage);
+	buf_pool->oldest_hp.move(bpage, dpage);
 
 	/* Must be done after we have removed it from the flush_rbt
 	because we assert on in_flush_list in comparison function. */
@@ -2599,6 +2606,15 @@ page_cleaner_flush_pages_recommendation(
 	lsn_t	target_lsn = oldest_lsn
 			     + lsn_avg_rate * buf_flush_lsn_scan_factor;
 
+	/* Cap the maximum IO capacity that we are going to use by
+	max_io_capacity. Limit the value to avoid too quick increase */
+	const ulint	sum_pages_max = srv_max_io_capacity * 2;
+
+	/* Limit individual BP scan based on overall capacity. */
+	const ulint	pages_for_lsn_max =
+		(sum_pages_max / srv_buf_pool_instances) *
+		buf_flush_lsn_scan_factor * 2;
+
 	for (ulint i = 0; i < srv_buf_pool_instances; i++) {
 		buf_pool_t*	buf_pool = buf_pool_from_array(i);
 		ulint		pages_for_lsn = 0;
@@ -2611,6 +2627,9 @@ page_cleaner_flush_pages_recommendation(
 				break;
 			}
 			++pages_for_lsn;
+			if (pages_for_lsn >= pages_for_lsn_max) {
+				break;
+			}
 		}
 		buf_flush_list_mutex_exit(buf_pool);
 
@@ -2632,7 +2651,7 @@ page_cleaner_flush_pages_recommendation(
 	/* Cap the maximum IO capacity that we are going to use by
 	max_io_capacity. Limit the value to avoid too quick increase */
 	ulint	pages_for_lsn =
-		std::min<ulint>(sum_pages_for_lsn, srv_max_io_capacity * 2);
+		std::min<ulint>(sum_pages_for_lsn, sum_pages_max);
 
 	n_pages = (PCT_IO(pct_total) + avg_page_rate + pages_for_lsn) / 3;
 
diff --git a/storage/innobase/include/buf0buf.h b/storage/innobase/include/buf0buf.h
@@ -1901,6 +1901,13 @@ class HazardPointer {
 	@param bpage	buffer block to be compared */
 	virtual void adjust(const buf_page_t*) = 0;
 
+	/** Adjust the value of hp for moving. This happens
+	when some other thread working on the same list
+	attempts to relocate the hp of the page.
+	@param bpage	buffer block to be compared
+	@param dpage	buffer block to be moved to */
+	void move(const buf_page_t *bpage, buf_page_t *dpage);
+
 protected:
 	/** Disable copying */
 	HazardPointer(const HazardPointer&);
@@ -2129,6 +2136,8 @@ struct buf_pool_t{
 					used during scan of flush_list
 					while doing flush list batch.
 					Protected by flush_list_mutex */
+	FlushHp			oldest_hp;/*!< entry pointer to scan the oldest
+					page except for system temporary */
 	UT_LIST_BASE_NODE_T(buf_page_t) flush_list;
 					/*!< base node of the modified block
 					list */
