Go Infobright is an open source data Warehouse integrated with MySQL

[Article Zhang Feast this article version: v1.1 Last modified: 2010.05.18 reproduced Please specify the original link: http://blog.zyan.cc/infobright/]

Infobright is a MySQL-integrated open source data Warehouse software that can be used as a storage engine for MySQL, and select queries are no different than normal MySQL.

　　First, the basic characteristics of Infobright:
　　Advantages:
High query performance: million, tens of millions, billions of records under the conditions of the same select query, speed than MyISAM, InnoDB and other common MySQL storage engine faster 5~60 times
Large storage data: Terabytes of data size, billions of records
High compression ratio: 18:1 in our project, greatly saving data storage space
Column-based storage: No indexing required, no partitioning required
For complex analytic SQL queries: SUM, COUNT, AVG, GROUP by

　　Limit:
Data Update not supported: Community Edition Infobright can only import data using "LOAD data INFILE" and does not support insert, UPDATE, DELETE
High concurrency is not supported: only more than 10 concurrent queries can be supported

　　Second, infobright installation and basic usage:
1. Download and install the Community Edition infobright binary Linux version, Port 3307

Ulimit-shn 65535
Mkdir-p/data0/mysql/3307
/usr/sbin/groupadd MySQL
/usr/sbin/useradd-g MySQL MySQL

Cd/usr/local

①, 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
MV 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
MV 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, start the installation, prompted the following information:
Infobright installation script is running ...
Checking System configuration ...
Infobright License Agreement ...
System tool ' less '-a text file viewer is used to display license agreement.
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, the space page to the end, and when you see the following prompt, select Q to continue the installation:

END of TERMS and CONDITIONS

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

Next, you will see the following information, select Y to agree:
Press Y-i agree, any and key-i do not agree [y/*]:

At this point, you will be prompted to register online, select n Do not register:
Installation have 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 $ value) as well as a copy of the Bloor Spotlight report "What's Cool about Columns" which explains the differences and benefits of a columnar versus row databas E.
Registration would require opening an HTTP connection to Infobright, does you wish to register now? [y/n]:

3. Modify Infobright Memory usage limit

Vi/data0/mysql/3307/data/brighthouse.ini

Modify the values of Servermainheapsize, servercompressedheapsize, loadermainheapsize according to their physical memory size, for reference:

############ 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

4. Create shell scripts to manage MySQL database:

Vi/data0/mysql/3307/mysql

Enter the following (user name here Admin and password 12345678 Next steps will be created):

#!/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} ')
Kill-9 $ (Ps-ef & #124; grep ' libexec/mysqld ' & #124; grep ${mysql_port} & #124; awk ' {printf} ')
}

If ["$" = "start"]; Then
Function_start_mysql
elif ["$" = "Stop"]; Then
Function_stop_mysql
elif ["$" = "restart"]; Then
Function_restart_mysql
elif ["$" = "kill"]; Then
Function_kill_mysql
Else
printf "Usage:/data0/mysql/${mysql_port}/mysql {start& #124;stop& #124;restart& #124; kill}\n"
Fi

5. Give shell scripts permission to execute:

chmod +x/data0/mysql/3307/mysql

6. Start Mysql/infobright:

/data0/mysql/3307/mysql start

7. Log in to manage MySQL server via command line (direct enter when prompted for password):

/usr/local/infobright/bin/mysql-u root-p-s/tmp/mysql3307.sock

8. Enter the following SQL statement to create a user (admin) and password (12345678) with root privileges:

GRANT all privileges on * * to ' admin ' @ ' localhost ' identified by ' 12345678 ';
GRANT all privileges on * * to ' admin ' @ ' 127.0.0.1 ' identified by ' 12345678 ';

9. Example: Export data to a CSV file from a normal MySQL database (assuming MySQL installation path is/usr/local/webserver/mysql):

/usr/local/webserver/mysql/bin/mysql-s/tmp/mysql3306.sock-d tongji_logs-e "SELECT * from Log_visits_2010_05_10 to O Utfile '/data0/test.csv ' fields TERMINATED by ', ' enclosed by ' \ ' escaped by ' \\\ ' LINES TERMINATED by ' \ n '; "

10. Example: normal MySQL and infobright table comparison
①, common MySQL InnoDB storage engine build table:

CREATE TABLE IF not EXISTS ' Log_visits_2010_05_12 ' (
' id ' int (one) not NULL auto_increment,
' cate_id ' int (one) is not NULL,
' site_id ' int (one) unsigned not NULL,
' Visitor_localtime ' char (8) Not NULL,
' Visitor_idcookie ' varchar (255) is not NULL,
PRIMARY KEY (' id '),
KEY ' cate_site_id ' (' cate_id ', ' site_id '),
KEY ' Visitor_localtime ' (' Visitor_localtime ')
) Engine=innodb DEFAULT Charset=utf8;

②, Infobright's BrightHouse Storage engine table:

CREATE TABLE IF not EXISTS ' log_visits ' (
' id ' int (one) is not NULL,
' cate_id ' int (one) is not NULL,
' site_id ' int (one) is not NULL,
' Visitor_localtime ' char (8) Not NULL,
' Visitor_idcookie ' varchar (255) is not NULL,
) Engine=brighthouse DEFAULT Charset=utf8;

Note: The BrightHouse storage engine can not have auto_increment self-increment, unsigned unsigned, unique unique, primary key primary key, index key when the table is built.

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 ' I NTO TABLE log_visits_2010_04_13 fields TERMINATED by ', ' escaped by ' \\\ ' LINES TERMINATED by ' \ n '; "

12, example: normal MySQL and infobright query speed comparison (total 220多万条 records):
①, common MySQL InnoDB storage engine (built-in index):

Mysql> SELECT Config_browser_name, COUNT (*) as total from ' Browser_info ' GROUP by config_browser_name order by Total DE SC;
+---------------------+---------+
| Config_browser_name | Total |
+---------------------+---------+
| IE | 2204016 |
|   CH | 20650 |
|   FF | 10475 |
|    MO | 6147 |
|    OT | 1631 |
|    OP | 1282 |
|     SF | 797 |
|       KM | 5 |
|       KO | 2 |
+---------------------+---------+
9 Rows in Set (1 min 28.13 sec)

②, Infobright's BrightHouse storage Engine:

Mysql> SELECT Config_browser_name, COUNT (*) as total from ' Browser_info ' GROUP by config_browser_name order by Total DE SC;
+---------------------+---------+
| Config_browser_name | Total |
+---------------------+---------+
| IE | 2204016 |
|   CH | 20650 |
|   FF | 10475 |
|    MO | 6147 |
|    OT | 1631 |
|    OP | 1282 |
|     SF | 797 |
|       KM | 5 |
|       KO | 2 |
+---------------------+---------+
9 rows in Set (0.84 sec)

13. (optional) Stop mysql/infobright:

/data0/mysql/3307/mysql stop

Go Infobright is an open source data Warehouse integrated with MySQL