成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

基于行事件導(dǎo)致從庫(kù)運(yùn)行緩慢及解決方法

IT那活兒 / 2517人閱讀
基于行事件導(dǎo)致從庫(kù)運(yùn)行緩慢及解決方法

點(diǎn)擊上方“IT那活兒”公眾號(hào),關(guān)注后了解更多內(nèi)容,不管IT什么活兒,干就完了?。?!




問題現(xiàn)象
最近,在處理了一個(gè)從庫(kù)延遲問題,在分析期間,發(fā)現(xiàn)從庫(kù) SQL 線程在處理來自主庫(kù)二進(jìn)制日志的基于行的事件時(shí)無法跟上。
例如:
1)從庫(kù)復(fù)制狀態(tài)
mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
...
Master_Log_File: binlog.0000185
Read_Master_Log_Pos: 86698585
...
Relay_Master_Log_File: binlog.0000185
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Exec_Master_Log_Pos: 380
Relay_Log_Space: 85699128
...
Master_UUID: 98974e7f-2fbc-18e9-72cd-07003817585c
...
Retrieved_Gtid_Set: 98974e7f-2fbc-18e9-72cd-07003817585c:1055-1057
Executed_Gtid_Set: 7f42e2c5-3fbc-16e7-7fb8-05003715789a:1-2,
98974e7f-2fbc-18e9-72cd-07003817585c:1-1056
...
SQL 線程的 processlist 狀態(tài)可以是以下其中之一: 
  • 從中繼日志中讀取事件;

  • System lock 或是其他的一些狀態(tài)。

2)目前

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+------+---------+------+----------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+---------+------+----------------------------------+------------------+
...
|
  4 | system user |                 | NULL | Connect | 268 | Reading event from the relay log | NULL |
...
+----+-----------------+-----------------+------+---------+------+----------------------------------+------------------+



導(dǎo)致此類行為的原因以及注意事項(xiàng)
當(dāng) SQL 線程應(yīng)用基于行的事件更改時(shí),它必須找到被更新的確切行。使用主鍵,因?yàn)橹挥幸恍胁趴赡芫哂邢嗤闹麈I。
但是,如果從庫(kù)表上沒有主鍵,SQL 線程必須搜索整個(gè)表才能找到要更新或刪除的行。它重復(fù)搜索每個(gè)更新的行。這種搜索會(huì)占用大量資源(CPU 占用率最高可達(dá) 100%),又會(huì)導(dǎo)致從庫(kù)延遲。
對(duì)于 InnoDB 表,不能使用沒有主鍵的表的聚集索引來避免在整個(gè)表中搜索更新或刪除的行。
“隱藏”主鍵僅對(duì)每個(gè) MySQL 實(shí)例是唯一的,因此主庫(kù)和從庫(kù)通常不會(huì)對(duì)同一行的“隱藏”主鍵具有相同的值。



如何解決這個(gè)問題
最好的解決方案是確保所有表都有一個(gè)主鍵。這不僅確保 SQL 線程可以輕松找到需要更新或刪除的行,因?yàn)樗_保所有行都是唯一的。
如果無法在邏輯上為表添加自然主鍵,一個(gè)潛在的解決方案是添加一個(gè)自增無符號(hào)整數(shù)列作為主鍵。
通過下面查詢找到?jīng)]有主鍵的表:
SELECT tables.table_schema, tables.table_name, tables.table_rows
FROM information_schema.tables
LEFT JOIN (
SELECT table_schema, table_name
FROM information_schema.statistics
GROUP BY table_schema, table_name, index_name
HAVING
SUM(
CASE WHEN non_unique = 0 AND nullable != YES THEN 1 ELSE 0 END
) = COUNT(*)
) puks
ON tables.table_schema = puks.table_schema AND tables.table_name = puks.table_name
WHERE puks.table_name IS NULL
AND tables.table_schema NOT IN (mysql, information_schema, performance_schema, sys)
AND tables.table_type = BASE TABLE AND engine=InnoDB;
如果應(yīng)用程序端或者業(yè)務(wù)系統(tǒng)上有很多關(guān)聯(lián),更改后需要測(cè)試的未知應(yīng)用程序行為等,并不可以立即將主鍵添加到表中。
在這種情況下,一個(gè)短期的解決方案是更改從庫(kù)使用的搜索算法,以定位由基于行的事件更改的行。
搜索算法是使用 MySQL 5.6 及更高版本中可用的 slave_rows_search_algorithms 選項(xiàng)設(shè)置的。默認(rèn)值是盡可能使用索引掃描,否則使用表掃描。
但是,對(duì)于沒有主鍵的表使用散列掃描,這會(huì)導(dǎo)致 SQL 線程臨時(shí)緩存散列以減少搜索整個(gè)表的開銷。slave_rows_search_algorithms的值可以使用以下方法動(dòng)態(tài)更改:
mysql> SET GLOBAL slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN;
使用哈希掃描時(shí)要注意的一件事是,哈希僅在一個(gè)基于行的事件中重復(fù)使用。(每個(gè)基于行的事件可能對(duì)主庫(kù)同一SQL 語句的同一表中的幾行進(jìn)行更改)。
復(fù)制主服務(wù)器上的 binlog_row_event_max_size 選項(xiàng)控制基于行的事件的最大大小。默認(rèn)的最大事件大小為 8kB。

意味著切換到哈希掃描只會(huì)在以下情況下提高 SQL 線程的性能:

  • 如果對(duì)大行(例如,使用 blob 或文本數(shù)據(jù))執(zhí)行更新或刪除,增加主庫(kù)上binlog_row_event_max_size的值可能會(huì)有所幫助 。

    只能在 MySQL 配置文件中設(shè)置 binlog_row_event_max_size  ,重置該值需要重啟數(shù)據(jù)庫(kù)。

總結(jié):即使啟用哈希掃描可以提高從庫(kù)的性能,但永久的解決方案是為每個(gè)表添加顯式主鍵,這種模式可以避免許許多多的問題。


END




本文作者:高智飛(上海新炬王翦團(tuán)隊(duì))

本文來源:“IT那活兒”公眾號(hào)

文章版權(quán)歸作者所有,未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址:http://systransis.cn/yun/129433.html

相關(guān)文章

  • 跨云遷移過程中的數(shù)據(jù)同步一致性校驗(yàn)實(shí)踐(一)

    摘要:通過對(duì)一些客戶的跨云遷移過程進(jìn)行總結(jié),發(fā)現(xiàn)普遍存在的挑戰(zhàn)有三點(diǎn)數(shù)據(jù)完整性和一致性挑戰(zhàn)。簡(jiǎn)而言之,跨云遷移過程中的數(shù)據(jù)一致性主要就集中在存量數(shù)據(jù)的遷移如何保證一致。前言隨著互聯(lián)網(wǎng)業(yè)務(wù)發(fā)展對(duì)容災(zāi)以及對(duì)訪問加速、多供應(yīng)商成本控制等需求的產(chǎn)生,互聯(lián)網(wǎng)公司的多云部署和跨云遷移逐漸成為剛需,而在此過程中,最困擾運(yùn)維和研發(fā)人員的就是數(shù)據(jù)的遷移和同步。俗語說 上屋搬下屋,搬灑一籮谷 ,在業(yè)務(wù)的遷移過程中一旦...

    Tecode 評(píng)論0 收藏0

發(fā)表評(píng)論

0條評(píng)論

最新活動(dòng)
閱讀需要支付1元查看
<