查看: 2845|回复: 0

[.NET源码] asp.net实现Postgresql快速写入/读取大量数据实例

发表于 2018-1-13 09:30:42

最近因为一些项目需要大量插入数据,研究了下asp.net实现Postgresql快速写入/读取大量数据,所以留个笔记

环境及测试

使用.net驱动npgsql连接post数据库。配置:win10 x64, i5-4590, 16G DDR3, SSD 850EVO.

postgresql 9.6.3,数据库与数据都安装在SSD上,默认配置,无扩展。

  1. CREATE TABLE public.mesh
  2. (
  3. x integer NOT NULL,
  4. y integer NOT NULL,
  5. z integer,
  6. CONSTRAINT prim PRIMARY KEY (x, y)
  7. )
复制代码

1. 导入

使用数据备份,csv格式导入,文件位于机械硬盘上,480MB,数据量2500w+。

使用COPY

  1. copy mesh from 'd:/user.csv' csv
复制代码

运行时间107s

使用insert

单连接,C# release any cpu 非调试模式。

  1. class Program
  2. {
  3. static void Main(string[] args)
  4. {
  5. var list = GetData("D:\\user.csv");
  6. TimeCalc.LogStartTime();
  7. using (var sm = new SqlManipulation(@"Strings", SqlType.PostgresQL))
  8. {
  9. sm.Init();
  10. foreach (var n in list)
  11. {
  12. sm.ExcuteNonQuery($"insert into mesh(x,y,z) values({n.x},{n.y},{n.z})");
  13. }
  14. }
  15. TimeCalc.ShowTotalDuration();
  16. Console.ReadKey();
  17. }
  18. static List<(int x, int y, int z)> GetData(string filepath)
  19. {
  20. List<ValueTuple<int, int, int>> list = new List<(int, int, int)>();
  21. foreach (var n in File.ReadLines(filepath))
  22. {
  23. string[] x = n.Split(',');
  24. list.Add((Convert.ToInt32(x[0]), Convert.ToInt32(x[1]), Convert.ToInt32(x[2])));
  25. }
  26. return list;
  27. }
  28. }
复制代码

Postgresql CPU占用率很低,但是跑了一年,程序依然不能结束,没有耐性了...,这么插入不行。

multiline insert

使用multiline插入,一条语句插入约100条数据。

  1. var bag = GetData("D:\\user.csv");
  2. //使用时,直接执行stringbuilder的tostring方法。
  3. List<StringBuilder> listbuilder = new List<StringBuilder>();
  4. StringBuilder sb = new StringBuilder();
  5. for (int i = 0; i < bag.Count; i++)
  6. {
  7. if (i % 100 == 0)
  8. {
  9. sb = new StringBuilder();
  10. listbuilder.Add(sb);
  11. sb.Append("insert into mesh(x,y,z) values");
  12. sb.Append($"({bag[i].x}, {bag[i].y}, {bag[i].z})");
  13. }
  14. else
  15. sb.Append($",({bag[i].x}, {bag[i].y}, {bag[i].z})");
  16. }
复制代码

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,最后的时候出了个异常,就是最后一组数据长度不满足条件,无伤大雅。

  1. static void Main(string[] args)
  2. {
  3. var bag = GetData("D:\\user.csv");
  4. List<StringBuilder> listbuilder = new List<StringBuilder>();
  5. StringBuilder sb = new StringBuilder();
  6. for (int i = 0; i < bag.Count; i++)
  7. {
  8. if (i % 1000 == 0)
  9. {
  10. sb = new StringBuilder();
  11. listbuilder.Add(sb);
  12. //sb.Append("insert into mesh(x,y,z) values");
  13. sb.Append($"{bag[i].x}, {bag[i].y}, {bag[i].z}");
  14. }
  15. else
  16. sb.Append($",{bag[i].x}, {bag[i].y}, {bag[i].z}");
  17. }
  18. StringBuilder sbp = new StringBuilder();
  19. sbp.Append("PREPARE insertplan (");
  20. for (int i = 0; i < 1000; i++)
  21. {
  22. sbp.Append("int,int,int,");
  23. }
  24. sbp.Remove(sbp.Length - 1, 1);
  25. sbp.Append(") AS INSERT INTO mesh(x, y, z) values");
  26. for (int i = 0; i < 1000; i++)
  27. {
  28. sbp.Append($"(${i*3 + 1},${i* 3 + 2},${i*3+ 3}),");
  29. }
  30. sbp.Remove(sbp.Length - 1, 1);
  31. TimeCalc.LogStartTime();
  32. using (var sm = new SqlManipulation(@"string", SqlType.PostgresQL))
  33. {
  34. sm.Init();
  35. sm.ExcuteNonQuery(sbp.ToString());
  36. foreach (var n in listbuilder)
  37. {
  38. sm.ExcuteNonQuery($"EXECUTE insertplan({n.ToString()})");
  39. }
  40. }
  41. TimeCalc.ShowTotalDuration();
  42. Console.ReadKey();
  43. }
复制代码

使用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返回的结果没有检查,可能导致时间不是很准确(偏小)。

  1. var lists = new List<List<string>>();
  2. var listt = new List<string>();
  3. for (int i = 0; i < listbuilder.Count; i++)
  4. {
  5. if (i % 1000 == 0)
  6. {
  7. listt = new List<string>();
  8. lists.Add(listt);
  9. }
  10. listt.Add(listbuilder[i].ToString());
  11. }
  12. TimeCalc.LogStartTime();
  13. Parallel.ForEach(lists, (x) =>
  14. {
  15. using (var sm = new SqlManipulation(@";string;MaxPoolSize=1000;", SqlType.PostgresQL))
  16. {
  17. sm.Init();
  18. foreach (var n in x)
  19. {
  20. sm.ExcuteNonQuery(n);
  21. }
  22. }
  23. });
  24. 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以后支持的新语法。

  1. 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方法

  1. postgresql的copy方法提供stdout binary方式,可以指定一条查询进行输出,耗时53.20s。
  2. public List<(int x, int y, int z)> BulkIQueryNpg()
  3. {
  4. List<(int, int, int)> dict = new List<(int, int, int)>();
  5. using (var reader = ((NpgsqlConnection)_conn).BeginBinaryExport("COPY (select x,y,z from mesh) TO STDOUT (FORMAT BINARY)"))
  6. {
  7. while (reader.StartRow() != -1)
  8. {
  9. var x = reader.Read<int>(NpgsqlDbType.Integer);
  10. var y = reader.Read<int>(NpgsqlDbType.Integer);
  11. var z = reader.Read<int>(NpgsqlDbType.Integer);
  12. dict.Add((x, y, z));
  13. }
  14. }
  15. return dict;
  16. }
复制代码

结论

总结测试结果,对于较多数据的情况下,可以得出以下结论:

  1. 向空数据表导入或者没有重复数据表的导入,优先使用COPY语句(为什么有这个前提详见P.S.);
  2. 使用一条语句插入多条数据的方式能够大幅度改善插入性能,可以实验确定最优条数;
  3. 使用transaction或者prepare插入,在本场景中优化效果不明显;
  4. 使用多连接/多线程操作,速度上有优势,但是把握不好容易造成资源占用率过高,连接数太大也容易影响其他应用;
  5. 写入更新是postgresql新特性,使用会造成一定的性能消耗(相对直接插入);
  6. 读取数据时,使用COPY语句能够获得较好的性能;
  7. 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高出不少。

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持程序员之家。



回复

使用道具 举报