同じジョブ、2つのエンジン: InnoDBとIcebergの内部構造

私の株価分析リポジトリの最初のバージョンは、OLTPエンジンで分析を実行し、2番目のバージョンは同じことをOLAPスタックで実行しました。 単一の最適化のためにOLAPスタックを選択しましたが、それを活用するためにはレイクハウスのすべてを理解する必要がありました。 OLTP InnoDBの3つの保証 行はすべての列をまとめて格納します。 1つのシンボルに対して1つのバーを取得すると、ディスク上の単一の場所に触れることになり、これはまさにインジェストが望むものです。 2つの列を必要とするインジケーターでさえ、バッファプールを通してすべての列を取得します。 インデックスは読み取りを高速化し、書き込みを遅くします。 すべてのインデックスは独自のBツリーであり、すべての書き込みはテーブル内のすべてのBツリーを更新する必要があります。 Bツリーは読み取りが非常に高速です。 あらゆるインデックスの目標は、書き込みを遅くするコストで読み取りを高速化することです。 選択的な読み取りには優れていますが、書き込み負荷の高いインジェスト作業には全く向きません。 これが、私のRaw*テーブルにプライマリキーのみを持ち、Raw*の作業を受け取るテーブルには重いインデックスとパーティショニングを施した理由です。 Undoログは同時読み書きを可能にします。 行が更新または削除されると、古い値はundoログと呼ばれるものに格納されます。 セッションが書き込み中にテーブルを読み取る場合、古い値はundoログから取得されます。 挿入にもより小さなundoログがあり、テーブルと挿入されるプライマリキーが格納されるため、InnoDBは挿入がコミットされなかった場合に元に戻す方法を知っています。 コミット時に、挿入のundoログはすぐにパージされます。 更新または削除のundoログは、ヒストリーリストと呼ばれるグローバルリストに参加し、セッションが変更されたデータの古いバージョンを読み取れるようにします。 更新と削除が多いデータベースでは、ヒストリーリストは急速に成長し、対処するのが悪夢となります。 幸いなことに、私よりも賢い誰かが解決策を構築し、あなたが正しく推測したように、それはInnoDBに組み込まれています。 InnoDBは、コミット前にまたはコミット中に開始されたすべてのセッションが完了するまでundoログをパージするようにフラグを付けます。 このシステムは、Multi-Version Concurrency Control、略してMVCCと呼ばれます。 すべての技術的な専門用語の後、英語での翻訳は、MVCCにより挿入と更新が読み取りをブロックすることなく行えるということです。 知らず知らずのうちに、私はOLAP設計の基本原則を使用してInnoDBデータベースを設計していました。 financialdataはdatetime、open、high、low、closeを保持していました。 当初はdatetimeを保持する唯一のテーブルでした。 クエリしたい技術的インジケーターはすべて、インジケーターの行が計算された日付を知るためにfinancialdataテーブルへの結合を必要としました。 これは私の最悪のデータ設計の1つでした。 データベースが100GBを超えた後にのみ気づいた設計です。 これを修正するのは過酷でした。 CREATE TABLE `FinancialData` ( `FinancialDataID` int NOT NULL AUTO_INCREMENT, `StockID` int NOT NULL, `StockDate` datetime DEFAULT NULL, `Open` decimal(20,5) DEFAULT NULL, `Low` decimal(20,5) DEFAULT NULL, `High` decimal(20,5) DEFAULT NULL, `Close` decimal(20,5) DEFAULT NULL, `Volume` decimal(20,5) DEFAULT NULL, `EMA` decimal(20,5) DEFAULT NULL, `VWAP` decimal(20,5) DEFAULT NULL, `RateOfChange` decimal(20,5) DEFAULT NULL, `OnBalanceVolume` decimal(20,5) DEFAULT NULL, PRIMARY KEY (`FinancialDataID`,`StockID`), UNIQUE KEY `StockID_Date_Idx` (`StockID`,`StockDate`), KEY `idx3` (`StockID`,`FinancialDataID`,`StockDate`,`Close`) ) ENGINE=InnoDB AUTO_INCREMENT=320075156 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY HASH (`StockID`) PARTITIONS 100 */; 私のfinancialdataテーブルパーティションを担当するファイルの写真です。 各.ibdはInnoDBテーブルスペースファイルで、テーブルデータの約1/100を含んでいます。 ファイル範囲: FinancialData#p#p0.ibd - FinancialData#p#p99.ibd。 公平を期すために言うと、設計のアイデアはしっかりしていました。 financialdataには、上記のスキーマからわかるように、datetime、open、close、low、high、StockID、そしてかなりの数の他の列が含まれていました。 このデータベースを始めたとき、私はこれらの列をすべてのクエリで必要とすると考えていました。 このデータベースを始めたとき、私は最も重要な価格が終値であることさえ知りませんでした。 言っておきますが、私は長い道のりを歩んできました。ウォール・ストリート・ジャーナルさえ読むようになりました。そして、それが何を意味するかは皆知っていると思います。 とにかく、この設計は私のすべてのインジケーターテーブルにFinancialDataIDとStockIDが含まれていることを意味し、これらは結合のための効率的な列として機能しました。 financialdataをベーステーブルとして使用し、作業中のインジケーターに基づいてどの列を選択するかというアイデアは、テーブル内の他の列を無視して、必要な列のみをクエリすることと非常に似ているように聞こえます。 ついに決心してStockDateをインジケーターテーブルに追加したとき、私はOLE信頼できるもの、つまり複数の異なるスケジュールされたイベントによって実行されるストアドプロシージャを使用しました。 各スケジュールされたイベントは、ストアドプロシージャを個別のテーブルで実行したため、ロックの問題はありませんでしたが、待機が問題でした。 CREATE TEMPORARY TABLE IF NOT EXISTS t ( StockDate DATETIME, FinancialDataID INT PRIMARY KEY ); INSERT INTO t(StockDate, FinancialDataID) SELECT StockDate, FinancialDataID FROM FinancialData WHERE StockID = iStockID; UPDATE sar sr INNER JOIN t on sr.FinancialDataID = t.FinancialDataID SET sr.StockDate = t.StockDate; UPDATE macd md INNER JOIN t on md.FinancialDataID = t.FinancialDataID SET md.StockDate = t.StockDate; UPDATE directionalmovement dm INNER JOIN t on dm.FinancialDataID = t.FinancialDataID SET dm.StockDate = t.StockDate; UPDATE boilerband bb INNER JOIN t on bb.FinancialDataID = t.FinancialDataID SET bb.StockDate = t.StockDate; UPDATE chaikinoscillator co INNER JOIN t on co.FinancialDataID = t.FinancialDataID SET co.StockDate = t.StockDate; DROP TEMPORARY TABLE IF EXISTS t; 各スケジュールされたイベントは、これらの更新の1つを実行し、他の更新はコメントアウトされていました。 v1の5年後、私の分析作業はテーブルごとに2〜3列を使用していたが、すべての列を読み取ることを余儀なくされていることに気づきました。 分析作業の最適化は理論的には市場で優位性をもたらす可能性があるため、それに最適化することにしました。 調査の結果、エンジンの選択が間違っていたという結論に至りました。 OLAP InnoDBのセクションが技術的だと思ったなら、ああ、あなたはひどい目にあうでしょう。 バージョン2のアナリティカルテーブルは、MinIO上のIceberg、Polarisカタログの後ろにあるParquetファイルで、Sparkが読み取りを処理しています。 パーティショニングの話全体は、DDLの1つの句であり、ここから取得したものです。 CREATE TABLE IF NOT EXISTS raw_bars ( `symbol` string, `time_stamp` timestamp, `open` double, `high` double, `low` double, `close` double, `volume` bigint, `vwap` double, `trade_count` bigint, `is_valid` boolean ) USING iceberg PARTITIONED BY (bucket(16,`symbol`)); Icebergで作業する贅沢の1つは、メタデータにパーティションを計算して格納することです。 これは、パーティションに列を追加したい場合、読み取り時にその代償を払う必要があることを意味します。 データはパーティションバージョンごとにグループ化され、バージョンごとに独立してプルーニングされ、その後プルーニングがマージされます。 このステップはスプリットプランニングと呼ばれます。 v1でテーブルをパーティション化するには数日から1週間かかりました。v1で作業している間はたくさんのコーヒー休憩があり、パーティションを変更したい場合は、テーブル全体を再パーティション化する必要がありました。だからこそ、通常のサイズよりも大きいハッシュ値100を選択したのです。 約13,000のStockIDを130のStockIDのグループに分割すると、再パーティション化が必要になるまで数年かかることになります。 読み取りパス 私のレイクハウスのクエリオプティマイザーを理解するには約1か月かかりました。なぜなら、それは比較的複雑なトピックであり、さらに、私のOLTPのバックグラウンドに関連付けることができなかったアイデアの1つだったからです。 技術的な魔法のいくつかをネタバレしていますが、マニフェストとデータファイルの違いを理解するのに苦労しました。 物事の仕組みにおいて、これらの2つの定義は比較的簡単な側面にありますが、データが複数の異なるレイヤーでグループ化されていることを把握できませんでした。 マニフェストとデータファイルを理解することが、私の「アハ体験」でした。 通常のプログラムに戻ると、レイクハウスは3つの部分で構成されています。Apache Polaris、MinIO、Apache Icebergです。 Polarisは私のカタログとして機能します。私にとっては、Postgresデータベースとそれに読み書きするためのAPIです。 クエリ最適化の観点から、Postgresデータベースの主な応答