MyISAM table is not working as expected in MySQL mysql&gt;show processlist;

+-------+------+------------------------------+---------------------+---------+-------+-------+------------------+
| Id    | User | Host                         | db                  | State   | Time  | State | Info             |
+-------+------+------------------------------+---------------------+---------+-------+-------+------------------+
| 27151 | root | localhost                    | 2206_pf_test        | W F T   | 22667 |       | NULL             |
| 27642 | root | 192.168.10.53:33506          | xxxxxxxx_central_db | W F T   | 21584 |       | NULL             |
| 27645 | root | 192.168.10.53:33508          | xxxxxxxx_central_db | W F T   | 21583 |       | NULL             |
| 27656 | root | 192.168.10.53:33519          | xxxxxxxx_central_db | W F T   | 21583 |       | NULL             |
| 27657 | root | 192.168.10.53:33520          | xxxxxxxx_central_db | W F T   | 21583 |       | NULL             |
| 27658 | root | 192.168.10.53:33521          | xxxxxxxx_central_db | Locked   | 21583 |       | NULL             |
+-------+------+------------------------------+---------------------+---------+-------+-------+------------------+


Every 10 minutes I run the query to table truncate old datat and recreate new data process. There was long time the table is locked. There is no response from that. 

I seen more than 20 times this table is showing the command as Waiting for the table.

Note: W F T is waiting for table

How to resolve this issue?

I am using MyISAM engine in mysql.
The command TRUNCATE TABLE is DDL. That would entail doing a full table lock regardless of storage engine. If other DB Connections are waiting to access the table, you can be sure there are MUTEXs.

For the sake of example, let's say the table you are truncating is mydb.mytable.

You can get around the MUTEX problem (as manifested by Waiting for Table...)

Instead of doing this:

TRUNCATE TABLE mydb.mytable;


make an empty table on the fly:

ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
CREATE TABLE mydb.mytablenew LIKE mydb.mytablezap;
TRUNCATE TABLE mydb.mytablenew;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
DROP TABLE mydb.mytablezap;


I still run TRUNCATE TABLE mydb.mytablenew;, even though it is empty, in order to reset the auto_increment value. The table should be immediately available for use once you execute  the ALTER TABLE mydb.mytablenew RENAME mydb.mytable;.

The last line, DROP TABLE mydb.mytablezap;, should only be handled by the one DB Connection that runs the above code.

Give it a Try !!!