カラム指向DB比較 - Infobright インストールの巻。

カラム指向DB比較 - MonetDB インストールの巻。
カラム指向DB比較 - MonetDB パフォーマンス検証の巻。
カラム指向DB比較 - InfiniDB インストールの巻。
カラム指向DB比較 - InfiniDB(番外編) - はまりポイント挙げるの巻。
カラム指向DB比較 - InfiniDB パフォーマンス検証の巻。
のつづき。

MonetDB, Infinidb に続いて Infobright をインストールしてみる。

1. インストール
2. 起動および準備
3. おためし

実施環境

• Cloudcore
• OS: Ubuntu 12.04
• CPU: AMD Phenom(tm) 9550 Quad-Core Processor 2.2GHz
• Memory: 2G

1. インストール

http://www.infobright.org/ からバイナリパッケージを取得。今回は Ubuntu 上で構築を行う事から deb パッケージを取得してきた。管理者権限で dpkg コマンドからインストールするだけの簡単なお仕事です。

$ sudo dpkg -i infobright-4.0.7-0-x86_64-ice.deb

Selecting previously unselected package infobright.
(Reading database ... 67287 files and directories currently installed.)
Unpacking infobright (from infobright-4.0.7-0-x86_64-ice.deb) ...
Installing infobright 4.0.7-0 (x86_64)
The installer will generate /tmp/ib4.0.7-0-install.log install trace log.
Setting up infobright (4.0.7-0) ...
Creating/Updating datadir and cachedir
Creating user mysql and group mysql
Installing default databases
Installing MySQL system tables...
OK
Filling help tables...
OK

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-x86_64/bin/mysqladmin -u root password 'new-password'
/usr/local/infobright-4.0.7-x86_64/bin/mysqladmin -u root -h shimajiro password 'new-password'

Alternatively you can run:
/usr/local/infobright-4.0.7-x86_64/bin/mysql_secure_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-x86_64 ; /usr/local/infobright-4.0.7-x86_64/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 http://www.mysql.com/
Support MySQL by buying support/licenses from http://shop.mysql.com/

System Physical memory: 2003(MB)
Infobright optimal ServerMainHeapSize is set to 600(MB)
Infobright optimal LoaderMainHeapSize is set to 320(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-x86_64.
Example command: cd /usr/local/infobright-4.0.7-x86_64; ./postconfig.sh

2. 起動および準備

# 止まっている事を確認
$ sudo /etc/init.d/mysqld-ib status
 * MySQL is not running

# 起動
$ sudo /etc/init.d/mysqld-ib start
Starting MySQL
. *

# 停止
$ sudo /etc/init.d/mysqld-ib stop
Shutting down MySQL
. *

3. おためし
とりあえず一通り触ってみる。

$ sudo /etc/init.d/mysqld-ib start
Starting MySQL
. *

# 接続
$ mysql-ib -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.40 build number (revision)=IB_4.0.7_r16961_17249(ice) (static)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# 初期DB一覧
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| BH_RSI_Repository  |
| mysql              |
| sys_infobright     |
| test               |
+--------------------+
5 rows in set (0.00 sec)

# DB エンジン一覧
mysql> show engines;
+-------------+---------+-----------------------------------------------------------+--------------+------+------------+
| Engine      | Support | Comment                                                   | Transactions | XA   | Savepoints |
+-------------+---------+-----------------------------------------------------------+--------------+------+------------+
| BRIGHTHOUSE | DEFAULT | Brighthouse storage engine                                | YES          | NO   | NO         |
| MRG_MYISAM  | YES     | Collection of identical MyISAM tables                     | NO           | NO   | NO         |
| CSV         | YES     | CSV storage engine                                        | NO           | NO   | NO         |
| MyISAM      | YES     | Default engine as of MySQL 3.23 with great performance    | NO           | NO   | NO         |
| MEMORY      | YES     | Hash based, stored in memory, useful for temporary tables | NO           | NO   | NO         |
+-------------+---------+-----------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)


mysql> use test
Database changed
mysql> show tables;
Empty set (0.00 sec)

# テーブル作成: engine = brighthouse
mysql> create table hoge(id int, value int) engine=brighthouse;
Query OK, 0 rows affected (0.00 sec)

# 確認
mysql> show table status;
+------+-------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+------------+----------+----------------+-------------------------------------------------+
| Name | Engine      | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation  | Checksum | Create_options | Comment                                         |
+------+-------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+------------+----------+----------------+-------------------------------------------------+
| hoge | BRIGHTHOUSE |      10 | Compressed |    0 |              0 |           0 |               0 |            0 |         0 |           NULL | 2012-09-15 00:34:49 | 2012-09-15 00:34:49 | NULL       | latin1_bin |     NULL |                | Overall compression ratio: 0.000, Raw size=0 MB |
+------+-------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+------------+----------+----------------+-------------------------------------------------+
1 row in set (0.00 sec)

# データの insert は不可
mysql> insert into hoge values (1, 100);
ERROR 1031 (HY000): Table storage engine for 'hoge' doesn't have this option

mysql> quit
Bye

insert が実施できないので代わりに infobright のデータローダを使用してデータを取り込んでみる。
まずはファイルを作成。

hoge.txt

1,100
2,200
3,300

データ取込の実施。

$ mysql-ib -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.40 build number (revision)=IB_4.0.7_r16961_17249(ice) (static)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed

# bulk load
mysql> load data infile '/home/you1025/tmp/hoge.txt' into table hoge fields terminated by ',';
Query OK, 3 rows affected (0.06 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from hoge;
+------+-------+
| id   | value |
+------+-------+
|    1 |   100 |
|    2 |   200 |
|    3 |   300 |
+------+-------+
3 rows in set (0.00 sec)

# insert はやはりダメ
mysql> insert into hoge values (4, 400);
ERROR 1031 (HY000): Table storage engine for 'hoge' doesn't have this option

# update も不可
mysql> update hoge set value = 111 where id = 1;
ERROR 1031 (HY000): Table storage engine for 'hoge' doesn't have this option

# delete も不可
mysql> delete from hoge;
ERROR 1031 (HY000): Table storage engine for 'hoge' doesn't have this option

# truncate も不可！！
mysql> truncate table hoge;
ERROR 1031 (HY000): Table storage engine for 'hoge' doesn't have this option

参考:

• Infobright_Data_Loading_Guide.pdf