| Create INDEX blocks writes to other tables [message #445] |
Mon, 04 December 2006 17:31  |
 jerry
Messages: 2
Registered: December 2006 |
Junior Member |
|
|
I noticed a problem that I cannot explain. I hope that you can help me on this.
When I do "ALTER TABLE t1 ADD CONSTRAINT PRIMARY KEY ...." on a vary large MYISAM table, I noticed that updates/inserts to other MYISAM tables are 'blocked'. Once the "ALTER" statement is done, all the updates statements are processed in a few seconds.
Those updates/inserts are very simple and quick primary key based operations on totally DIFFERENT tables. Without this large "ALTER TABLE t1 ADD CONSTRAINT PRIMARY KEY ...." running concurrently, this updates/inserts can be down in fraction of seconds.
It is not table lock problem since the ops are on different tables. Is there some critical resources, (Key buffer, sort buffer, tmp space), used by "Index Creation" that can cause this problem?
|
|
|
| Re: Create INDEX blocks writes to other tables [message #446 is a reply to message #445 ] |
Mon, 04 December 2006 17:49  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
Looks strange,
Can you send output of processlist when it happens ?
I assume they stall forever while alter table is completed as otherwise it may be some kind of IO stall or anything.
Also it is possible for some other query to try to access both the table you're altering and the one which you're inserting into and as one of table can't be locked it also stalls stalling everything else.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
