C++にてSQL発行部分をファイル出力に変更する際の最適化

Question

###前提
現在、
C++を使用してサービスを作成し、
対象機器よりXMLデータを受信して、
整形後にPostgreSQLへ書き込む処理になっています。
収集は1分サイクルとなっており、
都度最新データを受信します。
※XMLデータ受信 -> DB書込 と一連の流れは同期処理となっています。

ですが、
DBの負荷がネックになっており、
DB書込時に時間がかかり応答が帰ってくるまでに
周期である1分を過ぎてしまい欠落が発生しています。

そこで、
DB書込部分を指定ディレクトリにファイル出力するようにして、
別サービスを作成し、別サービスでファイル読込後にDBへ書込む処理に変更したいと考えています。

SQL形式に整形したテキストデータだけでも、
1日で約2GBぐらいの量になります。

データ量が多すぎるので、
30分毎に区切ったファイルでDB書込を行いたいと考えています。

質問内容

① C++にてファイル書込と読込部分の最適な書き方。(fopen等)

Insertの場合、valuesに纏めて書きたいのですが、
とりあえず段階を踏んで1文1文で書き込もうとしています。
また、デカイSQL文で5万文字ぐらいになります。
※トランザクション

ファイルの1行の最大文字数は限られているのでしょうか。
また、SQLの1文ずつ取り出せるでしょうか。

② ファイル形式について
.txtで考えているので、タイプ分けが難しいと思い、下記の形にパーティショニングしようと考えています。
速さを重視していますが、できればjson形式みたいにキーを持ち、1ファイルで完結したいとも考えています。

[DIR]20160101
  - [DIR]part1
    - [FILE]aaa.txt
    - [FILE]bbb.txt
    - [FILE]ccc.txt

  - [DIR]part2
    - ...

長文になりましたが、
上記で問題が解決するのではないかと考えていますが、
他にいい案や指摘等ございましたらご教授頂ければと思います。

###2016/11/09 13:17追記
InsertやUpdate時にWhere文等で条件を追加しているため、
COPYコマンドは使用できません。。

Answer 1

こんにちは。

DB書込時に時間がかかり応答が帰ってくるまでに

周期である1分を過ぎてしまい欠落が発生しています。
そこで、
DB書込部分を指定ディレクトリにファイル出力するようにして、
別サービスを作成し、別サービスでファイル読込後にDBへ書込む処理に変更したいと考えています。

別サービスではまとめてインポートさせるなどして高速化されるのでしょうか？
「とりあえず段階を踏んで1文1文で書き込もうとしています。」では、また同じ問題が起きそうにも感じられます。
PostgreSQLは使ったことありませんが、MySQLではインポート方法で速度が極端に（体感で数10倍）違いました。

まとめ処理して高速化する場合は、最初からまとめ処理可能なフォーマットでファイルへ出力しておいた方が好ましいと思います。この場合あまり選択肢はないかと。

---

しかし、高々50KBytes程度のレコードの追加に1分以上かかるというのはびっくりですね。
私はDBには詳しくないので分かりませんが、そんなにDBの性能って酷いのですか？
もっと高速な追加方法があるのではないでしょうか？

Comments

[mukkun]

ご回答ありがとうございます。
記載はしていませんでしたが、
私もDBに対してCopyコマンドなどでImportするほうが高速であると考えています。

> しかし、高々50KBytes程度のレコードの追加に1分以上かかるというのはびっくりですね。
他にも多数サービス等が動いており、1つのサーバで複数のアプリケーションをまかなっている為、ディスクI/Oによるオーバーヘッドも原因の一つかなと考えています。

環境が変えられればもう少しマシになるとは思うのですが。。

[Chironian]

HDDの性能は凄いですよ。ディスクI/Oが原因で50KBytes/minの性能がでないとしたら、無茶しすぎていると思います。
もしも、本当にディスクI/Oがボトルネックなら、ディスク書き込み用サービスを分けても全く解決にならないどころかディスクI/Oを増やしているので性能は劣化します。

ところで、数年前、Windowsですが10スレッド以上で画像やテキスト・ファイルを同時に書き込んでました。その時は、数10MBytes/秒程度の性能が安定して出てました。DBは使ってませんでしたけど。
Windowsに比べてlinuxのディクス性能が悲惨と言う話も聞いたことはないので、本当にディスクI/Oが原因で50KBytes/分が出ないのであれば、性能差が約6万倍あるので単純計算すると60万近いスレッドが並行して走っていることになります。
色々差っ引いても1万個くらいサービスが走ってないと説明付かないような気がします。そこまで詰め込んではいないのでは？

単純にDBの性能を出せていないだけのように感じます。

[mukkun]

ご返信有難うございます。
ためになります。

そうですね。
あとはInsertとUpdate時にXMLデータに加えて、
ものによるのですが、
5つのテーブルを結合した結果を書き込む処理をおこなっているので、
そちらもネックになっているのやもしれません。

テーブル設計はしっかり行っているので、問題はないとおもっているのですが。。

チューニングなどは行っているのですが、
インフラ知識/経験が乏しいので最適化されているDBやサービスをみたことないというのも問題なのかもしれません。

最近DBに振り回されています。。

[Chironian]

なるほど。DBで性能を出すことって意外に難しいのですね。
私はDBの方は詳しくないのでこの辺で。頑張って下さい。

[mukkun]

ご返信有り難うございます。

長々とありがとうございました！

Answer 2

INSERT文のファイルをDBに読み込ませると時間が掛かるケースの場合、COPYにて取り込めるようなTSV形式(タブセパレート）のファイルにより速度改善が見込めます。

copy hogetable from '/tmp/hoge_file.csv';

Comments

[mukkun]

ご回答ありがとうございます。

COPYコマンドの存在は知っていたのですが、
トランザクションを使用する場合と大分違うものでしょうか。
もちろんレコード数にもよると思いますが。。

[mukkun]

すみません。追記になります。

PostgreSQLにてトリガー関数を使用しており、
特定のテーブルにInsertまたはUpdateされたタイミングで発動させるようにしているのですが、
COPYコマンドでも発生できるようにできるのでしょうか。
もしくはCOPYコマンドでも内部的にはInsertと同様になるのでしょうか。。

[A.Ichi]

トランザクションとしても行えはずです。
外部ファイルに書き出して取込を起動する様な方式でしょうか？
copyでもインサートと同様な処理が行われますのでトリガも大丈夫です。

[mukkun]

そうなのですね。ありがとうございます。
質問内容にも追記させて頂きましたが、
InsertやUpdate時に条件式やテーブル結合を行っている為、COPYコマンドはやはり使えなさそうです。。

教えて頂いたのに申し訳ございません。

[A.Ichi]

begin;
copy hoge .....;
rollback;
が出来ましたのでトランザクションと言う意味では可能かと思います。

[A.Ichi]

以前、Cプログラムの中でスレッド(１００程度）を立上げて（それそれでコネクト）SQLを実行する方法で対応した事は有ります。

[mukkun]

ご返信有難うございます。
非同期化も考えたのですが、
サービスを動かしているサーバがチンケなものでしてそのまま落ちるのではと懸念しております。。

難しいです。。

[A.Ichi]

いろいろと検討さなっておられるんですね、ファイルに５万文字のSQL、ですがどのようなSQL文かは分かりませんが、DUMPファイルの中身もSQL文である事を考えると、大きさ的には問題は無いかと考えます。一文で、1GB が上限と聞いた事は有ります。
ファイルからの読込みでもサーバの能力にも寄りますが、SQL文はロット化してconnectの回数やコッミットの回数を減らした方が速度が出ます。

[mukkun]

ご返信有り難うございます。

そうなのですね。
色々考えた結果ですが、
纏めて入れられるものはトランザクションでコミットする方針に決めました。

長々とありがとうございました。

Answer 3

① C++にてファイル書込と読込部分の最適な書き方。(fopen等)
...
ファイルの1行の最大文字数は限られているのでしょうか。
また、SQLの1文ずつ取り出せるでしょうか。

SQL文に改行が含まれない限り istream& getline (istream& is, string& str); で、改行を区切りに読み込めます。メモリの許す限りどんなに長くても。

② ファイル形式について
...

...それで?

Comments

[mukkun]

ご回答ありがとうございます。
そういった関数も用意されているのですね。

ファイル形式については他に良い物があればご教授頂ければという意味合いでした。
申し訳ございません。