MyISAM table is not working as expected in MySQL

Question

mysql>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.

• RolandoMySQLDBA: Are you truncating only one table ???

Answer 1

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 !!!

• James Lupolt: Won't this just create blocking around the ALTER TABLE ... RENAME instead of around the TRUNCATE TABLE?
• RolandoMySQLDBA: @JamesLupolt Renaming the .frm, .MYD, and .MYI files is a whole lot faster that truncating a large .MYD and waiting to reclaim the space from the OS point-of-view, which is really the holdup mysqld experiences.
• RolandoMySQLDBA: @JamesLupolt Since we are talking MyISAM, any INSERT, UPDATE, or DELETE will do a full table lock. A table rename cannot be any worse than ordinary DML.