注册
登录
| 关键词： nbsp MySQL Infobright Data 3307 存储 function nbs 数据 browser |
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 65535mkdir -p /data0/mysql/3307/usr/sbin/groupadd mysql/usr/sbin/useradd -g mysql mysqlcd /usr/local ①、64位系统： wget http://www.infobright.org/downloads/ice/infobright-3.3.1-x86_64-ice.tar.gztar zxvf infobright-3.3.1-x86_64-ice.tar.gzmv infobright-3.3.1-x86_64 infobright ②、32位系统： wget http://www.infobright.org/downloads/ice/infobright-3.3.1-i686-ice.tar.gztar zxvf infobright-3.3.1-i686-ice.tar.gzmv 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/shmysql_port=3307mysql_username="admin"mysql_password="12345678"function_start_mysql(){ printf "Starting MySQL... " 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... " 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... " 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_mysqlelif [ "$1" = "stop" ]; then function_stop_mysqlelif [ "$1" = "restart" ]; thenfunction_restart_mysqlelif [ "$1" = "kill" ]; thenfunction_kill_mysqlelse printf "Usage: /data0/mysql/${mysql_port}/mysql {start|stop|restart|kill} "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 ' ';" 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 ' ';" 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 |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
