Q&A
やりたいこととか問題点とかを書いてるのは良いんだけど、肝心の質問は何なのコレ？
### 質問内容の目標
・１日毎に100億件を超えるデータの更新を行う
・大量のデータを高速に取り扱う方法
### 詳細
現在、Twitterアカウントのフォロワー情報を管理するシステムを構築しています。
アカウントの総数は約100万件で、それぞれのアカウントにフォロワーが平均１万人います。
アカウントのテーブルをuser_tableとし
フォロワー状態を格納するテーブルをstatus_tableとした場合
以下のような構造で構築をしています。
| user_table | status_table |
|---|---|
| id | user_id |
| follower_id |
user_tableはその他名前等のデータが格納され、idはprimaryな状態です。
status_tableではuser_idの重複が可能となっており、フォロワーアカウントIDを１つずつ格納する形でinsertしています。
目的としては、前日のフォロワーと当日のフォロワーを比較し、新規もしくは抜けたフォロワーを識別する事が目的となります。
現状の対応方法
現時点で試している方法としましては
status_tableとまったく同じ内容のテーブルをもう一つ作成しています（tmp_status_table）
tmp_status_tableに当日のフォロワー状態を格納し、status_tableには前日のデータを格納する事で
集計を行う際に、前日と当日のテーブルを比較する事で対応を考えています。
(比較の際はWhere文でuser_idを指定しまとめて取得※効率悪いです・・)
問題点について
アカウント100万件につきフォロワー数平均１万なので、status_tableではおよそ100億件のデータが格納されており
tmp_status_tableにも約100億件が格納されています
問題として、集計を確認し終えた際に、前日のデータを全て削除しなくてはならず
当日のデータをtmp_status_tableへ移行した後に当日のデータも全て削除する必要が有る為
かなりの負荷（200億件のDeleteと100億件のinsert）がかかる事となる為、現実的な速度は見込めないとゆう点が問題として有ります。
打開策案
１：BigQuery
処理速度が速く、分析に特化しているとゆう点でデータベースを利用するより効率が良いと思いましたが
サーバーと通信を取りたく、ネットワーク処理での不可が大きくなる為妥協
２：パーティション
アカウントID毎にパーティションを作成する事でdelete処理をパーティション単位で行う事が可能と考えていますが
アカウントによっては途中で凍結が行われ、削除されるアカウントが存在し、頻繁に変動をする予定なので
動的にパーティションを追加、削除をするのは管理として難しいのではないかとゆう考えです。
###追記
いくつものご回答いただきありがとうございます。
一度自分で回答内容をそれぞれ試してみたいので、質問欄は少し期間を開けてOpenのままにさせていただきます。
後に回答していただける方のご意見も全て読ませていただきます。
ベストアンサーについては、ある程度自分で触った後に私と同じような質問者の方のご意見もふまえて選定させていただく予定です。
皆さん貴重なご意見ありがとうございます。m(__)m
大量のデータを高速に取り扱う方法について質問させていただいたつもりです。
わかりにくくてすいません、追記します。
ネットでは１憶件や100万件のデータ管理方法は結構のっているのですが、100億件以上の情報が載っているサイトが無かったので質問させていただきます
ここで質問することではなく、もはやお金が必要な、エンタープライズ、コンサル案件な気もしますね。
Twitterからそれだけの情報を取得する、という部分での問題はありませんでしょうか？
＞＞Twitterからそれだけの情報を取得する、という部分での問題はありませんでしょうか？
こちらについては対応が済んでいる為問題有りません。
いや「問題があります」とか「難しいのではないか」とか言ってるだけで、これはあなたの意見の主張にしかなっておらず、全く意図が伝わるものではないと思うんですが。
質問であれば「○○について知りたいので、教えて下さい」という文章に繋がる具体的な「○○」の内容が記されているべきかと思うんですが。
なお、100億件のデータを日常的に扱うようなシステムというのは一般的な業務システムではありえず、インフラを含めて総合的に検討する必要があるような案件だろうと思いますので、ここで質問するより適切なベンダーに相談された方が良いんじゃないですかね。
なんか「とゆう」という表現を含め、全般的になかなか日本語が怪しいので、業務でやっておられるような方には見えませんけど…。
>こちらについては対応が済んでいる
どのように対応されたのか知らないですが、正しく対応できている(許可を得ている等)であれば、QAという形では解決不可能であることくらい検討つきそうなものですが、差し障りない範囲で、どう対応済みなのか追記いただけますか？
>>gentaro
『100億件のデータを高速に更新する事が出来るデータベースの構築方法について知りたい』といえば伝わりますでしょうか。
ただ、データ構造によっては高速化の方法が異なる為、詳細を記載している限りです。
打開策案を見ていただければ質問の意図がご理解いただけると思います。
>>m.ts10806
今回ご質問している内容はTwitterAPIの利用方法ではなくデータベース上でのデータ管理方法となる為、質問の内容からはずれるのですが、リクエスト回数による制限等につきましてはTwitter社の定めるルールを厳守した状態で利用しており、スクレイピング等によるものでもなく、悪用ではないのでご安心ください。
回答4件
0
おもしろそうなので BigQuery で 100億レコード x 2日分でやってみました。
結論としては、100億レコード程度であれば特に問題なく扱えると思います。
●status_table 相当のファイルを生成 (前日分データ)
ファイルイメージは下記。
user_id,follower_id -------------------- 0,0 0,1 0,2 (略) 0,9999 1,0 1,1 1,2 (略) 999999,9998 999999,9999
●status_table2 データ生成 (当日分) も作成
若干 follower_id をずらして差分がでるようにする。
生成された CSV ファイル は 110GB ×2ファイル。
●BigQuery にロード。
# データセット作成 bq mk bigdataset # ロード bq load bigdataset.status_table status_table.csv user_id:integer,follower_id:integer bq load bigdataset.status_table2 status_table2.csv user_id:integer,follower_id:integer
ロード完了までそれぞれ 20分 (load job で 38秒と出ているので、ローカル→GCS アップロードが 19分22秒、GCS→bq が 38秒なのかもしれない)
これで BigQuery にデータが載ったので、前日とのフォロワーの差分 (減った分、増えた分) を求めるクエリを実行。
-- 前日から減ったフォロワー select '-',* from (select * from myprojectzxcv.bigdataset.status_table except distinct select * from myprojectzxcv.bigdataset.status_table2) union all -- 増えたフォロワー select '+',* from (select * from myprojectzxcv.bigdataset.status_table2 except distinct select * from myprojectzxcv.bigdataset.status_table)
処理時間は75秒。
(結果を CSV 等に落とすなら別途時間はかかる)
●コスト
上記クエリのデータ量は 298GB (integer 8バイト×100億レコード×2テーブル)。クエリコストは 1TB で $5 なので、298GB で $1.44 (上記 SQL を実行するたびに $1.44 かかる)。月30回実行するなら $43/月。
ストレージコストは 298GB*$0.02/月=$5.96/月。
毎日 2日分しか保持しないので $5.96/月 が最終的なストレージコスト。
クエリコスト $43 + ストレージコスト $5.96≒$49/月
実際はもっと付加情報があったとして余裕を見て10倍としても、$490。月5万円ちょっと。
●その他
インスタンス立ち上げたりしていません (インスタンスという概念なし)。
クラスタリング・パーティションなど設定していません。
インデックスも張っていません (インデックスというものはない)。
フルスキャンで 200億レコード 75秒です。
投稿2021/01/26 00:25
総合スコア2022
興味本位からの質問ですが、BigQuery以外で現実的な手法だとどういう手立てが考えられますか？
-追記-
ただの興味なのでスルー頂いても結構です。
ぱっと思いつくのは下記です。なんとなくよさげな順で並べてみました。所見を書きましたが、がっつり使い込んだものはひとつもないので本当かどうかはわかりません。
・Snowflake
一番 bq 的で、なおかつ速い (という噂)。インスタンスサイズ選択はあるが、使わないときは無料なのでそれほど神経質にならなくてもよい。
・Azure Synapse Analytics
bq のように Azure 側でスケールしてくれる。サーバレスタイプもある (インスタンス起動するタイプある)。分散キー等は自分で指定する必要あり?
・Amazon Redshift
自分で立ち上げたインスタンス内での動作なのでサイジングが面倒。CPU とストレージサイズが連動しているので面倒。分散キー等は自分で指定するので面倒。
・Spark
オンメモリでいけるならよいのかも。分散キー等は自分で指定?
・Oracle In-Memory
オンメモリでいけるならよいのかも。高そう。
・Tresure Data
連携は得意だが大規模データ分析は不向き?
本件、user_id をキーに新旧テーブルを見て follower_id の突合するだけなので、bq じゃないとダメ感はあまりない気がしますが、サイジング・設計・管理不要という意味でやはり BigQuery は手軽だなぁと思います。
ありがとうございます。
サラッと紹介と事例見てみたけどSnowflake堅牢そう＆パフォーマンス良さそうですね。
やっぱり今時は自前で分散DB用意するのは愚の骨頂？みたいな。
※分散DB組んだ事ないのでイメージです
Snowflake はトランザクション対応していたり (bq は非対応)、過去データリカバリが最大90日までできたり (bq は7日)、機能的にもよい感じですね。
Oracle や MySQL などは write 1or2、read 複数という意味での分散DB しかできない (と思っています) ので自前構築でもいいんでしょうけど、大量データに対してコンピューティングも分散処理してほしい場合、bq や Snowflake のような選択肢しかないのではと考えます。
あとはもう BigQuery やってると管理がめんどくさくなって…。インスタンス上げ下げしたり、死活監視したり、データ容量や同時接続数監視したり、統計情報がどうこうとか今後一切関わりたくない気分です。
> Oracle や MySQL などは
> bq や Snowflake のような選択肢しかないのではと考えます。
mysqlspiderとかではなく、ErlangVMのMnesiaとか類似のもの（あるのか？）を考えてました。どの程度の規模なら自前分散するorしないみたいな判断基準も経験無い自分には判断つかないんですが、そもそも分散したいなら最初からそういうサービス使った方が良いの？とか気になる事多いです。
oracleはSQL打つ程度にしか使って無いので分かりません。
Mnesia を知らなかったので調べてみましたが、さっぱりわかりませんでした。Mnesia はデータまるごと他のノードでも保持する形であって、コンピューティングの分散ってやっていないのではと思いましたが、よくわかってないのでこれ以上語るのはやめておきますw
0
１日毎に100億件
すでにSQLの範疇ではないように感じます
ビッグデータなのでNoSQL的なアプローチを検討してみてはどうでしょうか？
投稿2020/10/06 04:15
総合スコア116724
0
・１日毎に100億件を超えるデータの更新を行う
・大量のデータを高速に取り扱う方法
この時点でパーティションもほぼ役に立たないでしょう
というのもパーティションは1サーバ辺りに同一データを複数ファイルに分散する事により
読み書きの範囲を狭めマルチタスクにする技術です。
ここまで大量だとパーティション分けしても1サーバ辺りでの1媒体(HDD,SSD)における読み書きの限界を超えそうです。
方法としては
0. 「ロードバランサ」により複数の「受信サーバ」に転送
0. 「受信サーバ」はメモリ上に通信内容を格納し応答を返す。
0. 「受信サーバ」は一定期間(1分等)で通信内容を「DBサーバ」へ追加を行う。
0. 「DBサーバ」自体も複数分散でパーティションも併用する。
0. 「分析用サーバ」は集計などの実行の際(1日単位？)複数DBサーバから情報を集計して処理を行う。
でしょうか？
(1日辺り1億件でこの様なシステムにした経験があります。)
※「受信サーバ」で必要となる情報は「DBサーバ」側から事前取得しメモリに展開する。
投稿2020/10/06 04:13
編集2020/10/06 04:15
総合スコア2506
大変貴重なご意見ありがとうございます。
AWSで構築できそうなのでさっそくやってみます。m(__)m
ネット検索だけではどうしてもデータベース上での話に限定されてしまう為、全体像を把握した高速化は必要とされる方も多いと思います。ご回答いただきありがとうございます。
> AWSで構築できそうなので
先にAWS側に「１日毎に100億件を超えるデータの更新を行う」事を相談されたほうがよいですよ
多分相当な金額の見積もりと期間が発生します。
(予想では10億以上期間半年以上)
ありがとうございます。
料金については、リクエスト件数を慣らしながら調整を行いますので問題ないとは思うのですが、最終的な金額が10憶以上になっても困るので、その辺の対応も込みで構築をしてみます。
私の場合ですが、数十年前1日辺り1億件最大10億件を発注する際、王手開発会社とコンペを行いましたが
最低でも1億以上かかりました。
機器の性能アップや機器単価、人件費の向上、等踏まえると計算上10億ぐらいはかかりますよ？
AWS側も1日辺り100万件程度は想定してはいるでしょうが、質問者様のような場合、別に専用システムを構築する必要があります。（でないと他ユーザに影響がでますから）
その準備と機器購入を考えるとその程度かかるという事です。
ちゃんと質問や確認しておかないと使用自体断られますよ
ロードバランサやメモリの分散については回答程の内容ではありませんがある程度必要とゆう想定でした
経営の兼ね合いを見ながら徐々に機器調整とユーザの誘導を行っていく予定ですので、金銭的な面ではおそらく処理内容の制限か、もしくは利用料金をグレード別にし内部負荷の大きいユーザのみ金額を上げるような形で処理の軽量化を測る想定です。
100億回のリクエスト数とゆうのは、あくまでも考えられる最悪のパターンの数字となります。
個人的な事ですが、実際に１日10億件の処理を必要とされるサービスに携わっておられた方とお話できて光栄です<(_ _)>
100万回と100億回では 大きな開きがあります。
一番悪いパターンですがAWS側に伝えず100万回でスタート。
利用者が増えて徐々に1000万回、1億回と増えた時どこかの時点でAWSがシステム利用に関してストップをかけます。(他のユーザに影響が出るため)多分はじめの契約時点で明記されているはずです。
これは事前契約のなかで先に取り決めないと必ず起こる事で、
外部のシステムを使うとはそういうリスクがあるという事です。
コストを抑えたいのなら自社で開発する事をおすすめします。(外部委託でも構いません。)
また先にも書きましたが金額を払えばすぐに対応できるものでもありません。
その点のご留意も必要かとおもいます。
AWSの請求金額についてはとんでもない請求を受けている方を何度か見たことがあるので、石橋を叩いて壊すぐらい慎重にやっていきます。
100万回を上限の目安に、今後のサーバー管理について考える事にします。運営段階で理解してはお金も時間も失うので、kuma_kumaさんのご意見を構築段階から参考とさせていただきます。
ありがとうございます。
0
大喜利のような質問ですね。
それなりのインフラが用意されており、あくまでDBでやる前提で、最小限にする方法を考えますと、
追加、削除の差分を記録するテーブルがあると処理を少なくできると思います。
フォローを追加したときに、追加したユーザID,追加したユーザのID、時間、をinsert
フォロー解除も同様に別テーブルにinset。
追加ユーザ、解除ユーザも、ユーザIDをキーに簡単に取得できます。
差分テーブルは、日付でパーミッションを切っておけば、削除もある程度高速ですし、
DB設計のアンチパターンですが、
日毎のテーブルを作って不要になったらDROPすれば処理的には負荷もかなり少なくなると思います。
投稿2020/10/15 03:28
総合スコア31
あなたの回答
tips
プレビュー
