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

Add Index

ALTER TABLE `FUND_PAY_TRADE_ RECORD`

ADD INDEX `IDX_PAY_THIRD_ ID` (`THIRD_ ID`) USING BTREE

Statistical table size 5.6G

Backup table:

Mysqldump-uroot-p-- master-data=2-- single-transaction-t xiaodai FUND_PAY_TRADE_RECORD > TRADE_RECORD0621.sql

Use the pt tool to change:

Pt-online-schema-change-- user=root-- password=-- alter= "ADD INDEX IDX_PAY_THIRD_ID (THIRD_ID) USING BTREE"

Error

Threads_running=52 exceeds its critical threshold 50

You can see from the prompt that Threads_running exceeds the warning threshold. There are two ways to set this parameter by looking at the official documentation:

-- 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.

-- 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".

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.