摘要:在業界中有一個比較成熟的工具,針對大表的場景,可以線上進行Alter變更,且不會出現鎖表的風險。除此之外,它還有其他的一些優點,讓我們開始探索吧。
背景
大家在日常工作中,往往需要對資料庫的表結構做變更,一般涉及到增删字段,修改字段屬性等ALTER的操作。然而,在大表場景下,特别是千萬級、億級的大表,如果處理不當。這些操作往往會引發鎖表的巨大隐患,特别是在生産環境中,一旦在變更表結構過程中,出現了長時間鎖表,會導緻使用者産生的資料長時間無法正常變更到表中,進而導緻服務功能異常,結果将是災難性的。
一般執行這種Alter類型的變更,我們可能有以下的想法:
1、停服,在停服期間做表結構的變更,自然就可以防止對使用者産生影響。但是,很多場景是不允許停服的。并且如果表的資料量達到上億,那麼需要停服時間可能需要十幾個小時,甚至更長,這是極不現實的;
2、淩晨執行,在使用者較少的時間段内,做變更,盡量減少對使用者産生影響。但是如果出現鎖表的話,萬一有使用者使用服務,服務将不可用;
3、使用換表,但是缺點是複制資料到新表期間,如果使用者在這期間做了update或delete操作,且資料發生在已經複制完成的部分,那麼将無法感覺到這部分資料,導緻丢失掉使用者的操作資料,風險太大;
4、使用存儲過程,缺點是執行時間會很久,且有可能影響到使用者的DDL操作。因為為了防止每次循環修改時,鎖住太多資料行,我們需要控制每次更新資料的行數,粒度不能太大,否則很有可能會鎖住使用者正在操作的資料行。
那麼針對以上實際的需求,就沒有一個很好的工具,來解決我們的痛點嗎?其實在業界中,就有一個比較成熟的工具,針對大表的場景,可以線上進行Alter變更,且不會出現鎖表的風險。除此之外,它還有其他的一些優點,讓我們開始探索吧。
一、pt-osc是什麼
pt-online-schema-change是Percona-toolkit一員,通過改進原生ddl的方式,達到不鎖表線上修改表結構的效果。在Percona的官網中,關于pt-osc工具,也特别提到了ALTER表不會出現鎖表的特性。
針對上面談到的避免鎖表、感覺使用者更新删除動作等,ps-osc工具是怎麼解決的呢?
pt-osc主要執行步驟如下:
1、建立一個跟原表一模一樣的新表,命名方式為'_正式表名_new';
2、使用alter語句将要變更的内容在新建立的新表上做變更,避免了對原表的alter操作;
3、在原表中建立3個觸發器,分别是insert、update和delete,主要是用于原表在往新表複制資料時,如果使用者有DDL操作,觸發器能夠将在這期間出現的DDL操作資料也寫入到新表中,確定新表的資料是最新的,不會丢失掉使用者的新操作資料;
4、按塊拷貝資料到新表,拷貝過程對資料行持有S鎖;
5、重命名,将原表重命名為老表,命名為“_正式表名_old”,将新表重命名為正式表,可通過配置決定執行完成後是否删除掉老表;
6、删除3個觸發器;
二、pt-osc的安裝
在linux系統中安裝步驟:
--下載下傳安裝包
wget http://szxge1-sw.artifactory.cd-cloud-artifact.tools.huawei.com/artifactory/CommonComponent/common/tool/percona-toolkit-3.1.0.tar.gz
--解壓安裝包
tar -zxvf percona-toolkit-3.1.0.tar.gz
--安裝依賴環境
yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
yum -y install perl-Digest-MD5
cd percona-toolkit-3.1.0
perl Makefile.PL
--編譯
make
make install
yum install mariadb
--安裝Mysql
yum install perl-DBD-MySQL
三、pt-osc的使用
pt-osc工具使用起來很簡單,直接在linux指令行輸入pt-osc格式的指令,即可直接執行。
以Mysql資料庫增加一個名字是MARK的字段為例:
pt-online-schema-change --user="root" --password="*****" --host="資料庫IP" --port=3306 --alter "ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT 'mark source region is 1';" D=my_test,t=t_test --no-drop-old-table --execute --print --no-check-replication-filters --charset=utf8 --no-check-unique-key-change --max-load="Threads_running=100" --critical-load="Threads_running=300" --recursion-method=none;
在上面的語句中:
1、user和password分别為資料庫執行變更操作的使用者名、密碼,需要高權限;
2、host為資料庫的IP位址;
3、port為資料庫的端口号;
4、alter後面跟上具體的alter語句;
5、D為database名字;
6、t為要執行變更的表名;
7、no-drop-old-table就是不要删除
8、charset,字元集,使用utf8;
9、max-load,在複制資料時,工具會監控資料庫中正在運作的線程數,如果大于配置的Threads_running值,那麼會暫停複制,直到小于該值。以此防止對資料庫造成較大壓力,影響現網業務正常使用;
10、critical-load,預設為50,在每個塊之後檢查SHOW GLOBAL STATUS,與max-load不同的是,如果負載太高,,直接中止,而不是暫停。可根據自己資料庫情況斟酌配置門檻值;
注意:在--alter後面跟着的變更語句中,列名不可以加`符号,否則會出現報錯。如--alter "ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT 'mark source region is 1';",MARK字段加了`符号,就會出現錯誤,COMMENT後面有`符号無影響。
下面是使用pt-osc工具,實際執行一個作業時,列印出來的資訊。為了安全起見,部分日志資訊做了隐藏忽略。
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicGcq5ydwIzNfRWO4IGZ0cDNwIDMycTNkZ2Y1QDMwU2NkVGNjBzM5QWOtIjdvwFM48CXt92YucWbphmeuIzYpB3Lc9CX6MHc0RHaiojIsJye.jpg)
[root@ttt ~]# `pt-online-schema-change --user="root" --password="*****" --host="資料庫IP" --port=3306 --alter "ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT 'mark source region is 1';" D=my_test,t=t_test --no-drop-old-table --execute --print --no-check-replication-filters --charset=utf8 --no-check-unique-key-change --max-load="Threads_running=100" --critical-load="Threads_running=300" --recursion-method=none;`
No slaves found. See --recursion-method if host EulerOS-BaseTemplate has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `my_test`.`t_test`...
Creating new table...
CREATE TABLE `my_test`.`_t_test_new` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '遞增ID',
.............建表語句資料................
Created new table my_test._t_test_new OK.
Altering new table...
ALTER TABLE `my_test`.`_t_test_new` ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT 'mark source region is 1';
Altered `my_test`.`_t_test_new` OK.
2020-10-14T11:14:48 Creating triggers...
2020-10-14T11:14:48 Created triggers OK.
2020-10-14T11:14:48 Copying approximately 346697 rows...
INSERT LOW_PRIORITY IGNORE INTO `my_test`.`_t_test_new` (`id`, ..建表語句資訊.... FROM `my_test`.`_t_test_new` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 31340 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `my_test`.`t_test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
2020-10-14T11:14:53 Copied rows OK.
2020-10-14T11:14:53 Analyzing new table...
2020-10-14T11:14:53 Swapping tables...
RENAME TABLE `my_test`.`t_test` TO `my_test`.`_t_test_old`, `my_test`.`_t_test_new` TO `my_test`.`t_test`
2020-10-14T11:14:53 Swapped original and new tables OK.
Not dropping old table because --no-drop-old-table was specified.
2020-10-14T11:14:53 Dropping triggers...
DROP TRIGGER IF EXISTS `my_test`.`pt_osc_my_test_t_test_del`
DROP TRIGGER IF EXISTS `my_test`.`pt_osc_my_test_t_test_upd`
DROP TRIGGER IF EXISTS `my_test`.`pt_osc_my_test_t_test_ins`
2020-10-14T11:14:54 Dropped triggers OK.
Successfully altered `my_test`.`t_test`.
四、性能對比
前面介紹了很多pt-osc的優點,以及良好的特性。那麼實際使用效果到底怎麼樣呢?在測試環境中,專門做了一個測試,讓大家有更加直覺的感受。
在測試庫中,準備了一張1600萬資料的大表,目标為對大表添加一個字段,分别使用存儲過程和pt-osc工具,進行測試。
4.1 使用存儲過程
首先使用存儲過程做測試,為防止鎖表,每次隻更新200行。整個變更從開始到完成,需要耗費90分鐘。其實,存儲過程在執行過程中,如果恰好使用者也在DDL操作存儲過程正在變更的資料行,還有可能會鎖住使用者的資料,導緻使用者不能變更成功。
4.2 使用pt-osc工具
pt-osc從開始執行到變更完成,耗時7分鐘左右,速度非常快。在執行的過程中,測試環境的服務連接配接到該資料庫,并執行多個會操作該表的任務,整個過程中,任務能夠正常執行,未出現異常情況。
5、結語
ps-osc的上述優點,在現網環境的不停服等要求下,能夠優雅地幫助我們實施變更,且保證在變更期間,資料庫不會受到鎖表、過載等的影響,進而保證了業務能夠正常運轉。
本文分享自華為雲社群《千萬級、億級大表線上不鎖表變更字段與索引》,原文作者:active_zhao 。
點選關注,第一時間了解華為雲新鮮技術~