MySQL Performance: MySQL => Create INDEX blocks writes to other tables

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?

Report message to a moderator