MySQL Infobright 数据仓库安装笔记

1.Infobright的基本特征

• 优点：

   查询性能高：百万、千万、亿级记录数条件下，同等的SELECT查询语句，速度比MyISAM、InnoDB等普通的mysql存储引擎快5～60倍
   存储数据量大：TB级数据大小，几十亿条记录
   高压缩比：在我们的项目中为18:1，极大地节省了数据存储空间
   基于列存储：无需建索引，无需分区
   适合复杂的分析性SQL查询：SUM, COUNT, AVG, GROUP BY

• 限制：

不支持数据更新：社区版Infobright只能使用“LOAD DATA INFILE”的方式导入数据
社区版不支持INSERT、UPDATE、DELETE
不支持高并发：只能支持10多个并发查询
不支持和其他mysql引擎做join查询

2.安装

• 系统环境：CentOS 6.5 X64
• infobright没啥定制的必要，使用编译安装的话依赖关系麻烦，所以不通过源码安装，直接使用rpm安装

wget http://www.infobright.org/downloads/ice/infobright-4.0.7-0-x86_64-ice.rpm  
rpm -ivh infobright-4.0.7-0-x86_64-ice.rpm 

cd /usr/local/infobright/data  
vim brighthouse.ini                   #调整infobright的内存符合系统内存。  
ServerMainHeapSize=1300  
LoaderMainHeapSize=400

cd /usr/local/infobright  
sh postconfig.sh                      #根据需要配置datadir，CacheFolder，socket，port  
(1) Do you want to copy current datadir [/data/mysql/3307/] to a new location? [y/n]:n
(2) Do you want to move current CacheFolder [/data/mysql/3307/cache] to a new location? [y/n]:n
(3) Do you want to change current socket [/tmp/mysql-ib.sock]? [y/n]:n
(4) Do you want to change current port [3307]? [y/n]:n
(5) Do you want to relocate to an existing datadir? Current datadir is [/data/mysql/3307/data]. [y/n]:n
#启动服务
/etc/init.d/mysqld-ib start
/etc/init.d/mysqld-ib stop
chkconfig mysqld-ib on  
#设置密码
/usr/local/infobright-4.0.7-x86_64/bin/mysqladmin -u root password 123456
#登陆
/usr/local/infobright/bin/mysql -u root -p -S /tmp/mysql-ib.sock

配置文件参考 [4core 16G]

[client]
port            = 3307
socket          = /data/tmp/mysql_3307.sock
[mysqld]
port            = 3307
user=mysql
socket          = /data/tmp/mysql_3307.sock
tmp_table_size=4096M
bind-address=0.0.0.0
datadir=/data/mysql/3307/
skip-external-locking
skip-name-resolve
key_buffer_size = 2048M
max_allowed_packet = 128M
max_connections = 2560
table_open_cache = 1024
open_files_limit = 65536
sort_buffer_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
query_cache_size = 128M
thread_concurrency = 24
innodb_file_per_table= 1
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 64M
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_lock_wait_timeout = 20
server-id       = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/local/infobright-4.0.7-x8664/bin/mysqladmin -u root password 'new-password' /usr/local/infobright-4.0.7-x8664/bin/mysqladmin -u root -h webdev password 'new-password'

Alternatively you can run:
/usr/local/infobright-4.0.7-x8664/bin/mysqlsecure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr/local/infobright-4.0.7-x8664 ; /usr/local/infobright-4.0.7-x8664/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/local/infobright-4.0.7-x86_64/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/local/infobright-4.0.7-x86_64/scripts/mysqlbug script!

The latest information about MySQL is available at
Support MySQL by buying support/licenses from

System Physical memory: 3829(MB)
Infobright optimal ServerMainHeapSize is set to 1300(MB)
Infobright optimal LoaderMainHeapSize is set to 400(MB)
Infobright server installed into folder /usr/local/infobright
Installation log file /tmp/ib4.0.7-0-install.log

To activate infobright server, please run ./postconfig.sh script from /usr/local/infobright-4.0.7-x8664.
Example command: cd /usr/local/infobright-4.0.7-x8664; ./postconfig.sh