Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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.

share|improve this question
    
Are you truncating only one table ??? –  RolandoMySQLDBA Jul 23 '13 at 17:00
add comment

1 Answer

up vote 1 down vote accepted

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

share|improve this answer
1  
Won't this just create blocking around the ALTER TABLE ... RENAME instead of around the TRUNCATE TABLE? –  James Jul 23 '13 at 20:29
    
@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 Jul 23 '13 at 20:33
1  
@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. –  RolandoMySQLDBA Jul 23 '13 at 20:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.