1.介绍
之前处理mysql历史数据归档,直接写存储过程实现的(《mysql历史数据自动归档》),换新东家后,还是决定研究下主流的pt-archiver并实施。
mysql除了社区版,还有percona、mariadb两个分支;其中percona是一家做mysql咨询的公司,除了开发了自己的mysql分支外,还开发了大量的mysql运维工具,统称percona toolkit。
使用最为广泛的,如pt-online-schema-change:解决DDL锁表问题,pt-archiver:数据归档。
pt-archiver是mysql开源归档工具的主流,是大多数mysql DBA的常用工具。
2.安装与使用
2.1.安装
官网下载最新的rpm包,yum安装即可
# yum localinstall percona-toolkit-3.3.1-1.el7.x86_64.rpm
2.2.使用
[root@iZbp1ckjqrnvsfle1x9kzdZ ~]# pt-archiver \
> --source h=***.mysql.rds.aliyuncs.com,D=crm,t=t1,u=username,p=password,A=utf8mb4 \
> --dest h=***.mysql.rds.aliyuncs.com,D=testdb,t=t1,u=username,p=password,A=utf8mb4 \
> --where "create_time < date_sub(now(), interval 3 month)" \
> --limit 1000 --commit-each --bulk-delete --bulk-insert --progress 10000 --run-time 600s --sleep 1
TIME ELAPSED COUNT
2021-08-19T15:11:07 0 0
2021-08-19T15:11:08 0 10000
2021-08-19T15:11:08 0 20000
2021-08-19T15:11:08 0 30000
2021-08-19T15:11:08 0 40000
2021-08-19T15:11:09 1 50000
2021-08-19T15:11:09 1 60000
2021-08-19T15:11:09 1 70000
2021-08-19T15:11:09 1 80000
2021-08-19T15:11:10 2 84820
2.3.参数说明
--source:源库信息
--dest:目标库信息
--where:归档的数据必须满足的条件,如3个月前
--limit:一个SQL处理的记录数,即一个批次归档多少条记录
--commit-each:每个批次处理后提交
--bulk-delete:通过一条语句删除一个批次:delete from table where id >= ? and id <= ?;如不指定,则一条一条删除:delete from table where id = ?
--bulk-insert:每个批次的数据生产临时文件,然后通过LOAD DATA LOCAL INFILE方式加载到历史库
--progress:输出执行进度
--run-time:脚本每次最大执行时长
--sleep:每个事务之间间隔的时间
2.4.原理说明
- 线上表、历史表可以在同一个实例,或不同的实例;
- pt-archiver通过perl DBI模块(Database Independent Interfacefan)远程访问数据库;
- pt-archiver底层通过执行sql和简单的分布式事务实现归档逻辑
2.4.1. 实现逻辑(无批量执行)
参数未指定--bulk-delete\--bulk-insert
执行顺序 | 线上表 | 历史表 | 分批 | 说明 |
1 | select * from source | 第一批 | 按主键升序获取limit条数据 | |
2 | insert into target values (1001,……) | 事务大小有两种控制方式: 1.一个批次提交一次,事务大小=limit,设置:--commit-each(推荐) 2.指定事务大小,设置:--txn-size | ||
3 | delete from source where id = 1001 | |||
4 | insert into target values (1002,……) | |||
5 | delete from source where id = 1002 | |||
6 | insert into target values (1003,……) | |||
7 | delete from source where id = 1003 | |||
8 | commit | 先提交insert,后提交delete | ||
9 | commit | |||
10 | select * from source | 第二批 | 与第一批相比,增加了条件: id > last_archived_id,缩小扫描范围 | |
11 | …… | |||
12 | …… |
2.4.2. 实现逻辑(批量执行)
参数指定--bulk-delete\--bulk-insert
执行顺序 | 线上表 | 历史表 | 分批 |
1 | select * from source where (归档条件) | 第一批 | |
2 | LOAD DATA LOCAL INFILE …… INTO TABLE | ||
3 | DELETE FROM `crm`.`t1` WHERE (((`id` >= ?))) AND (((`id` <= ?))) AND (归档条件) LIMIT 3 | ||
4 | commit | ||
5 | commit | ||
6 | select * from source order by id limit 3 | 第二批 | |
7 | …… | ||
8 | …… |
- 与单条记录insert、delete相比,批量执行性能更好;
- limit不能设置过大,否则可能会导致主从延迟、锁冲突等,建议设置为1000左右。
- 批量执行的一个缺点:load data过程中如果遇到主键或唯一键冲突,不会报错,直接忽略冲突的记录,整个事务正常提交。
2.4.3. 工具执行的SQL
# pt-archiver --source h=***.mysql.rds.aliyuncs.com,D=crm,t=t1,u=username,p=password,A=utf8mb4 --dest h=***.mysql.rds.aliyuncs.com,D=testdb,t=t1,u=username,p=password,A=utf8mb4 --where "create_time < date_sub(now(), interval 10 minute)" --limit 1000 --commit-each --bulk-delete --bulk-insert --progress 10000 --dry-run
SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`create_time` FROM `crm`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_time < date_sub(now(), interval 10 minute)) AND (`id` < '522372') ORDER BY `id` LIMIT 1000
SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`create_time` FROM `crm`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_time < date_sub(now(), interval 10 minute)) AND (`id` < '522372') AND ((`id` >= ?)) ORDER BY `id` LIMIT 1000
DELETE FROM `crm`.`t1` WHERE (((`id` >= ?))) AND (((`id` <= ?))) AND (create_time < date_sub(now(), interval 10 minute)) LIMIT 1000
LOAD DATA LOCAL INFILE ? INTO TABLE `testdb`.`t1`(`id`,`name`,`create_time`)
2.4.4. 源码中的事务控制
commit函数
sub commit {
my ( $o, $force ) = @_;
my $txnsize = $o->get('txn-size');
if ( $force || ($txnsize && $txn_cnt && $cnt % $txnsize == 0) ) { ## 事务提交的条件:force参数强制提交,或根据事无大小判断是否提交
if ( $o->get('buffer') && $archive_fh ) {
my $archive_file = $o->get('file');
trace('flush', sub {
$archive_fh->flush or die "Cannot flush $archive_file: $OS_ERROR\n";
});
}
if ( $dst ) {
trace('commit', sub {
$dst->{dbh}->commit; ## 先提交目标库的事务
});
}
trace('commit', sub {
$src->{dbh}->commit; ## 后提交源库的事务
});
$txn_cnt = 0;
}
}
主流程
ROW:
while ( # Quit if: ## 循环处理记录
$row # There is no data
&& $retries >= 0 # or retries are exceeded
&& (!$o->get('run-time') || $now < $end) # or time is exceeded
&& !-f $sentinel # or the sentinel is set
&& $oktorun # or instructed to quit
)
{
my $lastrow = $row;
if ( !$src->{plugin} ||
trace('is_archivable', sub {$src->{plugin}->is_archivable(row => $row)})
)
{
## 非批量操作,即逐行insert\delete
if ( $dst && !$bulkins_file ) {
my $ins_sth;
$ins_sth ||= $ins_row; # Default to the sth decided before.
my $success = do_with_retries($o, 'inserting', sub { ## insert
my $ins_cnt = $ins_sth->execute(@{$row}[@ins_slice]);
PTDEBUG && _d('Inserted', $ins_cnt, 'rows');
$statistics{INSERT} += $ins_sth->rows;
});
if ( $success == $OUT_OF_RETRIES ) {
$retries = -1;
last ROW; ## insert报错,终止循环
}
}
if ( !$bulk_del ) {
if ( !$o->get('no-delete') ) {
my $success = do_with_retries($o, 'deleting', sub { ## delete
$del_row->execute(@{$row}[@del_slice]);
PTDEBUG && _d('Deleted', $del_row->rows, 'rows');
$statistics{DELETE} += $del_row->rows;
});
if ( $success == $OUT_OF_RETRIES ) {
$retries = -1;
last ROW; ## delete报错,终止循环
}
}
}
}
$now = time();
++$cnt;
++$txn_cnt;
$retries = $o->get('retries');
commit($o) unless $commit_each; ## 提交场景1:未设置commit-each, 由txn-size控制是否提交,记录数被txn-size整除时提交
if ( $get_sth->{Active} ) { # Fetch until exhausted
$row = $get_sth->fetchrow_arrayref();
}
if ( !$row ) { ## 批量操作, 一条SELECT返回的的所有数据都遍历完毕
if ( $bulkins_file ) {
$bulkins_file->close()
or die "Cannot close bulk insert file: $OS_ERROR\n";
my $ins_sth; # Let plugin change which sth is used for the INSERT.
$ins_sth ||= $ins_row;
my $success = do_with_retries($o, 'bulk_inserting', sub { ## 批量insert
$ins_sth->execute($bulkins_file->filename());
$src->{dbh}->do("SELECT 'pt-archiver keepalive'") if $src;
PTDEBUG && _d('Bulk inserted', $del_row->rows, 'rows');
$statistics{INSERT} += $ins_sth->rows;
});
if ( $success != $ALL_IS_WELL ) {
$retries = -1;
last ROW; ## insert报错,终止循环
}
}
if ( $bulk_del ) {
if ( !$o->get('no-delete') ) {
my $success = do_with_retries($o, 'bulk_deleting', sub { ## 批量delete
$del_row->execute(
@{$first_row}[@bulkdel_slice],
@{$lastrow}[@bulkdel_slice],
);
PTDEBUG && _d('Bulk deleted', $del_row->rows, 'rows');
$statistics{DELETE} += $del_row->rows;
});
if ( $success != $ALL_IS_WELL ) {
$retries = -1;
last ROW; ## delete报错,终止循环
}
}
}
commit($o, 1) if $commit_each; ## 提交场景2:设置了commit-each的情况下,提交一个完整批次的数据
$get_sth = $get_next;
trace('select', sub { ## 获取下一个批次的数据
$get_sth->execute(@{$lastrow}[@asc_slice]);
});
}
}
commit($o, $txnsize || $commit_each); ## 最后再提交一次
3. 实施
3.1. 阿里云X-Engine引擎RDS
X-Engine是阿里云自研的OLTP数据库存储引擎。作为自研数据库PolarDB的存储引擎之一,已经广泛应用在交易历史库、钉钉历史库等核心应用,大幅缩减了业务成本(官方介绍:https://help.aliyun.com/document_detail/148660.html)。
阿里自研X-Engine引擎的目的:
- 极高的并发事务处理能力(尤其是双十一的流量突发式暴增)?
- 超大规模的数据存储。
X-Engine使用了LSM-Tree作为分层存储的架构基础,基于Copy-on-write技术,避免原地更新数据页,从而对只读数据页面进行编码压缩,相对于传统存储引擎(例如InnoDB),使用X-Engine可以将存储空间降低至10%~50%。
使用X-Engine注意事项:
- 只支持RDS MYSQL 8.0版本;
- 不支持分区表;
- 最好不要在同一个实例中混用Innodb和X-Engine,两种引擎对内存的要求和管理方式不同;
- RDS同等配置的两种引擎实例价格相同。
3.2. pt-archiver脚本
ptArchive.sh
# more ptArchiver.sh
#!/bin/bash
HOST_UAT_OMP=***.mysql.rds.aliyuncs.com
HOST_UAT_MMP=***.mysql.rds.aliyuncs.com
HOST_TEST_OMP=***.mysql.rds.aliyuncs.com
HOST_PROD_ARCHIV=***.mysql.rds.aliyuncs.com
LOG_FILE=/root/ptArchiver/log/ptArchiver.`date +%Y%m%d`
function log_begin()
{
TAB=$1
echo >> $LOG_FILE
echo `date +%Y-%m-%d"T"%H:%M:%S`" ${TAB} begin..." >> $LOG_FILE
echo "------------------------------------" >> $LOG_FILE
}
function log_end()
{
TAB=$1
echo "------------------------------------" >> $LOG_FILE
echo `date +%Y-%m-%d"T"%H:%M:%S`" ${TAB} end" >> $LOG_FILE
}
log_begin crm.t1
/usr/bin/pt-archiver \
--source h=${HOST_UAT_OMP},A=utf8mb4,D=crm,t=t1,u=username,p=password \
--dest h=${HOST_UAT_MMP},A=utf8mb4,D=testdb,t=t1,u=username,p=password \
--where "create_time < str_to_date(concat(date_format(date_sub(now(), interval 10 minute),'%Y-%m-%d %H:%i'),':00'),'%Y-%m-%d %H:%i:%S')" \
--limit 1000 --commit-each --bulk-delete --bulk-insert --progress 20000 --run-time=600s --sleep 1 >> $LOG_FILE 2>&1
log_end crm.t1
log_begin yunkc_finance_bak.finance_d_t_third_pay_info
/usr/bin/pt-archiver \
--source h=${HOST_TEST_OMP},A=utf8mb4,D=yunkc_finance_bak,t=finance_d_t_third_pay_info,u=username,p=password \
--dest h=${HOST_PROD_ARCHIV},A=utf8mb4,D=testdb,t=finance_d_t_third_pay_info,u=username,p=password \
--where "((create_time < str_to_date(concat(date_format(date_sub(now(),interval 351 day),'%Y-%m-%d %H'),':00:00'),'%Y-%m-%d %H:%i:%S')) and (flow_type in (1000211,1000212)))" \
--no-version-check \
--limit 1000 --commit-each --bulk-delete --bulk-insert --progress 20000 --run-time=600s --sleep 1 >> $LOG_FILE 2>&1
log_end yunkc_finance_bak.finance_d_t_third_pay_info
注:归档条件中的时间条件,最好取整到小时或天,如果直接用now()-n天,且线上表每秒都在入数据,则pt-archiver脚本会因为一直有数据需要归档而不退出,直到run-time结束。
log
# more ptArchiver.20210825
2021-08-25T10:00:01 crm.t1 begin...
------------------------------------
TIME ELAPSED COUNT
2021-08-25T10:00:01 0 0
2021-08-25T10:00:03 2 2160
------------------------------------
2021-08-25T10:00:04 crm.t1 end
2021-08-25T10:00:04 yunkc_finance_bak.finance_d_t_third_pay_info begin...
------------------------------------
TIME ELAPSED COUNT
2021-08-25T10:00:05 0 0
2021-08-25T10:00:31 26 20000
2021-08-25T10:00:55 49 40000
2021-08-25T10:01:18 73 60000
2021-08-25T10:01:41 96 80000
2021-08-25T10:02:05 120 100000
2021-08-25T10:02:28 143 120000
2021-08-25T10:02:51 166 140000
2021-08-25T10:03:15 190 160000
2021-08-25T10:03:38 213 180000
2021-08-25T10:04:01 236 200000
2021-08-25T10:04:24 259 220000
2021-08-25T10:04:48 283 240000
2021-08-25T10:05:12 306 260000
2021-08-25T10:05:35 330 280000
2021-08-25T10:05:59 354 300000
2021-08-25T10:06:22 377 320000
2021-08-25T10:06:46 401 340000
2021-08-25T10:07:09 424 360000
2021-08-25T10:07:32 447 380000
2021-08-25T10:07:56 471 400000
2021-08-25T10:08:20 494 420000
2021-08-25T10:08:43 518 440000
2021-08-25T10:09:07 542 460000
2021-08-25T10:09:30 565 480000
2021-08-25T10:09:53 588 500000
2021-08-25T10:10:05 600 509001
------------------------------------
2021-08-25T10:10:05 yunkc_finance_bak.finance_d_t_third_pay_info end