MySQL Bugs: #69316: Drop/Alter table takes much longer time in 5.6 than 5.5

Bug #69316	Drop/Alter table takes much longer time in 5.6 than 5.5
Submitted:	24 May 2013 23:50	Modified:	5 Jul 2013 15:56
Reporter:	Yoshinori Matsunobu (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	5.6.11	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb

Description:
It is a well known issue that drop/alter empty table takes long time in MySQL if large buffer pool is mostly filled. In 5.5, this is fixed. But in 5.6, drop/alter takes much longer time than 5.5.

In 5.5, when dropping a table, BUF_REMOVE_FLUSH_NO_WRITE -> buf_flush_dirty_pages() is called as below.
buf_LRU_flush_or_remove_pages(
                case BUF_REMOVE_ALL_NO_WRITE:
                        buf_LRU_drop_page_hash_for_tablespace(buf_pool, id);
                        buf_LRU_remove_all_pages(buf_pool, id);
                        break;

                case BUF_REMOVE_FLUSH_NO_WRITE:
                        buf_flush_dirty_pages(buf_pool, id);

But in 5.6, buf_LRU_drop_page_hash_for_tablespace() is called. This is much slower.
                case BUF_REMOVE_ALL_NO_WRITE:
                case BUF_REMOVE_FLUSH_NO_WRITE:
                        buf_LRU_drop_page_hash_for_tablespace(buf_pool, id);

How to repeat:
Read buf0lru.cc:buf_LRU_flush_or_remove_pages(), or do the following steps.

1. Allocate large enough buffer pool (i.e. 60GB)
2. Create a large innodb table and fill buffer pool (create + load data, etc)
3. Run "create table a (id int ) engine=innodb; drop table a;" then compare performance between 5.5 and 5.6.

Suggested fix:
Port 5.5 logic to 5.6

I also noticed this in my tests with innodb_buffer_pool_size=512M

drop table if exists t1;
create table t1(a int primary key)engine=innodb;

InnoDB: 100000 loops of drop/create with datadir on hard disk and ramdisk
-----------------------------------
                  time in seconds  
Version          , hdd , ramdisk
-----------------------------------
4.1.25-pro-gpl-nt, 233 , 66
5.0.96-community , 257 , 80
5.1.70-community , 261 , 84
5.5.32           , 257 , 83
5.6.12           , 745 , 242
5.7.2-m12        , 914 , 246

The buf_LRU_drop_page_hash_for_tablespace in 5.7.2 on ramdisk took 81 seconds:

mysqld.exe!buf_LRU_drop_page_hash_for_tablespace - buf0lru.cc
mysqld.exe!buf_LRU_flush_or_remove_pages+0x4a - buf0lru.cc:886
mysqld.exe!fil_delete_tablespace+0x14d - fil0fil.cc:2659
mysqld.exe!row_drop_table_for_mysql+0xb98 - row0mysql.cc:4544
mysqld.exe!ha_innobase::delete_table+0x250 - ha_innodb.cc:9737
mysqld.exe!ha_delete_table+0xb6 - handler.cc:2364
mysqld.exe!mysql_rm_table_no_locks+0x566 - sql_table.cc:2443
mysqld.exe!mysql_rm_table+0x1d6 - sql_table.cc:2129
mysqld.exe!mysql_execute_command+0x1af6 - sql_parse.cc:3448
mysqld.exe!mysql_parse+0x1e8 - sql_parse.cc:5227
mysqld.exe!dispatch_command+0x6e1 - sql_parse.cc:1327
mysqld.exe!do_command+0x147 - sql_parse.cc:1026
mysqld.exe!do_handle_one_connection+0x125 - sql_connect.cc:983
mysqld.exe!handle_one_connection+0x29 - sql_connect.cc:899
mysqld.exe!pfs_spawn_thread+0x160 - pfs.cc:1924
mysqld.exe!pthread_start+0x1d - my_winthread.c:61
mysqld.exe!callthreadstartex+0x16 - threadex.c:314
mysqld.exe!threadstartex+0x7e - threadex.c:292

minimal php script to time loops...

Attachment: ddl.php (application/octet-stream, text), 558 bytes.

Added a changelog entry for 5.6.13, 5.7.2:

"A code regression introduced in MySQL 5.6 negatively impacted "DROP TABLE"
and "ALTER TABLE" performance."

See http://bugs.mysql.com/bug.php?id=70139 .