0

I am using MySQL server 5.6.23 on CentOS and server configuration is like

RAM - 32 GB Cores - 6 cores Innodb buffer pool size = 8 GB Key cache size = 2 GB

We are using only select queries. But all select queries are getting locked and we are observing "Waiting for table metadata lock".

We have enabled slow query log and we observed the queries are getting locked and taking around more than 30 mins for execution. But when we try to execute the same query, it is getting executed in less than 3 secs.

As these queries are getting locked one by one, during the time, around 200 connections are showing same message as "Waiting for table metadata lock".

I had observed the Show engine innodb status output, in the the output also it is showing all the transactions locked.

But I did not used any CREATE/ALTER/INSERT/UPDATE/DELETE statements.

  • 1
    Look at SHOW PROCESSLIST; what non-SELECTs are running? Do you use LOCK TABLE? How many different tables are there? – Rick James Feb 20 '15 at 18:47
  • 1
    Perhaps a mysqldump is running? – Rick James Feb 20 '15 at 20:08
  • 1
    Check mysqlperformanceblog.com/2013/02/01/… – Rick James Feb 20 '15 at 20:11
  • 1
    Are you running with autocommit = 0, and have not issued a COMMIT in a long time? – Rick James Feb 20 '15 at 20:13
  • 1
    Perhaps there is an active transaction into SELECT * FROM information_schema.INNODB_TRX; that are associated with process with info equals to NULL? If any, could you try to kill the process? – Giovanni Feb 20 '15 at 21:18

Your Answer

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

Browse other questions tagged or ask your own question.