Quick installation notes for MySQL infobright Data Warehouse

Source: Internet
Author: User

[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
Related Article

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.