Hello List, Recently I needed to alter a large innodb table (~200k rows) to add a column on a live server. The alter table ran fine on the development server with close to the same number of rows in the table so I didn't anticipate too much trouble. The list archive and the documentation indicate that alter table has some inneficenties - like rebuilding the entire table, indexes and all and that the table would be locked as read only while the alter table command was executing -- and the the alter table was likely to take A LONG TIME. (one poor guy posted that he gave up on altering his table after it was still running for 24 hours!)
What we found is that the process list quickly filled up with SELECT statements that were 'Waiting for table' -- The connections maxed out (at 500) since the webservers were still sending queries, which I figured would be okay since they are reads, but apparantly these queries were locked out. I found myself locked out , unable to issue more commands and had to kill mysqld. Since all ALTER TABLE commands will probably exhibit suimilar locking - I am weary of even creating a new table with another name with the new structure, populating it with data from the table I want to alter, dropping the old table and renaming the new one back to the old one's name. So I'm wondering a few things if people can chime in: - does ALTER TABLE really just lock read only or does it just flat out LOCK? - might there be some configuration setting that influences this locking? - what strategies do people use to alter live busy tables ? Thanks for any and all input! Jeff Kolber ps: we are using: mysql Ver 12.18 Distrib 4.0.12, for pc-linux (i686) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
