カラム指向DB比較 - MonetDB パフォーマンス検証の巻。
のつづき。
今回は InfiniDB の環境構築を実施してみたいと思うよ。
- インストール
- 起動および準備
- おためし
実施環境
- CloudCore
- OS: Ubuntu 12.04
- CPU: AMD Phenom(tm) 9550 Quad-Core Processor 2.2GHz
- Momory: 2G
1. インストール
まずは http://www.infinidb.org/ からバイナリパッケージを取得してくる。個人情報の登録とか色々やったはずだがもう覚えていないので省略。 バイナリパッケージならお手軽に導入可能。
# まずは解凍 $ tar xfz calpont-infinidb-2.2.9-2.x86_64.bin.tar.gz # インストールは解凍したディレクトリを設置するだけ $ sudo mkdir /usr/local/infinidb $ sudo mv Calpont /usr/local/infinidb/2.2.9 # /usr/local/Calpont にパッケージが存在する事を前提にインストールスクリプトが作成されているので上記ディレクトリにシンボリックリンクを張る事で対応。 $ sudo ln -s /usr/local/infinidb/2.2.9 /usr/local/Calpont # インストールスクリプトの実施 $ sudo /usr/local/Calpont/bin/install-infinidb.sh DEPENDENCY LIBRARY CHECK All libraries found DISK SPACE CHECK Make sure there is enough local or mounted disk space for the InfiniDB System Catalog and the planned test Database. Filesystem Size Used Avail Use% Mounted on /dev/vda1 98G 17G 77G 19% / No '/usr/local/Calpont/data1' mounted disk found Check the JumpStart Guide for information on how to setup a mounted disk if you require additional disk space for the InfiniDB Database CONFIGURATION / DATA VALIDATION CHECK Validates Configuration and Data settings The Memory Configuration Setting are: NumBlocksPct = 66% TotalUmMemory = 512M SETUP INFINIDB MYSQL Starting MySQL . * Shutting down MySQL . * Starting MySQL . * Shutting down MySQL . * InfiniDB is setup for autostart using 'update-rc.d' InfiniDB Installation Completed $あっさり終了。
2. 起動及び準備
と言ってもやる事は特に無い。
# DB の起動 $ sudo service infinidb start Starting Calpont InfiniDB Database Platform: ...... DONE creating system catalog (be patient): . done. Starting Calpont InfiniDB MySQL: Starting MySQL . * Validate InfiniDB System Catalog: Validation Succesfully Completed Perform Functionality test: InfiniDB Logging check: DONE Platform Process check: DONE MySQL Daemon check: * MySQL running (10269) Database check: DONE/usr/local/Calpont/bin/calpontAlias を実施するか、下記を .bashrc なり .bash_aliases に追加して infinidb 用クライアント idbmysql を使えるようにする。
# .bash_aliases alias idbmysql='/usr/local/Calpont/mysql/bin/mysql --defaults-file=/usr/local/Calpont/mysql/my.cnf -u root'これで infinidb にアクセスできる。と言ってもDBエンジン以外は普通の MySQL と変わらないけど。
$ idbmysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.1.39 MySQL Community / Calpont InfiniDB Community 2.2.9-2 Final (COSS LA) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. # 初期 DB 一覧 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | calpontsys | | infinidb_vtable | | mysql | | test | +--------------------+ 5 rows in set (0.00 sec) # DB エンジン一覧。 # なぜかInnoDB が見当たらない。 mysql> show engines; +------------+---------+-----------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +------------+---------+-----------------------------------------------------------+--------------+------+------------+ | CSV | YES | CSV storage engine | NO | NO | NO | | InfiniDB | YES | Calpont InfiniDB storage engine | YES | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | +------------+---------+-----------------------------------------------------------+--------------+------+------------+ 5 rows in set (0.00 sec) mysql> quit Bye# plugin の一覧に innodb が含まれている事を確認。
$ ls /usr/local/Calpont/mysql/lib/mysql/plugin/ ha_archive.a ha_archive.so.0.0.0 ha_blackhole.so.0 ha_example.so ha_federated.la ha_innodb.a ha_innodb_plugin.so ha_innodb.so.0 ha_archive.la ha_blackhole.a ha_blackhole.so.0.0.0 ha_example.so.0 ha_federated.so ha_innodb.la ha_innodb_plugin.so.0 ha_innodb.so.0.0.0 ha_archive.so ha_blackhole.la ha_example.a ha_example.so.0.0.0 ha_federated.so.0 ha_innodb_plugin.a ha_innodb_plugin.so.0.0.0 libcalmysql.so ha_archive.so.0 ha_blackhole.so ha_example.la ha_federated.a ha_federated.so.0.0.0 ha_innodb_plugin.la ha_innodb.soinnodb プラグインのインストール
$ idbmysql mysql> install plugin innodb soname 'ha_innodb.so'; Query OK, 0 rows affected (0.37 sec) mysql> show engines; +------------+---------+------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +------------+---------+------------------------------------------------------------+--------------+------+------------+ | CSV | YES | CSV storage engine | NO | NO | NO | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | InfiniDB | YES | Calpont InfiniDB storage engine | YES | NO | NO | +------------+---------+------------------------------------------------------------+--------------+------+------------+ 6 rows in set (0.00 sec) mysql> quit Byeサービスの停止。
$ sudo service infinidb stop Shutting down Calpont InfiniDB MySQL: Shutting down MySQL . * Shutting down Calpont InfiniDB Database Platform:
3. おためし
データベース、テーブルの作成およびデータのインサート実施。
# サービスの起動と接続 $ sudo service infinidb start $ idbmysql # DB 作成 mysql> create database calpont; Query OK, 1 row affected (0.00 sec) # テーブルの作成 # 作成時に engine=infinidb の指定を忘れないこと mysql> use calpont; Database changed mysql> create table caltest(col1 int, col2 int) engine=infinidb; Query OK, 0 rows affected (0.25 sec) # テーブルの確認 mysql> show create table caltest; +---------+--------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+--------------------------------------------------------------------------------------------------------------------------------+ | caltest | CREATE TABLE `caltest` ( `col1` int(11) DEFAULT NULL, `col2` int(11) DEFAULT NULL ) ENGINE=InfiniDB DEFAULT CHARSET=latin1 | +---------+--------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (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 | +---------+----------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+ | caltest | InfiniDB | 10 | Fixed | 2000 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | latin1_swedish_ci | NULL | | | +---------+----------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+ 1 row in set (0.00 sec) # データ作成 mysql> set autocommit=0; mysql> insert into caltest values(1, 2),(3, 4); Records: 2 Duplicates: 0 Warnings: 0 mysql> commit; # データを確認 mysql> select * from caltest; +------+------+ | col1 | col2 | +------+------+ | 1 | 2 | | 3 | 4 | +------+------+ 2 rows in set (0.13 sec) # トランザクションを試す mysql> set autocommit=0; mysql> insert into caltest values(5, 6), (7, 8); Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into caltest values(5, 6); mysql> select * from caltest; +------+------+ | col1 | col2 | +------+------+ | 1 | 2 | | 3 | 4 | | 5 | 6 | +------+------+ mysql> rollback; mysql> select * from caltest; +------+------+ | col1 | col2 | +------+------+ | 1 | 2 | | 3 | 4 | +------+------+ # ちなみに分離レベルは REPEATABLE_READ mysql> select @@global.tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | REPEATABLE-READ | +-----------------------+ 1 row in set (0.00 sec) mysql> quit Bye
Bulk Load を試してみる。まずはデータの用意から。
# sample.csv $ cat sample.csv 5,6 7,8 9,10cpimport コマンドでデータのロードを実施。
# calimport dbName tblName loadFile [options] $ cpimport calpont caltest sample.csv -s ',' Column delimiter : , Using table OID 3000 as the default JOB ID Input file(s) will be read from : /home/you1025/tmp Job description file : /usr/local/Calpont/data/bulk/tmpjob/calpont_caltest_D20120806_T005142_Job_3000.xml I/O error : Permission denied I/O error : Permission denied 2012-08-06 00:51:42 (12593) ERR : file /usr/local/Calpont/data/bulk/tmpjob/calpont_caltest_D20120806_T005142_Job_3000.xml does not exist [1055] Error in loading job information, error code : 1055失敗。どうも調べてみるとロード用の設定ファイルを /usr/local/Calpont 以下に作成するらしいので権限が足りていないらしい。適切に権限設定を行う必要があるが、面倒なので root で実施。
$ sudo cpimport calpont caltest sample.csv -s ',' [sudo] password for you1025: Column delimiter : , Using table OID 3000 as the default JOB ID Input file(s) will be read from : /home/you1025/tmp Job description file : /usr/local/Calpont/data/bulk/tmpjob/calpont_caltest_D20120806_T005329_Job_3000.xml Log file for this job: /usr/local/Calpont/data/bulk/log/Job_3000.log 2012-08-06 00:53:29 (12636) INFO : successfully loaded job file /usr/local/Calpont/data/bulk/tmpjob/calpont_caltest_D20120806_T005329_Job_3000.xml 2012-08-06 00:53:29 (12636) INFO : Job file loaded, run time for this step : 0.00149584 seconds 2012-08-06 00:53:29 (12636) INFO : PreProcessing check starts 2012-08-06 00:53:29 (12636) INFO : PreProcessing check completed 2012-08-06 00:53:29 (12636) INFO : preProcess completed, run time for this step : 0.00455284 seconds 2012-08-06 00:53:29 (12636) INFO : No of Read Threads Spawned = 1 2012-08-06 00:53:29 (12636) INFO : No of Parse Threads Spawned = 3 2012-08-06 00:53:29 (12636) INFO : For table calpont.caltest: 3 rows processed and 3 rows inserted. 2012-08-06 00:53:29 (12636) INFO : Bulk load completed, total run time : 0.109458 secondsどうも成功したっぽいので確認。
$ idbmysql mysql> select * from calpont.caltest; +------+------+ | col1 | col2 | +------+------+ | 1 | 2 | | 3 | 4 | | 5 | 6 | | 7 | 8 | | 9 | 10 | +------+------+ 5 rows in set (0.05 sec)データが増えとる。成功！！
参考:
- /usr/local/Calpont/README
- /usr/local/Calpont/INSTALL の 68 行目辺り("To use bulk load:")
- OSSなDWH InfiniDB の話 ④ ～InnoDB エンジンと共存する方法
0 件のコメント:
コメントを投稿