最近はNonSQL DBばかりに注力していたので、ひさしぶりにRDBMSに回帰してみました。
データは最近扱っているiptablesログか郵便番号か、どちらにしようかと思ったのですが、MongoDBでは郵便番号情報を扱っていたので、今回はRubyを使って郵便番号DBを作成しています。
さいしょに感想らしきものを一言
MongoDBとCouchDBの比較はいろいろありますが、DB2を使ってみて改めて感じるのはチューニングポイントが沢山あって使いこなすマニアックな喜びはありそうだという点です。
けれど、ACIDがなくてもOptimisticな処理で十分対応できる用途に対しては、RDBMSを導入する利点よりもメンテナンスコストが上回ってしまう気がします。
あと、DB2と関係ないですが、MongoDBが物理メモリをかなり消費する点も気になっています。
本体の消費メモリは少ないはずですが、memmapを使ってファイルにアクセスしているようです。
CouchDBが動いているErlangのbeamプロセスは負荷をかけてもだいたい30MB前後、MongoDBは物理メモリの搭載量にも依存するようですが、できるだけ空き領域をキャッシュとして使うようにみえます。
DB2は比較にならないほどのプロセス数とスレッド数とメモリを消費してくれますが、NODEディレクトリにある物理ファイルのサイズはMongoDBよりも小さいです。
もっともMongoDBは使うファイルを最初に領域を確保してしまいますから、db.stats()で表示されるdataSizeをみると純粋なデータサイズはかなり小さくですけどね。
今回は ibm_db ライブラリを使って、Ruby から DB2 CLI Driver を呼び出しています。
オフィシャルのibm_db API documentだけでは役に立たない模様で、DeveloperWorksの参考書を読まないと何がなんだかさっぱりでした。
環境の説明
今回は次のような環境で作業を行ないました。
- CPU: PhenomII 940 X4
- Memory: 8GB (FileCache: 約4GB)
- Disks: 500GBx2 (Software RAID-1)
- DB2 Express-C 9.7 (db2leve: DB2 v9.7.0.2)
- Ruby: 1.9.2-p136
- Ruby CLI Driver: IBM_DB 2.5.6
Ruby CLI Driverの導入
gemを使ってダウンロードしますが、gemsの仕組みは使わないので、ibm_dbのlibディレクトリだけコピーしてきます。
$ export IBM_DB_LIB=~/sqllib/lib
$ export IBM_DB_INCLUDE=~/sqllib/include
$ gem install --install-dir tmp_rubylib ibm_db
$ cp -r tmp_rubylib/gems/ibm_db-2.5.6/lib .
ここから先はコピーしたlibディレクトリのあるディレクトリで作業を行ないます。
DBの作成とテーブルの定義
Databaseの作成は最初にちょっとするだけなので、手動でやっておきます。
$ db2 create db postaldb using codeset UTF-8 territory en
しばらくはリモート接続をしないので、nodeの作成やらDB2COMMの設定などはしないでおきます。
次はテーブルを作成します。
分割はせずに一つの巨大なテーブルを作成しています。
POSTALテーブル作成スクリプト
#!/bin/bash
db2 'connect to postaldb'
db2 'DROP TABLE POSTAL'
db2 'CREATE TABLE POSTAL ( SERNUM INTEGER PRIMARY KEY NOT NULL, CITYID INTEGER, PCODEOLD CHAR(6), PCODE CHAR(8), PREFKANA GRAPHIC(7), CITYKANA GRAPHIC(25), STREETKANA GRAPHIC(70), PREF GRAPHIC(7), CITY GRAPHIC(25), STREET GRAPHIC(70), OP0 INTEGER, OP1 INTEGER, OP2 INTEGER, OP3 INTEGER, OP4 INTEGER, OP5 INTEGER )'
db2 'terminate'
preparedステートメントを使ったデータのINSERT
Rubyを使ったINSERT文の使い方はドキュメントになくて、executeUpdateに相当するメソッドもないようなので、普通にexecuteメソッドを使いました。
ken_all.utf8.csvファイルをPOSTALテーブルにINSERTするRubyスクリプト (insert_csv_prepare.rb)
#!/usr/local/bin/ruby
# -*- coding: utf-8 -*-
require 'csv'
$:.unshift "lib"
require 'ibm_db'
conn = IBM_DB::connect("postaldb","","")
IBM_DB::autocommit(conn, IBM_DB::SQL_AUTOCOMMIT_ON)
sql = "INSERT INTO POSTAL (SERNUM,CITYID,PCODEOLD,PCODE,PREFKANA,CITYKANA,STREETKANA,PREF,CITY,STREET,OP0,OP1,OP2,OP3,OP4,OP5) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
pstmt = IBM_DB::prepare(conn,sql)
sernum = 1
opts={}
opts[:headers] = [:i,:pcode_old,:pcode,:pref_kana,:city_kana,:street_kana,:pref,:city,:street,:op0,:op1,:op2,:op3,:op4,:op5]
CSV.new(open("ken_all.utf8.csv"), opts).each do |row|
IBM_DB::execute(pstmt, [sernum.to_i, row[:i].to_i,
row[:pcode_old], row[:pcode], row[:pref_kana],row[:city_kana],row[:street_kana],row[:pref],row[:city],row[:street],row[:op0].to_i,row[:op1].to_i,row[:op2].to_i,row[:op3].to_i,row[:op4].to_i,row[:op5].to_i])
sernum += 1
end
IBM_DB::close(conn)
このスクリプトを準備するために、郵便局のWebサイトから郵便番号データをダウンロードしておきます。
$ unzip ken_all.zip
$ nkf -w ken_all.csv > ken_all.utf8.csv
$ ruby insert_csv_prepare.rb
約12万件(元データ約17MB)分のテーブルを作成するのに、2分30秒ほどかかりました。
Prepared statement(プリペアード・ステートメント)を使わずにSQLを毎回生成すると、7分30秒ほどでしたから、だいたい処理効率は3倍くらい違いがあります。
パフォーマンスについて
MongoDBでもDISTINCT(PREF),PREFKANAの結果を47行出力させてみましたが、MongoDBではPREFに対するINDEXがかなりパフォーマンスに寄与しました。
DB2でINDEXを作成せずにSQLを投げると、だいたいdb2start直後で接続時間を省いて3秒前後くらいです。
2回目以降はキャッシュが効くのか、0.2秒くらいになりました。
DB2のINDEXを使ってどうなるのか。
いろいろ謎なパラメータが沢山あるので、使い方によってはマッチしないんじゃないかなという心配がありました。
そんな理由で調査にはdb2advisが便利そうだったので、EXPLAIN表を作ってからSQLを実行して、DB2に最適なINDEXを考えさせました。
$ db2 connect to postaldb
$ db2 -tvf /opt/ibm/db2/V9.7.2/misc/EXPLAIN.DDL
$ db2advis -d postaldb -s "select distinct(pref),prefkana from postal"
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- index[1], 0.771MB
CREATE INDEX "YASU "."IDX1103110446310" ON "YASU "."POSTAL"
("PREFKANA" ASC, "PREF" ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
COMMIT WORK ;
おなじことをしてみた結果は、予想どおり、この程度のデータ量ではあまり変化はなく、むしろDB2の接続にかかる時間が全体のパフォーマンスを低下させています。
RubyスクリプトでMongDBとDB2で同じような結果になるようにして時間を計測しましたが、MongoDBはdistinctしたPREFに対応するPREFKANA列を別に検索するカーソルの1ページ分の結果をprefetchするロジック的には効率は低いはずです。
また、結果はそれぞれ数回実行した後のものを載せています。
MongoDB | 2.182[s] (index無。接続時間含む)
MongoDB | 0.282[s] (index有。接続時間含む)
DB2 | 3.225[s] (index無。connect時間含)
DB2 | 1.751[s] (index有。connect時間含)
DB2 | 0.164[s] (index無。connect済)
DB2 | 0.121[s] (index有。connect済)
当たり前の結果ですが、DB2を使うならDBPoolingの仕組みは大切だということになりそうです。
SELECT文を発行するRubyスクリプトはprepared statementを利用する必要はありませんが、参考までにprepared state版を載せておきます。
SELECTを行なうSQL文 ibm_db driver/prepared statement版 (select_distinct_pref_pstmt.rb)
#!/usr/local/bin/ruby
# -*- coding: utf-8 -*-
$:.unshift "lib"
require 'ibm_db'
conn = IBM_DB::connect("postaldb","","")
IBM_DB::autocommit(conn, IBM_DB::SQL_AUTOCOMMIT_ON)
sql = "SELECT DISTINCT(PREF),PREFKANA FROM POSTAL"
pstmt = IBM_DB::prepare(conn,sql)
if IBM_DB::execute(pstmt, [])
while row = IBM_DB::fetch_array(pstmt)
puts "#{row[0].strip},#{row[1].strip}."
end
end
IBM_DB::close(conn)
