Quick installation notes for MySQL infobright Data Warehouse

[Author: Zhang banquet this article version: V1.1 final modification: 2010.05.18 reprinted please specify the original article link: http://blog.s135.com/infobright/]

[For details, refer:Data Warehouse http://tech.techweb.com.cn/thread-389359-1-1.html Based on MySQL and infobright
]

Infobright is an open-source data warehouse software integrated with MySQL. It can be used as a storage engine of MySQL. select queries are no different from common MySQL queries.

　　I. Basic Features of infobright:
Advantages:
High query performance: the same SELECT query statement under the conditions of millions, tens of millions, and hundreds of millions of records. The query speed is 5-5 faster than that of common MySQL storage engines such as MyISAM and InnoDB ~ 60 times
Large data volume: terabytes of data and billions of records
High compression ratio: in our project, greatly saving data storage space
Column-based storage: No index required, no partition required
Suitable for complex analytical SQL queries: Sum, Count, AVG, group

　　Restrictions:
Data Update Not supported: for the Community edition, infobright can only import data using the "load data infile" method, but does not support insert, update, and delete
High concurrency not supported: only more than 10 concurrent queries are supported

　　 Ii. Installation and basic usage of infobright:
1. download and install the Community edition infobright binary Linux, port 3307 ulimit-shn 65535
Mkdir-P/data0/MySQL/3307.
/Usr/sbin/groupadd MySQL
/Usr/sbin/useradd-G MySQL

CD/usr/local

(1) 64-bit system: wget http://www.infobright.org/downloads/ice/infobright-3.3.1-x86_64-ice.tar.gz
Tar zxvf infobright-3.3.1-x86_64-ice.tar.gz
Music infobright-3.3.1-x86_64 infobright

② 32-bit system: wget http://www.infobright.org/downloads/ice/infobright-3.3.1-i686-ice.tar.gz
Tar zxvf infobright-3.3.1-i686-ice.tar.gz
Music infobright-3.3.1-i686 infobright

CD infobright
. /Install-infobright.sh -- datadir =/data0/MySQL/3307/data -- cachedir =/data0/MySQL/3307/cache -- Config =/data0/MySQL/3307/My. CNF -- Port = 3307 -- socket =/tmp/mysql3307.sock -- user = MySQL -- group = MySQL 2. To start installation, the following message is displayed:
Infobright installation script is running...
Checking System Configuration...
Infobright license agreement...
System Tool 'less '-a text file viewer will be used to display license agreement.
Please only use up/down arrow keys for scrolling license text and press Q when finished reading.
Press R-read License Agreement, N-exit the installation [R/n]:

Select R and flip the page to the end of the page. When the following prompt is displayed, select Q to continue installation:

End of Terms and Conditions

============= Press Q to continue installation ================
(End)

The following information is displayed. Select y to agree:
Press Y-I agree, any other key-I do not agree [Y/*]:

At this time, you will be prompted whether to register online. Select n not to register:
Installation has been made for system user root and MySQL.
Please see readme or user guide for instructions related to start/stop the infobright server and connect to it.
Register your copy of ice and receive a free copy of the user manual (A $50 value) as well as a copy of the Bloor research spotlight Report "what's cool about columns" which explains the differences and benefits of a columnar versus row database.
Registration will require opening an HTTP connection to infobright, do you wish to register now? [Y/n]:

3. Modify the infobright memory usage limit VI/data0/MySQL/3307/data/brighthouse. ini

Modify the values of servermainheapsize, servercompressedheapsize, and loadermainheapsize based on the physical memory size. For details, refer: ########### critical memory settings ############
# System memory server main heap size server compressed heap size loader main heap size
#32 GB 24000 4000 800
#16 GB 10000 1000 800
#8 GB 4000 500 800
#4 GB 1300 400 400
#2 GB 600 250 320

4. Create a shell script for MySQL database management: VI/data0/MySQL/3307/MySQL

Enter the following content (here the User Name Admin and password 12345678 will be created in the Next Step ):#! /Bin/sh

Mysql_port = 3307
Mysql_username = "admin"
Mysql_password = "12345678"

Function_start_mysql ()
{
Printf "Starting MySQL... \ n"
CD/usr/local/infobright/&/bin/sh. /bin/mysqld_safe -- defaults-file =/data0/MySQL/$ {mysql_port}/My. CNF 2> & 1>/dev/null &
}

Function_stop_mysql ()
{
Printf "stoping MySQL... \ n"
CD/usr/local/infobright/&./bin/mysqladmin-U $ {mysql_username}-p $ {mysql_password}-S/tmp/MySQL $ {mysql_port}. Sock Shutdown
}

Function_restart_mysql ()
{
Printf "Restarting MySQL... \ n"
Function_stop_mysql
Sleep 5
Function_start_mysql
}

Function_kill_mysql ()
{
Kill-9 $ (PS-Ef & #124; grep 'bin/mysqld_safe '& #124; grep $ {mysql_port} & #124; awk' {printf $2 }')
Kill-9 $ (PS-Ef & #124; grep 'libexec/mysqld' & #124; grep $ {mysql_port} & #124; awk '{printf $2 }')
}

If ["$1" = "start"]; then
Function_start_mysql
Elif ["$1" = "stop"]; then
Function_stop_mysql
Elif ["$1" = "restart"]; then
Function_restart_mysql
Elif ["$1" = "kill"]; then
Function_kill_mysql
Else
Printf "Usage:/data0/MySQL/$ {mysql_port}/MySQL {start & #124; stop & #124; restart & #124; kill} \ n"
Fi

5. Grant the shell script executable permission: chmod + x/data0/MySQL/3307/MySQL 6. Start MySQL/infobright:/data0/MySQL/3307/MySQL start 7. log on to the MySQL server through the command line (press Enter when prompted):/usr/local/infobright/bin/MySQL-u root-p-S/tmp/mysql3307.sock 8. Enter the following SQL statement to create a user with root permissions (Admin) and password (12345678): grant all privileges on *. * To 'admin' @ 'localhost' identified by '20140901 ';
Grant all privileges on *. * To 'admin' @ '1970. 0.0.1 'identified by '20140901 '; 9. Example: export data from a common MySQL database (assuming the MySQL installation path is/usr/local/webserver/MySQL) to a CSV file: /usr/local/webserver/MySQL/bin/MySQL-S/tmp/mysql3306.sock-D tongji_logs-e "select * From log_visits_2010_05_10 into OUTFILE '/data0/test.csv 'fields terminated', 'enabledby' \ "'elasticby' \ 'Lines terminated by '\ n ';" 10. Example: Comparison between normal MySQL and infobright tables
① Create a table using the InnoDB Storage engine of MySQL: Create Table if not exists 'Log _ visits_2010_05_12 '(
'Id' int (11) not null auto_increment,
'Cate _ id' int (11) not null,
'Site _ id' int (11) unsigned not null,
'Visitor _ localtime' char (8) Not null,
'Itor _ idcookie 'varchar (255) not null,
Primary Key ('id '),
Key 'cate _ site_id '('cate _ id', 'site _ id '),
Key 'visitor _ localtime' ('visitor _ localtime ')
) Engine = InnoDB default charset = utf8;

② Create a table using the brighthouse storage engine of infobright: Create Table if not exists 'Log _ visits '(
'Id' int (11) not null,
'Cate _ id' int (11) not null,
'Site _ id' int (11) not null,
'Visitor _ localtime' char (8) Not null,
'Itor _ idcookie 'varchar (255) not null,
) Engine = brighthouse default charset = utf8;

Note: The brighthouse storage engine cannot create tables with auto_increment auto-increment, unsigned, unique, primary key, and index key.

11. Example: import data from a CSV file to the infobright data warehouse: /usr/local/infobright/bin/MySQL-S/tmp/mysql3307.sock-d dw -- skip-column-names-e "load data infile '/data0/test.csv 'into table log_visits_2010_04_13 fields terminated ', 'escaped by' \ 'lines terminated by' \ n ';" 12. Example: Compare the query speed of common MySQL and infobright (a total of more than 2.2 million records ):
① General MySQL InnoDB Storage engine (Index created): mysql> select config_browser_name, count (*) as total from 'browser _ info' group by config_browser_name order by total DESC;
+ --------------------- + --------- +
| Config_browser_name | Total |
+ --------------------- + --------- +
| Internet Explorer | 2204016 |
| Ch| 20650 |
| FF | 1, 10475 |
| Mo | 6147 |
| Ot | 1631 |
| Op | 1282 |
| SF | 1, 797 |
| Km | 5 |
| KO | 2 |
+ --------------------- + --------- +
9 rows in SET (1 min 28.13 Sec)

②. Brighthouse storage engine of infobright: mysql> select config_browser_name, count (*) as total from 'browser _ info' group by config_browser_name order by total DESC;
+ --------------------- + --------- +
| Config_browser_name | Total |
+ --------------------- + --------- +
| Internet Explorer | 2204016 |
| Ch| 20650 |
| FF | 1, 10475 |
| Mo | 6147 |
| Ot | 1631 |
| Op | 1282 |
| SF | 1, 797 |
| Km | 5 |
| KO | 2 |
+ --------------------- + --------- +
9 rows in SET (0.84 Sec)

13. (optional) Stop MySQL/infobright:/data0/MySQL/3307/MySQL stop