リモートのoracleからデータを抜いてローカルのoraceにコピーしようとしたら、抜いてくる段階で異様に時間がかかってしまいはまりました。。
環境
- MacOS X Yosemite
- Ruby 2.1.5
- ruby-oci8
- oracle11.2.0.4
やりたかったこと
- リモートのマスターDBからデータをCSVで抜いて、ローカルの開発用DBにコピーしたい
問題
- テスト環境では問題ないのに、本番DBにつなぐとSELECTした後のfetchが異様に遅い
本題
とあるシステムの開発のために、リモートのDBから定期的にローカルにデータをコピーしてくる必要がありました。
rubyからoracleにつなぐのは初めてだったんですが、とりあえず調べながらさくっと作ったコードが以下
@conn = OCI8.new('scott','tiger','hogehogetns')
sql = 'select * from hoge_mst'
result = @conn.exec(sql)
while row = result.fetch()
#csvに書き込む処理
end
これでローカルでテストした際は何も問題なく動いてガッツポーズ
したのもつかの間、いざ本番のDBにつないでみたら
お、、、おそい。。。びっくりするくらいおそい。。。
調べてみたところ、このwhileの部分で140秒もかかっています。
データは1万行程度で、ローカルでやった時は3秒程度で終わっていました。
確かにDBサーバーは貧弱で回線も激遅ではあるんですが、それにしてもSQLDeveloperなどで同じクエリを投げた時に比べて遅すぎます。
というわけで色々調べたところ、ociのprefetch_rowsのデフォルト値が1になっていることを発見。
極端にはしょっていえば、selectの結果をフェッチする時に、一度のリクエストで1行ずつしか取得してこない、ということのようです。
つまり、1万行をフェッチするのに1万回リクエストを飛ばす、ということ（でいいんだと思う。。。ここらへんはもっとよく勉強します。）
今回繋ぎたいDBは回線が非常に遅いので、1万回もやりとりしてたらそりゃ遅くなりますよね、ということです。
なのでクエリを飛ばす前にこのprefetch_rowsの値を大きくしてあげます。
@conn = OCI8.new('scott','tiger','hogehogetns')
@conn.prefetch_rows = 1000
とりあえず今回のシステムで扱うデータは1万行〜1万5千行程度の範囲でほぼ固定なので、prefetch_rowsを1000にしてみます。
結果
大成功！
先ほど140秒程度かかっていた1万行のfetchが3秒で終わるようになりました。
ここまで見事に効果が現れると気持ち良いですね。
というわけで実はrubyからoracleを扱うのは初めてだったので、おそらく割と初歩的なところでハマってしまったようです。
何はともあれ解決して一安心でした。
余談ですが、ociの仕様を調べようとしてもPHPの情報ばっかり出てきますね。
RubyからOracleを扱う人ってそんなにいないんでしょうか？
追記(2015/05/08)
プリフェッチ行数を調整することでパフォーマンスが向上する原理についてはこちらの記事がとてもわかりやすかったのでご紹介しておきます。
ちなみに一般的にプリフェッチ行数は100〜200行程度が最適なことが多いそうです。