Infobright ICE数据仓库在线实战手册

Infobright(1)简介和特性

Infobright简介:

Infobright是开源的MySQL数据仓库解决方案,引入了列存储方案,高强度的数据压缩,优化的统计计算(类似sum/avg/group by之类),

infobright 是基于mysql的,但不装mysql亦可,因为它本身就自带了一个。mysql可以粗分为逻辑层和物理存储引擎,infobright主要实现的就是一个存储引擎,但因为它自身存储逻辑跟关系型数据库根本不同,所以,它不能像InnoDB那样直接作为插件挂接到mysql,它的逻辑层是mysql的逻辑 层加上它自身的优化器。Infobright有社区版和企业版两个不同的版本。社区版是免费的,但是功能也有局限性。

 

特性:

1.存储数据量大,理想情况下可存储50TB数据

2.高压缩比例,平均压缩比例达到10:1,最高可以到达40:1 彻底的减少IO,提高查询性能

3.随着数据库数据大小的增大,查询性能和数据库负载会保持不变,即时数据量很大,查询速度也很快

4.基于列存储,不需要建物化视图,分区策略

5.不需要建索引,就避免了维护索引及索引随着数据膨胀的问题。把每列数据分块压缩存放,每块有知识网格节点记录块内的统计信息,代替索引,加速搜索。

6.管理操作简单

7.没有许可证费用

 

Infobright缺点:

不支持数据更新:社区版 Infobright 只能使用“LOAD DATA INFILE”的方式导入数据,不支持 INSERT、UPDATE、DELETE。

不支持高并发:只能支持10多个并发查询。

 

Infobright(2)适用场景分析

Infobright比较适合日志型数据库的存储:

1.        网站日志数据记录后就为既定事实,无须更新。可归档为历史数据。

2.        分析人员对日志的操作往往集中于某几列。所以适合列式数据库的存储。查询性能较高。比查询MySQL更有优势。

3.        日志数据往往占用空间比较多,infobright的高压缩比可以解决空间不足的问题。

 

Infobright(3)架构分析

架构图如下:

 

灰色部分是mysql原有的模块,白色与蓝色部分则是 infobright自身的。

系统结构分析:

如上图所示,Infobright采用了和MySQL一致的构架,分为两层。上层是服务及应用管理,下层是存储引擎。Infobright的默认存储引擎是brighthouse,但是Infobright还可以支持其他的存储引擎,比如MyISAM、MRG_MyISAM、Memory、CSV。Infobright通过三层来组织数据,分别是DP(Data Pack)、DPN(Data Pack Node)、KN(Knowledge Node)。而在这三层之上就是无比强大的知识网络(Knowledge Grid)。

数据块(DP)是存储的最低层,列中每64K个单元组成一个DP。DP比列更小,具有更好的压缩比率;又比单个数据单元更大,具有更好的查询性能。

数据块节点(DPN),DPN和DP之间是一对一的关系。DPN记录着每一个DP里面存储和压缩的一些统计数据,包括最大值、最小值、null的个数、单元总数count、sum等等。

KN里面存储着指向DP之间或者列之间关系的一些元数据集合,比如值发生的范围(MIin_Max)、列数据之间的关联。大部分的KN数据是装载数据的时候产生的,另外一些事是查询的时候产生。

在这三层之上是知识网络(Knowledge Grid),Knowledge Grid构架是Infobright高性能的重要原因。

 

 

Knowledge Grid可分为四部分,DPN、Histogram、CMAP、P-2-P。

DPN如上所述。Histogram用来提高数字类型(比如date,time,decimal)的查询的性能。Histogram是装载数据的时候就产生的。DPN中有mix、max。Histogram中把Min-Max分成1024段,如果Mix_Max范围小于1024的话,每一段就是就是一个单独的值。这个时候KN就是一个数值是否在当前段的二进制表示。

 

 

Histogram的作用就是快速判断当前DP是否满足查询条件。如上图所示,比如select id from customerInfo where id>50 and id<70。那么很容易就可以得到当前DP不满足条件。所以Histogram对于那种数字限定的查询能够很有效地减少查询DP的数量。

 

CMAP是针对于文本类型的查询,也是装载数据的时候就产生的。CMAP是统计当前DP内,ASCII在1-64位置出现的情况。如下图所示

 

比如上面的图说明了A在文本的第二个、第三个、第四个位置从来没有出现过。0表示没有出现,1表示出现过。查询中文本的比较归根究底还是按照字节进行比较,所以根据CMAP能够很好地提高文本查询的性能。

Pack-To-Pack是Join操作的时候产生的,它是表示join的两个DP中操作的两个列之间关系的位图,也就是二进制表示的矩阵。

Knowledge Grid还是比较复杂的,里面还有很多细节的东西,可以参考官方的白皮书和Brighthouse: an analytic data warehouse for ad-hoc queries这篇论文。

 

Infobright(4)工作原理

前面已经简要分析了Infobright的构架,现在来介绍Infobright的工作原理。

粗糙集(Rough Sets)是Infobright的核心技术之一。Infobright在执行查询的时候会根据知识网络(Knowledge Grid)把DP分成三类:

相关的DP(Relevant Packs),满足查询条件限制的DP

不相关的DP(Irrelevant Packs),不满足查询条件限制的DP

可疑的DP(Suspect Packs),DP里面的数据部分满足查询条件的限制

 

下面是一个案例:

 

如图所示,每一列总共有5个DP,其中限制条件是A>6。所以A1、A2、A4就是不相关的DP,A3是相关的DP,A5是可疑的DP。那么执行查询的时候只需要计算B5中满足条件的记录的和然后加上Sum(B3),Sum(B3)是已知的。此时只需要解压缩B5这个DP。从上面的分析可以知道,Infobright能够很高效地执行一些查询,而且执行的时候where语句的区分度越高越好。where区分度高可以更精确地确认是否是相关DP或者是不相关DP亦或是可以DP,尽可能减少DP的数量、减少解压缩带来的性能损耗。在做条件判断的使用,一般会用到上一章所讲到的Histogram和CMAP,它们能够有效地提高查询性能。

多表连接的的时候原理也是相似的。先是利用Pack-To-Pack产生join的那两列的DP之间的关系。

比如:SELECT MAX(X.D) FROM T JOIN X ON T.B = X.C WHERE T.A > 6。Pack-To-Pack产生T.B和X.C的DP之间的关系矩阵M。假设T.B的第一个DP和X.C的第一个DP之间有元素交叉,那么M[1,1]=1,否则M[1,1]=0。这样就有效地减少了join操作时DP的数量。

前面降到了解压缩,顺便提一提DP的压缩。每个DP中的64K个元素被当成是一个序列,其中所有的null的位置都会被单独存储,然后其余的non-null的数据会被压缩。数据的压缩跟数据的类型有关,infobright会根据数据的类型选择压缩算法。infobright会自适应地调节算法的参数以达到最优的压缩比。

 

Infobright(5)数据类型

Infobright里面支持所有的MySQL原有的数据类型。其中Integer类型比其他数据类型更加高效。尽可能使用以下的数据类型:

TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT

DECIMAL(尽量减少小数点位数)

DATE ,TIME

效率比较低的、不推荐使用的数据类型有:

BINARY VARBINARY

FLOAT

DOUBLE

VARCHAR

TINYTEXT TEXT

Infobright数据类型使用的一些经验和注意点:

(1)Infobright的数值类型的范围和MySQL有点不一样,比如Infobright的Int的最小值是-2147483647,而MySQl的Int最小值应该是-2147483648。其他的数值类型都存在这样的问题。

(2)能够使用小数据类型就使用小数据类型,比如能够使用SMALLINT就不适用INT,这一点上Infobright和MySQL保持一致。

(3)避免效率低的数据类型,像TEXT之类能不用就不用,像FLOAT尽量用DECIMAL代替,但是需要权衡毕竟DECIMAL会损失精度。

(4)尽量少用VARCHAR,在MySQL里面动态的Varchar性能就不强,所以尽量避免VARCHAR。如果适合的话可以选择把VARCHAR改成CHAR存储甚至专程INTEGER类型。VARCHAR的优势在于分配空间的长度可变,既然Infobright具有那么优秀的压缩性能,个人认为完全可以把VARCHAR转成CHAR。CHAR会具有更好的查询和压缩性能。

(5)能够使用INT的情况尽量使用INT,很多时候甚至可以把一些CHAR类型的数据往整型转化。比如搜索日志里面的客户永久id、客户id等等数据就可以用BIGINT存储而不用CHAR存储。其实把时间分割成year、month、day三列存储也是很好的选择。在我能见到的系统里面时间基本上是使用频率最高的字段,提高时间字段的查询性能显然是非常重要的。当然这个还是要根据系统的具体情况,做数据分析时有时候很需要MySQL的那些时间函数。

(6)varchar和char字段还可以使用comment lookup,comment lookup能够显著地提高压缩比率和查询性能。

 

Infobright

 

Infobright(6)查询优化

前面已经分析了Infobright的构架,简要介绍了Infobright的压缩过程和工作原理。现在来讨论查询优化的问题。

 

(1)配置环境

在Linux下面,Infobright环境的配置可以根据README里的要求,配置brighthouse.ini文件。

(2) 选取高效的数据类型

参见前面章节。

(3)使用comment lookup

参见前面章节。

(4)尽量有序地导入数据

前面分析过Infobright的构架,每一列分成n个DP,每个DPN列面存储着DP的一些统计信息。有序地导入数据能够使不同的DP的DPN内的数据差异化更明显。比如按时间date顺序导入数据,那么前一个DP的max(date)<=下一个DP的min(date),查询的时候就能够减少可疑DP,提高查询性能。换句话说,有序地导入数据就是使DP内部数据更加集中,而不再那么分散。

(5)使用高效的查询语句。

这里涉及的内容比较多了,总结如下:

尽量不适用or,可以采用in或者union取而代之

减少IO操作,原因是infobright里面数据是压缩的,解压缩的过程要消耗很多的时间。

查询的时候尽量条件选择差异化更明显的语句

Select中尽量使用where中出现的字段。原因是Infobright按照列处理的,每一列都是单独处理的。所以避免使用where中未出现的字段可以得到较好的性能。

限制在结果中的表的数量,也就是限制select中出现表的数量。

尽量使用独立的子查询和join操作代替非独立的子查询

尽量不在where里面使用MySQL函数和类型转换符

尽量避免会使用MySQL优化器的查询操作

使用跨越Infobright表和MySQL表的查询操作

尽量不在group by 里或者子查询里面使用数学操作,如sum(a*b)。

select里面尽量剔除不要的字段。

Infobright执行查询语句的时候,大部分的时间都是花在优化阶段。Infobright优化器虽然已经很强大,但是编写查询语句的时候很多的细节问题还是需要程序员注意。

Infobright(7)安装部署

Infobright支持Linux和Windows操作系统的安装。可以使用rpm包方式和源码编译方式安装。下面我们通过RPM包方式在Linux下安装和配置infobright.

用户文档 https://www.infobright.org/index.php/ICE_Wiki/wiki-4/

 

1.在官方网站下载最新的安装包,下载地址为:

https://www.infobright.org/index.php/download/ICE/

2.用root权限进入系统,上传软件包

3.安装rpm包

# rpm -i infobright-4.0.7-0-x86_64-ice.rpm

Installing infobright 4.0.7-0 (x86_64)

The installer will generate /tmp/ib4.0.7-0-install.log install trace log.

Creating/Updating datadir and cachedir

Creating user mysql and group mysql

Installing default databases

Installing MySQL system tables…

OK

Filling help tables…

OK

 

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

 

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

 

/usr/local/infobright-4.0.7-x86_64/bin/mysqladmin -u root password ‘new-password’

/usr/local/infobright-4.0.7-x86_64/bin/mysqladmin -u root -h db-2-47 password ‘new-password’

 

Alternatively you can run:

/usr/local/infobright-4.0.7-x86_64/bin/mysql_secure_installation

 

which will also give you the option of removing the test

databases and anonymous user created by default.  This is

strongly recommended for production servers.

 

See the manual for more instructions.

 

You can start the MySQL daemon with:

cd /usr/local/infobright-4.0.7-x86_64 ; /usr/local/infobright-4.0.7-x86_64/bin/mysqld_safe &

 

You can test the MySQL daemon with mysql-test-run.pl

cd /usr/local/infobright-4.0.7-x86_64/mysql-test ; perl mysql-test-run.pl

 

Please report any problems with the /usr/local/infobright-4.0.7-x86_64/scripts/mysqlbug script!

 

The latest information about MySQL is available at http://www.mysql.com/

Support MySQL by buying support/licenses from http://shop.mysql.com/

 

System Physical memory: 258029(MB)

Infobright optimal ServerMainHeapSize is set to 32000(MB)

Infobright optimal LoaderMainHeapSize is set to 800(MB)

Infobright server installed into folder /usr/local/infobright

Installation log file /tmp/ib4.0.7-0-install.log

————————————–

To activate infobright server, please run ./postconfig.sh script from /usr/local/infobright-4.0.7-x86_64.

Example command: cd /usr/local/infobright-4.0.7-x86_64; ./postconfig.sh

 

 

4.配置文件

默认安装后配置信息如下:

配置文件: [/etc/my-ib.cnf]

brighthouse.ini 文件: [/usr/local/infobright-4.0.7-x86_64/data/brighthouse.ini]

数据存放目录: [/usr/local/infobright-4.0.7-x86_64/data]

缓存目录: [/usr/local/infobright-4.0.7-x86_64/cache]

socket 位置: [/tmp/mysql-ib.sock]

port 端口: [5029]

 

5.改变安装的默认路径,执行命令:

# cd /usr/local/infobright

# /usr/local/infobright/postconfig.sh

这个脚本的执行可以在安装之后的任意时间执行,它是用来改变安装的datadir,CacheFolder, socket和port的。这个脚本执行的时候必须保证ICE没有在运行。

 

 

6.启动 Infobright 数据库

# /etc/init.d/mysqld-ib start

Starting MySQL.    [  OK  ]

 

7.修改密码

# /usr/local/infobright/bin/mysqladmin -h127.0.0.1 -P5029 -uroot password yihaodian

 

8.登录数据库

[root@db-2-47 bin]# mysql-ib -uroot -pyihaodian

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.1.40 build number (revision)=IB_4.0.7_r16961_17249(ice) (static)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

 

mysql> show engines;

+————-+———+———————————————————–+————–+——+————+

| Engine      | Support | Comment                                                   | Transactions | XA   | Savepoints |

+————-+———+———————————————————–+————–+——+————+

| BRIGHTHOUSE | DEFAULT | Brighthouse storage engine                                | YES          | NO   | NO         |

| MRG_MYISAM  | YES     | Collection of identical MyISAM tables                     | NO           | NO   | NO         |

| CSV         | YES     | CSV storage engine                                        | NO           | NO   | NO         |

| MyISAM      | YES     | Default engine as of MySQL 3.23 with great performance    | NO           | NO   | NO         |

| MEMORY      | YES     | Hash based, stored in memory, useful for temporary tables | NO           | NO   | NO         |

+————-+———+———————————————————–+————–+——+————+

5 rows in set (0.00 sec)

 

Infobright(8)数据导入测试

首先我们需要一些测试数据,我们从线上一个日志库里拉去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

 

Infobright(9)压缩比例测试

三个表分别导入1千万数据后对三个表进行大小统计,结果如下:

Innodb:

mysql>  select concat(truncate(sum(data_length)/1024/1024,2),’MB’) as data_size,

->  concat(truncate(sum(max_data_length)/1024/1024,2),’MB’) as max_data_size,

->  concat(truncate(sum(data_free)/1024/1024,2),’MB’) as data_free,

->  concat(truncate(sum(index_length)/1024/1024,2),’MB’) as index_size

->  from information_schema.tables where TABLE_NAME = ‘gos_so2do_statistic_innodb';

+———–+—————+———–+————+

| data_size | max_data_size | data_free | index_size |

+———–+—————+———–+————+

| 687.00MB  | 0.00MB        | 6.00MB    | 792.89MB   |

+———–+—————+———–+————+

1 row in set (0.49 sec)

 

Myisam:

mysql> select concat(truncate(sum(data_length)/1024/1024,2),’MB’) as data_size,

->  concat(truncate(sum(max_data_length)/1024/1024,2),’MB’) as max_data_size,

->  concat(truncate(sum(data_free)/1024/1024,2),’MB’) as data_free,

->  concat(truncate(sum(index_length)/1024/1024,2),’MB’) as index_size

->  from information_schema.tables where TABLE_NAME = ‘gos_so2do_statistic_myisam';

+———–+—————-+———–+————+

| data_size | max_data_size  | data_free | index_size |

+———–+—————-+———–+————+

| 510.16MB  | 268435455.99MB | 0.00MB    | 399.07MB   |

+———–+—————-+———–+————+

1 row in set (0.00 sec)

 

Brighthouse:

mysql> select concat(truncate(sum(data_length)/1024/1024,2),’MB’) as data_size,

->  concat(truncate(sum(max_data_length)/1024/1024,2),’MB’) as max_data_size,

->  concat(truncate(sum(data_free)/1024/1024,2),’MB’) as data_free,

->  concat(truncate(sum(index_length)/1024/1024,2),’MB’) as index_size

->  from information_schema.tables where TABLE_NAME = ‘gos_so2do_statistic_brighthouse';

+———–+—————+———–+————+

| data_size | max_data_size | data_free | index_size |

+———–+—————+———–+————+

| 22.03MB   | 0.00MB        | 0.00MB    | 0.00MB     |

+———–+—————+———–+————+

1 row in set (0.01 sec)

Infobright(10)查询效率测试

分别对三种不同引擎的表执行聚合查询,我们统计执行时间如下:

Innodb:

mysql> select count(*) from gos_so2do_statistic_innodb;

+———-+

| count(*) |

+———-+

| 10000000 |

+———-+

1 row in set (23.11 sec)

mysql> select count(server_ip) as server_ip,warehouse_id from gos_so2do_statistic_innodb group by server_ip;

+———–+————–+

| server_ip | warehouse_id |

+———–+————–+

|   1233545 |        13642 |

|   1252395 |        17459 |

|   1276362 |         1512 |

|   1215126 |         7732 |

|   1259328 |          356 |

|   1242216 |         9643 |

|   1268948 |        18286 |

|   1234421 |         3577 |

|     17659 |         3845 |

+———–+————–+

9 rows in set, 1 warning (31.62 sec)

Myisam:

mysql> select count(*) from gos_so2do_statistic_myisam;

+———-+

| count(*) |

+———-+

| 10000000 |

+———-+

1 row in set (0.00 sec)

mysql> select count(server_ip) as server_ip,warehouse_id from gos_so2do_statistic_myisam group by server_ip;

+———–+————–+

| server_ip | warehouse_id |

+———–+————–+

|   1233545 |        13642 |

|   1252395 |        17459 |

|   1276362 |         1512 |

|   1215126 |         7732 |

|   1259328 |          356 |

|   1242216 |         9643 |

|   1268948 |        18286 |

|   1234421 |         3577 |

|     17659 |         3845 |

+———–+————–+

9 rows in set, 1 warning (12.81 sec)

 

Brighthouse:

mysql> select count(*) from gos_so2do_statistic_brighthouse;

+———-+

| count(*) |

+———-+

| 10000000 |

+———-+

1 row in set (0.00 sec)

mysql> select count(server_ip) as server_ip,warehouse_id from gos_so2do_statistic_brighthouse group by server_ip;

+———–+————–+

| server_ip | warehouse_id |

+———–+————–+

|   1242216 |         9643 |

|   1276362 |         1512 |

|   1233545 |        13642 |

|   1259328 |          356 |

|   1252395 |        17459 |

|   1234421 |         3577 |

|   1215126 |         7732 |

|   1268948 |        18286 |

|     17659 |         3845 |

+———–+————–+

9 rows in set, 1 warning (5.30 sec)

 

Infobright(11)删除数据测试

Innodb:

mysql> delete from gos_so2do_statistic_innodb;

Query OK, 10000000 rows affected (19 min 17.17 sec)

 

 

Myisam:

mysql> delete from gos_so2do_statistic_myisam;

Query OK, 10000000 rows affected (0.30 sec)

 

Brighthouse:

mysql> delete from gos_so2do_statistic_brighthouse;

ERROR 1031 (HY000): Table storage engine for ‘gos_so2do_statistic_brighthouse’ doesn’t have this option

mysql>

 

Infobright(12)性能测试报告

1000万数据处理报告:
导入时间
占用磁盘空间
聚合查询时间
删除数据
MySQL5.5 Innodb
7 min 45.20 sec
1.5G
31.62 sec
19 min 17.17 sec
MySQL5.5 MyISAM
2 min 2.41 sec
900M
12.81 sec
0.30 sec
MySQL Infobright
30.10 sec
22M
5.30 sec
Not support
 

 

MySQL到Infobright数据自动化迁移

基本架构图:

架构说明如下:数据库为主备模式,最小单位为1主1备。首先线上业务将日志数据实实记录到MySQL主库,并同步到备库。在备库基于表订制迁移规则,将抽取的日志数据远程传输到infobright仓库,并load到数据仓库中。并发送通知到DBA。线上主库案仅保存1个月数据即可。备库可案需保存1~3个月数据。作为数据备份和即时查询。数据仓库保留1年以上的历史数据,供查询分析人员随时查询。

 

需要注意事项:

1.主库建日志表的时候最好按日期建立为分区表。方便清理数据和提高数据抽取时的效率。

2.数据仓库采用分表方式建立。每月一个表。查询分析时需要指定表后缀。因为infobright社区版不支持数据删除操作,这样分表可以方便以后清理数据。并且按月查询也可以提高查询效率。

3.因为数据抽取和导入是基于表级别的,需要为每个表的迁移写不同的程序抽取和导入。所以适合单表数据比较大,但是表数量不多的日志表 。

 

迁移案例

数据仓库创建表

CREATE TABLE `INSHOP_API_ACCESS_LOG_201407` (

`ID` bigint(20) NOT NULL COMMENT ‘主键ID’,

`MERCHANT_ID` bigint(18) DEFAULT NULL,

`INVOKE_METHOD` varchar(100) DEFAULT NULL COMMENT ‘方法名称’,

`METHOD_VER` varchar(20) DEFAULT NULL COMMENT ‘版本号’,

`RESULT_TYPE` int(2) DEFAULT NULL COMMENT ‘返回值类型’,

`RESULT_CODE` varchar(50) DEFAULT NULL COMMENT ‘返回错误编码’,

`ERP` varchar(20) DEFAULT NULL COMMENT ‘erp标识’,

`ERP_VER` varchar(20) DEFAULT NULL COMMENT ‘erp版本号’,

`SDK_TYPE` varchar(50) DEFAULT NULL COMMENT ‘SDK版本类型’,

`VISIT_DATE` timestamp NOT NULL COMMENT ‘访问时间’,

`IP` varchar(40) DEFAULT NULL COMMENT ‘调用接口的IP’,

`INVOKE_PARAM` varchar(4000) DEFAULT NULL COMMENT ‘调用接口的参数’,

`VISIT_TIME_COST` int(18) DEFAULT NULL COMMENT ‘接口响应时间’

) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;

 

11:22:04 api_log> show tables;

+——————————+

| Tables_in_api_log            |

+——————————+

| INSHOP_API_ACCESS_LOG_201404 |

| INSHOP_API_ACCESS_LOG_201405 |

| INSHOP_API_ACCESS_LOG_201406 |

| INSHOP_API_ACCESS_LOG_201407 |

| INSHOP_API_ACCESS_LOG_201408 |

| INSHOP_API_ACCESS_LOG_201409 |

| INSHOP_API_ACCESS_LOG_201410 |

| INSHOP_API_ACCESS_LOG_201411 |

| INSHOP_API_ACCESS_LOG_201412 |

+——————————+

 

MySQL库

11:36:18 api_log> select * from INSHOP_API_ACCESS_LOG where VISIT_DATE>=’2014-05-01 00:00:00′ and VISIT_DATE<‘2014-05-27 00:00:00′ into outfile ‘/data/house_apilog/INSHOP_API_ACCESS_LOG_5.csv’  FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\”‘   ESCAPED BY ‘\\’  LINES TERMINATED BY ‘\n';

Query OK, 942548825 rows affected (8 hours 9 min 1.71 sec)

 

Infobright:

11:22:07 api_log> load data  infile ‘/data/house_apilog/INSHOP_API_ACCESS_LOG_5.csv’ into table INSHOP_API_ACCESS_LOG_201405 FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\”‘   ESCAPED BY ‘\\’  LINES TERMINATED BY ‘\n';

Query OK, 942548825 rows affected (6 hours 42 min 10.90 sec)

Records: 942548825  Deleted: 0  Skipped: 0  Warnings: 0

 

09:55:25 api_log> select concat(truncate(sum(data_length)/1024/1024,2),’MB’) as data_size,      concat(truncate(sum(max_data_length)/1024/1024,2),’MB’) as max_data_size,      concat(truncate(sum(data_free)/1024/1024,2),’MB’) as data_free,      concat(truncate(sum(index_length)/1024/1024,2),’MB’) as index_size      from information_schema.tables where TABLE_NAME = ‘INSHOP_API_ACCESS_LOG';

+————-+—————+———–+————-+

| data_size   | max_data_size | data_free | index_size  |

+————-+—————+———–+————-+

| 640783.09MB | 0.00MB        | 179.00MB  | 111563.18MB |

+————-+—————+———–+————-+

1 row in set (19.16 sec)

 

09:54:17 api_log> select concat(truncate(sum(data_length)/1024/1024,2),’MB’) as data_size,      concat(truncate(sum(max_data_length)/1024/1024,2),’MB’) as max_data_size,      concat(truncate(sum(data_free)/1024/1024,2),’MB’) as data_free,      concat(truncate(sum(index_length)/1024/1024,2),’MB’) as index_size      from information_schema.tables where TABLE_NAME = ‘INSHOP_API_ACCESS_LOG_201405′;

+————+—————+———–+————+

| data_size  | max_data_size | data_free | index_size |

+————+—————+———–+————+

| 43760.19MB | 0.00MB        | 0.00MB    | 0.00MB     |

+————+—————+———–+————+

1 row in set (0.01 sec)

共有 0 条评论

Top