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: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S5 (Performance) |
| Version: | 5.6.11 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | innodb | ||
[13 Jun 2013 13:28]
Shane Bester
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
[13 Jun 2013 13:34]
Shane Bester
minimal php script to time loops...
Attachment: ddl.php (application/octet-stream, text), 558 bytes.
[5 Jul 2013 15:56]
Bugs System
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."
[23 Aug 2013 16:48]
MySQL Verification Team
See http://bugs.mysql.com/bug.php?id=70139 .
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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