Category: Software Engineering
“Alter table” Applied to a Large Table
Do you have a large table and want to alter it? Only for InnoDB?
It can be done!
What is a large table?
For some it’s 100 MB, for others it’s 100 GB, but the question is: How do you make an alter table in a way that doesn’t ruin the database?
Step one – disk space
You will need 2.5 times more free disk space than your table uses.
If the table is 100 GB, don’t create an alter table without a minimum of at least 250 GB of free disk space. Also, make sure that this free space is in the right directories, because in addition to /var/lib/mysql, tmpdir (usually /tmp or /var/tmp) is also used for storing temporary files.
If you have more than one volume then you probably have one on /var or /var/lib/mysql and the other on/ , you have to check if there is enough space in tmpdir.
It may turn out that it’s on a different volume, so check where tmpdir is, by running the query “SELECT @@ tmpdir;”
Where do we need free disk space:
- tmpdir, you need at least as much space as your table has
- mysqldir – you need at least as much space as your table + space for new data (if you add a column or index, the new table will take up more space than the old one).
Therefore, I suggest taking care of 25% of the stock in both places, just in case.
Step two – you don’t want to lock the table
Table altering is very tricky and does not lock the database, which is important, because in large databases the query ALTER TABLE can last from just several hours to several dozen.
Unfortunately, though table altering doesn’t lock the table, it might be another query, or process, that is doing this.
- make sure that no queries or processes that are running on the database LOCK TABLE, if they do, then they will block the table until they execute. Additionally, they’ll do it only when the altering has finished,
- which is why it’s important to check that the backups are disabled for the duration of the table altering. Unfortunately, there is a very high probability that the backups will block the database during the alteration,
- do not execute any other commands like: FLUSH TABLES, ALTER, RENAME, OPTIMIZE or REPAIR, while altering the table; these two commands run simultaneously on the same table will definitely block it,
- before altering a table, make sure that no long queries are performed on the database (eg SELECT may take several minutes).
The third step – innodb_online_alter_log_max_size
→ this is a strange variable, which by default is set to 128 MB, leaving you to find that you may need to increase it (especially if the database has a large increase of data)
→ online_alter_log is used while altering the table to save the changes that have occurred during the alteration, so it must be sized to accommodate the entire data increment.
Table alteration scheme
→ Create an empty new innodb table in the mysql-directory (eg/var/lib/mysla/[dbname])
→ Create a temporary myisam table in the RAM memory (which if not stored in the memory, is saved in the tmpdir directory)
→ Copy the data from the innodb table to the temporary table in myisam – at this point there will be a large increase in disk usage in the tmp directory
→ Align the myisam table with the innodb (the myisam table must be a snapshot of the innodb, during the table snapshot it’s definitely blocked from writing)
→ Log all the new changes to the online_alter_log – from this moment the log will begin to grow (in the memory or on the disk)
→ Copy the data from the temporary myisam table to the new innodb table created in step 1 – temporarily there will be a large increase in disk usage in the mysql directory
→ Align the new table with the online_alter_log-iem – which means loading all the changes that have occurred since the snapshot
→ Replace the old table with a new one (the database is definitely a locked because it has to align the new table with the old one, delete the old table and rename the new one).
