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: | |
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
[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.