The 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
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!
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.