5

We have a mysql database with a size of 2 TB, in which we have for to five big tables ranging from 110GB to 350GB. When we try to drop these tables the mysql goes to the defunct mode and hangs in that state for a long time. Is there a solution to drop these tables without mysql hanging. We have a machine which has a memory of 122GB and swap of 60 GB. We use an InnoDB Engine Hope the information suffices kindly help us with this issue

  • try to truncate first – dimitar Dec 4 '14 at 7:30
  • Even that tries to go to defunct mode , we have tried to truncate. – Prashanth Ganesh Dec 4 '14 at 8:25
  • Just curious: What version of MySQL ? – RolandoMySQLDBA Dec 4 '14 at 15:23
  • We Mysql 5.6.13 – Prashanth Ganesh Dec 5 '14 at 9:19
2

This is an old verified bug in MySQL for InnoDB on larger tables. As this has been patched quite some time ago, perhaps it's not the same situation, but it sure seems similar.

The workarounds for the older versions are:

  1. Defragment in the filesystem. When you extend many tables the chunks for each end up very fragmented, so the defragmenting can be of benefit for table scans as well as dropping speed. If you don't have a defragmenting program, copying the files somewhere and copying them back will do the job.

  2. When dropping, create a filesystem link to the IBD file. Drop the table in MySQL and the OS won't drop the file in the filesystem because the link exists, so the drop will be fast within MySQL. Remove the link and the OS will finish the deleting work in the background.

If that doesnt work... The answer in this question will allow you to delete ABOUT half of your data. The idea being that you can remove enough data to stop InnoDB from hanging when you drop the table.

0

Use pt-archiver to delete all the rows from the table first, in batches. This will take much more time but it will not block other operations. Once the tables have no rows (they are empty) and the rows have been purged, you can delete the tables, which should only take a microseconds lock.

What you are suffering is a full buffer pool global lock. You can prevent it by updating to a newer version of the server, where that problem has more or less been solved, using Percona Server with the lazy drop table feature, using the global tablespace for those big tables in particular, or using multiple buffer pool partitions, so you will only suffer from blocking on one of them.

0

You may need to try something a little unorthodox.

Let's say you have the following scenario:

  • Database is mydb
  • table is mygianttable
  • datadir is /var/lib/mysql
  • the physical file is /var/lib/mysql/mydb/mygianttable.ibd
  • You want to drop mydb.mygianttable

I found PalominoDB's How to recreate an InnoDB table after the tablespace has been removed. Based on the paradigm in that post, I would suggest doing a manual truncation by running the following:

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="CREATE TABLE mygiantcopy LIKE mygianttable"
mysql ${MYSQL_CONN} -Dmydb -ANe"${SQL}"
rm -f /var/lib/mysql/mydb/mygianttable.ibd
SQL="ALTER TABLE mygianttable DISCARD TABLESPACE;"
SQL="${SQL} DROP TABLE mygianttable;"
SQL="${SQL} ALTER TABLE mygiantcopy RENAME mygianttable;"
mysql ${MYSQL_CONN} -Dmydb -ANe"${SQL}"

Give it a Try !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.