asp.net实现Postgresql快速写入/读取大量数据实例
最近因为一些项目需要大量插入数据,研究了下asp.net实现postgresql快速写入/读取大量数据,所以留个笔记
环境及测试
使用.net驱动npgsql连接post数据库。配置:win10 x64, i5-4590, 16g ddr3, ssd 850evo.
postgresql 9.6.3,数据库与数据都安装在ssd上,默认配置,无扩展。
create table public.mesh ( x integer not null, y integer not null, z integer, constraint prim primary key (x, y) )
1. 导入
使用数据备份,csv格式导入,文件位于机械硬盘上,480mb,数据量2500w+。
使用copy
copy mesh from 'd:/user.csv' csv
运行时间107s
使用insert
单连接,c# release any cpu 非调试模式。
class program { static void main(string[] args) { var list = getdata("d:\\user.csv"); timecalc.logstarttime(); using (var sm = new sqlmanipulation(@"strings", sqltype.postgresql)) { sm.init(); foreach (var n in list) { sm.excutenonquery($"insert into mesh(x,y,z) values({n.x},{n.y},{n.z})"); } } timecalc.showtotalduration(); console.readkey(); } static list<(int x, int y, int z)> getdata(string filepath) { list<valuetuple<int, int, int>> list = new list<(int, int, int)>(); foreach (var n in file.readlines(filepath)) { string[] x = n.split(','); list.add((convert.toint32(x[0]), convert.toint32(x[1]), convert.toint32(x[2]))); } return list; } }
postgresql cpu占用率很低,但是跑了一年,程序依然不能结束,没有耐性了...,这么插入不行。
multiline insert
使用multiline插入,一条语句插入约100条数据。
var bag = getdata("d:\\user.csv"); //使用时,直接执行stringbuilder的tostring方法。 list<stringbuilder> listbuilder = new list<stringbuilder>(); stringbuilder sb = new stringbuilder(); for (int i = 0; i < bag.count; i++) { if (i % 100 == 0) { sb = new stringbuilder(); listbuilder.add(sb); sb.append("insert into mesh(x,y,z) values"); sb.append($"({bag[i].x}, {bag[i].y}, {bag[i].z})"); } else sb.append($",({bag[i].x}, {bag[i].y}, {bag[i].z})"); }
postgresql cpu占用率差不多27%,磁盘写入大约45mb/s,感觉就是在干活,最后时间217.36s。
改为1000一行的话,cpu占用率提高,但是磁盘写入平均来看有所降低,最后时间160.58s.
prepare语法
prepare语法可以让postgresql提前规划sql,优化性能。
使用单行插入 cpu占用率不到25%,磁盘写入63mb/s左右,但是,使用单行插入的方式,效率没有改观,时间太长还是等不来结果。
使用多行插入 cpu占用率30%,磁盘写入50mb/s,最后结果163.02,最后的时候出了个异常,就是最后一组数据长度不满足条件,无伤大雅。
static void main(string[] args) { var bag = getdata("d:\\user.csv"); list<stringbuilder> listbuilder = new list<stringbuilder>(); stringbuilder sb = new stringbuilder(); for (int i = 0; i < bag.count; i++) { if (i % 1000 == 0) { sb = new stringbuilder(); listbuilder.add(sb); //sb.append("insert into mesh(x,y,z) values"); sb.append($"{bag[i].x}, {bag[i].y}, {bag[i].z}"); } else sb.append($",{bag[i].x}, {bag[i].y}, {bag[i].z}"); } stringbuilder sbp = new stringbuilder(); sbp.append("prepare insertplan ("); for (int i = 0; i < 1000; i++) { sbp.append("int,int,int,"); } sbp.remove(sbp.length - 1, 1); sbp.append(") as insert into mesh(x, y, z) values"); for (int i = 0; i < 1000; i++) { sbp.append($"(${i*3 + 1},${i* 3 + 2},${i*3+ 3}),"); } sbp.remove(sbp.length - 1, 1); timecalc.logstarttime(); using (var sm = new sqlmanipulation(@"string", sqltype.postgresql)) { sm.init(); sm.excutenonquery(sbp.tostring()); foreach (var n in listbuilder) { sm.excutenonquery($"execute insertplan({n.tostring()})"); } } timecalc.showtotalduration(); console.readkey(); }
使用transaction
在前面的基础上,使用事务改造。每条语句插入1000条数据,每1000条作为一个事务,cpu 30%,磁盘34mb/s,耗时170.16s。
改成100条一个事务,耗时167.78s。
使用多线程
还在前面的基础上,使用多线程,每个线程建立一个连接,一个连接处理100条sql语句,每条sql语句插入1000条数据,以此种方式进行导入。注意,连接字符串可以将maxpoolsize设置大一些,我机器上实测,不设置会报连接超时错误。
cpu占用率上到80%, 磁盘这里需要注意,由于生成了非常多个postgresql server进程,不好统计,累积算上应该有小100mb/s,最终时间,98.18s。
使用tpl,由于parallel.foreach返回的结果没有检查,可能导致时间不是很准确(偏小)。
var lists = new list<list<string>>(); var listt = new list<string>(); for (int i = 0; i < listbuilder.count; i++) { if (i % 1000 == 0) { listt = new list<string>(); lists.add(listt); } listt.add(listbuilder[i].tostring()); } timecalc.logstarttime(); parallel.foreach(lists, (x) => { using (var sm = new sqlmanipulation(@";string;maxpoolsize=1000;", sqltype.postgresql)) { sm.init(); foreach (var n in x) { sm.excutenonquery(n); } } }); timecalc.showtotalduration();
写入方式 | 耗时(1000条/行) | |
---|---|---|
copy | 107s | |
insert | n/a | |
多行insert | 160.58s | |
prepare多行insert | 163.02s | |
事务多行insert | 170.16s | |
多连接多行insert | 98.18s |
2. 写入更新
数据实时更新,数量可能继续增长,使用简单的insert或者update是不行的,操作使用postgresql 9.5以后支持的新语法。
insert into mesh on conflict (x,y) do update set z = excluded.z
吐槽postgresql这么晚才支持on conflict,mysql早有了...
在表中既有数据2500w+的前提下,重复往数据库里面写这些数据。这里只做多行插入更新测试,其他的结果应该差不多。
普通多行插入,耗时272.15s。
多线程插入的情况,耗时362.26s,cpu占用率一度到了100%。猜测多连接的情况下,更新互锁导致性能下降。
3. 读取
select方法
标准读取还是用select方法,ado.net直接读取。
使用adapter方式,耗时135.39s;使用dbreader方式,耗时71.62s。
copy方法
postgresql的copy方法提供stdout binary方式,可以指定一条查询进行输出,耗时53.20s。 public list<(int x, int y, int z)> bulkiquerynpg() { list<(int, int, int)> dict = new list<(int, int, int)>(); using (var reader = ((npgsqlconnection)_conn).beginbinaryexport("copy (select x,y,z from mesh) to stdout (format binary)")) { while (reader.startrow() != -1) { var x = reader.read<int>(npgsqldbtype.integer); var y = reader.read<int>(npgsqldbtype.integer); var z = reader.read<int>(npgsqldbtype.integer); dict.add((x, y, z)); } } return dict; }
结论
总结测试结果,对于较多数据的情况下,可以得出以下结论:
- 向空数据表导入或者没有重复数据表的导入,优先使用copy语句(为什么有这个前提详见p.s.);
- 使用一条语句插入多条数据的方式能够大幅度改善插入性能,可以实验确定最优条数;
- 使用transaction或者prepare插入,在本场景中优化效果不明显;
- 使用多连接/多线程操作,速度上有优势,但是把握不好容易造成资源占用率过高,连接数太大也容易影响其他应用;
- 写入更新是postgresql新特性,使用会造成一定的性能消耗(相对直接插入);
- 读取数据时,使用copy语句能够获得较好的性能;
- ado.net dbreader对象由于不需要fill的过程,读取速度也较快(虽然赶不上copy),也可优先考虑。
p.s.
为什么不用mysql
没有最好的,只有最合适的,讲道理我也是挺喜欢用mysql的。使用postgresql的原因主要在于:
postgresql导入导出的sql指令“copy”直接支持binary模式到stdin和stdout,如果程序想直接集成,那么用这个是比较方便的;相比较,mysql的sql语法(load data infile)并不支持到stdin或者stdout,导出可以通过mysqldump.exe实现,导入暂时没什么特别好的办法(mysqlimport或许可以)。
相较于mysql缺点
postgresql使用copy导入的时候,如果目标表已经有数据,那么在有主键约束的表遇到错误时,copy自动终止,而且可能导致不完全插入的情况,换言之,是不支持导入的过程进行update操作;mysql的load语法可以显式指定出错之后的动作(ignore/replace),不会打断导入过程。
其他
如果需要使用mysql从程序导入数据,可以考虑先通过程序导出到文件,然后借助文件进行导入,据说效率也要比insert高出不少。
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。
下一篇: 详解Mysql中的视图