"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
14.02.2019
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!

Bialas-WLC

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
SEE OUR BLOG
Check related articles
how to prepare design for web development
12 Tips on how to prepare your design for web development

Admit it or not, we simply judge things based on their look. And that goes for websites design too. If you make them look and function well, as designers and developers try to do, people are going to trust the content more. That’s how things are.

Read more
Teamwork
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
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
automating testing
Automatic Testing

Creating automated tests is a very important element of the software development process. This approach to creating tests utilizes TDD and BDD methodologies

Read more
Optimizing WordPress Database
Optimizing WordPress – database optimization issues and solutions

The WordPress database, how it’s used, what are the limits, and how to overcome them, while performing database optimization.

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?

wp
woo
php
node
nest
js
angular-2