【mysql】Infobright和mysql数据入库性能测试

时间:2021-11-07 10:45:35
【mysql】Infobright和mysql数据入库性能测试

产生测试文件

测试文件部分内容如下:
【mysql】Infobright和mysql数据入库性能测试

产生测试文件代码:
  1. package foo;
  2. import java.io.File;
  3. import java.io.FileWriter;
  4. import java.io.IOException;
  5. import java.text.SimpleDateFormat;
  6. import java.util.Date;
  7. public class WriteIBdata {
  8. public static void main(String[] args) {
  9. String file = "C:\\linliu\\ib666";
  10. writeFile(file);
  11. }
  12. public static void writeFile(String fileName) {
  13. try {
  14. FileWriter fw = new FileWriter(new File(fileName));
  15. for (int i = 0; i < 10000 * 1500; i++) {
  16. SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  17. fw.write("'");
  18. fw.write(sdf.format(new Date()));
  19. fw.write("'");
  20. fw.write(",");
  21. fw.write("'");
  22. fw.write(String.valueOf(System.nanoTime()));
  23. fw.write("'");
  24. fw.write(",");
  25. fw.write("'");
  26. fw.write("aaaaaaaaaaaaaaaaaaaa");
  27. fw.write("'");
  28. fw.write("\n");
  29. if (i % 50000 == 0) {
  30. System.out.println("Finish:" + i/50000);
  31. }
  32. }
  33. fw.close();
  34. } catch (IOException e1) {
  35. }
  36. }
  37. }


测试Mysql数据库

创建数据表

  1. CREATE TABLE `loadtable` (
  2. `insertTstamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  3. `logType` varchar(64) NOT NULL,
  4. `content` varchar(64) NOT NULL,
  5. PRIMARY KEY (`insertTstamp`,`logType`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
【mysql】Infobright和mysql数据入库性能测试
【mysql】Infobright和mysql数据入库性能测试

导入数据

  1. load data infile '/home/testFile916M' into table loadtable fields terminated by ',' enclosed by '\'';
【mysql】Infobright和mysql数据入库性能测试

查看入库情况:
【mysql】Infobright和mysql数据入库性能测试

另外的入库数据:
【mysql】Infobright和mysql数据入库性能测试


测试Infobright数据库

创建数据表

  1. CREATE TABLE `loadtable` (
  2. `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  3. `type` varchar(64) NOT NULL,
  4. `content` varchar(64) NOT NULL
  5. ) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;
【mysql】Infobright和mysql数据入库性能测试

导入数据

  1. load data infile '/home/ib916M' into table loadtable fields terminated by ',' enclosed by '\'';
【mysql】Infobright和mysql数据入库性能测试

查看入库结果:
【mysql】Infobright和mysql数据入库性能测试


另外的入库数据:
【mysql】Infobright和mysql数据入库性能测试

总结

  • mysql入库:1500万条数据,约900M,入库时间大约在5min左右;
  • Infobright入库:1500万条数据,约900M,入库时间大约在2~3min左右;