DROP TABLE Syntax Causing Memory Leak?
I have a Server with 2 Xeon E5645 total 12cores and 24 threads on a X8DAH+-F-B Motherboard and 96Gigs of Memory and 4 Terabytes of hard drive space. We are running the newest stable release of MariaDB 5.5.25. every night I run a PHP script to bring a large data set roughly 500K records or so into a XtraDB Table I use as a "SWAP" table. I processing,filtering, massaging the data in this table then insert this processed data into other tables where it can be accessed by a web application. At the end of this Script I DROP this "SWAP" table. This Script runs 24 times a day and does not seem to have much issues. But For Some reason seemingly randomly the DROP table Command while checking permissions to drop the Table will lockup the database. I have tried truncate too and it seems to have the same issue. The innodb_buffer_pool_size=35G. Any Ideas what the problem could be or where to start looking?
Noctechie
Answer Answered by Elena Stepanova in this comment.
Hi,
There have been several issues in MySQL related to DROP TABLE + InnoDB. Although they are marked as fixed, they might give you some ideas about where to start looking:
MySQL Bug #51325
MySQL Bug #64284
MySQL Bug #61188
Also, there is one which is suspected to be related, although it's not confirmed - this one is still open:
MySQL Bug #59727
It is difficult to start guessing from your description what exactly the problem is, in your case. First of all, you say in the subject that DROP TABLE causes a memory leak, but in the description you only mention the database lockup, nothing about memory issues.
Also, you did not say how you detect the lock-up, and what exactly you mean by that -- is it just the DROP TABLE itself that hangs, or does the entire flow stop? Apparently you can at least run SHOW PROCESSLIST, but what else you can or cannot do?
Furthermore, you did not say how the situation eventually resolves: do you kill the query, or do you restart the server, or does the query finish on its own at the end?
Maybe you could try to run SHOW ENGINE INNODB STATUS while DROP TABLE is hanging and see what it says, it might give some clues about what is going on.
