MySQL Open Source Engine infobright Data Warehouse Solutions

Infobright is an open source MySQL Data Warehouse solution, which is based on two development on MySQL, infobright mainly implements a storage engine, but because its own storage logic and relational database is fundamentally different, so, It cannot be directly attached to MySQL as a plug-in like InnoDB, and its logical layer is the logical layer of MySQL plus its own optimizer.

Infobright has the following features:

1, high-strength data compression in the ordinary 10:1, in the limit of the case can reach 40:1 or even higher, the use of their own in the process of infobright 5.1G data can be compressed into about 152M;

2, optimized statistical calculation (using Sum/avg/group by/count, etc.), especially applicable to statistical functions generated by the report data;

3, does not need to build index, avoids maintains the index and the index as the data expands the question. Each column of data block compressed storage, each block has a knowledge grid node record block of statistical information, in place of the index, speed up the search;

4, column storage, in the absence of an index in the same case there is no primary key, auto increment field, unsigned.

Infobright products are divided into the community version of Ice and Enterprise Edition IEE, which use the engine is BrightHouse, there is no limit on the use of IEE version, but the use of the ice version has 3 restrictions:

1, does not support data update, so when importing data can only use "LOAD data INFILE" way to import data, in addition, once the storage can not be in the warehouse with update, insert and other ways to write;

2, do not support the use of CPU multicore, so that the performance is very low, to know that the data into the Data warehouse is a huge data, no multi-core support query a large number of data extremely slow;

3, can only be used alone, do not have any replication and expansion, which limits the possibility of large-scale cluster.

So the daily use of the recommendation or use the IEE version, and Infobright installation is very simple to download the RPM package on the official website to install the deployment, can be installed on the server after the file is packaged directly decompression after the parameter deployment, Its configuration file my-ib.cnf can be written in advance, if you need to re-modify parameters after installation can use the built-in postconfig.sh script to modify the default parameters, the default configuration file in Support-files

[[email protected] local]# cd /usr/local/infobright/[[email protected]  infobright]# chmod +x ./*.sh && chmod +x bin/* &&  chmod +x scripts/mysql_install_db[[email protected] infobright]# cp  Support-files/mysql.server /etc/init.d/mysqld-ib[[email protected] infobright]# chmod  +x /etc/init.d/mysqld-ib[[email protected] infobright]# cp support-files/ my-ib.cnf.in /etc/my-ib.cnf# where parameter variables need to be modified ... Slightly... [Client] ... A little ... default_character_set=utf8 ... Slightly... [mysqld]user        = root# can also be used with MySQL user port             = 5029socket           = /tmp/mysql-ib.sockbasedir = /usr/local/infobrightdatadir =  /data/infobright_datalog-error = /data/infobright_data/localhosT.err ... Slightly ... default_character_set=utf8collation_server=utf8_general_cicharacter_set_server=utf8[[email protected]  infobright]# vim /etc/profile.d/infobright.sh# add an environment variable on a server without MySQL export path= $PATH:/usr/ local/infobright/bin[[email protected] infobright]# source /etc/profile.d/infobright.sh[[ Email protected] infobright]# ./scripts/mysql_install_db --force --defaults-file=/etc /my-ib.cnf --datadir=/data/infobright_data --user=root[[email protected] infobright]#  cd /data/infobright_data/[[email protected] infobright_data]# vim  brighthouse.ini# modifying BrightHouse engine parameters ################## brighthouse configuration file ####### ############## to change values, uncomment the parameter and specify  desired value.############ Critical Disk Settings ############# Data  Folder: check where you installed brighthouse data folder  (Directory this file is  in)  - it should be on a fast disk.# cachefolder -  a place in which temporary database objects  (Memory cache)   Are stored.# should be on a fast drive, possibly not the  same as data. Allow at least 20 GB of free space  (depending on database size). Cachefolder = /data/infobright_data/cache############  critical memory settings  ############ # system memory    server main heap size      server compressed heap size   loader main  heap size# 32gb                 24000                       4000                         800# 16gb                  10000                       1000                        800#  8GB                   4000                         500                        800#  4GB                   1300                        400                         400#  2gb                   600                         250                        320# the  default values are&nbsP;set to 600, 250, and 320 respectively, and performance may  be  impacted.# servermainheapsize - size of the main memory  heap in the server process, in mbservermainheapsize = 4000#  loadermainheapsize - size of the memory heap in the loader  PROCESS, IN MB. loadermainheapsize = 800############ logging settings ############ #  Controlmessages - set to 2 to turn the control messages on.  this is usually needed by infobright to support performance  investigation.# controlmessages = 0############  other settings ##### ######## knfolder - directory where the knowledge grid is stored. Knfolder = bh_rsi_repository# allowmysqlquerypath can be set to 0  To disable mysql query path or 1 to enable it. Allowmysqlquerypath = 1[[email protected] infobright_data]# /etc/init.d/mysqld-ib  start[[email protected] infobright_data]# mysql -s /tmp/mysql-ib.sock -p

It should be noted here that the important parameters when modifying the BrightHouse parameters are as follows:

Cachefolder temporary Data directory, used to cache the intermediate result set for processing queries, and datadir is not appropriate, the free space is larger than the 20GServerCompressedHeapSize service process compressed stack space, Storing compressed data loadermainheapsize bhloader Data import buffer, adjusted to increase the number of columns in the target table, loader the stack space of the process, generally maximum not more than 800MControlMessages Information level of the Control box query log (between 1-3) Knfolder Knowledge network directory, default in DataDir directory allowmysqlquerypath support MySQL native SQL query, support modification to 1, otherwise 0

650) this.width=650; "Src=" Https://s5.51cto.com/oss/201710/22/8f7ab2b336b88b049de95c8732293b2a.png-wh_500x0-wm_3 -wmp_4-s_204123840.png "title=" BrightHouse "alt=" 8f7ab2b336b88b049de95c8732293b2a.png-wh_ "/>650" this.width= 650; "Src=" Https://s2.51cto.com/oss/201710/22/4b2bda455b8b044733a647f155a1c64a.png-wh_500x0-wm_3-wmp_4-s_ 4244572167.png "title=" ENGINES "alt=" 4b2bda455b8b044733a647f155a1c64a.png-wh_ "/>

Finally, use the data type as much as possible in Infobright: Tinyint,smallint,mediumint,int,bigint,decimal (minimizing the number of decimal places), DATE, time, and less efficient, Deprecated data types are: BINARY varbinary,float,double,varchar,tinytext TEXT, in Infobright to export the data into SQL script, you need to use mysqldump "-- Single-transaction "parameters are exported using a single transaction, and the transaction repeatable read isolation mode, the MySQL transaction has previously mentioned need to see: http://jim123.blog.51cto.com/4763600/1964004

This article from "Jim's Technical Essay" blog, declined to reprint!

MySQL Open Source Engine infobright Data Warehouse Solutions