Overview

While updating the application to the latest version, the database structure is updated as part of the process. Updating the database schema by running delta files via MySQL causes a block for data modification and, thus, makes it impossible to read and write data in the altered tables. This leads to server performance downtime that may extend over 12 hours when altering large SQL tables. In order to alter tables with large amounts of data without locking them and reduce the downtime period to less than 30 minutes, the application deployment process may be performed using PT-ONLINE-SCHEMA-CHANGE (also referred to as PT-ONLINE, PT-OSC) tool. PT-ONLINE-SCHEMA-CHANGE is a software product included in the toolkit designed by Percona. This tool is used to alter tables without locking them by creating a temporary table (an empty copy of the original table) with the desired new structure, and then replacing the original one with the new one by renaming the original and new tables simultaneously. You can refer to the manufacturer’s documentation for more details.
The application of PT-ONLINE is limited: it works only with an ALTER table operation. UPDATE and INSERT operations can be executed only via MySQL. Thus, there are two ways to perform the update of SQL schema:

1) using the combination of PT-ONLINE-SCHEMA-CHANGE for altering large SQL tables, and MySQL for the rest of operations;

2) using MySQL for operations of all types.

Preliminary Steps

In order to perform an update of the SQL schema, you should configure the active database node in the following way:

1) Make sure that the server parameters meet these minimum hardware requirements:

• Free disc space (HDD/SSD) must exceed the volume of the largest database table that is going to be altered*

the exaсt numbers will be provided by UT's support team after pre-deployment checks.
2) Install Percona toolkit (v3.0.3 released 2017-05-18)* following the manufacturer's’ instructions:
3) To run PT-ONLINE-SCHEMA-CHANGE, grant to unipaya user PROCESS and REPLICATION SLAVE privileges using the following commands:

4) Create .my.cnf file in the Home folder assigned to the OS user. The file must contain the following parameters: [client]
user=[unipaya] password=[password]**
host=[host]*
port=[port]*
*database connection settings **if the database user password is not specified in the configuration file, you will have to enter it during each shell script execution after getting this message:

In case you have entered incorrect password, you will get the following message:

Concepts

Delta - the difference between two database schemas across the application releases.

The application is built supporting delta files in the following formats:

• - original (not optimized) delta file in shell script (.sh) format (considered offline by default);
• - optimized delta files: сlassified into ‘online’ and ‘offline’, and used for running database schema update with PT-ONLINE-SCHEMA-CHANGE utility.

Original delta file - a set of statements, each statement corresponding to one modification of a particular table. If a table was subject to several modifications, a separate statement is executed to apply each modification during the update.

Optimized delta file - a set of statements split into two groups: executed ‘Online’ (via PT-ONLINE) and ‘Offline’ delta files.

Online delta file - a set of statements altering large SQL tables. MySQL is locks the table when executing the ALTER table command on a large table causing server downtime. Therefore, the online delta contains statements that should be executed at a pre-deployment stage via PT-ONLINE-SCHEMA-CHANGE to avoid locks and downtime periods.

Offline delta file - a set of UPDATE, INSERT statements, and statements altering small SQL tables. Offline delta is executed as part of the deployment procedure and causes downtime up to 15 minutes.

Delta registry - new delta files are registered in iapp_update_log table. Thus, if the database schema update with pt-online has been interrupted, it is possible to restart the process. All statements that have been executed successfully, starting from the last statement that has been executed.

Please note that delta files in SQL format are no longer supported.

Updating database schema

Pre-deployment steps

Determining approximate downtime

1) Receive mysql.zip file from the UT support team. 2) Unzip the file on the server where the pre-deployment check is going to be executed.
3) Determine approximate downtime:

Restore a backup of production DB on MySQL instance.

-Grant permission for run_u.sh script execution by running the following command in the Linux console:

Indicate ‘online’ in the /mysql/run_u.sh file and execute it against the backup Prod DB.

Save /mysql/ run_u.log file once the execution process is complete.

Indicate ‘offline’ in the /mysql/run_u.sh file and execute it against the backup Prod DB.

Provide the results and logs to the UT support team: logs are mandatory for UT to optimize delta files.

Wait until the UT support team provides the following recommendations:

1) Recommended SQL schema update method (with or without PT-ONLINE-SCHEMA-CHANGE).
2) The optimal time when the online delta file should be executed (usually it’s 3-5 days before the scheduled deployment date).
3) Estimated original offline delta execution time.
4) Recommended free HDD/SSD volume.

Deployment steps

Updating MySQL schema with PT-ONLINE-SCHEMA-CHANGE

1.1. Executing online delta via PT-ONLINE-SCHEMA-CHANGE

1) Build the application in accordance with the deployment instructions.
2) Grant permission for run_u.sh script execution by running the following command in the Linux console:

3) Indicate ‘online’ in the output/{PROFILE-NAME}/mysql/run_u.sh file and execute it in the production database 3-5 days before the scheduled update (follow UT support recommendations).


4) Save output/{PROFILE-NAME}/mysql/ run_u.log file once the execution process is complete.

1.2. Executing offline delta via MySQL

Indicate ‘offline’ in the output/{PROFILE-NAME}/mysql/run_u.sh file and execute it against the production database during the deployment process

Updating MySQL schema without PT-ONLINE-SCHEMA-CHANGE

In case there is no need to apply PT-ONLINE-SCHEMA-CHANGE, indicate ‘original’ in the output/{PROFILE-NAME}/mysql/run_u.sh file and execute it against the production database during the deployment process.

Comparison of database schema update with and without PT-ONLINE-SCHEMA-CHANGE: