Trying to insert into a table while Optimizing it??? will that work

MySQL & MariaDB Other MySQL® Questions
1

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)

2

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

I don’t know of a way to do it. A read lock means that other readers can read but no writing is possible. A write lock means no reading or writing is possible. (Optimize table on InnoDB is mapped to alter table I believe, and alter table grabs a read lock)

One possiblity (depending on your needs) is that you might be able to get away with running just Analyze rather than Optimize as it’s vastly faster.

(Your optimize has been running for 5 hours now. I hope it’s a pretty big table…)

So, question for Peter or someone else who knows: If someone has a very large InnoDB table that needs optimizing, what’s the best approach for doing it without making the table effectively unavailable through locking?

Toasty

3

Its not really a huge table, but in few hundred megs, the interesting thing is that the optimize completed within 5 minutes after I went ahead and killed the insert thread.