[文章作者:张宴 本文版本:v1.1 最后修改:2010.05.18 转载请注明原文链接:http://blog.zyan.cc/infobright/]

  Infobright是一个与MySQL集成的开源数据仓库(Data Warehouse)软件,可作为MySQL的一个存储引擎来使用,SELECT查询与普通MySQL无区别。

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

  限制:
  不支持数据更新:社区版Infobright只能使用“LOAD DATA INFILE”的方式导入数据,不支持INSERT、UPDATE、DELETE
  不支持高并发:只能支持10多个并发查询



  二、Infobright 安装与基本用法:
  1、下载安装社区版Infobright二进制Linux版本,端口3307
ulimit -SHn 65535
mkdir -p /data0/mysql/3307
/usr/sbin/groupadd mysql
/usr/sbin/useradd -g mysql mysql

cd /usr/local


  ①、64位系统:
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位系统:
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、开始安装,提示以下信息:
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]:


  选择R,空格翻页到页尾,看到以下提示时,选择Q继续安装:

                     END OF TERMS AND CONDITIONS

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


  接下来会显示以下信息,选择Y同意:
Press Y -I agree, Any other key -I do not agree [Y/*]:

  这时,会提示是否在线注册,选择N不注册:
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、修改Infobright内存使用限制
vi /data0/mysql/3307/data/brighthouse.ini

  根据自身的物理内存大小修改ServerMainHeapSize、ServerCompressedHeapSize、LoaderMainHeapSize的值,有参考:
############  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、创建管理MySQL数据库的shell脚本:
vi /data0/mysql/3307/mysql

  输入以下内容(这里的用户名admin和密码12345678接下来的步骤会创建):
#!/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 | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
    kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | 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|stop|restart|kill}\n"
fi




  5、赋予shell脚本可执行权限:
chmod +x /data0/mysql/3307/mysql




  6、启动MySQL/Infobright:
/data0/mysql/3307/mysql start




  7、通过命令行登录管理MySQL服务器(提示输入密码时直接回车):
/usr/local/infobright/bin/mysql -u root -p -S /tmp/mysql3307.sock




  8、输入以下SQL语句,创建一个具有root权限的用户(admin)和密码(12345678):
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY '12345678';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'127.0.0.1' IDENTIFIED BY '12345678';




  9、示例:从普通的MySQL数据库(假设MySQL安装路径为/usr/local/webserver/mysql)导出数据到csv文件:
/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 BY ',' ENCLOSED BY '\"'  ESCAPED BY '\\\' LINES TERMINATED BY '\n';"




  10、示例:普通MySQL和Infobright建表对比
  ①、普通MySQL的InnoDB存储引擎建表:
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,
  `visitor_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;


  ②、Infobright的BRIGHTHOUSE存储引擎建表:
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,
  `visitor_idcookie` varchar(255) NOT NULL,
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;

  注:BRIGHTHOUSE存储引擎建表时不能有AUTO_INCREMENT自增、unsigned无符号、unique唯一、主键PRIMARY KEY、索引KEY。



  11、示例:从csv文件导入数据到Infobright数据仓库:
/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 BY ',' ESCAPED BY '\\\' LINES TERMINATED BY '\n';"




  12、示例:普通MySQL和Infobright查询速度对比(共220多万条记录):
  ①、普通MySQL的InnoDB存储引擎(已建索引):
mysql> SELECT config_browser_name, count(*) AS total FROM `browser_info` GROUP BY config_browser_name order by total DESC;
+---------------------+---------+
| 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的BRIGHTHOUSE存储引擎:
mysql> SELECT config_browser_name, count(*) AS total FROM `browser_info` GROUP BY config_browser_name order by total DESC;
+---------------------+---------+
| 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、(可选)停止MySQL/Infobright:
/data0/mysql/3307/mysql stop






技术大类 » 数据库技术 | 评论(62) | 引用(0) | 阅读(117100)
lephone Email Homepage
2010-6-29 09:54
这个适合20G的数据库吗
张宴 回复于 2010-6-29 11:39
20G很容易,我们存储的数据是TB级的。
mmxcq Email Homepage
2010-7-4 09:56
nosql用过mongodb  感觉还不错 但是32位下只支持单表文件2g限制 确实不爽 一直在寻找好的替代方案
~
2010-7-9 20:07
我看了一下建表示例,其中有visitor_idcookie这个字段,实际业务是否有人数的计算?
如果有,能通过SQL语句的查询实现么?效率如何?
gma011 Homepage
2010-7-14 16:33
很不错。学习了。精灵爱论坛,好人品网
Mike.Meng
2010-8-3 17:05
非商业性的列存储型数据库有很多啊,比如像C-Store,MonetDB,Rasdaman, etc.. 为什么一定要用Infobright呢? 博主有对这四个列存储数据库的性能有比较吗? 只能Load data in,10个最大并发连接,确实限制很多。 估计你们的数据量很大,对事实表的操作比较多,像Mondrian之类的ROLAP系统,已经满足不了你们的要求。
学习
2010-10-24 21:39
smile都是大家,领教了!O(∩_∩)O哈哈~
kevintse Homepage
2010-11-20 10:03
很不错的介绍。使用install-infobright.sh安装,默认会产生/etc/init.d/mysqld-ib的文件,可以用来启动,停止,重启infobright。不用自己写mysqld脚本的。
xierwa
2011-1-6 09:42
通过文本导入数据时,有数据长短限制,就算设置为varchar(100),依然不能导入,同样的数据导入innodb表就可以正常导入,infobright本身是否有数据长短限制,大概是多少?
xkxmud
2011-3-24 15:26
做日志比较合适!
sdj1225
2011-5-18 10:53
按照安装步骤前面都很顺利通过,只是在建表的时候出现错误
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8' at line 7
sdj1225
2011-5-18 11:05
找到错误的原因了,是  `visitor_idcookie` varchar(255) NOT NULL,这句最后多个逗号,去掉逗号后建表成功
louis vuitton uk Email Homepage
2011-11-22 17:10
This louis vuitton uk for sale belongs to the sounding just what are termed as Louis Vuitton vintage best sellers, many other products and services for the reason that range appearing companies.You will easily notice the unfold zippers of this coach outlet store online. That is the decoration. There are some inside pockets for you as well. They are easy to match your clothes and to carry.Let us inspire your inner beauty with fine christian louboutin sale. Purse the elegance in bridal wedding. Enjoy the fashion.
armani watches Email Homepage
2011-11-23 10:11
If you are looking for armani Bags, our armani watches Handbags Canada outlet store is your first choice. We promise Original Packing and Best Discount,3-5 Workdays To Your Door!If you buy bags and purses of the latest new designs from the louis vuitton outlet now, you can enjoy special discounts. What else are you waiting for?After you choose the right kind of plants and the size pot for the plant.
wedding520 Email Homepage
2011-12-27 15:53
ワイドネックライン、'V'ネックやボートネックラインは、一般にキャップスリーブとドレスのために選ばれている。ボートスタイルのネックラインと幅広いネックラインのためには、オフショルダーのキャップスリーブを追加することができます。オフショルダーのウエディングドレスは、数年以来、結婚式の摩耗で人気のパターンになっても流行で、今日でもあります。カラードレス 格安.のこのスタイルで素敵に見える模様のオフショルダーの袖に行く同じシースのサテンからステッチ/オフショルダーのキャップスリーブの上に薄いと飾ったときに裾フレアのビットとシースのサテンのイブニングドレスとスパゲッティネックラインがすごい見えます。
掂量 Homepage
2012-1-18 16:41
我是下载在 /home/josh 目录下的,解压,然后执行: ./install-infobright.sh --datadir= ........结果安装成功,也可以建数据据,建表。就是 load data infile 的时候,老是报错提示:System error(13) on bhloader execution: Permission denied.折腾了好久,最后在官网找到了问题的解答:Try installing somewhere other than under /root or /home and see if that solves your problem.改安装目录为 /usr/local终于好了
卡盟 Email
2012-2-20 10:49
我用myisam和brighthouse两种存储引擎做测试,相同数据量下,相同的条件,没有博主说的那么大差距啊.而且sum+group by的速度比myisam还要慢,不知道为啥,myisam也没有做索引.
500w条数据
SELECT SUM(log_5) AS tl,log_6 FROM log_test GROUP BY log_6 ORDER BY tl; myisam,大概执行15秒,
SELECT SUM(log_5) AS tl,log_6 FROM log_test1 GROUP BY log_6 ORDER BY tl;brighthouse,大概执行26秒,


SELECT count(log_5) AS tl,log_6 FROM log_test GROUP BY log_6 ORDER BY tl; myisam,大概执行14秒,
SELECT count(log_5) AS tl,log_6 FROM log_test1 GROUP BY log_6 ORDER BY tl;brighthouse,大概执行6秒,

压缩比到是相当高的,大概15:1的样子...
Alan
2012-3-29 10:33
最近也在用,测试以后发现, in 语句, 在使用infobright 后, 速度比MyIsam 要慢很多.. 这方面是否有什么优化可言?
Hogan Email Homepage
2012-5-10 17:54
Ci sono diversi motivi principali per cui si può sicuramente trovare sul proprio volere le soluzioni contabili che coinvolgono tutta Stansted, anche se alcune persone potrebbero uomini e donne, probabilmente si dovrà disporre di soluzioni contabili rispetto con la gente. Stai con me per scoprire di più su molti dei motivi per prendere in considerazione l'impiego contabili in tutta Hogan Stansted. Costruire un businessIf vi capita di essere in funzione, è abbastanza possibile che si può fare uso di soluzioni contabili. Non date per scontato tutte le società hanno abbastanza soldi per utilizzare a tempo pieno il personale fiscali, così utilizzando una conditi insieme con l'agenzia affidabile che coinvolge tutta commercialisti Stansted potrebbe rendere meno difficili problemi.
分页: 2/4 第一页 上页 1 2 3 4 下页 最后页
发表评论
表情
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
打开HTML
打开UBB
打开表情
隐藏
记住我
昵称   密码   游客无需密码
网址   电邮   [注册]