MySQL · 数据归档

发表于 | 更新于 | 分类于 | 阅读次数：

背景

大表 base_barcode_info 数据量 5000w+，遇到很大的性能瓶颈，需要将表中的历史数据进行归档。

实践

  1. 安装 Percona Toolkit

  2. 创建归档表 base_barcode_info_history_20190801

  3. 使用 crontab 执行定时任务，archiver.sh 脚本

1
2
3
4
5
# 后台执行任务
nohup sh archiver.sh > myout.file 2>&1 &

# archiver.sh
pt-archiver --source h=10.138.232.85,P=3306,u=sre,p='xx',D='csomo-barcode',t=base_barcode_info --charset=utf8 --dest  h=10.138.232.85,P=3306,u=sre,p='xx',D='csomo-barcode',t=base_barcode_info_history_20190801 --where "barcode_request_id IN (SELECT id FROM base_barcode_request WHERE TYPE = '5' AND est < '2019-08-01 00:00:00')" --limit 1000 --commit-each --bulk-insert --no-version-check
  1. 查看归档进度
1
2
3
mysql> use information_schema;
mysql> select table_name,table_rows from tables where TABLE_SCHEMA = 'csomo-barcode' and table_name='base_barcode_info_history_20190801';
mysql> select table_name,table_rows from tables where TABLE_SCHEMA = 'csomo-barcode' and table_name='base_barcode_info';
  1. 归档 46598706 花费约 16 小时
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# Fri Sep 13 08:19:34 CST 2019
+------------------------------------+------------+
| table_name                         | table_rows |
+------------------------------------+------------+
| base_barcode_info_history_20190801 |     0      |
+------------------------------------+------------+

# Fri Sep 13 12:29:07 CST 2019
+------------------------------------+------------+
| table_name                         | table_rows |
+------------------------------------+------------+
| base_barcode_info_history_20190801 |   13462385 |
+------------------------------------+------------+

# Fri Sep 13 17:29:47 CST 2019
+------------------------------------+------------+
| table_name                         | table_rows |
+------------------------------------+------------+
| base_barcode_info_history_20190801 |   26300649 |
+------------------------------------+------------+

# Fri Sep 13 22:21:25 CST 2019
+------------------------------------+------------+
| table_name                         | table_rows |
+------------------------------------+------------+
| base_barcode_info_history_20190801 |   36529410 |
+------------------------------------+------------+

# ?
+------------------------------------+------------+
| table_name                         | table_rows |
+------------------------------------+------------+
| base_barcode_info_history_20190801 |   46598706 |
+------------------------------------+------------+