Shulou(Shulou.com)06/01 Report--

Pt-online-schema-change recently used pt-online-schema-change for online DDL of large online tables and found several problems.

The sentences I use are as follows:

Pt-online-schema-change-- user=root-- password= "xxxxx"-- host=192.168.xx.xx dumped Mirrorxx revising tasking Trouxx-- alter "ADD Fxxxxx'"-- charset=utf8-- no-check-replication-filters-- alter-foreign-keys-method=auto-- recursion-method=none-- print-- execute

During execution, a library was interrupted with the following error message:

Copying `table_ 01005`.`T _ xxx`: 19% 16:30 remain

Copying `table_ 01005`.`T _ xxx`: 21% 16:21 remain

Copying `table_ 01005`.`T _ xxx`: 22% 16:58 remain

2014-11-04T18:20:25 Dropping triggers...

DROP TRIGGER IF EXISTS `table_ 01005`.`pt _ osc_table_01005_T_xxx_ del`

DROP TRIGGER IF EXISTS `table_ 01005`.`pt _ osc_table_01005_T_xxx_ upd`

DROP TRIGGER IF EXISTS `table_ 01005`.`pt _ osc_table_01005_T_xxx_ ins`

2014-11-04T18:20:28 Dropped triggers OK.

2014-11-04T18:20:28 Dropping new table...

DROP TABLE IF EXISTS `table_ 01005`.` _ Trouxxxx _ new`

2014-11-04T18:20:30 Dropped new table OK.

`table_ 01005`.`T _ xxx` was not altered.

2014-11-04T18:20:25 Error copying rows from `table_ 01005`.`T _ xxx` to `table_ 01005`.` _ Trouxxxxnew`: Threads_running=199 exceeds its critical threshold 50.

Then it is interrupted, and this Threads_running is the number of active threads. According to this error prompt, I checked the percona document.

Simply look at the bug list according to the error prompt and find a bug, which is a bug of the tool itself according to the phenomenon. It happens to be the same as the version we use.

Then I will upgrade the version, come again after that, and then reproduce it, which shows that it is not as simple as bug. Or look at the error message again, indicating that Threads_running exceeds the warning threshold. Since it's a threshold.

That can be set, find the official website document to take a closer look, there is a parameter to pay attention to,-critical-load. The document is explained as follows:

-- critical-load

Type: Array; default: Threads_running=50

Examine SHOW GLOBAL STATUS after every chunk

And abort if the load is too high. The option accepts a comma-separated list of MySQL status variables and thresholds.

An optional = MAX_VALUE (or: MAX_VALUE) can follow each variable. If not given

The tool determines a threshold by examining the current value at startup and doubling it.

See-- max-load for further details. These options work similarly

Except that this option will abort the tool's operation instead of pausing it

And the default value is computed differently if you specify no threshold.

The reason for this option is as a safety check in case the triggers on the

Original table add so much load to the server that it causes downtime.

There is probably no single value of Threads_running that is wrong for

Every server, but a default of 50 seems likely to be unacceptably high

For most servers, indicating that the operation should be canceled immediately.

The general meaning is as follows:

Before and after each chunk operation, the change of the specified state quantity is counted according to the show global status. The default is statistical Thread_running.

The goal is to protect against excessive load caused by triggers on the original table. This is also to prevent the impact of online DDL on the online.

If you exceed the set threshold, the operation will be terminated and the online DDL will be interrupted. The exception prompted, such as reporting the wrong information.

There is a similar parameter to this-- max-load:

-- max-load

Type: Array; default: Threads_running=25

Examine SHOW GLOBAL STATUS after every chunk, and pause if any status variables are higher than their thresholds.

The option accepts a comma-separated list of MySQL status variables. An optional = MAX_VALUE (or: MAX_VALUE) can

Follow each variable. If not given, the tool determines a threshold by examining the current value and increasing it by 20%.

For example, if you want the tool to pause when Threads_connected gets too high, you can specify "Threads_connected"

And the tool will check the current value when it starts working and add 20% to that value. If the current value is 100

Then the tool will pause when Threads_connected exceeds 120, and resume working when it is below 120 again. If you want to

Specify an explicit threshold, such as 110, you can use either "Threads_connected:110" or "Threads_connected=110".

The purpose of this option is to prevent the tool from adding too much load to the server. If the data-copy queries are

Intrusive, or if they cause lock waits, then other queries on the server will tend to block and queue. This will typically

Cause Threads_running to increase, and the tool can detect that by running SHOW GLOBAL STATUS immediately after each query finishes.

If you specify a threshold for this variable, then you can instruct the tool to wait until queries are running normally again. This will

Not prevent queueing, however; it will only give the server a chance to recover from the queueing. If you notice queueing, it is best to decrease the chunk time.

The-- max-load option defines a threshold, and after each chunk operation, check to see if the show global status status value is higher than the specified threshold. This parameter accepts a mysql status state variable and a threshold

If no threshold is given, a threshold is defined as 20% higher than the current value.

Note that this parameter does not terminate the operation like-- critical-load, but just pauses the operation. When the status value is lower than the threshold, continue to operate.

Whether to suspend or terminate the operation is the difference between-- max-load and-- critical-load.

The parameter value is in the form of a list, and you can specify the state value in which the show global status appears. For example, Thread_connect and so on.

The format is:-- critical-load= "Threads_running=200" or-- critical-load= "Threads_running:200".

According to the parameter requirements, the threshold of Threads_runnings is modified and executed successfully. So the document is still very important, before using a tool, the document must be read.

It is not just a simple use, but must deal with the exception errors that may occur in the process of use.

Several considerations:

The tool refuses to operate if it detects replication filters. See-[no] check-replication-filters for details.

The tool pauses the data copy operation if it observes any replicas that are delayed in replication. See-- max-lag for details.

The tool pauses or aborts its operation if it detects too much load on the server. See-max-load and-critical-load for details.

The tool sets its lock wait timeout to 1 second so that it is more likely to be the victim of any lock contention, and less likely to disrupt other transactions. See-- lock-wait-timeout for details.

The tool refuses to alter the table if foreign key constraints reference it, unless you specify-- alter-foreign-keys-method.

The tool cannot alter MyISAM tables on "Percona XtraDB Cluster" nodes.

Pt-online-schema-change online DDL tool, although it will not lock the table, but it still has a certain impact on performance, the implementation process of the whole table to do a select.

This process exchanges all the active data in the buffer_cache, which results in requests for active data from disk, resulting in an increase in slow SQL and an increase in file_reads.

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.