How to avoid the problem that the table cannot be used when MySQL modifies the table structure

When modifying the table structure, MySQL may interrupt the normal operation of the product and affect the user experience, or even worse, resulting in data loss. Not all database administrators, programmers,

When modifying the table structure, MySQL may interrupt the normal operation of the product and affect the user experience, or even worse, resulting in data loss. Not all database administrators, programmers,

When modifying the table structure, MySQL may interrupt the normal operation of the product and affect the user experience, or even worse, resulting in data loss. Not all database administrators, programmers, and system administrators know that MySQL can avoid this situation. DBAs often encounter this kind of production interruption. When the upgrade script modifies the application layer and database layer, or the inexperienced administrator or developer has modified the standard file without having a good understanding of the internal working mechanism of Mysql.

The truth is:

The table is locked when the table structure is directly modified (before version 5.6)

The online data definition language version 5.6 is not always online and also locks the table.

Even if you use the Percona Toolkit (online definition file modification), several steps will lock the table.

Percona MySQL Server Development Team encourages users to communicate with us before planning or performing database migration. Our goal is to provide the best solution based on various situations provided by users. It is designed to avoid locking tables when users execute DDL statements on very large tables to ensure that applications can run normally as usual, while also striving to improve response time or add system functions. The worst case is to ensure that those systems that cannot handle the crash run normally during the golden trading period.

Most of the installation packages we use are still less than MySQL5.6, which requires us to constantly try new installation environments to minimize the loss caused by database migration. This may require a tool that can "Modify the specification definition file online" to upgrade or modify the specification file. MySQL5.6 solves this problem by reducing the scenario of table reconstruction and table lock, but this method cannot cover all possible operations, for example, when modifying the Data Type of a column, full table reconstruction is required. Przemysaw and malkoski discussed how to modify the definition in Mysql5.6 last year in as much detail as possible.

With the new features of MySQL 5.7, we are looking for DDL operations that do not lock tables, such as table optimization and index rename. (More info)

For Mysql5.6 users, it is recommended that you review the Number Matrix to familiarize yourself with the changes made outside of MYSQL. The good news is that we are very good at solving this problem.

To be honest, the lock table operation is often ignored. When operating a 30 m table, we prefer to directly modify it, but the 30G, G table should be considered. When the usage is not high or the lock time requirement is not high, direct operations may be better. However, we often encounter an SQL statement that needs to be executed immediately, or an index needs to be urgently added due to performance problems to reduce loading time.

Need to modify the table definition during system online period?

As mentioned above, modifying table definitions online is a module in a workflow. It is usually a good solution, but it may also be unavailable, for example, when a table uses a trigger. It is important to understand the working process of pt-osc in our project. Let's take a look at the source code:

[Moore @ localhost] $ egrep 'step' pt-online-schema-change
# Step 1: Create a new table

# Step 2: Modify and clear the table. This should be faster,
# Step 3: Create a trigger to capture changes to the original table <-- (Lock metadata)

# Step 4: Copy data.
# Step 5: rename a table: <-- (Lock metadata

# Step 6: update a foreign key if it is a sub-table.

# Step 7: Delete the old table.

I highlight step 3 to Step 5 above, which is the time when the lock table may cause system downtime. However, in step 6, designing a foreign key update operation is a loop operation that avoids implicit table reconstruction when updating the relationship. There are many ways to ensure table integrity constraints, which are described in the pt-osc instruction document. Preview your table structure before you start, including constraints, and know how to minimize the impact of table definition modification.

Recently, we have notified a user with a high concurrency and high transaction volume system to run pt-osc on large data tables. This is common for them. A few hours later, our Customer Service was told that the customer had encountered a problem where the maximum number of connections had exceeded. How did this problem occur? When pt-osc runs to step 5, it tries to lock the data and rename the original table and hide the table. However, this will not be executed immediately when the transaction is started, therefore, this thread will be placed behind the rename. This is manifested in system downtime for user applications. The database cannot start new connections and all threads are blocked after the RENAME Command.

Data locked

5.5.3 indicates that when a transaction is started, the data of all tables used by the transaction will be locked (not dependent on the storage engine), and the lock will be released when the transaction is committed. This ensures that the table definition cannot be modified during the start of the transaction.

In the long run, we can use some new technologies to avoid this situation, such as the non-default pt-osc option. In other words, we will not delete the original table and change the data to the new table. This combination is separated from a hidden table and trigger. We should encourage atomic renaming.

Revision: In percona 2.2, a variable-tries and variable-set-vars were added to the tool for co-deployment, which solved the possibility of various pt-osc operations locking tables. By default, pt-osc (-set-vars) sets the following session variables when connected to the database server.

Wait_timeout = 10000
Innodb_lock_wait_timeout = 1
Lock_wait_timeout = 60

When we use-tries, we can identify the operation in a granular manner, the number of attempts, waiting at the interval of attempts. This combination ensures that pt-osc kills its own waiting session process at the right time and ensures that the thread stack is idle, it also provides cyclic operations to obtain and manage the locks caused by triggers, renaming, and modifying Foreign keys.

-Tries swap_tables: 5: 0.5, drop_triggers: 5: 0.5

The instruction is here

It explains that even if you use tools such as pt-osc, it is important to fully understand the issues you want to solve. The following flowchart will help you understand the precautions for modifying the structure of the MYSQL database. Please read the suggestions carefully, even though some figures are not marked, such as disk space and I/O loading.

Select appropriate DDL operations

Make sure that you have a clear understanding of the impact of modifying the table structure on your system, and select an appropriate method to minimize this impact. Sometimes this means that the changes need to be extended until the system is not frequently used or the tool that can lock the table during the operation is used. When a trigger exists in your table, you can directly modify the table structure.

-In most cases, pt-osc is exactly what we need.

-In many cases, pt-osc is required, but the usage needs to be slightly adjusted.

-In a few cases, pt-osc is not very suitable. We need to consider local blocking modifications, or change the transfer operation to copy in replica sets.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------