Bug#11759044 - 51325: DROPPING AN EMPTY INNODB TABLE TAKES A LONG TIME

WITH LARGE BUFFER POOL

(Note: this a backport of revno:3472 from mysql-trunk)

rb://845
approved by: Marko

  When dropping a table (with an .ibd file i.e.: with
  innodb_file_per_table set) we scan entire LRU to invalidate pages from
  that table. This can be painful in case of large buffer pools as we hold
  the buf_pool->mutex for the scan. Note that gravity of the problem does
  not depend on the size of the table. Even with an empty table but a
  large and filled up buffer pool we'll end up scanning a very long LRU
  list.
  
  The fix is to scan flush_list and just remove the blocks belonging to
  the table from the flush_list, marking them as non-dirty. The blocks
  are left in the LRU list for eventual eviction due to aging. The
  flush_list is typically much smaller than the LRU list but for cases
  where it is very long we have the solution of releasing the
  buf_pool->mutex after scanning 1K pages.
  
  buf_page_[set|unset]_sticky(): Use new IO-state BUF_IO_PIN to ensure
  that a block stays in the flush_list and LRU list when we release
  buf_pool->mutex. Previously we have been abusing BUF_IO_READ to achieve
  this.

2 files changed, 3 insertions, 2 deletions

diff --git a/mysql-test/suite/innodb/r/innodb_cmp_drop_table.result b/mysql-test/suite/innodb/r/innodb_cmp_drop_table.result
index bae2a17bd02..1f6d6948756 100644
--- a/mysql-test/suite/innodb/r/innodb_cmp_drop_table.result
+++ b/mysql-test/suite/innodb/r/innodb_cmp_drop_table.result
@@ -7,6 +7,7 @@ page_size
 drop table t1;
 SELECT page_size FROM information_schema.innodb_cmpmem WHERE pages_used > 0;
 page_size
+8192
 create table t2(a text) engine=innodb;
 SELECT page_size FROM information_schema.innodb_cmpmem WHERE pages_used > 0;
 page_size
diff --git a/mysql-test/suite/innodb/t/innodb_cmp_drop_table.test b/mysql-test/suite/innodb/t/innodb_cmp_drop_table.test
index 481ccd646f8..92f4f715241 100644
--- a/mysql-test/suite/innodb/t/innodb_cmp_drop_table.test
+++ b/mysql-test/suite/innodb/t/innodb_cmp_drop_table.test
@@ -26,7 +26,7 @@ while ($i)
 
 drop table t1;
 
-# no lazy eviction at drop table in 5.1 and 5.5 there should be no
+# because of lazy eviction at drop table in 5.5 there should be some
 # used 8K pages
 -- eval $query_i_s
 
@@ -36,7 +36,7 @@ create table t2(a text) engine=innodb;
 
 -- disable_query_log
 
--- let $i = 200
+-- let $i = 400
 while ($i)
 {
   insert into t2 values(repeat('abcdefghijklmnopqrstuvwxyz',1000));