SQL Relay + Oracle で悪戦苦闘

JAVA を使って構築した商用サイトでは、ほぼ必須技術でもあるＤＢコネクションプーリング。ＤＢコネクションプーリングを導入するメリットは、データベース操作の中でも非常に負荷の高い接続処理の隠蔽と、コネクション数の削減によるリソースの浪費回避につきる。

今回は、どうしても Perl で Oracle のコネクションプーリングをしたくて、SQL Relay の導入を検討してみた。SQL Relay オープンソースのＤＢコネクションプーリングサーバで、Oracle、MySQL、PostgreSQL 等々、通常利用するほとんどのデータベースに対応している。また、C/C++、Perl、PHP、Ruby、Java 等々の多言語対応も実装し、独自 API と DBI によるアクセス方法が用意されているので大変便利です。

はじめに C++ 用の汎用クラスライブラリである Rudiments をインストールをします。

cd /usr/local/src/
wget http://belnet.dl.sourceforge.net/sourceforge/rudiments/rudiments-0.27.tar.gz
tar xvfz rudiments-0.27.tar.gz
cd rudiments-0.27
./configure
make
make install

次に SQL Relay をインストールをします（cofigure の設定で Perl での利用に限定しました）。

cd /usr/local/src/
wget http://optusnet.dl.sourceforge.net/sourceforge/sqlrelay/sqlrelay-0.35.tar.gz
tar xvfz sqlrelay-0.35.tar.gz
cd sqlrelay-0.35
./configure --disable-ruby --disable-python --disable-tcl --disable-java
make
make install

インストールが終わったら、SQL Relay の設定ファイルを編集する。詳細はマニュアルを参照。

<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
  <instance id="test" port="9000" socket="/tmp/testdb.socket" dbase="oracle8" 
   connections="10" maxconnections="10" maxqueuelength="5" growby="1" ttl="60" 
   endofsession="commit" sessiontimeout="600" runasuser="oraclei" runasgroup="dba" cursors="5"  debug="none">
    <users>
      <user user="user" password="passwd"/>
    </users>
    <connections>
      <connection connectionid="TESTDB" string="user=user;password=passwd;oracle_sid=TESTDB;" metric="1"/>
    </connections>
  </instance>
</instances>

その後、SQL Relay ライブラリへのパスを通す必要がある。たとえば下記のように /etc/profile に設定

PATH=$PATH:/usr/local/firstworks/bin
LD_LIBRARY_PATH=/usr/local/firstworks/lib:$LD_LIBRARY_PATH
export PATH LD_LIBRARY_PATH

SQL Relay の起動、停止は下記の通り。

sqlr-start -id test
sqlr-stop

もし、/etc/init.d/sqlrelay によって自動起動したい場合は、/etc/sysconfig/sqlrelay にサービス名設定を追加

# Enter here all SQL Relay connections to start automatically with the
# service command. (one per line)
res

以上で、概ねの設定は完了。簡単なベンチマークのプログラムが付属しているので、試してみる。

cd /usr/local/src/sqlrelay-0.35/bench/oracle8
make
cd /usr/local/src/sqlrelay-0.35/bench/sqlrelay
make

Oracle でもっとも処理が軽い SQL を使ってベンチマークを計ってみた。

/usr/local/src/sqlrelay-0.35/bench/oracle8/ora8test user passwd TESTDB "select 1 from dual" 50
total system time used: 300000
total real time: 4

/usr/local/src/sqlrelay-0.35/bench/sqlrelay/sqlrtest localhost 9000 /tmp/testdb.socket user passwd \
"select 1 from dual" 50
total system time used : 170000
total real time : 0

結果からＤＢ接続処理に関しては抜群のパフォーマンスを発揮していることがわかる。
しかしいろいろと検証を重ねていくにつれて、商用サイトで使うにはあまりに厳しい事実が判明してきたので以下まとめ。

SQL Relay のちょっとアレな仕様制限

• SQL Query の結果が大量の行を返すとき、パフォーマンスが劣化する。100行程度なら実用範囲内。
  →ログイン認証等、軽い SQL query に対してはパフォーマンスを発揮するが、管理・batch系の重い SQL には不適合。
• Insert のパフォーマンスが悪い。
• Bind 変数が 256 以上をサポートしない。→256 以上のカラムをもつテーブルで不具合発生。
• コネクションの解放に異様に時間がかかる場合がある。
• DBI:SQLRelay の処理が重い。SQL によっては DBI::Oracle よりパフォーマンス劣化。
  →SQLRelay ネイティブの API を perl から call すればよいが、DBI の記述から離れるため、移植性が悪い。
• mod_perl 配下で DESTROY メソッドが未定義でエラーをはく。

な感じです。幾つかは patch を作れば解決するが、いくつかのパフォーマンス劣化は如何ともしがたく、データ更新系のサービスではちょっと使いづらい感じです。以下、検証で気がついた不具合解消のメモです。

Bind 変数の制限を改善する方法 → src/common/defines.h と src/api/c++/include/sqlrelay/private/sqlrdefines.h を修正

//#define MAXQUERYSIZE 32768
//#define MAXVAR 256
#define MAXQUERYSIZE 65536
#define MAXVAR 512

DBI:SQLRelayの処理速度を改善する方法 → src/api/perl/DBD/SQLRelay.pm.in を修正（修正箇所が多いので抜粋。）

・・・
#my $cursor->setResultSetBufferSize(100);
my $cursor->setResultSetBufferSize(10000);
・・・

mod_perl 配下でのエラーを改善する方法 → src/api/perl/DBD/SQLRelay.pm.in を修正（修正箇所が多いので抜粋。）

・・・
$DBD::SQLRelay::dr::imp_data_size=0;
sub DESTROY {}
・・・
$DBD::SQLRelay::db::imp_data_size=0;
sub DESTROY {}
・・・
$DBD::SQLRelay::st::imp_data_size=0;
sub DESTROY {}
・・・

SQL Relay の検証はかなりハマリました。Oracle 以外のデータベースではまた違った結果になると思いますので、一つの情報までに。