用了 mysql 的同事遇到 pg 都相逢恨晚

[cstj0505]

某业务部门的同事们，生成一亿条数据写入 mysql 数据库，花了好几个小时。

我告诉他 pg 一秒可以写十几万条，并拿出压测数据的时候，他们纷纷表示非常震惊。 这几位程序员，运维的都是工作好些年的，但之前都没听过 pg。

另外一个部门每天要把几亿条数据从 oracle 库用 navcat 搬到 mysql 库（恩，他们也没听过 pg，把最后的 mysql 库作为结果查询库），然后每天要跑 7 个小时。 听说我们大数据很快，就过来问我们怎么加快速度。乱七八糟的叹了一下午，他们想法很多，最后说要加快速度你可以装个 pg 试试。

然后他们在原来的 mysql 集群上，装了个 pg 的主备。在 oracle 生成了几亿条数据。然后分别跑了测试。 mysql 的速度和上面差不多， 但是 pg 呢，开了 10 个并行，原话如下： “ pg 的 copyin 方式，432 秒拷贝 3.68 亿条记录，cpu 最高 62%，磁盘写 io 最高 100MB/s，网络带宽最高 670Mbps。另外，磁盘占用比 mysql 少多了（大概少 50GB ）。”

估计现在他们也会感觉和 pg 相逢恨晚了。

[firefox12]

呵呵 我们线上数据库 pg 大概几亿条数据 要加一个列 设一个默认值 没有 index 你知道要多少小时吗？

真以为 pg 好？我就呵呵了。

[dy7338]

各有所长。

[chenqh]

@firefox12 你这个 mysql 也一样吧

[cstj0505]

@kiddult 我全程没说开发者的智商吧，至于方式，能给个方案吗，几亿条数据 7 分钟从 oralce 导进 8 核 16g 顺序 io100MB/s 左右的机器上的 mysql

[sunsh2017]

不知那些冷潮热讽的是什么心态。我是从 mysql 转向 pg 的，我的感受是，PG 要强大太多，并且作为唯 一能与 oracle 相媲美的开源数据库，mysql 与之相比，就是一坨屎。MYSQL 唯一的优势就是生态比较成熟，各种工具较多，但弥补不了功能不足的各种缺陷。我支持楼主，也支持 PG.

[bk201]

@sunchen 大哥链接你试一下

[reus]

pg 一个 fdw 连接 oracle，oracle 也能一个外部表连接 pg

mysql 就没有了

[yangqi]

@cstj0505 没这个经验我会随便乱说么。你既然不懂说了也没用，你觉得 pg 好就用呗，反正你们的场景很简单，用啥都差不多。不过不要以为用了就是专家了，到处吹牛，给人看笑话

[cstj0505]

@yangqi 又说场景简单了，那么在这个简单场景下，您经验丰富，有这个经验，能给个方案吗，几亿条数据 7 分钟从 oralce 导进 8 核 16g 顺序 io100MB/s 左右的机器上的 mysql。

打嘴炮没意思，能用数据或者事实说话吗？

另外一个我哪一个字说自己是专家，这数据也不是我测的。

[kiddult]

@cstj0505 最好把测试数据放出来让大家看一下吧，比如一般情况下一条记录多少字段，每个字段的正常数据，然后多少索引，总共多少条数据，数据的总大小，PostgreSQL 导入的时候中间格式是什么。字段名直接换成一般点的名字就行

刚才查了一下 PostgreSQL 的 CopyIn 本身就是批量插入，类似 MySQL 的 LOAD DATA INFILE ？这个得问一下具体接触过这块的人才行。拿这个和普通 insert 比，本身就没法比

https://www.postgresql.org/docs/9.1/static/populate.html

Use COPY to load all the rows in one command, instead of using a series of INSERT commands. The COPY command is optimized for loading large numbers of rows; it is less flexible than INSERT, but incurs significantly less overhead for large data loads. Since COPY is a single command, there is no need to disable autocommit if you use this method to populate a table.

If you cannot use COPY, it might help to use PREPARE to create a prepared INSERT statement, and then use EXECUTE as many times as required. This avoids some of the overhead of repeatedly parsing and planning INSERT. Different interfaces provide this facility in different ways; look for "prepared statements" in the interface documentation.

Note that loading a large number of rows using COPY is almost always faster than using INSERT, even if PREPARE is used and multiple insertions are batched into a single transaction.

COPY is fastest when used within the same transaction as an earlier CREATE TABLE or TRUNCATE command. In such cases no WAL needs to be written, because in case of an error, the files containing the newly loaded data will be removed anyway. However, this consideration only applies when wal_level is minimal as all commands must write WAL otherwise.

[cstj0505]

@kiddult 确实是批量插入，不过在 jdbc 端有这样的方式，至少可以在数据流中不用把数据落地就能快速的入库，另外一个关键点就在于不用拼 insert 语句，对方不管什么数据库，只要按照约定格式把数据给过来基本上不存在性能的瓶颈。我们现在实时数据接入、计算都采取的这种方式。这功能也不算新，再配上 spark+kafka 简直不要太好用

数据大小 34G
索引大小 11G
表一共 100 张，每张表上面一个索引，都是建在数字上的
数据样例：
表 A
表
CREATE TABLE A (
storegid integer,
gid integer,
code character varying(20),
name character varying(80),
spec character varying(40),
sort character varying(255),
munit character varying(255),
rtlprc numeric(24,4),
inprc numeric(24,4),
mbrprc numeric(24,4),
isdisp integer,
alword integer,
alwsord integer,
prctype integer,
promote integer,
lwtrtlprc numeric(24,4),
gft integer,
mcode character varying(20),
dep character varying(64),
shortname character varying(40),
catalog character varying(1),
alcprc numeric(24,4),
brand character varying(20),
description character varying(255),
alwout integer,
costtype integer,
extendedattributes character varying(255),
validperiod integer,
code2 character varying(32),
brandname character varying(64),
origin character varying(40),
vdrcode character varying(100),
sortname character varying(255),
impprc numeric(24,4),
bgnwarnday integer,
endwarnday integer,
busgatename character varying(255),
permilgoal numeric(24,4),
ordflag character varying(4),
isbind integer,
indeadvalidday integer,
outdeadvalidday integer,
validdayunit character varying(4),
dayalc integer,
orddatelst character varying(20),
acode character varying(20),
bcode character varying(20),
ccode character varying(20),
dcode character varying(20),
usevalidate integer,
storenature character varying(20),
invprc numeric(24,4),
isgft integer,
originalcreator character varying(20),
validperioddes character varying(100),
alwmodprc integer,
stdrtlprc numeric(24,4),
stdmbrprc numeric(24,4),
highrtlprc numeric(24,4),
alwxf integer,
alwmod integer,
specreturnmemo character varying(255),
alwinputckqty integer,
cntinprc numeric(24,4),
vdrname character varying(100),
validdaycalc integer,
unsaleproperty integer,
strcol1 character varying(80),
strcol2 character varying(80),
strcol3 character varying(80),
strcol4 character varying(80),
strcol5 character varying(80),
strcol6 character varying(80),
numcol1 numeric(20,4),
numcol2 numeric(20,4),
numcol3 numeric(20,4),
numcol4 numeric(20,4)
);

索引 btree (storegid, gid)

1000021 | 3000035 | 06020125 | 泰优美牛肚 30g | 30g | 0802 | 包 | 5.0000 | 0.0000 | 5.0000 | 0 | 1 | 1 | 0 | -1 | 0.0000 | 0 |
| - | | A | 4.0000 | 1076 | | 1 | 0 | | | 6923405100516 | 泰优美 | | 800
328 | 牛肉类 | 5.0000 | 0 | 0 | - | | 00 | 0 | 0 | 0 | | |
| 08 | 0802 | 0802 | 0802 | | 默认 | 0.0000 | | | | 0 | 5.0000 | 5.0000 | 9999.0
000 | 1 | 1 | | | 3.3000 | | | | | | | | |
| | | |

表 B
CREATE TABLE B
storegid integer,
gdgid integer,
highinv numeric(24,4),
lowinv numeric(24,4),
stdshow numeric(24,2),
alc character varying(10),
busgate integer,
busgatename character varying(255),
abctype character varying(20)
);
索引
btree (storegid, gdgid);
数据
1000025 | 3004329 | 9999.0000 | 0.0000 | 0.00 | 直送 | 7000020 | 正常 |

表 C
CREATE TABLE C (
storegid integer,
gid integer,
qpc numeric(24,4),
rtlprc numeric(24,4),
mbrprc numeric(24,4),
lwtrtlprc numeric(24,4),
impprc numeric(24,4)
);
索引：btree (storegid, gid, qpc)
数据： 1000036 | 3001832 | 1.0000 | 5.0000 | 4.5000 | 0.0000 | 5.0000

表 D
CREATE TABLE D (
storegid integer,
gid integer,
qpcstr character varying(20),
qpc numeric(24,4),
isdu integer,
ispu integer,
iswu integer,
isru integer
);
索引：btree (storegid, gid)
1000033 | 3053299 | 1*1 | 1.0000 | 2 | 2 | 2 | 2


表 E
表：CREATE TABLE E (
storegid integer,
code character varying(20),
codetype integer,
gid integer,
rtlprc numeric(24,4),
qpc numeric(24,4),
munit character varying(6),
mbrprc numeric(24,4),
lwtrtlprc numeric(24,4),
score integer,
catalog character varying(6),
impprc numeric(24,4)
);

索引：btree (storegid, gid)
数据： 1000033 | 6917246013081 | 0 | 3003501 | 23.9000 | 1.0000 | 支 | 23.9000 | 0.0000 | 0 | A | 23.9000


很期待那几位经验丰富的大神能给出好的方案。

[cstj0505]

检查了几遍还是忘了，每张表是 20 张，所以一共 20*5