9

If I try to run queries (even as easy as select id from table limit 1 ) on some specific tables in a schema (only a few of them have this problem) I get stuck. When looking at the processlist, the state is "Waiting for table flush". Any suggestion about how do I unlock these tables so that I can query them?

Carlo
  • 395
  • 1
  • 8
  • 22
  • 3
    There's likely some big query running that has locks on those tables. Once the query times out or finishes, those locks will be resolved. You should look at the currently running queries and see what's there. https://stackoverflow.com/questions/16571416/how-can-i-get-a-full-list-of-all-queries-currently-running-on-my-mysql-server – Marshall Tigerus Jun 20 '18 at 13:33
  • 1
    Please post (add to your question) what you have already tried and/or researched. – PM 77-1 Jun 20 '18 at 13:35
  • 3
    Thank you @MarshallTigerus that must have been the case, I killed all processes in processlist and now I can again access the tables. – Carlo Jun 20 '18 at 13:40

2 Answers2

2

Pileups of queries stuck in "Waiting for table flush" state are typically caused by something in the background excessively running "FLUSH TABLES" or ANALYZE TABLE statements. Most backup methods need to do this briefly when they begin, but if you are getting it all the time, the chances are that you have a process or a cron job somewhere doing this excessively. Find the source of this and disable it, and the problem should go away.

Gordan Bobić
  • 1,748
  • 13
  • 16
1

For MySQL

  1. Identify the processes causing issues.

    • This command will help to detect processes waiting for disk I/O in D state:

      watch "ps -eo pid,user,state,command | awk '\$3 == /D/ { print \$0 }'"

    • You can also seek processes with long runtime like this:

      SHOW FULL PROCESSLIST\G

  2. Fix the processes or related queries you previously detected.

  3. If you need to kill the detected processes, you can find their ids with the previous full processlist and execute the command: KILL <pid>;

In case you want to fix the same issue For SQL :

  1. Find wich processes are causing issues. This query will list them:
SELECT TOP 20
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
        qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
        (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
        qs.total_elapsed_time/1000000,
    st.text,
    qp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY
    sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY 
    qs.total_worker_time DESC

  1. Fix the processes or related queries you previously detected.

  2. If you need to kill the detected processes, you can find their ids with the previous full processlist and execute the command: 

KILL <SPID>
GO
EXEC sp_who2
GO

You can find more alternatives and details at the following source questions/answers/comments:

https://serverfault.com/questions/316922/how-to-detect-the-process-and-mysql-query-that-makes-high-load-on-server How do I find out what is hammering my SQL Server?

You also can use step by step SQL related article: https://www.wearediagram.com/blog/terminating-sql-server-blocking-processes

María Antignolo
  • 388
  • 4
  • 17