MySQL Performance: MySQL => Trying to insert into a table while Optimizing it??? will that work

Trying to insert into a table while Optimizing it??? will that work [message #183]

Wed, 20 September 2006 08:33

vrkris
Messages: 6
Registered: August 2006

Junior Member

What are my options when a process needs to insert data from infile into a table which is currently getting optimized (using optimize table). I know for sure I cannot kill the optimize command as it would put the table into corrupt state, but I cannot wait anymore for the optimize to finish either...If the optimize process has only a read lock on the 'acounts' table, is there a way I can force the insert thread to proceed with inserting onto that table???

from processlist >>>

| 32493 | root | localhost | mydb | Query | 18512 | copy to tmp table | optimize table acounts

| 32499 | cmt | localhost | mydb | Query | 15880 | Locked | load data local infile '/var/tmp/Insertpc' into table acounts fields terminated by ',' (m_id, in_id, pcount, cdate, created_by, creation_date, last_updated_by, last_updated) |

from innodb status >>>

---TRANSACTION 1 671615098, ACTIVE 28 sec, process no 14525, OS thread id 4517909 inserting, thread declared inside InnoDB 430
mysql tables in use 2, locked 3
48 lock struct(s), heap size 5504, undo log entries 8040
MySQL thread id 32493, query id 1838572000 localhost root copy to tmp table
optimize table acounts

---TRANSACTION 1 670026546, not started, process no 2550, OS thread id 4648976
mysql tables in use 1, locked 1
MySQL thread id 32499, query id 1838625474 localhost mydb Table lock
load data local infile '/var/tmp/Insertpc' into table acounts fields terminated by ',' (m_id, in_id, pcount, cdate, created_by, creation_date, last_updated_by, last_updated)

Report message to a moderator