• notice
  • Congratulations on the launch of the Sought Tech site

Some places that need to be paying attention to when modifying the table structure in mysql

MySql may interrupt the normal operation of the product when modifying the table structure, affect the user experience, or even worse, lose data.Not all database administrators, programmers, system administrators know very well that Mysql can avoid this situation.DBAs often encounter this production outage, when upgrade scripts modify the application layer and database layer, or inexperienced administrators, developers modify the specification files without knowing much about the inner workings of Mysql.

The truth is:

  • The table will be locked in the process of directly modifying the table structure (before version 5.6)
  • Online DDL in version 5.6 is not always online and also locks tables
  • Even using the Percona toolkit (modifying definition files online) there are several steps that will lock the table

Percona   The server development team encourages users to communicate with us before planning or executing a database migration.Our goal is to give the best solution based on the various situations given by the user.Designed to avoid table locks when users perform DDL on very large tables, to ensure that the application runs as normal, while also working to improve response time or increase system functionality.The worst-case scenario is to ensure that systems that cannot survive downtime are up and running during prime trading hours.

Most of the installation packages we use are still smaller than Mysql5.6, which requires us to keep trying 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 scene of rebuilding the table and locking the table, but this method cannot cover all possible operations.For example, when the data type of a column is modified, a full table reconstruction is necessary.Przemys?aw and Malkowski discussed in the last year as much detail as possible modifying definitions on the fly in Mysql 5.6.

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


For users of MySQL 5.6, the best advice is to review the number matrix to familiarize yourself with implementing changes to definitions outside of MYSQL, the good news is that we're pretty good at fixing that.

To be honest, the lock table operation will often be overlooked.When operating a 30M table, we are more inclined to modify it directly, but 30G and 300G tables need to be considered.Direct operation may be better when the usage is not high or the lock time requirement is not very high.However, we often encounter a SQL that needs to be executed immediately, or because of performance issues, an index needs to be added urgently to reduce load time.


Whether the table definition needs to be modified while the system is online

As mentioned above, online modification of table definitions is a module in the workflow.Usually a good solution, but there are also occasions where it cannot be used, for example: when a table uses a trigger.It's important to understand how pt-osc works in our project, let's take a look at the source code:
 

The code is as follows:
[moore@localhost]$ egrep 'Step' pt-online-schema-change
# Step 1: Create a new table
 
# Step 2: Modify the empty table.This should be faster,
# Step 3: Create a trigger to capture changes to the original table <--(lock metadata)
 
# Step 4: Copy the data.
# Step 5: Rename table: <--(Lock metadata
 
# Step 6: Update foreign key if it is a child table.
 
# Step 7: Delete the old table.

 I've highlighted steps 3 to 5 above, which are times when the lock table may cause system downtime.But step 6 design foreign key update is a circular operation, is to avoid implicitly rebuilding the table when updating the relationship.There are many ways to ensure table integrity constraints, detailed in the pt-osc documentation, preview your table structure including constraints before you begin, and know how to minimize the impact of changing the table definition.


Recently, we notified a user with a high-concurrency, high-transaction system to run pt-osc on large data tables.It was a common occurrence for them, and after a few hours our customer service was told that the customer had a problem with the maximum number of connections being exceeded.How did this problem arise? When pt-osc runs to step 5, it will try to lock the data and rename the original and hidden tables, but this will not be executed immediately when the transaction is opened, so this thread will be queued after the rename.This is manifested as system downtime in user applications.The database cannot open new connections and all threads are blocked after the rename command.

 

    The description of version 5.5.3, when opening a transaction, it will lock the data of all tables it will use (not dependent on the storage engine), and release the lock when the transaction commits.Doing so ensures that the table definition cannot be modified during the open transaction.


In the long run, we can use some new technologies to avoid this situation, such as the option of non-default pt-osc, in other words, the original table will not be deleted and the data will be changed to the new table.This union breaks away from hidden tables and triggers, and we should encourage renaming operations to be atomic.

Revision: A new variable has been added to the percona tool in version 2.2

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

lipitor 80mg drug & lt;a href="https://lipiws.top/"& gt;atorvastatin 20mg pill& lt;/a& gt; buy lipitor 10mg for sale

Vuqfex

2024-03-07

Leave a Reply

+