MySQL,ODBC,CRecordsetの組合せで大量データを扱う場合の動きについて

[戻る]
PATIO 2007/06/29(金) 11:53:04
いつもお世話になっています、PATIOです。
VS2005でC++、MFC使用の環境でMySQLを使ったデータ処理を
やってみようとしています。
掲題にも書いていますが、ODBC経由でMFCのCDatabaseとCRecordsetを
使って処理を行っています。
CRecordsetは派生を行わずにそのまま使用しており、
CRecordset::OpenでSQL文を実行してGetFieldValeでデータの取り出し
を行うような流れで処理しています。
データベースには約800万件のデータが入っており、
これをデータベース上のインデックス順に一つずつ取り出して
特定の形式に変換してCSV形式のテキストファイルに出力する処理です。

私のイメージだとCRecordset::OpenでSQLを実行すると最初の一行目が
取り出されて、以降CRecordset::MoveNextで一行ずつ取り出されてくると
考えていたんですが、MySQL Administratorでコネクション情報を見ながら
出力ファイルの作成状況を見ていると私の予想と違い、最初のOpenで
全てのデータを返却しようとしているように見えます。

プログラム上では、出力ファイルのOpenがCRecordset::Openの成功時に
行うようになっており、MySQL Administratorでコネクション情報で
返却終了になるまで当該ファイルが作成されないことからそのように考えています。

この事については最初のSQL文(Limit制限無し)で実行すると
いつまでたっても出力ファイルの作成が行われない為、
SQL文にLimit指定を行い、返却数を制限すると返却終了後に
ファイルが作成される事で確認しました。

この辺りのMySQL、ODBC、CRecordsetを組合せ使った時の挙動について
情報をお持ちの方がいらっしゃいましたら教えてください。
できれば、一気に返却するのではなくて一度の返却量を調整できると
良いのですけれど。

データベースのOpenEx呼び出し時の指定文字列は以下のようなもので
特にオプションは指定していません。

"Driver={mySQL ODBC 3.51 Driver};Server=localhost;Port=3306;Stmt=SET CHARACTER
SET SJIS;Database=xxxxxxx;Uid=xxxx;pwd=xxxxxx";

xxxxは実際の値は問題あるので伏字にしています。
ちなみにどちらのOpenでもエラーが発生していないのは確認しています。

PATIO 2007/06/29(金) 11:58:28
あと、CRecordset::Openに引き渡しているnOpenTypeは、AFX_DB_USE_DEFAULT_TYPEで
dwOptionsは特に指定していません。

ITO 2007/06/29(金) 14:14:01
「CRecordset」は確認できていないのですが、ODBCSDKでは、ODBCドライバーからの
メッセージを取得できます。
 関数名 「SQLError」です。
 「CRecordset」にもあると思いますが、見つかっていません。
SQLの構文等のミスも見つけることができるので、便利です。
調べてみて下さい。

「CRecordset::MoveNext」
  は、アクセルのデータ操作を想定している可能性はありませんか。
アクセルとmySQLではSQL構文の仕様に食い違いがあると思います。
僕も、オラクルとの食い違いに苦労しました。

PATIO 2007/06/29(金) 15:47:15
返信有難うございます。

アクセルと言うのはMicrosoft Accessの事だと仮定して書くと
ODBCからのエラーに関してはCRecordsetの場合、CDBExceptionで例外がスローされると
思います。今回例外を補足するようにしていますが、特にエラーが発生しているという
事は無いと思います。少なくとも例外が補足される事はありません。

MySQLのC APIには結果を一気にクライアントに持ってきて処理するAPIと
一行ずつクライアントに持ってくるAPIの両方があります。
ODBCドライバが一気に持ってくる方と同じ実装している為に
上記のような動きをしているのであれば、外部からどうこうは無理ですね。
ODBC側の設定オプションに該当するような物が有れば、それが影響しているかも
しれませんけれど。
CRecordsetの動きと言うわけではないのかもしれません。

PATIO 2007/06/29(金) 15:52:00
あうあう。

>アクセルと言うのはMicrosoft Accessの事だと仮定して書くと

と書いておきながらAccessに全く触れてなかった。
うーん、これに関しては何ともわかりませんね。
裏の動きはどうあれ、表向きはMoveNextで次のデータが取れるわけで
裏で一気に取ってきて小出しに引き渡すのか、
その都度取ってきて引き渡すのかの差ですからメモリの問題さえなければ、
使う方からは同じ動きに見えますからね。

MSDNの説明を見る限りではCRecordsetその物はODBCを意識して作られた物だと
いう風に理解していたんですが、Accessを意識したつくりなのかなぁ。
Accessっぽいインターフェイスで使えるようにと言う話ならわかる気はしますけれど。

ITO 2007/06/29(金) 17:49:19
> アクセルと言うのはMicrosoft Accessの事だと仮定して書くと
 すみませんあたりです。

> MySQLのC APIには結果を一気にクライアントに持ってきて処理するAPIと
> 一行ずつクライアントに持ってくるAPIの両方があります。
MSDNライブラリーを調べてみましたら、「CRecordset::MoveNext」で正解みたいです。
ただ「 一行ずつ」なのかどうかは「GetRowsetSize( )」で調べるみたいです。
一行ずつにしたい場合は、OPENする前に、
SetRowsetSizeで1行に設定すればいいみたいです。
EOFはIsEOF( )でわかるみたいですね。

>dwOptionsは特に指定していません。
なのでいいと思います。

ITO 2007/06/29(金) 17:54:45
あと「結果を一気にクライアントに持ってきて処理する」は、
「CRecordset」では「バルク行フェッチ」といっているみたいです。

ITO 2007/06/29(金) 18:12:40
>MSDNの説明を見る限りではCRecordsetその物はODBCを意識して作られた物だと
>いう風に理解していたんですが、Accessを意識したつくりなのかなぁ。
>Accessっぽいインターフェイスで使えるようにと言う話ならわかる気はしますけれど。
すみません、僕も「ODBCを意識して作られた物」と理解しています。
ただ、「CRecordset::MoveNext」が自動的にSQL文を作成して1行ずつフェッチして
いくように勘違いしてしまいました。
>MySQL Administratorでコネクション情報を見ながら
>出力ファイルの作成状況を見ていると私の予想と違い、最初のOpenで
>全てのデータを返却しようとしているように見えます。
えっとー、SQL文で一致したすべての行を指定していればそうなるものと理解してます。
それは、「MySQL」のクライアント側ソフトでSQL文を実行してみるとわかると
思います。

PATIO 2007/07/02(月) 13:29:28
すいません、土日は仕事の方に手を出せる状態では無いので返事が遅れました。

>えっとー、SQL文で一致したすべての行を指定していればそうなるものと理解してます。
>それは、「MySQL」のクライアント側ソフトでSQL文を実行してみるとわかると
>思います。

やっぱりそうなるんでしょうねぇ。
現状は仕方が無いのでLimit句を使って500000毎に取り出すような処理にしてますが、
次のSQL文を実行する時にCRecordset::Closeをすると前の内容を開放してしまって
最初から処理をやり直すらしくてオフセットを500000にして500000件取り出そうとすると
時間が倍くらい掛かってしまうと言う現象が起きています。
そのテーブルには取り出す時のソート順と一致したインデックスもつけているのに
最初の500000件を読み飛ばす為に時間が掛かっているような感じです。
ストアドプロシジャーで行うようにフェッチを意識的に行わない限りは
駄目なのかも知れませんねぇ。

CRecordsetでは、「バルク行フェッチ」は実装されていないように書いてありますね。
「バルク行フェッチ」は派生クラスを作成してそちらで実装する事になっているみたいです。
現状ではCRecordsetを生で使っているので「バルク行フェッチ」は使われていないと言う
認識なんですが、
そもそもここの認識が間違っているんでしょうか。
「バルク行フェッチ」の説明があったので、「バルク行フェッチ」無い時は1行ずつ
取り出すのだという解釈をしたんですけれど。
ちなみにCRecordset::SetRowsetSizeは、「バルク行フェッチ」でないとアサートする
とMSDNには書いてあります。
「バルク行フェッチ」を実装すれば、一行ずつ取り出せると言う話ならやってみる価値は
有りそうですけれど、MySQLがバルクフェッチに対応していたかは調べてみないと分から
ないです。

PATIO 2007/07/02(月) 14:05:21
MSDNを読み返して見て思ったんですが、
1行ずつ取り出すとか言っているのはODBCドライバからの話であって
ODBCドライバには一括でデータが来ているのでは無いかと言う気がしてきました。
バルクフェッチにしてもODBCドライバから持ってくる処理を複数行一括で行うという
話なのでは無いかなと。
そうなると基本的にはクライアントにはSQLの結果は一気に送られてくると言う部分は
プログラムからはコントロールできないのではないかと言う気がしてきました。
そうなると今のLimitを使った方法しかないわけで、その動作結果からすると
MySQLも大量データになるとやはりそんなに早くないのではと言う気がしてきました。
とはいえ、C APIを直接叩く場合は、サーバーから一行ずつ取り出す為の関数があるので
その方法を使えばできるのかもしれませんが、私が調べた本にはVC++6.0を使えと
書いてあったのでこの時点で無理なのではと考えています。
VS2005で使った場合、ランタイムの関係とかでまずいのかなぁ。

ITO 2007/07/02(月) 16:32:18
>現状は仕方が無いのでLimit句を使って500000毎に取り出すような処理にしてますが、
>次のSQL文を実行する時にCRecordset::Closeをすると前の内容を開放してしまって
>最初から処理をやり直すらしくてオフセットを500000にして500000件取り出そうとする
>と時間が倍くらい掛かってしまうと言う現象が起きています。
 オラクルを扱っている方を見ていると大量のデータを扱う場合、もっと検索項目数を
増やして効率を上げてますね。
 後から項目数のふえたSQL文をみせてこれを使ってくださいと言われたことがありま
す。

>現状ではCRecordsetを生で使っているので「バルク行フェッチ」は使われていないと言
>う認識なんですが、
>そもそもここの認識が間違っているんでしょうか。
間違ってないと思います。
>「バルク行フェッチ」の説明があったので、「バルク行フェッチ」無い時は1行ずつ
> 取り出すのだという解釈をしたんですけれど。
> ちなみにCRecordset::SetRowsetSizeは、「バルク行フェッチ」でないとアサートする
> とMSDNには書いてあります。
それで正しいと思います。


>そうなると今のLimitを使った方法しかないわけで、その動作結果からすると
>MySQLも大量データになるとやはりそんなに早くないのではと言う気がしてきました。
>とはいえ、C APIを直接叩く場合は、サーバーから一行ずつ取り出す為の関数があるの
>でその方法を使えばできるのかもしれませんが、私が調べた本にはVC++6.0を使えと
>書いてあったのでこの時点で無理なのではと考えています。
うーん、フリーソフトですから速くないのかも知れません。
でも、ソフトの歴史は長いですよね。
なのでそこまで歴然とした差があるとは思えないのですが.....

PATIO 2007/07/03(火) 14:22:18
その後、調査続行中。
何せ8500000件のデータをソートしてソート順にアクセスし、
特定の形式のCSVファイルに出力すると言う処理をするのに数日掛かってしまう
という状況はいくらなんでもあんまりなので何とかしないといけないのです。

とあるテーブルには出力順に一致したインデックスが作成済みです。
で、これに対して以下のようにSQL文を発行します。(ODBC + CRecordset経由)

SELECT * FROM XXXXX ORDER BY AAA,BBB LIMIT 0, 500000;

テーブルの形式はInnoDBです。
データ件数は上記の通り、8500000件あります。
テーブル上の項目は全て出力対象のもので無駄な物は有りません。

上記のSQLを発行すると直にクライアントにデータの引渡しが始まり、
今の状態だとこの引渡しだけで2時間半くらい掛かっています。
これは、DBの状態も関係しそうなので一度まっさらにしてから
再度計ってみる必要がありそうですけれど。
で、次に

SELECT * FROM XXXXX ORDER BY AAA,BBB LIMIT 500000, 500000;

が流れるとこんどは直にクライアントにデータが来ません。
2時間半くらい立った後、データが来始める状態です。
最初の直にデータが来始める部分はインデックスを利用しているわけなので
納得できますし、期待した動きです。
ところが、次のSQL文でデータがクライアントに来始めるまでに
2時間半も掛かるのがよくわかりません。
まるで500000件をスキップする為に2時間半も掛かっているように
みえます。実際にはインデックスの設定を行っているわけなので
500000件のスキップにそこまで時間が掛かるとは思えないのですけれど
私の勘違いなんでしょうか。

Ban 2007/07/03(火) 21:03:44
> 1行ずつ取り出すとか言っているのはODBCドライバからの話であって
> ODBCドライバには一括でデータが来ているのでは無いかと言う気がしてきました。
> バルクフェッチにしてもODBCドライバから持ってくる処理を複数行一括で行うという話なのでは無
いかなと。

ODBC経由でのCRecordsetは通常、DBからの呼び出しは全部一括なはずです。
# ぶっちゃけ中身は単にSELECTなわけですから。

8500000件を全部一度オンメモリに置こうとしてスラッシングとか起こしてませんか?
「チューニングしたOracle」とかには勝てないとしても、MySQL自体は速いと思いますよ。


# 例えばVIEWとか自分で作れば改善されるのでは?

PATIO 2007/07/04(水) 09:12:44
返信有難うございます。

>8500000件を全部一度オンメモリに置こうとしてスラッシングとか起こしてませんか?

これってMySQLの内部でと言う話なんでしょうか。
要は、WHERE文なんかでロード量を減らせば改善されると言うことであれば、
その部分の検討は行っていなかったので検討してみます。
クライアント側でスワップが起こると言う話ならLIMITで対応可能だと思うので
多分、上記の事を言われているのではと思うのですが、いかがでしょう?

みけ 2007/07/04(水) 10:55:20
試していないので憶測になってしまいますが、下記URLにあるオプションで
「Don't Cache Result」を使用してみてはいかがでしょうか。

『22.1.3.5. Connector/ODBC Connection Parameters』
http://dev.mysql.com/doc/refman/5.1/ja/myodbc-configuration-connection-parameters.html

Ban 2007/07/04(水) 10:58:17
御意。

# まずはざっくり、(パフォーマンスモニタ等で?)メモリ使用量の確認から?

PATIO 2007/07/05(木) 11:40:51
レス有難うございます。

>みけさん
Don't Cache Resultを指定してみましたが、期待した効果は得られませんでした。
リードオンリーにしてforward-onlyも指定してみましたが、やはり駄目でした。

>Banさん
タスクマネージャーで見ている限りではmysqld-nt.exeのメモリ使用量は一定量からふえる
気配が無いので取っては捨てを繰り返しているかもしれませんね。
いずれにしても手詰まり状態でしたので試してみます。

PATIO 2007/07/05(木) 11:44:40
すいません、みけさんの方法でメモリの消費は抑えられました。
肝心の所を書き換えていなかったのでちゃんと動いていませんでした。
とりあえず、実際の出力に掛かる時間も確認しつつ、
調査を続けて見て、また結果を書き込みますね。

ITO 2007/07/05(木) 13:13:15
うーん
850万件のデータを1つのデータベースで検索するのはやはり時間が掛かると思います。
LANで繋がっているのであれば2つ以上に分散させることは出来ないのでしょうか。
使用頻度の高いデータを選んで新しいサーバに移動するとか出来ないですか?

PATIO 2007/07/05(木) 17:07:38
>850万件のデータを1つのデータベースで検索するのはやはり時間が掛かると思います。
>LANで繋がっているのであれば2つ以上に分散させることは出来ないのでしょうか。
>使用頻度の高いデータを選んで新しいサーバに移動するとか出来ないですか?

実はサーバーもクライアントも同一マシンだったりします。
やっている事はとある製品に使うデータの加工だったりするんですが、
二つもマシンを用意してやるような作業でもないので単一マシンでやっているんです。
なので、サーバーを分割するような事は多分無いと思います。
バッチ処理ですのでリアルタイム性は求めませんが、三日も掛かるのはあんまりでは
と言う話で私自身もそう思ったので色々と手を考えていたのです。

その後の実験でみけさんの方法でもBanさんの方法でも1.7日くらいには
処理時間を縮められそうです。
最初の状況では下手をすると三日以上掛かりそうだったのでかなり短縮できたのでは
と考えています。
とは言え、実際に流している最中ですので最終的にどのくらい掛かるのかは
最後まで流してみないと断言できませんね。
実際に処理が終わってから実測値ついてはお知らせしようと思います。
解決のチェックはそのときに付ける事にします。

PATIO 2007/07/10(火) 12:00:29
[[解決]]
最終的な実測が出来ましたので報告して終わりにしたいと思います。

結局、Banさんから提案があったSELECT文で絞り込む事により
一回のヒット件数を抑える事で全体の処理時間を短縮する事ができました。
最終的には3日以上掛かっていた処理が1.6日程度で終わるようになりました。
Banさんの指摘にあったようにMySQL内で管理している結果の総数が多すぎて
処理時間が余計に掛かっていたのではないかと思います。

たくさんの助言有難うございました。

古いスレッドには追加発言できません。