pt-table-checksum工具使用報(bào)錯(cuò)一例

圖片來(lái)自Percona官網(wǎng)

今天同事在用?percona toolkit?工具中的?pt-table-checksum?對(duì)主從數(shù)據(jù)庫(kù)進(jìn)行校驗(yàn)，提交命令后，一直提示下面的信息：

Pausing because Threads_running=0

看字面意思是在提示當(dāng)前活躍線程數(shù)為0，但為什么不繼續(xù)執(zhí)行呢。這個(gè)提示信息有點(diǎn)含糊其辭，該工具是用Perl寫的，因此直接打開(kāi)看腳本跟蹤一下，大概就明白怎么回事了，原來(lái)是這個(gè)工具有負(fù)載保護(hù)機(jī)制，避免運(yùn)行時(shí)對(duì)線上數(shù)據(jù)庫(kù)產(chǎn)生影響。

和這個(gè)機(jī)制相關(guān)的參數(shù)名是：?–max-load，其類型是：Array，用法是一個(gè)或多個(gè)?variables = value?組成的判斷條件，然后根據(jù)這個(gè)規(guī)則判斷某些條件是否超標(biāo)。例如，設(shè)定?–max-load=”Threads_running=25″，意思是當(dāng)前活躍線程數(shù)如果超過(guò)25，就暫停 checksum 工作，直到活躍線程數(shù)低于 25。

因此，在我們這個(gè)案例中，想要強(qiáng)制讓 table-checksum 繼續(xù)工作的話，可以設(shè)定 –max-load 的值，例如：

pt-table-checksum --max-load="Threads_running=25" ...其他選項(xiàng)...

或者

pt-table-checksum --max-load="Threads_connected=25" ...其他選項(xiàng)...

前面的選項(xiàng)意思是判斷活躍線程數(shù)不要超過(guò)25個(gè)，后面的選項(xiàng)意思是當(dāng)前打開(kāi)的線程數(shù)不要超過(guò)25個(gè)。

下面是 pt-table-checksum 幫助手冊(cè)里的一段話：

–max-load
type: Array; default: Threads_running=25; group: Throttle

Examine SHOW GLOBAL STATUS after every chunk, and pause if any status variables are higher than the threshold. The option accepts a comma-sep-
arated list of MySQL status variables to check for a threshold. 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_con-
nected 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 checksum 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 checksum query finishes. If you specify a threshold for this vari-
able, 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.