Q&A
OSは何か？DBMSに何を使おうとしているのか？ system 関数で呼び出そうとしているプログラムは何か？を追記してもらえるでしょうか。また、直接データベースのAPIを呼び出さずに一旦SQL文をファイルに書き出す理由もあれば書いてください。
DBに書き込むSQL文を全てファイル化する処理を書き終えました。
生成したSQL文が記載されたファイルを別プログラムで読み込ませて、
DBに書き込む処理を書こうと思っています。
1. system関数で単純に呼び出す 引数 - Fileのパス - DBのIP - DBポート - DBネーム - DBUser - DBPassword 2. 特定ディレクトリを監視するプログラム n分周期でディレクトリ一覧を取得し、 引っかかるものがあればファイルを読み込み、 iniファイルに記載されているDB情報に合わせてDBへ書き込む。
という2通りのどちらかを考えているのですが、
それぞれ懸念があります。
1はDB書き込み処理中に呼びだされた場合の挙動。
2はFile書き込み処理中に呼びだされた場合の挙動。
現実的なのは1かなと思うのですが、
マルチスレッドではなく、
順次処理にしたいと考えております。
その場合、どういった処理(ロジック)になるのかよくつかめていません。
経験がないもので作り終えてからダメということにならないよう質問させて頂きました。
似たようなプログラム経験がある方等、
もっと良い方法があるよ！という方、
こうすればいいよ。
などなどご教授頂ければと思います。
追記
OS: CentOS6.7
DB: PostgreSQL9.3
失礼致しました。質問を修正しました。呼び出そうとしているプログラムは題材となっていますが、どういう作りにするか決まっていないのでまだ存在しません。また、一旦ファイルに書き出す理由は過去に質問させていただいておりますので宜しければご覧頂ければと思います。
回答1件
0
ベストアンサー
ちょっと近いものを開発した経験がありますが、まず、前の質問を解決するほうが先決かと思われます。
前回の質問： C++にてSQL発行部分をファイル出力に変更する際の最適化
で、
InsertやUpdate時にWhere文等で条件を追加しているため、 COPYコマンドは使用できません
とおっしゃってますが、実装されようとしているアプリの性能要件から、 COPY でのインポートが必須であると思われます。
それで、生データのテーブルと完成テーブルを分けてはどうでしょうか。分ける前提として、
- PostgreSQL はデータを一括して処理するほうが1件ずつ処理するより数倍早い
- 一括処理する塊は大きければ大きいほど有利である
というのがあります。つまり、COPY とか INSERT SELECT は普通の INSERT 文を実行するより数倍早いはずです。正確な数値で示すことができませんが、大量データ取込のいろいろ PostgreSQL編によると、数十倍の差があります。
複数回 INSERT VS INSERT SELECT のほうは、ちょうどいいのがぱっと見つけられなかったのですが、カーソルのループでINSERT100万とSELECT INSERT100万の速度比較してみたに ORACLE で10倍以上差がついている記事がありました。
具体的には、テーブルへの登録処理を2段のパイプラインに分けます。1段目の処理でデータを加工処理せずに受信したままの形でデータベースに登録します。この登録処理では速度を最大にするために TSV ファイル形式からのCOPY命令を利用します。今回の質問の 1. の考え方に近いです。
- データを受信したプログラムで TSVファイルを生成
- 生成したファイルから COPY で生データテーブルにインポートする
このとき、後の処理のために生データテーブルにはシリアルデータ型で通番をふっておきます。また、インポートを高速化するため、生データテーブルにはインデックスをはらないほうが良いでしょう。
2段目の処理で、生テーブルに登録されているデータを完成形テーブルに転記します。今回の質問の2.の考え方に近いです。
- 一定時間ごとのバッチ処理で生データテーブルの内容から完成形テーブルに INSERT 文で転記する（WHERE, JOIN,GROUP BY などの加工処理はこの転記時に実行する:参考 表をSELECTして別の表へINSERTする)
- 転記時に生テーブルどこまで転記したかを別テーブルに記録しておく（前述の通番を参照）
- 転記が終わった範囲のデータを生データテーブルから削除する（性能を上げるためにここをテーブルパーティショニングを使って DELETE ではなく DROP で掃除するようにする必要があります）
で、上記の設計が可能であれば、今回の質問については、 TSV ファイルで生データを出力し、C++ のAPIで COPY 文を実行するというのが最適解になるかと思います。
この方法ですと、1段目の処理と2段めの処理は同期する必要がありませんので、以下のメリットが得られると思います。
- 生データテーブルの読み込みのほうはかなりの性能が出ますので、取りこぼしの心配の方がなくなる
- 完成形テーブルへの転記の INSERT 文の性能がボトルネックになってくると思いますが、受信スループットにピークがあるような場合、ピーク時に遅れが出ても後で暇になったときに追いつかせることができます（単純に一定時間ごとに一定量処理するのではなく、遅れが出ている場合は処理量が増やすようにロジックを組む必要があります）
あと、ステマみたいで恐縮ですが、AWS Redshiftはこういう要件に強いです。
投稿2016/11/15 09:46
編集2016/11/16 02:41総合スコア3401
ご回答ありがとうございます。
すみません。
解釈はあっているでしょうか。
1. 今回DB書込からファイル出力に変更したものではなく、宛先を生テーブルに変更し書込を行う。
2. 一定量に達したら、生テーブルから本来のテーブルに移行する。(INSERT SELECT)
3. 移行後、1で使用したテーブルをDROPし、新たなテーブルを作成する。
4. 1に戻る。
上記解釈の場合、
> 今回出力したSQL文を記載したファイルではなく、TSV形式のものを用意する。
がよくわからなく。。
それとも
1. 今回ファイル出力したSQL文が記載されているファイルをTSV形式にフォーマットする。
2. 生データテーブルに対してCOPYを行う。
3. 一定量に達したら、生データから完成形テーブルに移行する。(INSERT SELECT)
4. 移行後、生テーブルをDROPし、新たな生テーブルを作成する。
5. 1に戻る。
でしょうか。
後者の場合、生テーブルに分ける必要はあるのでしょうか。
>あと、ステマみたいで恐縮ですが、AWS Redshiftはこういう要件に強いです。
AWSに移行したいのですが、使用できません。。
わかりにくい説明ですみません。後者です。回答を編集して、テーブルを分ける必要性を解説してみたいと思います。
> 後者の場合、生テーブルに分ける必要はあるのでしょうか。
回答を編集しました。参考URLは ORACLE のものですが、 PostgreSQL でも、ファイルから INSERT 文を読み込むよりも INSERT SELECT で転記するほうが数倍早いはずです。ですので、2段パイプライン化が必要だと思います。
わかりやすい説明ありがとうございます。
COPYは考えたことがあるのですが、
INSERT用にテーブルを作成するという考え方は無かったので良い方法だと思いました。
ですが、
今回の問題としてINSERTだけではなく、
条件により、既存テーブルにUPDATEもしなくてはいけません。
また、
TSV形式にフォーマットできないのも使用できない要因となっております。
現在はテーブル複数の各カラムを紐付けており、
1つのSQLをINSERTしたタイミングで採番(SERIAL)され、
returningで採番されたIDを他テーブルにINSERTして。
みたいなこと(実際はもっと細かいです)を行っております。
あまり褒められたものではないのですが、
今までの私の質問は個人開発ではなく、仕事に当たる質問になります。
研究開発を行っており、「こういうことをして」と次々に依頼が発生し、
その依頼に対して機能を追加するので、どうしてもごちゃごちゃしてしまいます。
※リソースに見合わない為、再設計等は行っていません。
ただ、確かに色々と効率が良さそうだと感じましたので、
どこかで一度再設計することも視野に入れようと思います。
とりあえず欠落の応急処置として、
別プログラムを作成し、system関数で呼び出すようにしようと思います。
時間を割いて頂き、ありがとうございます。
お疲れ様です。まだ、いろいろ制約条件があるのですね。この手の要件に対するコツとしてまとめると、
- パイプライン化で非同期にする→取りこぼし防止、ピークの平準化、モジュール化
- パイプラインのキューにテーブルを使う
というようなことになるでしょうか。似たような案件で、JOIN による合流含めて、3段とか4段のパイプラインで対応したことがあります。あと、それでも性能が苦しいようであれば、完成形テーブルのほうもテーブルパーティショニングすることをご検討ください。インデックスのサイズが小さくなってINSERT の性能が向上します。
がんばってください。
あなたの回答
tips
プレビュー
