4 ms·

Is it normal for a MySQL ALTER TABLE to run for 22 hours on a table with less than 50 million rows?

• chrisbolt 17y ago
  There are many variables. Memory, disk speed, average row length, whether or not MySQL has been correctly tuned to handle that kind of load.
  • jacquesm 17y ago
    And of course whatever else is running on that machine. If you do this on a production server then you might end up seeking back and forth across the disk all the time. Alter tables are pretty bad, there is a quick way to do them though, here is how: - create new table, right structure, no keys - load data in it (now you should have a .MYD) - create table with all keys defined, copy the .frm and .MYI files over the ones of the .MYD above - flush tables (don't forget this, alternatively you can shut down mysql at this point and use the my*chk tool with the -r option to recreate your indexes) - repair table (skip if you shutdown mysql above) This will work a whole lot faster than an alter table on a table of the same size, but it is a very gory hack and I would advise you to make a backup before trying any of this.
    • chrisbolt 17y ago
      If you have a highly available setup with master-master replication, you can simply run SET SQL_LOG_BIN=0; ALTER TABLE whatever; on the inactive master, then swap masters and do the same on the previously active master.
• snprbob86 17y ago
  I was under the impression that it is normal to never alter or drop columns in large production tables for performance reasons. Instead, I'd prefer to to deploy a new version which can handle only the old OR both the old and new columns. Then, the data organically evolves to the new schema; maybe a background job does it over time. This is the approach you need to use with schema-less solutions like BigTable.