List:General Discussion« Previous MessageNext Message »
From:mos Date:July 22 2007 3:23am
Subject:Re: MySQL slow when altering tables.
View as plain text  
At 01:32 PM 7/20/2007, Andrew Rosolino wrote:

>Whenever I alter a mysql table like add a new field it gets really slow and
>all other processes lag.
>Is there a memory variable I can adjust for this?


It may be slow because if it is rebuilding the indexes to the table. Don't 
forget that when you Alter a table, it creates a copy of the table and 
after the data is loaded into the new table, the indexes have to be 
rebuilt.  Run Show ProcessList when the server is running slow to see what 
it is actually doing.

If you have a few million rows in the table and lots of indexes, you may 
want to increase the size of key_buffer to a few hundred MB if you have the 
memory. The upper limit I think on 32 bit machines is 2GB

Mike 
Thread
MySQL slow when altering tables.Andrew Rosolino20 Jul
  • Re: MySQL slow when altering tables.Wm Mussatto20 Jul
    • max_connect_errors Charles Brown23 Jul
  • Re: MySQL slow when altering tables.mos23 Jul