Home > Guides

Guide Contents

Database Schema Update v1.0

Added on:  09/06/19     Updated on:  08/11/20
Table of Contents

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:

grant PROCESS on *.* to unipaya@'%';
grant REPLICATION SLAVE on *.* to unipaya@'%';
flush privileges;

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:

No password for mysql is found in file /home/Stanislav/.my.cnf. Enter it manually:


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

Authentication to MySQL failed. Try to re-enter password:

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:

    chmod +x run_u.sh

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

  • ./run_u.sh online

    We recommend using a terminal multiplexer to execute the online delta file (for example, tmux). This allows keeping the Linux terminal session running in case of connection issues.


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

    Note: you should save run_u.log file after each run_u.sh file execution, since the content of the log is removed after each session.


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

  • ./run_u.sh offline

  • 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:

    chmod +x run_u.sh

    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).

    ./run_u.sh online

    We recommend using terminal multiplexer to execute the online delta file (for example, tmux. This allows keeping the Linux terminal session running in case of connection issues.


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

    Note: you should save run_u.log file after each run_u.sh file execution, since the content of the log is removed after each session.

    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

    ./run_u.sh offline

    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.

    ./run_u.sh original

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



















    Database schema update with PT-ONLINE Database schema update without PT-ONLINE
    Delta file type Online delta Offline delta Offline delta
    Parameter value in run_u.sh file online offline original
    Delta file name 301-update-db_online.sh 401-update-db_offline.sh
    701-update-db_manual.sql
    201-update-db.sh
    When the file is executed? pre-deployment stage within the deployment process within the deployment process
    File execution time up to 5 days up to 15 minutes up to 12 hours
    Server downtime period no up to 15 minutes up to 12 hours
    Execution mode via PT-ONLINE via MySQL via MySQL
    Operations ALTER TABLE, CREATE INDEX (large tables) ALTER TABLE (small tables)
    UPDATE
    INSERT
    ALTER TABLE (both large and small tables)
    UPDATE
    INSERT

    Appendix 1. output/{PROFILE-NAME}/mysql folder structure

    output/{PROFILE-NAME}/mysql folder contains the following files:

    1) run_u.sh - shell script used to perform database schema update in one of the selected modes: online, offline, original.

    2) support scripts to run database schema update

  • 101-generate-cleanup-script.sh - generates cleanup scripts to remove constraints and triggers in case online delta execution was interrupted and the process must be restarted.

  • 102-functions.sh - all statements are split up into those executed online and offline by function.sh script.

  • 3) original and optimized delta files in shell script format:

    Offline Online
    Original delta file 201-update-db.sh
    Optimized delta files 401-update-db_offline.sh 301-update-db_online.sh


    3) ‘manual-all-in-one’ procedure in SQL format:
    output/{PROFILE-NAME}/ mysql/701-update-db_manual.sql
    ‘manual-all-in-one’ procedure will also be put in shell script format in 8.1 release.

    4) SQL scripts creating users necessary for the application running:
    output/{PROFILE-NAME}/ mysql/ 601-create-users.sql

    5) SQL scripts creating ‘clean’ database:

    output/{PROFILE-NAME}/ mysql/602-create-db-unicharge.sql
    output/{PROFILE-NAME}/mysql/603-create-db-unibill.sql


    Monitoring and troubleshooting
    Online delta execution performed via PT-ONLINE can take several days. Thus, it is necessary to monitor the execution process in the Linux console. In case any errors occur, the process can be restarted. Before you restart the online delta execution, it’s necessary to remove