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
responsive web design
What is responsive web design?

Read more
practical applications of design patterns
Practical Application of Design Patterns

The use of SOLID, KISS and other guidelines not only help maintain order in the code itself, but also in communication between programmers.

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
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
How to migrate system?
How Do You Migrate Systems?

Well-prepared system migrations can go smoothly and exactly as planned, or just the opposite, they can go sideways. They can take minutes, or they may drag on for hours or even days. In the worst case, everybody involved is unhappy and the system is frozen.

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