Go Infobright is an open source data Warehouse integrated with MySQL

Source: Internet
Author: User

[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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.