Shulou(Shulou.com)05/31 Report--

The main content of this article is to explain how the MySQL table is changed by DDL. Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "how the MySQL table is changed by DDL".

DDL in MySQL

Overview of DDL

There are many forms of DDL statements in MySQL, which can be summarized as follows: CREATE,ALTER,DROP,RENAME,TRUNCATE.

These operations are implicitly committed and atomic, either successful or failed, and DDL operations were not logged before MySQL 8.0.

Today, let's talk about the database structure changes related to the release of the system version, mainly ALTER TABLE changes, and the DDL change process is similar to ordinary DML changes, as shown below.

Note: here involves the basic knowledge of MySQL, friends who do not know can refer to my basic chapter of MySQL.

In the early version of MySQL, DDL changes would cause the whole table to be locked, block the DML operation on the table, and affect the normal operation of the business. The good thing is, with the iteration of the MySQL version, the execution mode of DDL is also changing.

MetaData metadata

The MetaData of MySQL is the same as other RDBMS databases. The structure information of the described objects is stored under information_schema architecture, such as common TABLES, COLUMNS, etc. The following example is that creating a table crm_users,MySQL automatically inserts data into related data dictionary tables such as Information_schema.tables and columns. These data are called metadata and are generally static. Only DDL operations occur on the table will be updated in real time.

MetaData Lock

MySQL uses MetaData Lock to manage object access and ensure data consistency. For some core business tables, DML operations on the table are more frequent, and adding fields may trigger MetaData Lock.

You can see the Waiting for table metadata lock wait event, thread 155is executing the alter table waiting for the select lock to be executed by thread 154to release the lock, because DML will hold the SHARED_READ lock during execution, and the SHARED_UPGRADABLE (shared upgradeable lock, abbreviated to SU, allowing concurrent updates and reading of the same table) lock was successfully acquired when DDL was executed, but failed to acquire the EXCLUSIVE MetaData Lock lock and was in a suspended PENDING state.

DDL execution mode

As you can see from the MySQL official documentation, ALTER TABLE has many options, and the main performance-related options are ALGORITHM and LOCK.

In the early change mode of ALGORITHM OPTIONDESCRIPTIONCOPYMySQL, you need to create a modified temporary table, then copy the original table data to the temporary table according to the data row, and rename the rename to complete the creation. During this period, concurrent DML operations are not allowed. The original table is readable and unwritable, and double the disk space is required. INPLACE modifies the original table directly. There is no need to create a temporary table to copy data and rename it. The original table will hold Exclusive Metadata Lock, usually allowing concurrent DML operations. INSTANTMySQL 5.8starts to support, only modify metadata in the data dictionary, table data is not affected, there is no Exclusive Metadata Lock during execution, and concurrent DML operations are allowed.

As can be seen from this table, MySQL has been optimizing the execution mode of DDL in order to improve the efficiency of DDL execution, reduce lock waiting, do not affect table data, and do not affect normal DML operations.

LOCK option

LOCK OPTiONDESCRIPTIONDEFAULT default mode: MySQL automatically selects LOCK mode without locking the table as much as possible according to the operation. NONE lock-free: allows concurrent read and write operations during Online DDL. If the Online DDL operation does not support concurrent DML operations on the table, the DDL operation fails and the table modification is invalid. SHARED shared lock: reads are not affected and writes are blocked during Online DDL operations. EXCLUSIVE exclusive lock: no operation on the lock table is allowed during the Online DDL operation.

The following examples illustrate the implementation process of these ways, first create a test table and create some data.

COPY

The change process in COPY mode is as follows:

According to the business needs, you need to add a field user_type to crm_users and execute the changes in COPY mode.

As can be seen from the execution process and profile, the temporary table # sql-564_85 will be created through COPY, the System Lock will be obtained, the data will be copied to the temporary table, and finally the rename table name will be switched to release Lock resources. Concurrent DML operations are not supported during execution.

INPLACE

The INPLACE mode is directly modified on the original table. For operations such as adding indexes, adding / deleting columns, and modifying field NULL/NOT NULL attributes, you need to modify the data records within the MySQL and rebuild the table (Rebuild Table).

As you can see from the execution process, you need to get the Exclusive Metadata Lock, modify the table data, release the Lock, and support concurrent DML operations during execution.

INSTANT

DDL only modifies metadata in the data dictionary, without Exclusive Metadata Lock, allows concurrent DML operations, and supports limited DDL changes, which currently mainly include adding fields, adding / deleting generated columns, modifying ENUM or SET columns, changing index types and renaming tables.

Compare the execution efficiency of these three ways.

Execution mode / amount of project data (w) execution time (s) rebuilding table modification MetaData modification Data allows concurrent DMLCOPY65029.89YESNoYesNoINPLACE65010.56YESNoYesYesINSTANT6500.19NoYesNoYes

ONLINE DDL

As of MySQL 8.0 COPY,INPLACE,INSTANT,MySQL online DDL, there are three ways COPY,INPLACE,INSTANT,MySQL will automatically choose which one to use according to the DDL executed. Generally, it will give priority to INSTANT. If it does not support it, choose INPLANCE. If it does not support it, you can only choose COPY.

The MySQL official document also gives the support matrix of Online DDL, and the commonly used DDL operations under the column. The comparison items mainly include whether to rebuild the table, allow concurrent DML operations and only modify metadata, and the table data will not be affected.

OperationInstantIn PlaceCopyRebuilds TablePermits Concurrent DMLOnly Modifies MetadataAdding a columnYesYes*YesNo*Yes*YesDropping a columnNoYesYesYesYesNoRenaming a columnNoYesYesNoYesYesSetting a column default valueYesYesYesNoYesYesDropping the column default valueYesYesYesNoYesYesChanging the auto-increment valueNoYesYesNoYesNoMaking a column NULLNoYesYesYes*YesNoMaking a column NOT NULLNoYesYesYes*YesNoAdding a primary keyNoYes*YesYes*YesNoDropping a primary keyNoNoYesYesNoNoCreating or adding a secondary indexNoYesYesNoYesNoDropping an indexNoYesYesNoYesYesRenaming an indexNoYesYesNoNoNoAdding a FULLTEXT indexNoYes*YesNo*NoNo

In the actual business system, the business development of the large table DDL scheme is relatively fast, the amount of data of the table is relatively large, and the read and write separation is done at the business level. at the same time, the MySQL data will be synchronized to the data warehouse (including real-time data warehouse and offline data warehouse). The actual database architecture is as follows.

Suppose this is a transaction system database, and the order table booking has 8000w data and is connected to real-time and offline warehouses. According to business needs, add a field to the order table booking. It is a high-risk operation to add a field before MySQL 5.7.It is necessary to fully consider the impact on the business, mainly in two aspects:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

In the read-write separation scenario, master-slave synchronization delay leads to inconsistency of business data

Real-time data warehouse ADB does not allow source-end MySQL table renaming. If you use COPY or pt-osc, gh-ost and other tools to rename table names, you need to delete the table from the data warehouse and reconfigure synchronization (full volume + increment), which will affect the data warehouse business.

ONLINE DDL mode

For versions of MySQL 5.6 to 5.7, you can use OnLine DDL to change. For large tables, the execution time will be very long. The advantage is that the DML operation on Master will not be affected, but it will cause master-slave delay.

If the added fields on the Master are executed for 20 minutes, the corresponding Slave will also be executed for 20 minutes. During this period, the Slave has been in a delayed state, which will cause business data inconsistency. For example, the user successfully placed an order in Master, and the user mistakenly thought that the order was not issued successfully due to Slave delay, and the user mistakenly thought that the order was not issued successfully due to network reasons.

This method will cause master-slave delay, but it will not affect the business of real-time data warehouses. According to the business situation, you can only choose to implement it during the business trough.

Pt-osc tool

In order to solve the impact of master-slave delay caused by DDL changes on business, you will want to use pt-osc (pt-online-schema-change) or gh-ost tools to do it. The implementation process and principles of these two tools are more or less the same. The change process is as follows (foreign keys are not allowed to be used according to MySQL specifications):

Create a new table with a modified data table structure for importing data from the source data table into the new table.

Create a trigger on the source table to record the operations that continue to modify the source data table after copying the data, and to perform these operations after the end of the data copy to ensure that the data will not be lost.

Copy data, copy data from the source data table to the new table.

Modify the child table related to the foreign key, and modify the child table associated with the foreign key according to the modified data.

The rename source data table is the old table, the new table rename is the source table name, and the old table is deleted.

Delete the trigger.

When performing pt-osc, you also need to obtain an Exclusive Metadata Lock. If a DML operation is in progress on the table during this period, the pt-osc operation will always be in a suspended PENDING state. At this time, the normal DML operation on the table will be blocked, the number of MySQL active connections will suddenly soar, the CPU utilization rate will be 100%, and the interfaces of the table you depend on will report errors, so choose to execute during the business trough. At the same time, monitor the MetaData Lock lock so that the business will not be affected. Let's take a look at an example:

D=trade, t=booking: database trade, table name booking.

-- chunk-size=1000: the number of rows of data per copy.

-- max-log = 1: make sure that the delay from the database does not exceed 1 second, and stop copying data if it exceeds the delay.

-- check-interval=2: means to continue copying data after waiting for 2 seconds.

-- recursion-method= "hosts": if you are not using the default port 3306, it is more reliable to use hosts to find slave libraries.

Generally speaking, the MySQL binlog format is that ROW,pt-osc will generate a large number of binlog in the process of copying data, which may also lead to master-slave delay, so it is necessary to control the size and frequency of each copy, and the concurrency of DML will be reduced during execution.

MySQL 8.0 change mode

Anyone who has used Oracle knows that DDL changes are all changes to metadata, and hundreds of millions of tables are DDL changes in Oracle in an instant.

Excitedly, MySQL 8.0 also introduces INSTANT, which really only modifies MetaData and does not affect table data, so its execution efficiency has little to do with table size. It is suggested that if the new system is launched with MySQL, try to use MySQL 8.0. the old database can also be upgraded to MySQL 8.0 for better performance.

The official document's interpretation of INSTANT:

INSTANT: Operations only modify metadata in the data dictionary. No exclusive metadata locks are taken on the table during preparation and execution, and table data is unaffected, making operations instantaneous. Concurrent DML is permitted. (Introduced in MySQL 8.0.12)

To solve not only the synchronization between master and slave, but also the problem of non-synchronization of rename data warehouse, only INSTANT can meet the demand.

Monitor DDL execution

Progress in the implementation of DDL changes in the table, very concerned about the progress of its implementation, before MySQL 5.7, there is no good tool to monitor, basically have to wait. In MySQL 8.0, you can monitor by opening performance_schema and opening events_stages_current events.

It is necessary to summarize the iterative process of DDL in the version of the business system. How to realize the smooth change of DDL without affecting the business and peripheral systems needs to be considered by integrating the characteristics of the system and evaluating the importance and priority. At the same time, it is also necessary to master the implementation mode of different MySQL versions of DDL, so that we can make a better choice.

For example, as mentioned above, when I am in the big data team, our businesses are separated from reading and writing, and are connected to the real-time data warehouse. The data warehouse does not support rename operation. In this case, you can choose to use ONLINE DDL during the business trough, which has the least impact on the business system and does not affect the data warehouse.

At this point, I believe you have a deeper understanding of "MySQL big table is how to change DDL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.