Infobright Columnstore Database

Infobright is a very powerful Columnstore database, based on MySQL's efficient Data warehouse.

The reason why the Data warehouse is used is that the data in MySQL database is growing quickly, and some history tables are cleaned up regularly, but later statistical analysis will also use these historical data, and as the amount of data increases, the query is more and more slow, and the storage format unique to the database warehouse can reduce the occupation in disk space. At the same time the characteristics of the column to make the query speed greatly improved. Choose Infobright is because it locks support more data types, closer to MySQL, more disk space, after all, the main statistical query is not in the Data Warehouse, the occasional query speed is not the best, but ice the biggest constant after you can not do DM operation, I have a deep understanding of this, every time if the insertion of data some inappropriate place, need to delete, you can only drop table, and then from the new table and import data, trouble. and INFINIDB in this convenient let you very happy.

Advantages of Infobright:

1. Data compression: Suitable for storing large amounts of data, saving disk storage

2. Query speed: Basic summary statement, sum avg min max count () GroupBy faster than Oracle, no indexing, no large table partitioning, save a lot of work, suitable for data summary, report statistics

Limitations of Infobright Ice:

1. Infobright does not support DML (select only supported)

Only select can support, Update/insert/deltete, and TRUNCATE table cannot be used, inserting tables data: Using Laod-infile

2. Support only click, single core

Since Infobright has provided the RPM package, it is relatively simple to install:

RPM-IVH infobright-4.0.7-0-x86_64-ice.rpm--prefix=/usr/local/infobright

This will install to/usr/local/infobright/infobright-4.0.7-0-x86_64

For the entire installation process, quite simple, more cumbersome is the setting of the relevant parameters:

A, configuring Memory size

Vim/usr/local/infobright-4.0.7-x86_64/data/brighthouse.ini

Modify the memory configuration to participate in its recommended values to set:

############ Critical memorysettings ############

# System Memory Server main heap size servercompressed heap size Loader Main heapsize

# 32GB 24000 4000 800

# 16GB 10000 1000 800

# 8GB 4000 500 800

# 4GB 1300 400 400

# 2GB 600 250 320

B, the system comes with the configuration function

sh/usr/local/infobright-4.0.7-x86_64/postconfig.sh

This script can change the configuration such as Datadir,cachedir,socket,port, requires root to execute, the information returned after execution is as follows: (if no modification is required, all n can be)

Infobright Post Configuration

--------------------------------------

Using Postconfig You can:

--------------------------------------

(1) Move existing data directory to other location,

(2) Move existing Cachedirectoryto other location,

(3) Configure server socket,

(4) Configure Server Port,

(5) Relocate DataDir pathto an existing data directory.

Please type ' y ' foroption this you want or press CTRL + C for exit.

Current configuration:

--------------------------------------

Current config file: [/etc/my-ib.cnf]

Current Brighthouse.ini file: [/usr/local/infobright-4.0.7-x86_64/data/brighthouse.ini]

Current DataDir: [/usr/local/infobright-4.0.7-x86_64/data]

Current Cachefolder in Brighthouse.ini file: [/usr/local/infobright-4.0.7-x86_64/cache]

Current socket: [/tmp/mysql-ib.sock]

Current port: [5029]

--------------------------------------

(1) Do you want to copy current DataDir [/usr/local/infobright-4.0.7-x86_64/data] to a new location? [Y/n]:n

(2) Do you want tomovecurrent Cachefolder [/usr/local/infobright-4.0.7-x86_64/cache] to a new location? [Y/n]:n

(3) Do you want tochangecurrent socket [/tmp/mysql-ib.sock]? [Y/n]:n

(4) Do you want tochangecurrent port [5029]? [Y/n]:n

(5) Do you want torelocateto an existing datadir? Current DataDir is [/usr/local/infobright-4.0.7-x86_64/data]. [Y/n]:n

--------------------------------------

--------------------------------------

No changes has been made.

--------------------------------------

C. Set character Sets

Infobright does not support Chinese by default, in order to better support Chinese, you need to set the default character set.

Vim/etc/my-ib.cnf

Find the following:

Collation_server=latin1_bin

Character_set_server=latin1

Modify it to:

Collation_server=utf8_bin

Character_set_server=utf8

D. Install the startup script

Cp/usr/local/infobright-4.0.7-x86_64/share/mysql/mysql.server/etc/init.d/mysqld-ib

Vim/etc/init.d/mysqld-ib

Find the following two lines of code:

[Email protected] [Email protected]

[Email protected] [Email protected]

Modified to:

Conf=/etc/my-ib.cnf

user=root# #这里只能用root启动服务, other users need to study how to start

Other directives related to:

/etc/init.d/mysqld-ib stop

/etc/init.d/mysqld-ib restart

Add boot up:

Chkconfig--add Mysqld-ib

E, MySQL security settings

Path= $PATH:/usr/local/infobright-4.0.7-x86_64/bin

Mysql_secure_installation

When you are finished, add a remote connection account to MySQL and just want to enter the MySQL client as follows:

Mysql-uroot-p

After adding the remote user method, the following methods:

GRANT All Privilegeson * * to ' infobright ' @ '% ' identifiedby ' password ' withgrantoption;

Flushprivileges;

MySQL data import into Infobright

CREATE TABLE ' Ricci_var ' (

' id ' int (one) DEFAULT NULL,

' Name ' varchar (DEFAULT NULL),

' C_time ' timestamp not NULL DEFAULT current_timestamp on Updatecurrent_timestamp

) Engine=innodb

SELECT * from Ricci_var to outfile '/tmp/var.csv ' fields terminated by ', ' optionallyenclosed by ' "' lines terminated by ' \ n '

# # #红色部分在导入的数据设置的分隔符等信息, the import will be the same

#导出数据的时候需要存放在数据库目录下或者/tmp directory, MySQL5.7 is not authorized to export needs to be set

Impact of SECURE_FILE_PRIV configuration on data import and export:

Secure_file_priv mysqld Use this configuration item to complete restrictions on data import and export

1, limit mysqld not allowed to import | Export

Mysqld--secure_file_prive=null

2, restrict the import of Mysqld | Export can only occur in the/tmp/directory

Mysqld--secure_file_priv=/tmp/

3, do not import the Mysqld | Export to make restrictions

/etc/my.cnf
[Mysqld]
Secure_file_priv

Import the data into the Infobright library.

Add the same type of table in the INF library to import the data:

Load data infile "/tmp/var.csv" into table var fields terminated by ', ' optionally enclosed by ' "' linesterminated by ' \ n '

In the text data import INF:

[email protected] home]# cat Aa.txt

1, "Noe,two or three", 2222

2,3,4

CREATE TABLE AA (ID int,textfiedl varchar (+), number int)

Load data infile "/home/aa.txt" into the table AA fields terminated by ', ' enclosed by ' ";

Mysql> select * from AA;

+------+------------------+--------+

| ID | Textfiedl | number |

+------+------------------+--------+

| 1 |   Noe,two or three | 2222 |

| 2 |      3 | 4 |

+------+------------------+--------+

(1) "" is to separate the columns

(2) After each line is written, you must enter the car, or not lead in

# #自己验证正确性把

Guide the database when the use of client tools is not recommended to do, always feel a lot of pits.

This article is from the "DBSpace" blog, so be sure to keep this source http://dbspace.blog.51cto.com/6873717/1885668

Infobright Columnstore Database