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]

Reply via email to