By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,494 Members | 898 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,494 IT Pros & Developers. It's quick & easy.

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

P: n/a
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?

Mar 30 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.
Mar 30 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.