slow / server crash when adding a column to a big table

[bennett]

I have a table with about 100,000 records whose description is:

+-----------------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+-----------------------+----------------------+------+-----+---------+----------------+
| ID | int(10) unsigned | | PRI | NULL |
auto_increment |
| url | varchar(255) | YES | MUL | NULL |
|
| how_found | varchar(255) | YES | | NULL |
|
| use_for_parser | smallint(5) unsigned | YES | | 1
| |
| checked_by_parser | smallint(5) unsigned | YES | MUL | NULL
| |
| monitored | smallint(5) unsigned | YES | MUL | NULL |
|
| date_found | datetime | YES | | NULL |
|
+-----------------------+----------------------+------+-----+---------+----------------+

I tried adding a column with the command:
alter table url add column use_for_aol_mail smallint unsigned default 1
after checked_by_parser;

This caused the command prompt to hang and apparently crashed the Web
server which then had to be rebooted. Is there anything special to
keep in mind about adding columns to such a large table?

[Bill Karwin]

be*****@peacefire.org wrote:

alter table url add column use_for_aol_mail smallint unsigned default 1
after checked_by_parser;

This caused the command prompt to hang and apparently crashed the Web
server which then had to be rebooted. Is there anything special to
keep in mind about adding columns to such a large table?

Any ALTER TABLE statement causes MySQL to "rebuild" the table. That is,
it creates a new table according to the schema changes you specified,
copies all data to a new table, then removes the old version of the table.

This can take a while if the table is populated with many rows, and it
requires additional disk space temporarily. It could also cause a lot
of resource usage (I/O, CPU, memory) that competes with the activity of
other services.

I would expect it to cause some slowness on the system, but I'd be
surprised if it caused a crash of any kind, unless your server is
severely undersized.

Regards,
Bill K.