MySQL very slow for alter table query

Question

Why is it taking more than an hour to simply update this table to add a column? This table has 15M rows. It has 2 indexes and a single key primary key. The ALTER TABLE query has been in "copy to tmp table" state for 1 hour 15 minutes now.

ALTER TABLE `frugg`.`item_catalog_map` 
ADD COLUMN `conversion_url` TEXT NULL DEFAULT NULL

Table:

mysql> describe item_catalog_map;
+------------------------+---------------+------+-----+---------+-------+
| Field                  | Type          | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| catalog_unique_item_id | varchar(255)  | NO   | PRI | NULL    |       |
| catalog_id             | int(11)       | YES  | MUL | NULL    |       |
| item_id                | int(11)       | YES  | MUL | NULL    |       |
| price                  | decimal(10,2) | YES  |     | 0.00    |       |
+------------------------+---------------+------+-----+---------+-------+

mysql> show index from item_catalog_map;
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name             | Seq_in_index | Column_name            | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+
| item_catalog_map |          0 | PRIMARY              |            1 | catalog_unique_item_id | A         |    15485115 |     NULL | NULL   |      | BTREE      |         |
| item_catalog_map |          1 | IDX_ACD6184FCC3C66FC |            1 | catalog_id             | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |
| item_catalog_map |          1 | IDX_ACD6184F126F525E |            1 | item_id                | A         |    15485115 |     NULL | NULL   | YES  | BTREE      |         |
+------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+

May be this thread with similar question will help you out...http://stackoverflow.com/questions/1359097/mysql-alter-table-on-very-large-table-is-it-safe-to-run-it — Mudassir Hasan, Oct 08 '12 at 02:53
Go through this thread also...http://stackoverflow.com/questions/5677932/optimize-mysql-for-faster-alter-table-add-column — Mudassir Hasan, Oct 08 '12 at 02:56
It would be cool if there was some kind of progress bar, status message or at the very least a spinning cursor, LOL. Especially since the "modify column" has taken my site offline. — PJ Brunet, Oct 07 '13 at 19:46
Working answer here - https://stackoverflow.com/questions/7599519/alter-table-add-column-takes-a-long-time — Oded Ben Dov, Jan 12 '23 at 12:37

Ernesto Campohermoso · Accepted Answer · 2018-01-06T20:52:01.667

81

MySQL’s ALTER TABLE performance can become a problem with very large tables. MySQL performs most alterations by making an empty table with the desired new structure, inserting all the data from the old table into the new one, and deleting the old table. This can take a very long time, especially if you’re short on memory and the table is large and has lots of indexes. Many people have experience with ALTER TABLE operations that have taken hours or days to complete.

Anyway if you need to proceed with alter table, maybe the following resources could help you:

edited Jan 06 '18 at 20:52

answered Oct 08 '12 at 02:58

Ernesto Campohermoso

7,213
1
40
51

4

If it takes that long there is something wrong with your storage system, it's not a MySQL thing. – AndreKR Oct 08 '12 at 15:10
3

im altering from varchar to text in an 30 rows table , been waiting for 20 mins and still going on. What can it be ? – kommradHomer Jan 09 '14 at 16:14
VARCHAR is stored in the table, while text is stored separately and the reference is stored in the table. So they are very different. I assume the change was prompted because you have a very large varchar and you want to allow for much larger values. In this case, it needs to move all the values. – techdude Feb 02 '15 at 22:04
4

@AndreKR - no, I think it's pretty clearly a MySQL issue. Adding a column to a table containing 900MB of data (around 25K rows containing smallish blobs) shouldn't take > 3 hours, which is where I'm at now. There's nothing wrong with my storage system either; the database is stored on a reasonably good SSD and I have plenty of free RAM for cacheing. I think InnoDB just sucks at maintaining indices during this kind of operation; I guess it doesn't have an optimized bulk insert mechanism, so is rebuilding the new index one row at a time. – Jules Oct 01 '17 at 07:50
2

@Jules You might be right. I think when I wrote the comment I assumed it's a MyISAM table because I thought "copy to tmp table" doesn't happen with InnoDB, but indeed nowhere in the question the table type is given. With InnoDB it's normal to plan a few days for an ALTER TABLE operation, which is why software like https://github.com/facebookincubator/OnlineSchemaChange exists. – AndreKR Oct 02 '17 at 06:28
The size of the table has nothing to do with it. I have seen tables with 60 rows take half an hour to alter. MySQL is too inept to have any sort of fairness or lock starvation mitigations, so it will just let other transactions lock the table all day. If any thing uses FOR UPDATE, you are screwed. – doug65536 Sep 14 '22 at 04:11

score 38 · Answer 2 · edited Jun 19 '17 at 04:42

If you don't care about downtime, my suggestion is using three separated ALTER TABLE statements. The first statement removes all existing secondary indexes. The second statement applies all column related changes. The last statement adds dropped secondary indexes back and applies other index changes.

Another two tips:

Before apply index changes, execute the two following statements and change the values back to 1 after finishing the index change.
```
SET unique_checks=0;
SET foreign_key_checks=0;
```
When create multiple secondary indexes, put them in one ALTER TABLE statement rather than multiple separated ALTER TABLE statements.

The following picture shows the performance difference. Approach 1 is your approach and approach 2 is my way. Approach 2 takes about 3.47% time comparing with approach 1 for a 50m table. The solution only works for MySQL (>=5.5) InnoDB engine.

enter image description here

Should be noted that I believe the Percona variant of MySQL has this built in by setting `expand_fast_index_creation=ON` — Brian Leishman, Jul 10 '18 at 15:19

score 11 · Answer 3 · answered Jun 09 '13 at 23:00

For minimize locking up of the large table that I want to alter, I do the following:

Create a new empty table based on the existing table and alter this new empty table.
Do a mysqldump of the large table such that it has one complete insert statement per record in the large table (switches -c and --skip-extended-insert)
Import this mysqldump into a different (empty) database with the empty renamed large_table.
Take a mysqldump of this new rename table from the other database and import it into the original database

Rename large_table and large_table_new in the original database.

mysql> create table DATABASE_NAME.LARGE_TABLE_NEW like DATABASE_NAME.LARGE_TABLE;
mysql> alter table DATABASE_NAME.LARGE_TABLE_NEW add column NEW_COLUMN_NAME COL_DATA_TYPE(SIZE) default null;

$ mysqldump -c --no-create-info --skip-extended-insert --no-create-db -u root -p DATABASE_NAME LARGE_TABLE > LARGE_TABLE.sql

mysql> create table test.LARGE_TABLE like DATABASE_NAME.LARGE_TABLE;

$ mysql -u root -p -D test < LARGE_TABLE.sql

mysql> rename table test.LARGE_TABLE to test.LARGE_TABLE_NEW;

$ mysqldump -c --no-create-info --skip-extended-insert --no-create-db -u root -p test LARGE_TABLE_NEW > LARGE_TABLE_NEW.sql

$ mysql -u root -p -D DATABASE_NAME < LARGE_TABLE_NEW.sql

mysql> rename table DATABASE_NAME.LARGE_TABLE to DATABASE_NAME.LARGE_TABLE_OLD, DATABASE_NAME.LARGE_TABLE_NEW to DATABASE_NAME.LARGE_TABLE;

score 10 · Answer 4 · answered Aug 15 '14 at 12:46

10

The Percona tools are a lifesaver for this stuff w/ big tables.

http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

they basically:

create duplicate table
create trigger to sync tables
bulk copy data
verify
swap tables

Takes forever, but who cares because this means you can change columns without downtime.

answered Aug 15 '14 at 12:46

jdwyah

1,253
1
11
22

2

nothing can help if you have Foreign Keys on your tables, because you cannot atomically rename two tables – kommradHomer Dec 10 '15 at 08:25
Obviously, you cannot change the data type of one side of a foreign key without also changing the other. That's a given. You must then drop the foreign key, alter both columns, and reintroduce the foreign key after. – Kafoso Nov 18 '20 at 09:56

score 8 · Answer 5 · answered Oct 08 '12 at 02:51

Your table has 15 million rows, which is something. The ALTER TABLE involves copying over all the data from the table and recreating the indexes. As a first measurement try copying the data file (item_catalog_map.MYD if it's MyISAM) in your filesystem and see how long that takes. This is the time the ALTER TABLE will at least take.

score 3 · Answer 6 · edited Jan 08 '22 at 23:17

3

I had the same issue and I just restarted the MySQL service using:

sudo service mysql restart

in Ubuntu, and after this the ALTER TABLE command went through immediately.

edited Jan 08 '22 at 23:17

kmoser

8,780
3
24
40

answered Jan 04 '22 at 23:09

Charles Spicer

59
3

1

This should be the accepted answer! Magic, this works – George Chalhoub Mar 06 '23 at 17:18

score 0 · Answer 7 · answered Jul 20 '22 at 22:45

I had a similar problem where an ALTER TABLE was incredibly slow for a table with 11 million rows running inside a MariaDB Docker container. The same ALTER TABLE was lightning fast on an RDS instance also with 11 million rows.

I'm running Mac OS 12.4 and the data for the Docker container lives on a filesystem volume. The bottleneck was the creation of the temporary table as other answers have noted.

The fix was to enable VirtioFS inside the Docker experimental settings. The ALTER TABLE then took around 10 minutes instead of what was likely to take 8 hours.

MySQL very slow for alter table query

7 Answers7

Linked