| Bug #44757 | Dropping large tables freezes innodb engine | ||
|---|---|---|---|
| Submitted: | 8 May 20:24 | Modified: | 10 Nov 4:44 |
| Reporter: | bhushan uparkar | ||
| Status: | Duplicate | ||
| Category: | Server: InnoDB | Severity: | S3 (Non-critical) |
| Version: | 5.0.32, 5.1.28 | OS: | Other (Debian Linux) |
| Assigned to: | Andrew Dalgleish | Target Version: | |
| Tags: | Innodb freeze large | ||
[8 May 20: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 20: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 22:41]
Shane Bester
I reported the same thing in bug #37963
[9 May 1: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 2: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 23:58]
Vojtech Kurka
Workaround maybe using a filesystem with fast file deletion, XFS for example. I assume you are using something like EXT3...
[21 Jul 0: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 4:45]
Andrew Dalgleish
Verified as described, only happens with innodb_file_per_table. Dup of bug #41158, see also bug #37983.
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.