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

Members

Help

Home

Home » Performance » MySQL » Trying to insert into a table while Optimizing it??? will that work

Show: Today's Messages :: Show Polls :: Message Navigator

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

Re: Trying to insert into a table while Optimizing it??? will that work [message #184 is a reply to message #183 ]

Wed, 20 September 2006 09:37

toasty
Messages: 19
Registered: August 2006
Location: UK

Junior Member

>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

Report message to a moderator

Re: Trying to insert into a table while Optimizing it??? will that work [message #187 is a reply to message #184 ]

Fri, 22 September 2006 07:19

vrkris
Messages: 6
Registered: August 2006

Junior Member

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.

Report message to a moderator

Re: Trying to insert into a table while Optimizing it??? will that work [message #188 is a reply to message #187 ]

Sat, 23 September 2006 13:54

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

OPTIMIZE is currently blocking operation so there is not much you can do. Sometimes you simply can forget about it as fragmentation would not affect you so badly.

Other approach you can take is creating shadow table which gets copy of table to be optimized (ie via SELECT INTO OUTFILE/LOAD DATA INFILE) and updates merged to it afterwards.

Other approach is to have Master-Master setup so you can run OPTIMIZE on one of the nodes and than on other one.

Hopefully sometime in the future online defragmentation will be implemented

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Previous Topic:	Help needed in Innodb buffer pool size increase issue
Next Topic:	One query on a big table vs multiple queries on multiple smalle ones

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 10 02:41:49 EDT 2009

Total time taken to generate the page: 0.02828 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.