【MariaDB】約5億レコードを登録できるのでしょうか？

Question

概要

非常に件数の多いデータをデータベースにINSERTする必要がありまして、
処理に膨大な時間がかかりそうなので、途中失敗を避けるためありえそうなリスクをあらかじめ知っておきたいと考えています。
もし何か知見をお持ちの方がいらっしゃいましたら、アドバイスいただけると助かります。

要件

使用するハードとソフトの要件は以下の通りです。
PC：amazon RDSのdb.m7g.large（AWS Graviton3 プロセッサ、メモリ8GB、ストレージ1TB）
OS：Amazon Linux2
データベース：MariaDB10.6

登録するデータ：１レコード当たり1kB程度のデータを約5億(500M)件分
付与するインデックス：auto incrementするIDと、別に登録するIDの　計２つ

具体的に質問したいこと

• そもそもPCのスペックは足りそうでしょうか？

まず、データの合計サイズは1kB x 500M件 なので、少なくとも500GBのストレージは必要になるはずです（インデックスも含めるともっとかも）。
一方、メモリの方は実際どの程度必要かよくわかりません。（処理の途中でメモリ上限を超えたりすると困ります。）
他にスペックで問題のありそうなところがあれば教えてほしいです。

　

• ありがちな設定ミスがあれば教えてください。

例えば、データベースの隠れた上限閾値があるとか、何か特別な設定が必要とか、
ビッグデータをデータベースに登録するにあたり、何か気を付ける点があれば教えてほしいです。

Answer 1

データ挿入時の懸念・ご質問のようなので、挿入後のクエリー等については触れません。

私は毎日１～2億ぐらいのレコード量をmysqlに挿入/集計していますが（RDSではない）、インデックスはいったん外し、後からつけたほうが良いと思います。

またRDSを利用ということですが、IOPS（ディスクの秒間入出力数）の上限が決まっています。
INSERTやインデックスの生成でディスク書込みが一時的に多くなり、IOPS上限を超えてクレジットバランスを使い切ると読み書きの性能がほとんど出なくなります。
一度きりの挿入で、挿入処理中は他のクエリー性能が出なくても良ければ問題ないですが、問題であれば（その後のクエリーでの利用も要考慮）容量やストレージタイプ（gp3やプロビジョンド）を検討された方が良いと思います。
gp2で1TBを確保した場合は3000IOPSあたりになると思うので大丈夫かもしれません。

Comments

[hatarson]

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

> インデックスはいったん外し、後からつけたほうが良いと思います。
この情報は助かります。
一括でデータがあるなら、途中で毎回実施するより後で一括でIndex付与を実施したほうが効率が良いということでよね。

まったく考慮していなかったため、助かります。
Indexの有無でストレージの減り具合も確認できそうです。

なるほどIOPSも注意が必要ですね。以下の情報も非常に参考になります
> gp2で1TBを確保した場合は3000IOPSあたりになると思うので大丈夫かもしれません。

Answer 2

５億件を一度に処理する事は現実的ではありません。
一番の理由は、メモリなどのリソースの問題です。
一時的な処理（注）の為だけに、リソースの追加を考える事はせず、現状のリソースの範囲で行えるように考えるべきです。
注：データ追加による、ストレージ容量や照会に必要なメモリの増設などは含みません

上記を踏まえ考慮するべきことは、処理トランザクションの分割です。
リソースに対して負荷の無い単位に分割します。
５億件に対して、仮に単位を１万件毎とした場合、分割されるトランザクションは５万個となります。
※効率を考えると分割単位は１万～２万件程度を最初の目安にするのが良いのではないかと思います。

尚、トランザクションを分割すると、並列実行によって直列実行より完了までの時間が短縮できます。
どの程度の並列動作が効率的かはOSやCPUに依存しますので、計測の上決定するのが良いでしょう。
※CPUの負荷が８０～９０％程度になるのを目安と考えれば良いかと思います

また、（エラーや検証による）処理中断と再開については当然考慮すべきです。
併せて、進捗状況も分かるようにしておくのが良いでしょう。
あと少しで完了という所で失敗して、最初からやり直しとなったら、ダメージは計り知れません。

Comments

[hatarson]

ご回答ありがとうございます。
普通にループを回すと数日くらいかかりそうなので、効率的に動作させる方法も考えていただき助かります。

> また、（エラーや検証による）処理中断と再開については当然考慮すべきです。
> 併せて、進捗状況も分かるようにしておくのが良いでしょう。

そうですよね。「処理中断と再開」は十分考えておこうと思います。

Answer 3

もう少し大量のデータを扱ったことがありますが、あらゆる事象にあてはまる情報提示はできないため、参考情報にしていただければ幸いです。

CPU・メモリについて

データを登録するだけで、処理時間を気にしなければご提示のCPU・メモリでも処理は完了すると思います。
ただし、並列でINSERTしたり、INSERT中に他の処理を実施しないことが前提になります。

ストレージについて

ストレージは少しこころもとないです。ご想像の通り付帯する管理情報などもあるため、余裕を持って2TBは用意された方が良いかと思います。
もしギリギリで投入が成功したとしても、その後の運用でDiskfullになってしまったら意味がないと考えるからです。RDSなので後から運用回避もできますが、投入に時間がかかることを考えると、なるべく余裕を持たたほうがいいと思います。

indexについて

些細かもしれませんが、indexについてはINSERT速度は上げるためにPK以外は投入後につけたほうが良いと思います。

その他気になる点

ちなみに、スペックについてはその後の運用でどのくらいの負荷を想定されているかの方が気になります。

「利用者が数人で、1日数クエリ程度」など負荷がかからない条件であれば問題ないと思いますが、並列での検索処理やレコードの削除・更新などが頻繁におきる場合には、CPU・メモリが不安になるため、もう少し上のスペックが必要になると思います。

Comments

[hatarson]

ご回答ありがとうございます。
具体的で丁寧な解説、非常に助かりました。

途中ストレージがパンクしては最悪なので、やはり余裕を見て2Tは必要ですね。
Indexのアドバイスも、考えが及ばなかったところだったので助かります。
運用についてのアドバイスも参考になりました。

Answer 4

RDBへの投入が前提になっていますが、ビッグデータならNoSQLの方が最適かもしれないので仕様をもっと詰めたほうがよいでしょう。かりにRDBが正解だとしても運用開始後のデータの追加・削除、検索・集計の方法、頻度などによってテーブルの設計も変わってくると思います

Comments

[hatarson]

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

NoSQLの選択肢は少しだけ考えていました。
恥ずかしながら、使用経験がないためなるべくRDSでできないかと思っていましたが、この機会にNoSQLを勉強すべきだと思いなおしました。

Answer 5

実際にやってみるのが良いです。

アプローチ1

私であれば、まず、bulk insertを使います。
1万件を1文で挿入するようにして、1000万件ぐらいinsertしてみます。

このSQLを生成するプログラミング言語によってもパフォーマンスが変わってくると思うので実測しないと結論を出せないと思います。

その際に、1回あたりの実行時間を計測しておきます。そうすれば、5億件やったときにどのくらの時間がかかるかを予測できると思います。

同時に、CPUやメモリのリソース状況も計測しておくと予測できます。

アプローチ2

アプローチ1だと時間がかかりすぎたりする場合はLOAD DATAを使います。

こっちのほうが楽だと思います。
https://dev.mysql.com/doc/refman/8.0/ja/load-data.html

アプローチ3

RDSとのことなので、AWS Database Migration Serviceを検討します。
フルマネージドですので速い気はします。

https://docs.aws.amazon.com/ja_jp/dms/latest/userguide/CHAP_Target.S3.html

Comments

[hatarson]

ご回答ありがとうございます。
> 実際にやってみるのが良いです。
> 私であれば、まず、bulk insertを使います。

ぜひ試してみたいと思います。1千万なら恐らく数時間で終わると思うので、比較的簡単に良いデータが取れそうです。

アプローチ2とアプローチ3のアドバイスも、参考になります。
INSERT前のデータはS3に置くつもりだったので、そこからマイグレーションできるなら楽ができそうです。