"Alter table” Applied to a Large Table - White Label Coders
White Label Coders  /  Blog  /  “Alter table” Applied to a Large Table

Category: Software Engineering

“Alter table” Applied to a Large Table

alter table
3 min read

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).

Finally – the clean up 🙂 check the temporary files and delete the old table.

Good luck!


JavaScript Developer / Team Leader

Professional in full-stack web development. His competencies, besides solid technical background, include strengths such as business analysis, solution design or writing technical documentation. His interpersonal skills led him to be involved in project management, team leadership and recruitment.

Related Articles
Check related articles
solid and kiss in programming
Best Practices in Programming Based on SOLID, KISS and Personal Experience

The guidelines a programmer should follow on a day to day basis are just as important as they are in any field. 

Read more
Testing before Deployment
Testing of a website, our best practicies for testing before deployment

Testing a website or an app before it’s launch is like a rehearsal before the show. This is like the last wardrobe fitting before the gala.

Read more
How to implement an IT project
How to implement an IT Project nicely and easily?

Nowadays many commercial companies create an IT product that becomes the basis of business. An example is the Yearful startup (London, UK), who created a calendar for planning the whole year and setting milestones in it.

Read more
Code Reviews - Putting the “Team” in Teamwork

The quality of the code defines the final effect of the completed project for the client. Regardless of the complexity of the project, software development can be divided into specific pieces of functionality, which ultimately make up the whole - business effect recommended by the client: online store, website, order management platform, game creation system, application ...

Read more
Software development quality testing
Quality means money - learn how to decrease development costs

Quality costs. Money, time, and resources. So why do we even bother? Because Quality is a metric that verifies if we are delivering the specified product and validating that it will satisfy users. How? Based on the testing process. Testers are the only ones that can provide you with precise info about the app.

Read more
delighted programmer with glasses using computer
Let’s talk about your WordPress project!

Do you have an exciting strategic project coming up that you would like to talk about?