Bug #44757 Dropping large tables freezes innodb engine
Submitted: 8 May 2009 18:24 Modified: 10 Nov 2009 3:44
Reporter: bhushan uparkar Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0.32, 5.1.28 OS:Other (Debian Linux)
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: Innodb freeze large

[8 May 2009 18:24] bhushan uparkar
Description:
We have large innodb tables, some are 50GB+ while others are 800GB+. These tables have multiple indexes as well as have data existing on them for 2+ years. When we try to drop these large tables, following behavior is observed

1. Dropping of tables take order of 10+ minutes. 
2. If there are other processes running [ which comes after the drop table issued ]  they go in 'Opening tables' mode . The tables referred in queries , do not have any relationship with dropped table. No relationship exists between them. They remain in this mode till the 'drop table is completed'. In short you can no longer issues any more queries, you are freezed.
3. You can not abort the process as well.  
4. I observed similar behavior with mysql version 5.0.32 as well as 5.1.33, so the problem is not specific to partition or 5.1 .

Here is the process list output in this case,
========================
mysql> show processlist;
+---------+-------------+--------------------+--------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id      | User        | Host               | db           | Command     | Time    | State                                                          | Info                                                                                                 |
+---------+-------------+--------------------+--------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
|  587002 | system user |                    | NULL         | Connect     | 2589181 | Waiting for master to send event                               | NULL                                                                                                 | 
|  587003 | system user |                    | NULL         | Connect     |     111 | Opening tables                                                 | NULL                                                                                                 | 
| 1166566 | repl        | 10.11.152.44:35756 | NULL         | Binlog Dump |  994334 | Has sent all binlog to slave; waiting for binlog to be updated | NULL                                                                                                 | 
| 1252979 | repl        | 10.11.152.44:5015  | test         | Query       |     205 | Opening tables                                                 | INSERT INTO heartbeat (time_int, time_ts, slave_host) VALUES (UNIX_TIMESTAMP(), CURRENT_TIMESTAMP(), | 
| 1344929 | dmcpcadmin  | 10.11.16.77:44525  | admob_dm_rep | Query       |     210 | freeing items                                                  | insert into fact_cpc_day_ad_site_location (linked_list_request, traffic, linked_list_traffic, multi_ | 
| 1345638 | dbbackup    | localhost          | admob_dm_rep | Query       |     210 | NULL                                                           | drop table fact_cpc_day_uu_new                                                                       | 
| 1345676 | dmreport    | 10.11.16.124:33539 | admob_dm_rep | Query       |      37 | Opening tables                                                 | create temporary table admob_dm_rep.fact_cpc_hour_budget_stats_stage as select * from admob_dm_rep.f | 
| 1345678 | dbbackup    | 10.11.152.44:24449 | NULL         | Query       |       0 | NULL                                                           | show processlist                                                                                     | 
+---------+-------------+--------------------+--------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
========================

And here is the output when the operation is completed,

================
Query OK, 0 rows affected (17 min 21.37 sec)

mysql> 

================

How to repeat:
Have large tables [50GB+ ] on the server, and run queries on all of them for more than 1 month + time . Try to drop one of the table, and you should see the problem.

Suggested fix:
Dropping table should not affect other unrelated tables, the innodb engine should be able to operate on other tables. Dropping big tables should be handled more intelligently by doing some data dictionary operation so have little or no impact.
[8 May 2009 18:27] bhushan uparkar
Innodb status output while dropping the table

Attachment: innodb_status_op_while_dropping_table.rtf (application/rtf, text), 5.34 KiB.

[8 May 2009 18:31] bhushan uparkar
Here is os information, 
Linux dmdb906 2.6.24-etchnhalf.1-amd64 #1 SMP Tue Oct 14 03:11:45 UTC 2008 x86_64 GNU/Linux

We are using mysql version,
mysql  Ver 14.14 Distrib 5.1.28-rc, for debian-linux-gnu (x86_64) using  EditLine wrapper
[8 May 2009 20:41] MySQL Verification Team
I reported the same thing in bug #37963
[8 May 2009 23:54] bhushan uparkar
I like to clarify in this case, once the table drop operation was finished the database was back to NORMAL life. The database was non responsive during the entire period i.e. 17 minutes while drop table was executing.
[9 May 2009 0:03] bhushan uparkar
Here is the output , which shows the other process in opening table state ,

=====================
mysql> show processlist;
+---------+-------------+--------------------+--------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id      | User        | Host               | db           | Command     | Time    | State                                                          | Info                                                                                                 |
+---------+-------------+--------------------+--------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
|  587002 | system user |                    | NULL         | Connect     | 2589521 | Waiting for master to send event                               | NULL                                                                                    |
|  587003 | system user |                    | NULL         | Connect     |     451 | Opening tables                                                 | NULL                                                                                                 |
| 1166566 | repl        | 10.11.152.44:35756 | NULL         | Binlog Dump |  994674 | Has sent all binlog to slave; waiting for binlog to be updated | NULL                                                 |
| 1252979 | repl        | 10.11.152.44:5015  | test         | Query       |     545 | Opening tables                                                 | INSERT INTO heartbeat (time_int, time_ts, slave_host) VALUES (UNIX_TIMESTAMP(), CURRENT_TIMESTAMP(), |
| 1344929 | dmcpcadmin  | 10.11.16.77:44525  | admob_dm_rep | Query       |     550 | freeing items                                                  | insert into fact_cpc_day_ad_site_location (linked_list_request, traffic, linked_list_traffic, multi_ |
| 1345638 | dbbackup    | localhost          | admob_dm_rep | Killed      |     550 | NULL                                                           | drop table fact_cpc_day_uu_new                                             |
| 1345676 | dmreport    | 10.11.16.124:33539 | admob_dm_rep | Query       |     377 | Opening tables                                                 | create temporary table admob_dm_rep.fact_cpc_hour_budget_stats_stage as select * from admob_dm_rep.f |
| 1345679 | dbbackup    | 10.11.152.44:24450 | NULL         | Query       |       0 | NULL                                                           | show processlist                                                                           |
+---------+-------------+--------------------+--------------+-------------+---------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
========================
[1 Jul 2009 21:58] Vojtech Kurka
Workaround maybe using a filesystem with fast file deletion, XFS for example. I assume you are using something like EXT3...
[20 Jul 2009 22:36] James Day
Try truncate before drop. The server takes a server-wide lock during DDL operations and truncating might split out the space freeing from the DDL part.
[10 Nov 2009 3:45] MySQL Verification Team
Verified as described, only happens with innodb_file_per_table.
Dup of bug #41158, see also bug #37983.