Some places to be aware of when modifying table structures in Mysql _mysql

MYSQL may interrupt the normal operation of the product when modifying the table structure to affect the user experience, even worse results, loss of data. Not all database administrators, programmers, and system administrators know very well that MySQL can avoid this. DBAs often run into this kind of production disruption when the upgrade script modifies the application and database tiers, or the inexperienced administrator, develops a specification file that is not well understood within the MySQL internal working mechanism.

The truth is:

• Lock the table in the process of modifying the table structure directly (before version 5.6)
• The online data definition language is not always online in version 5.6 and also locks the table
• Even using the Percona toolkit (modifying the definition file online) There are several steps that lock the table

Percona the MySQL Server development team encourages users to communicate with us first when planning or performing a database migration. Our goal is to give the best possible solution based on the various situations given by the user. Designed to avoid locking tables when users perform DDL on very large tables to ensure that the application works as usual, while also trying to improve response time or increase system functionality. The worst case scenario is to make sure those systems that don't stand up to the machine work properly during the gold trading hours.

Most of the packages we use are still less than Mysql5.6, which requires us to constantly try out a new installation environment to minimize the damage caused by database migrations. This may require an "online modification of the specification definition file" tool to upgrade or modify the specification file. Mysql5.6 solves this problem by reducing the scenario of rebuilding tables and locking tables, but this method does not cover all possible operations, such as the need for full table refactoring when modifying a column of data types. Przemys?aw and Malkowski discussed the definition of modification in Mysql5.6 run as thoroughly as possible last year.

• With the new features of MySQL 5.7, we seek DDL operations that do not lock the table for example; Table optimization and index renaming. (More info)

For Mysql5.6 users, the best advice is to review the number matrix to familiarize yourself with the changes that are being made outside of MySQL, and the good news is that we are very good at solving the problem.

To be honest, lock-table operations are often overlooked, and we tend to modify the table in 30M sizes more directly, but the 30g,300g table is considered. It may be better to operate directly when the usage rate is low or if the locking time requirement is not high. However, we often encounter a SQL that needs to be executed immediately, or because of a performance problem requiring an urgent increase in the number of calls to reduce load time.

Whether you need to modify the table definition in the system online period

As mentioned above, the online modify table definition is a module in the workflow. It's usually a good solution, but you'll also encounter situations where you can't use it, such as when a table uses a trigger. It's important to understand how PT-OSC works in our project, so let's take a look at the source code:

Copy Code code as follows:

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

# Step 2: Modify the emptying table. This should be faster,
# Step 3: Create triggers to capture changes to the original table <--(lock meta data)

# Step 4: Copy the data.
# Step 5: Rename table: <--(lock meta data

# Step 6: Update the foreign key if it is a child table.

# Step 7: Delete old tables.

I highlight the third step to step fifth above, which is the time that the lock table may cause system downtime. However, the design of a foreign key update in step six is a circular operation, avoiding the implicit rebuilding of the table when updating the relationship. There are a number of ways to ensure table integrity constraints, as detailed in the Pt-osc documentation, to preview your table structure including constraints before you begin, and to know how to minimize the impact of modifying table definitions.

Recently, we have informed a user with a high concurrency high transaction volume system to run Pt-osc on large data tables. It was very common for them, and a few hours later, our customer service was told that the customer had encountered a problem that exceeded the maximum number of connections. How did this problem arise? When Pt-osc runs to step five, it tries to lock the data and renames the original and hidden tables, but this does not execute immediately when the transaction is opened, so this thread is queued for renaming. This performance in the user application is the system downtime. The database cannot open a new connection and all threads are blocked after renaming the command.

The description of the 5.5.3 version locks the data for all tables it uses (not relying on the storage engine) when a transaction is opened, and releases the lock when the transaction is committed. This ensures that the table's definition cannot be modified during transaction opening.

In the long run we can adopt some new techniques to avoid this situation, such as Non-default pt-osc option, in other words, do not delete the original table to change the data to the new table. This union has been detached from hidden tables and triggers, and we should encourage the renaming of operations to become atomized.

Revised: The 2.2 version of the Percona tool adds a variable –tries and variable –set-vars that are deployed together to solve situations where various PT-OSC operations may lock the table. Pt-osc (–set-vars) defaults to setting the following session variables when connecting to the database server.

Copy Code code as follows:

wait_timeout=10000
Innodb_lock_wait_timeout=1
Lock_wait_timeout=60

When using –tries we can granular identification of the operation, the number of attempts, at the interval of the attempt to wait. This combination ensures that Pt-osc kills its own waiting session process at the right time, ensures that the thread stack is idle, and provides us with a circular operation to get the locks that are caused by triggers, renaming, and modifying foreign keys.

Copy Code code as follows:

–tries swap_tables:5:0.5,drop_triggers:5:0.5

Description document here Http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html#cmdoption-pt-online-schema-change–tries

It explains that even with tools such as Pt-osc, it's important to fully understand the issues you want to solve. The following flowchart will help you when you understand the considerations for modifying the structure of the MySQL database. Please read the suggestions carefully although some of the diagrams are not marked out, such as disk space, Io loading, and so on.

Select the appropriate DDL operation

Make sure you have a clear idea of how the table structure will affect your system and choose the right way to minimize the impact. Sometimes this means you need to postpone the changes until the system is not used frequently or use tools that do not lock the table during the operation. When you have triggers in your table, you modify the table structure directly.

• -Most of the time Pt-osc is exactly what we need.
• -In many cases pt-osc is needed, but the usage needs to be adjusted slightly
• -In a few cases pt-osc is not very appropriate, we need to consider the local blocking modification, or take the transfer of operations to replicate in the replica set.