How Can You Avoid The ALTER Table Downtime In MySQL

php mysql developmentThe production traffic can get interrupted if the alterations take place in MySQL Tables.

In the worst cases, there can be bad reviews from the customer and also the loss of revenue can take place. The database expert, developer and the system administrator must be aware of this problem to avoid the pitfall. The interruptions can occur while upgrading the scripts or when any inexperienced developer performs the schema change in MySQL.

Therefore, you are required to remember these few things:

  • Activate the MySQL ALTER table locks during the period of change
  • The online version of DDL in MySQL 5.6 may sometimes incur locks and may not perform the online process
  • The Percona Toolkit can also face interruptions while changing the schema online and the workloads can experience blocking

MySQL version 5.6 helps to address the issues in the cases where the tables get locked after re-building them. But sometimes this version cannot cover few risks. For example: when the data type gets changed for a full table, then re-building is required. With some new features in the MySQL 5.7, the DDL operations can be performed without any blocking. For example: OPTIMIZE, TABLE, RENAME INDEX. The MySQL 5.6 users can easily analyze the matrix where the schema changes can take place without any interruptions.

Use pt-osc or without pt-osc?

#Step 1: Generate the new table
#Step 2: Modify the new empty table very quickly
#Step 3: Produce all the triggers for capturing all the changes in the table which was original (lock metadata)
#Step 4: Then copy all the rows
#Step 5: You need to rename the tables orig > old, new> orig< (lock metadata)
#Step 6: Foreign key constraints are to be updated if there are child tables
#7 Step 7: Eliminate the old table

The foreign keys require special attention as they can be the real source for the downtime. If the rebuilding of the tables cannot be done properly, there might be interruptions and problems occurring in the server. The metadata locks were introduced in the level of 5.5.3. At the beginning of the transaction, the metadata locks can appear in any of the storage engines. Therefore, the tables cannot be altered if the transaction remains open.

The pt-osc functions can be used without any hesitation. The no-drop new table and no-swap tables can be used with a proper planning. With the toolkit of Percona 2.2 version, the –set-vars combined with the –tries variable are required to prevent the metadata lock. For example, the default operation of the pt-osc and –set-vars can be:

Wait_timeout = 1000
InnoDB_lock_wait_timeout= 1
Lock_wait_timeout= 50

When the value –tries is used you can easily identify which operation is taking place and can also wait between the intervals. So the pt-osc will ensure that the waiting session will not be much longer and the loop is to be provided for acquiring the locks of metadata.

-tries swap_tables 6 : 0.6 drop_triggers 6:0.6

Thus you can ensure the ALTER TABLE will definitely result in the uptime now. When you have triggers already installed in the table, the direct ALTER function can be of great help. You will require the exact pt-osc in most of the cases. In some cases, tweaking is required with the function of the pt-osc. Where the pt-osc function does not work; you need to consider the fallovers and the ALTER function during the native blocking. Thus, the ALTER TABLE requires a suitable platform to ensure the uptime.

Do you know some other ways for avoiding the downtime in the MySQL ALTER Tables? Share with us and let us ensure a smooth production traffic altogether!

WriterAuthor Bio:
Benn Roberts is a well known database expert who works with the developer and the system administrator for the smooth performance of SQL servers. Here, he is describing the methods for avoiding the downtime in the ALTER Tables of MySQL.


Check Also

Front-End Developer

Tips to Help You Become a Better Front-End Developer

Becoming a better developer is the goal of every developer in the world. However, this …

One comment

  1. Alexander Fernandise

    Thank you Benn for providing such a great information about avoiding the alter table downtime in mysql. This article explains the one way to avoid downtime, also there are many other ways to avoid it. Overall the article is good. If you get any problem with these steps you can take help of experts through remote dba support. Keep posting such articles on future. Keep it up.

Leave a Reply

Your email address will not be published. Required fields are marked *