patternsqlMinor
Drop Tables with Bigger size
Viewed 0 times
tablesbiggerwithsizedrop
Problem
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
Solution
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:
-
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.
-
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.
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:
-
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.
-
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.
Context
StackExchange Database Administrators Q#84264, answer score: 2
Revisions (0)
No revisions yet.