Pt-online-schema-change Error

Source: Internet
Author: User

Today, I failed to index a large table, as follows:

SELECT/*!40001 Sql_no_cache */' goods_id ' from ' rosegal_db '. ' Eload_goods ' Force INDEX (' PRIMARY ') WHERE (' goods_id ' > = ?)) ORDER BY ' goods_id ' LIMIT, 2/*next Chunk boundary*/

2016-01-05T23:32:28 dropping triggers ...

DROP TRIGGER IF EXISTS ' rosegal_db '. ' Pt_osc_rosegal_db_eload_goods_del ';

DROP TRIGGER IF EXISTS ' rosegal_db '. ' Pt_osc_rosegal_db_eload_goods_upd ';

DROP TRIGGER IF EXISTS ' rosegal_db '. ' Pt_osc_rosegal_db_eload_goods_ins ';

2016-01-05T23:32:28 Dropped triggers OK.

2016-01-05t23:32:28 Dropping new Table ...

DROP TABLE IF EXISTS ' rosegal_db '. ' _eload_goods_new ';

2016-01-05t23:32:28 Dropped new Table OK.

' rosegal_db '. ' Eload_goods ' is not altered.

2016-01-05t23:32:28 Error copying rows from ' rosegal_db ', ' eload_goods ' to ' rosegal_db '. ' _eload_goods_new ': Threads _RUNNING=52 exceeds its critical threshold

From the tip you can see that the threads_running exceeds the warning threshold, viewing the official documentation, there are two ways to set this parameter:

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.
A 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 would 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 was 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 a single value of threads_running that's wrong for
Every server, but a default of $ seems likely to is unacceptably high
For more servers, indicating that the operation should is canceled immediately.

The general meaning is as follows:
Each time before and after the chunk operation, a change in the state amount specified according to the show global status statistic, by default, is statistical thread_running.
The goal is to be secure, preventing triggers on the original table from causing excessive load. This is also to prevent online DDL impact on the line.
Exceeding the set threshold, the operation is terminated and the online DDL is interrupted. The exception to the prompt is the wrong information.

Type:array; Default:threads_running=25
Examine SHOW GLOBAL status after every chunk, and pause if any STATUS variables is 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 would check the current value when it starts working and add 20% to that value. If the current value is 100,
Then the tool would pause when threads_connected exceeds, and resume working if it is below-again. If you want to
Specify an explicit threshold, such as, you can use either "threads_connected:110" or "threads_connected=110".

The purpose of this option was to prevent the tool from adding too much load to the server. If the Data-copy queries is
Intrusive, or if they cause lock waits, then other queries on the server would tend to block and queue. This would typically
Cause threads_running to increase, and the tool can detect this by running SHOW GLOBAL STATUS immediately after each query Finishes.
If you specify a threshold for this variable and then you can instruct the tool to wait until queries is running normally AG Ain. This would
Not prevent queueing, however; It would only give the server a chance to recover from the queueing. If you notice queueing, it's best to decrease the chunk time.

The--max-load option defines a threshold that, after each chunk operation, checks to see if the show global status value is higher than the specified threshold. This parameter accepts a MySQL status variable and a threshold value,
If no threshold is given, a threshold value of 20% above the current value is defined.
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 down.
Whether to pause or terminate the operation this is the difference between--max-load and--critical-load.

The parameter values are in list form, and you can specify the status values that appear in Show global status. For example, Thread_connect and so on.
The format is as follows:--critical-load= "threads_running=200" or--critical-load= "threads_running:200".

Pt-online-schema-change Error

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.