1. Home
  2. Infobright基础手册
  3. Infobrigh数据导入性能

Infobrigh数据导入性能

首先我们需要一些测试数据,我们从线上一个日志库里拉去1千万条数据导出为CSV文件,命名为gos_so2do_statistic.csv,作为测试备用数据。

mysql> select * from gos_so2do_statistic limit 10000000 into outfile ‘/tmp/gos_so2do_statistic.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\”‘   ESCAPED BY ‘\\’  LINES TERMINATED BY ‘\n’;

Query OK, 10000000 rows affected (48.86 sec)

我们分别在同一服务器上安装MySQL5.5和infobright数据库。MySQL5.5内存和参数都作了优化,infobright采用默认配置。

然后MySQL上建立Innodb,Myisam的两个测试表,在infobright数据库里建立Brighthouse引擎的测试表。三个表表结构一致。

建表语句如下所示:

Innodb:

CREATE TABLE `gos_so2do_statistic_innodb` (

`ID` bigint(20) NOT NULL AUTO_INCREMENT,

`WAREHOUSE_ID` bigint(20) NOT NULL COMMENT ‘仓库ID’,

`INVOKE_TIME_PERIOD` bigint(20) NOT NULL,

`INVOKE_TIMES` bigint(20) NOT NULL DEFAULT ‘0’,

`SERVER_IP` varchar(80) DEFAULT NULL COMMENT ‘服务器IP’,

`CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘日志创建时间’,

`UPDATE_TIME` datetime DEFAULT NULL COMMENT ‘更新时间’,

PRIMARY KEY (`ID`),

KEY `IDX_SERVER_IP` (`SERVER_IP`),

KEY `IDX_CREATE_TIME` (`CREATE_TIME`),

KEY `IDX_WAREHOUSE_ID` (`WAREHOUSE_ID`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT=’GOS,SO转DO统计表’;

 

MyISAM:

CREATE TABLE `gos_so2do_statistic_myisam` (

`ID` bigint(20) NOT NULL AUTO_INCREMENT,

`WAREHOUSE_ID` bigint(20) NOT NULL COMMENT ‘仓库ID’,

`INVOKE_TIME_PERIOD` bigint(20) NOT NULL,

`INVOKE_TIMES` bigint(20) NOT NULL DEFAULT ‘0’,

`SERVER_IP` varchar(80) DEFAULT NULL COMMENT ‘服务器IP’,

`CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘日志创建时间’,

`UPDATE_TIME` datetime DEFAULT NULL COMMENT ‘更新时间’,

PRIMARY KEY (`ID`),

KEY `IDX_SERVER_IP` (`SERVER_IP`),

KEY `IDX_CREATE_TIME` (`CREATE_TIME`),

KEY `IDX_WAREHOUSE_ID` (`WAREHOUSE_ID`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT=’GOS,SO转DO统计表’;

 

Brighthouse:

CREATE TABLE `gos_so2do_statistic_brighthouse` (

`ID` bigint(20) NOT NULL ,

`WAREHOUSE_ID` bigint(20) NOT NULL COMMENT ‘仓库ID’,

`INVOKE_TIME_PERIOD` bigint(20) NOT NULL,

`INVOKE_TIMES` bigint(20) NOT NULL DEFAULT ‘0’,

`SERVER_IP` varchar(80) DEFAULT NULL COMMENT ‘服务器IP’,

`CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘日志创建时间’,

`UPDATE_TIME` datetime DEFAULT NULL COMMENT ‘更新时间’

) ENGINE=BRIGHTHOUSE  DEFAULT CHARSET=utf8 COMMENT=’GOS,SO转DO统计表’;

 

我们将之前导出的1千万条测试记录分别插入到Innodb,Myisam,Brighthouse三种引擎,然后统计三种引擎load data的时间。测试性能如下所示:

Innodb:

mysql> load data infile ‘/tmp/gos_so2do_statistic.csv’ into table gos_so2do_statistic_innodb FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\”‘   ESCAPED BY ‘\\’  LINES TERMINATED BY ‘\n’;

Query OK, 10000000 rows affected (7 min 45.20 sec)

Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

Myisam:

mysql> load data infile ‘/tmp/gos_so2do_statistic.csv’ into table gos_so2do_statistic_myisam FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\”‘   ESCAPED BY ‘\\’  LINES TERMINATED BY ‘\n’;

Query OK, 10000000 rows affected (2 min 2.41 sec)

Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

Brighthouse:

mysql> load data infile ‘/tmp/gos_so2do_statistic.csv’ into table gos_so2do_statistic_brighthouse FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\”‘   ESCAPED BY ‘\\’  LINES TERMINATED BY ‘\n’;

Query OK, 10000000 rows affected (30.10 sec)

Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

这篇文档是否帮助到了您? 2