数据库 单表 13 亿记录创建索引需要多长时间?

hooopo · 2020年10月07日 · 最后由 hooopo 回复于 2020年10月14日 · 1585 次阅读

这个问题来自 twitter 上一个网友的问题:

https://twitter.com/laixintao/status/1312963759346860037

万推,对 13 亿的 IP 地址(已经转成 int 存储)在 MySQL 中建立索引最快的方法是什么啊?不要求在线,可以锁表可以停机。机械硬盘... 64C256G 配置...

有生之年跑不完的样子...

一堆网友说要分表,还有网友给出了分表之后估算时间,64C256G 的服务器要 24 小时跑完。

13 亿 IP 地址,必须要做读写分离了,读库也应该分表了。建议 5000W 一张表。如果 sharding 算法速度快的话,建议 1000W 一张表。1000W 数据停机建立索引,10 分钟应该可以了。你 64C,估算 130 张表 24 小时可以跑完。

最后原 PO 给出了在自己的 64C256G 服务器上跑的结果,34 分钟左右跑完:

这个话题不错,拿来测试一下挺有意思,在 vultr 上买了一个 8C32G 的服务器,大概每小时 0.2 刀,也不贵...

顺便记录一下测试的脚本,方便感兴趣的可以验证。

安装 Postgres 13 和 Ubuntu 20.04:https://hackershare.dev/cn/bookmarks/4978

初始化表结构:

CREATE unlogged TABLE  "test" (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "ip" integer NOT NULL,
  "domain" varchar DEFAULT 'drawerd.com'
);

插入 13 亿条记录:

insert into test(ip) select generate_series(1,1280000000);

插入过程耗时挺久的,大概一个多小时,这里图简单,没做什么优化。感兴趣的可以优化插入速度,比如先把主键索引关掉,用脚本生成 csv,再 copy。

\dt+ test
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-------+-------+----------+-------------+-------+-------------
public |  test | table | postgres | unlogged | 62 GB |

创建索引:

SET maintenance_work_mem to '30GB';
SET max_parallel_maintenance_workers to 8;
ALTER TABLE test SET (parallel_workers = 8);

执行创建语句:

CREATE INDEX idx ON test (ip);

最终 24 分钟跑完。如果和原 PO 同样配置 64C256G 的话,开 64 个并行,按线性算大概 3 分钟跑完,但实际应该达不到,并行切换和调度还会有很大开销,但至少比目前的 8C32G 的要快很多。

最后又测了一下 Postgres 内建的 partition,看看对创建索引会不会起到加速的作用,测试的结果是 18 分钟左右:

CREATE unlogged TABLE "ptest" (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "ip" integer NOT NULL,
  "domain" varchar DEFAULT 'drawerd.com'
) partition by hash(id);

create table dept_1 partition of ptest FOR VALUES WITH (MODULUS 8, REMAINDER 0);
create table dept_2 partition of ptest FOR VALUES WITH (MODULUS 8, REMAINDER 1);
create table dept_3 partition of ptest FOR VALUES WITH (MODULUS 8, REMAINDER 2);
create table dept_4 partition of ptest FOR VALUES WITH (MODULUS 8, REMAINDER 3);
create table dept_5 partition of ptest FOR VALUES WITH (MODULUS 8, REMAINDER 4);
create table dept_6 partition of ptest FOR VALUES WITH (MODULUS 8, REMAINDER 5);
create table dept_7 partition of ptest FOR VALUES WITH (MODULUS 8, REMAINDER 6);
create table dept_8 partition of ptest FOR VALUES WITH (MODULUS 8, REMAINDER 7);

除了建表语句不一样,其他都和上面一样操作。

"insert into test(ip) select generate_series(1,1280000000);"

这个地方插入的时候实际上可以认为是有序的,对性能可能有正面影响,order by random 乱序之后可能测试的更真实一些。

不过对于 pg 的 heap 表存储来说应该影响不大,对于 mysql 的 b 树来说可能会有一定的影响

其实导入数据确实是最麻烦的,我花了 4 个小时,方法是这样的,不知道大家有没有更好的方法:

数据是公开的,压缩的是 11G,解压是 ~129G,每一行是一个 json,就 ip,domain,type 这几个字段。

我是先 split -l 1000000 file ,1 千万行一个文件,大约 130 个文件。

然后批量转换成 mysql load 需要的 csv,(让 server 直接去 load 本地的文件应该是最快的,文档说比 insert 快 20 倍左右 https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html ,一开始尝试一行一行 insert,大约 20 行/s,实在太慢了,然后一次 insert 一万行,依然很慢)。

先写好这个脚本:

cat scripts/json2csv.py 
import fileinput
import json
import ipaddress
import csv
import sys

writer = csv.writer(sys.stdout, quoting=csv.QUOTE_ALL)
for line in fileinput.input():
    data = json.loads(line)
    ip = int(ipaddress.IPv4Address(data.get('name')))
    value = data.get('value')
    t = data.get('type')
    writer.writerow([ip, value, t])

然后开 130 个进程一起跑,毕竟是 64C 机器

for sf in $(ls source_file)
do
        cat source_file/$sf | python3 scripts/json2csv.py > target_csv/$sf.csv &
done

最后在 mysql server 上执行 load data infile 语句,大约跑了 4 个小时。

然后我以为建索引毕竟也是顺序写,估计也有 4 个小时。就去 twitter 求助了... 结果半个小时竟然神器地跑完了...

laixintao 回复

mysql load infile 就是最快的了,再优化就是调参了 之前记录过 http://shopperplus.github.io/blog/2014/11/08/fastest-way-to-load-data-in-mysql.html

laixintao 回复

我那个插入快还有一个原因应该是 varchar 那里由于是假数据 我直接给了默认值

没对比一下查询效率吗?

numbcoder 回复

查询网上好多基于 tpc-x 数据集的对比了 差别不大 会有差别的是 join 和聚合查询 这种 pg 一些场景是可以开并行的 多核机器上有优势

请教所以如何预测创建索引需要多少时间呢?

把线上一部分数据 dump 下来,然后在类似配置机器上把数据集的 1/8、1/4、1/2 的部分进行创建索引。然后根据增长的趋势预测真实创建完索引大概需要多少时间?

一般都是在小数据上测,然后乘以系数,比如你可以测 100w 的,如果你真实数据是 1 亿,就乘以 100

最实际的办法还是 如果你有闲置服务器,模拟一下数据量,实际跑一遍

hooopo 如何 10 分钟内插入 13 亿条记录? 中提及了此贴 10月10日 04:42
laixintao 回复

在 96 核、192G 内存的机器上,Ubuntu 18, mysql-server 5.7,没有调参数优化。13 亿行数据。

Load 到数据库花了大概 1 小时。

mysql> LOAD DATA INFILE '/data/mysql/ii.csv' INTO TABLE `ip` FIELDS TERMINATED BY ',' ;
Query OK, 1280000000 rows affected (1 hour 9 min 55.01 sec)
Records: 1280000000  Deleted: 0  Skipped: 0  Warnings: 0

加索引花了大概 40 分钟:

mysql> alter table `ip` add key `idx_ip` (`ip`);
Query OK, 0 rows affected (43 min 54.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

观察下来,CPU 核心只用到了两个。主要瓶颈还在磁盘 IO 上。

wych42 回复

主要是你的 mysql 在划水吧,CPU 应该只用了一个

hooopo 回复

是的。磁盘压力比较大。

70521 be/4 mysql     924.43 K/s  280.63 M/s  0.00 %  0.68 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
wych42 回复

你是物理机还是云服务 有 ssd 没

hooopo 回复

测试环境的物理机,HDD。

同样配置,我又用 postgresql 试了一下。

  • create table 没加 unlogged
  • cerate index 的 workers 设置到 32 个。

导入数据用 copy 从 csv 复制,忘记开 \timing,不过大概也是 30-40 分钟。 加索引耗时大概 13 分钟。

postgres=# CREATE INDEX idx ON test (ip);

Time: 781496.452 ms (13:01.496)
wych42 回复

有试 https://ruby-china.org/topics/40472 这个帖子用的 并行 copy 吗

hooopo 回复

没注意到。待我一试。

hooopo 回复

试了。瓶颈在磁盘的情况下,用并行 copy 或者 直接 copy 时间差别不大。

at 30s, row rate 666546.92/sec (period), row rate 666546.92/sec (overall), 2.000000E+07 total rows
at 1m0s, row rate 653333.72/sec (period), row rate 659940.91/sec (overall), 3.960000E+07 total rows
at 1m30s, row rate 530000.43/sec (period), row rate 616630.03/sec (overall), 5.550000E+07 total rows
at 2m0s, row rate 546666.11/sec (period), row rate 599139.82/sec (overall), 7.190000E+07 total rows
at 2m30s, row rate 546659.98/sec (period), row rate 588644.12/sec (overall), 8.830000E+07 total rows
at 3m0s, row rate 530004.70/sec (period), row rate 578871.27/sec (overall), 1.042000E+08 total rows
at 3m30s, row rate 543335.38/sec (period), row rate 573794.86/sec (overall), 1.205000E+08 total rows
at 4m0s, row rate 523331.66/sec (period), row rate 567487.09/sec (overall), 1.362000E+08 total rows

4 分钟插入了一亿多条数据,13 亿差不多也得 40 分钟左右。

但是,关闭日志(建表时 unlogged), 速度提升很大:

at 30s, row rate 1826650.79/sec (period), row rate 1826650.79/sec (overall), 5.480000E+07 total rows
at 1m0s, row rate 2005766.79/sec (period), row rate 1916228.49/sec (overall), 1.150000E+08 total rows
at 1m30s, row rate 1990897.15/sec (period), row rate 1941106.77/sec (overall), 1.747000E+08 total rows

一亿多条数据,只需要 1 分钟左右。

综合看,如果用 HDD,瓶颈大概率在硬盘这里,关闭日志可以提升写性能。原推可能用了比较慢的硬盘。 回头有空,找一台 SSD 的物理服务器测试下。

wych42 回复

好的,期待

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册