🚠 💤 🧘🏽 ユーザーによるデータ変更のログをデータベースに保存し、別のデータベースに保存する方法 🤒 👨🏿‍⚕️ 🏙️

親愛なる読者これは、データベースシリーズの2番目の記事です。私は、このサイクルの予定されている記事の目次を作成することにしました。

同じサーバー上の異なるデータベースに異なるタイムゾーンを作成する方法。
ユーザーによるデータ変更のログをデータベースに保存し、別のデータベースに保存して、メインデータベースがゴミで詰まらないようにし、成長しないようにする方法。
データベースのblobフィールドに基づいてファイルシステムを作成する方法。なぜ便利なのですか。ファイルストレージの効率の問題：最大のパフォーマンスを得ると同時に最小のスペースを確保する方法。

最初の記事のコメントの数に驚いたので、すぐにそれを実装する唯一の正しい方法のふりをしていないことに注意したいと思います。創造的な人々は、このタスクを実現するためのさらに多くの方法を見つけると確信しています。しかし、そのことに気付いたとき、私はそのような機能を説明する単一の記事を見つけられず、このタスクをゼロから実行しなければなりませんでしたが、それは私の意見では関連しています。説明する実装は完全に機能し、実際に使用されています。

建設的な批判も歓迎します。人々が面白いことを書いたり、予期しない角度から問題を調べたり、何らかの形でメカニズムを改善したりすることがあります。

それでは始めましょう。

Firebird 3データベース。

問題のステートメントは次のとおりです。データベース内のユーザーによるデータ変更の詳細なログ（挿入、更新、削除）を書き込む必要がありますが、同時に別のサーバー上の別のデータベースに書き込みます。これは、メインデータベースのサイズが急激に増加しないようにするために必要です。バックアップ、復元して、すぐに動作し、ゴミを蓄積せず、不要でまれな情報が含まれないようにするのが便利です。

このメカニズムはエンタープライズオートメーション用のクラウドプラットフォーム（ https://erp-platforma.com ）向けに開発されましたが、通信会社の請求部門で働いているときに初めてこの問題に遭遇しました。すべてのシステムが機能する課金データベース（メインデータベースと呼びましょう）と、顧客の個人アカウント用のデータベース（LCデータベースと呼びましょう）があり、メインデータベースのほとんどのデータも含まれている必要があります。これは、「外部」にあるLKが会社のメインデータベースを見ることが誰にとっても愚かだったからです。

メインデータベースからのデータがLCデータベース（および場合によってはLCデータベースからメインデータベース）に分類されるように、請求開発者は複製メカニズムを実装し、特定の「中間」テーブルにすべての変更を書き込む挿入、更新、削除トリガーをトリガーしました。特定の周期性を持つ外部スクリプトは、このテーブルから新しいレコードを教科書にアンロードし、これらの教科書はLCデータベースからサーバーにアップロードされ、そこでそのデータベースで実行されました。すべてが順調に進んでいるように見えますが、ベースは飛躍的に成長し、すべてのログがマージされたこのテーブルにより、数か月でGIANTサイズに達しました。削除されたデータはその中に残りましたが、すぐには削除されませんでした。削除は長いプロセスであり、ガベージの蓄積に寄与し、単純にデータベースは時間とともに遅くなるためです。そのため、開発者が決定しました。その結果、サーバースペースが不足したため、3か月ごとにこのデータを削除し、バックアップデータベースの復元を行う必要がありました。そのようなボリュームとそのようなサーバーでのバックアップ復元-すべての作業にほぼ1日。これは、3か月ごとに会社全体の1日を停止することです。そして、それをしなければなりませんでした。ヘルプデスクはこの時点でメモを紙に書きました...

経験を考慮して、クラウドプラットフォームを開発し、この問題を既に認識していたため、これらの欠点のない詳細なログを記録するシステムを作成することにしました。特に、1つのサーバーに1つのデータベースではなく1000のボリュームが存在する可能性があることを考慮し、この1000のバックアップ復元を行うのは非現実的です。

まず、一般的な概念を説明します。

記録管理を構造化するために、それらはバッチ方式で維持されます。

パッケージに関する情報を含むテーブルと、このパッケージのデータを含むテーブルの2つのテーブルがあります。それらをLOG_PACKETおよびLOGと呼びましょう

LOG_PACKET

一意の識別子
記録日
レコード作成者（ユーザーID）
処理属性（0または1）
レコードタイプ（ins、up、del）
テーブル番号（ログを書き込むテーブルの名前）
データテーブル番号
パッケージレコード識別子
処理の試行回数
最終処理日

LOG

一意の識別子
OLDデータのBlobフィールド
BLOBデータフィールドNEW
パッケージレコード識別子
テーブルフィールド番号（テーブルフィールド名）

データを変更するときのトリガーでは、LOG_PACKETのエントリに、このレコードとLOGテーブルのデータ（変更前後）に関する情報を入力する必要があります。

次に、特定の頻度で実行される外部スクリプトは、LOG_PACKETで未処理のエントリを見つけ、それらのコピーを別のサーバーのログデータベースに作成し、LOGテーブルのすべてのエントリで識別し、コピーを作成します。

次に、メインデータベースのゴミを取り除く必要があります。 LOGからデータを削除-データベースの速度を低下させ、ゴミを節約します。より高速で優れたDROP TABLEプロシージャがあります。

PS： DROPがDELETEよりも望ましい理由については詳しく説明しません。 この記事ではすべてが非常によく説明されています 。

したがって、次の解決策が適用されます。

LOGテーブルは3つの部分で構成されます。 LOG_1-最初の日にデータがここに書き込まれます。LOG_2-翌日にデータがここに書き込まれます。 LOG_1で1日目、LOG_2で2日目が交互に行われます。データがLOG_2に書き込まれ、LOG_1を参照するすべてのパケットが処理されると、DROP LOG_1およびCREATE TABLE LOG_1が発生します。したがって、現時点ではすべてのレコードが削除され、テーブルはクリーンになります。翌日、LOG_2と同様。

しかし、それほど単純ではありません。なぜなら LOG_1とLOG_2への参照は、ログに記録されたすべてのテーブルのトリガーに登録されます-削除できません。これを行うには、トリックを適用します。それらを表示します。そして、このVIEWを介してトリガーにデータの追加を登録します。

CREATE OR ALTER VIEW V_LOG_1( ID, BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) AS select p.id, p.blob_old, p.blob_new, p.packet, p.num_pole from LOG_1 p;

それでも、トリガー内のVIEWはそれを参照するため、トリガーを持つテーブルを削除しても機能しません。

これを行うには、テーブルLOG_3を作成します。これは、構造LOG_1およびLOG_2のコピーになります。 VIOG LOG_1をLOG_3およびvaulに置き換えます。テーブルLOG_1を削除して再作成できます。その後、VIEWでLOG_3をLOG_1に戻します。すべてがさらに機能します。

この操作は問題なく実行できます。現時点では、LOG_1テーブルにレコードは発生しません。この日、記録はLOG_2にあります。このようなクリーニング操作は、その日にエントリがないテーブルで常に発生します。翌日、同じ操作がLOG_2テーブルで発生します。

また、この操作は、このテーブルのLOG_PACKETのすべてのデータが処理された場合にのみ発生します。それらが処理されていない場合、データ損失になるため、テーブルに触れることはできません。まず、データが処理されない理由を理解する必要があります。たとえば、スクリプトがハングして、ログを使用してデータベースに移動する場合があります。その日にクリーニング操作がキャンセルされた場合、次の試行が1日おきに行われます。

コンセプトを分析しました。次に、最適な作業スキームを分析します。

まず、今日作成しているテーブルに関する情報を含むテーブルを入力する必要があります。単一のレコードを持つテーブル（この場合、操作はデータをすばやく取得できます）でも、インデックスでもありません。日付、更新日、およびログ記録を許可するかどうかを含める必要があります（管理者は、ログをまったく必要としない場合は、ログ記録を禁止してスペースを節約できる必要があります）

例えば

 CREATE TABLE LOG_INFO ( ZAPIS SMALLINT, DATA TIMESTAMP, ID_TABLE SMALLINT );

ログに記録されたテーブルのすべてのトリガーでは、最初に記録が許可されているかどうかがチェックされます

 select p.zapis from LOG_INFO p into: zapis;

許可されている場合、どのテーブルでデータを書き込むかがチェックされます

 select first 1 case when p.data=current_date then p.id_table else case when p.id_table=1 then 2 when p.id_table=2 then 1 end end, case when p.data=current_date then 1 else -1 end from LOG_INFO p into: log_info, log_info_check;

PS：懐疑論者の場合、複数のレコードがLOG_INFOテーブルに突然現れた場合にトリガーがクラッシュする可能性を排除するために、最初の1が必要です。 この場合、ログに記録されたすべてのテーブルにデータを書き込むときにエラーが発生します（通常、複数行のエラー）。 そして、最初の1つは、このオプションを除外することが保証されています。

このリクエストにより：

1）日付が現在の日付と一致するかどうかを確認し、一致する場合は、現在のテーブルに書き込み、そうでない場合は、テーブルを別のテーブルに変更します（日から次への移行がありました）。

2）このテーブルを1日の移行中に別のテーブルに更新する必要があるという兆候を設定します。

次のステップは、その日の移動時に更新が必要な場合、更新です

 if (log_info_check=-1) then UPDATE LOG_INFO SET ID_TABLE = :log_info, DATA = current_date;

つまり翌日の最初のレコードは、次のテーブルに書き込む必要があるデータを更新します。そして、リソースはこれに無駄になりません。

次に、データはLOG_PACKETに書き込まれ、その識別子を取得します

 if (inserting) then TYPE_=1; if (updating) then TYPE_=2; if (deleting) then TYPE_=3; if (TYPE_ in (1,2)) then INSERT INTO LOG_PACKET (TYPE_, TABLE_, NUM_TABLE, AVTOR, ID_ZAPISI) VALUES (:TYPE_, 15, :log_info, new.avtor, new.id) RETURNING ID into: id_packet; else INSERT INTO LOG_PACKET (TYPE_, TABLE_, NUM_TABLE, AVTOR, ID_ZAPISI) VALUES (:TYPE_, 15, :log_info, old.avtor, old.id) RETURNING ID into: id_packet;

さらに、受信したテーブル番号に応じて、データはV_LOG_1またはV_LOG_2に書き込まれる必要があります。

たとえば、レコードは次のようになります。

 if (log_info=1) then begin if (TYPE_=1) then begin INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (NULL, new.n_687, :id_packet, 687); INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (NULL, new.n_688, :id_packet, 688); INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (NULL, new.n_689, :id_packet, 689); INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (NULL, new.n_690, :id_packet, 690); INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (NULL, new.n_691, :id_packet, 691); end if (TYPE_=2) then begin if (new.n_687<>old.n_687) then INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_687, new.n_687, :id_packet, 687); if (new.n_688<>old.n_688) then INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_688, new.n_688, :id_packet, 688); if (new.n_689<>old.n_689) then INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_689, new.n_689, :id_packet, 689); if (new.n_690<>old.n_690) then INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_690, new.n_690, :id_packet, 690); if (new.n_691<>old.n_691) then INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_691, new.n_691, :id_packet, 691); end if (TYPE_=3) then begin INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_687, NULL, :id_packet, 687); INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_688, NULL, :id_packet, 688); INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_689, NULL, :id_packet, 689); INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_690, NULL, :id_packet, 690); INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_691, NULL, :id_packet, 691); end end

PS：システムに独自の組み込みプログラミング言語があり、すべてのテーブルとフィールドにはテキストではなく数字が付いているという事実によって、人生を少し簡略化しました。また、このおかげで、このようなトリガーは、新しいテーブルが表示されたときに完全に自動的に作成されるか、フィールドが変更されたときに再作成されます。 つまり ユーザーがインターフェースの「ログを有効にする」テーブルエディターのインターフェースのボタンをひっかけるだけで十分です。それ以降はすべて自動的に行われます。読者はより複雑になります。このデータベースが単純な場合、これらのトリガーは手動で作成する必要があり、NUM_POLEおよびNUM_TABLEのデータ型はテキスト形式である必要があります。 そこで、テーブルとフィールドの名前を書く必要があります。 または、テーブルとフィールドに番号が割り当てられ、そこからデータを取得する特定のテーブルを入力します。

次に、レコードが発生したかどうかにかかわらず、フラグ付きのテーブルを作成する必要があります。 1つのフィールドを持つテーブルでクエリを実行する方が、新しいレコードのフラグを検索してLOG_PACKETをソートするよりもはるかに高速であるため、システムリソースを節約します。

たとえば、次のように：

 CREATE TABLE LOG_PACKET_FLAG (ID SMALLINT);

新しいパッケージレコードを追加するときにトリガーのLOG_PACETテーブルに配置します

 UPDATE LOG_PACKET_FLAG SET ID = 1;

スクリプトを実行してデータをログデータベースに転送する場合、新しいエントリがあるかどうかを確認する必要があります（ない場合は、それ以上何もしません）。

 select first 1 p.id from LOG_PACKET_FLAG p

これは、たとえば次の場合よりもはるかに高速に機能します。

 select count(*) from LOG_PACKET p where p.check_=0

レコードの転送の最後に、転送されたレコードを転送している間に新しいレコードが出現したかどうかを確認する必要があります。

 UPDATE LOG_PACKET_FLAG SET ID = 0;

一般に、システムのリソースを節約します。

次に、レコードを転送するプロセスを検討します。

まず、メインデータベースからすべての新しいLOG_PACKETエントリを選択し、ループでこのデータをログデータベースに挿入します。

ログデータベースにパッケージレコードを作成した後、このパッケージのデータをテーブルLOG_1（2）から転送する必要があります。

ブロブフィールドからデータを抽出して別の場所に追加することは、特定のhemoとタンバリンとのダンスです。多くの実験の後、最も簡単で効果的な方法は、データベースへのクエリがあるスクリプトからクエリを開始することであるという結論に達しました（一般的に、ログデータベースからメインデータベースへの直接クエリでblobフィールドをドラッグアンドドロップします）。

パッケージデータを圧縮する手順。パッケージを追加した後にスクリプトをプルする必要があります。

 reate or alter procedure BLOB_INS ( SELECT_ varchar(250), BASE_ varchar(100), USER_ varchar(50), PASS_ varchar(50), PACKET integer) AS declare variable BLOB_OLD BLOB SUB_TYPE 0 SEGMENT SIZE 80; declare variable BLOB_NEW BLOB SUB_TYPE 0 SEGMENT SIZE 80; declare variable PACKET BIGINT; declare variable NUM_POLE INTEGER; begin FOR EXECUTE STATEMENT (:select_) ON EXTERNAL :base_ AS USER :user_ PASSWORD :pass_ INTO :BLOB_OLD,:BLOB_NEW,:NUM_POLE DO INSERT INTO LOG (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (:BLOB_OLD,:BLOB_NEW, :pacet, :NUM_POLE); End

：selectには、スクリプトから次のようなサービスが提供されます。

 select p.blob_old, p.blob_new, p.num_pole from log_'.< >.' p where p.packet='.< >

パッケージが正常に処理された後、メインデータベースでこのパッケージが処理されたことを示すフラグを設定する必要があります。

パッケージデータの追加でエラーが発生した場合、試行回数でデータを更新します。5回試行すると、フラグが設定され、データがエラーで処理され、プログラムはそれらの処理を停止します。このメカニズムを作成しないと、何らかのエラーが発生した場合に無限のサイクルに入る可能性があり、プログラムはその機能の実行を停止します。誤ったパッケージは後で処理でき、プログラムはさらに動作するはずです。

ほんの小さな別れの言葉：ログデータベース用に別の仮想マシンを作成します。このマシンにより少ないコアとメモリを割り当てることができます。より悪いディスクシステムを割り当てることもできます;たとえば、メインの作業を行うメインデータベースとして、SSDにログを保存する必要はありません。

パフォーマンス要件の増加はありません。これは遅延操作です。ログが1分または1.5分後に記録されることは重要ではありません。主なことは、ログが記録されることです。ユーザーがこのデータにアクセスすることは非常にまれであり、何らかの問題が発生した場合のみです。ログページの読み込み時間が200ミリ秒以上であれば問題ありません。

一般に、これを行うことでリソースを節約できます。これらのリソースをロードされたマシンに割り当てることをお勧めします。

ユーザーによるデータ変更のログをデータベースに保存し、別のデータベースに保存する方法

More articles: