データベースのアーカイブ

著者
Damian
Terlecki
2分間の読書
データベース

大規模でアクティビティの高いシステムは、時間とともにパフォーマンスが低下しがちです。これは、特に長期間稼働している、ユーザーベースの大きいレガシープロジェクトの大部分に当てはまります。 ユーザーデータが増加し、テーブルスペースが拡大し、エンティティの数が数百万から数十億に膨れ上がり、インデックスのサイズが大きくなる、というのは大した発見ではありません。データベースのクエリ(適切にインデックスが作成されていれば)は完了までにそれほど時間はかかりませんが、全盛期の輝きもありません。システムは正常に動作しているとさえ言えるかもしれませんが、ある日、何かの派手なイベントでユーザーがサービスに殺到し…そして、システムは詰まってしまいます。

データベースのアーカイブは単純なプロセスではありません。一晩で終わるものではないのです。後々安定したパフォーマンスを達成するために、プロジェクトの分析段階で検討することをお勧めします。また、データベースの運用コストを多かれ少なかれ安定したレベルに保ちたいと思うかもしれません。ユーザーを考慮に入れると、変更を最小限に抑えるか、早い段階で導入するのが最善です。それでも、パフォーマンスの向上が昨日までに必要だという窮地に立たされた場合、いくつかの選択肢から選ばなければなりません。

  • アプリケーションコードの最適化
  • データベースクエリの最適化
  • データベース構造(インデックス、パーティション、テーブル)の最適化
  • データのアーカイブ
  • ユースケースの再定義

データアーカイブを検討する場合、さらに2つの選択肢があります。

  • データを外部のデータソースやストレージに移動する
  • インデータベースアーカイブ データベースのアーカイブは、構造の最適化と密接に関連していなければならないことに注意してください。そうでなければ、パフォーマンスの向上は最小限か、まったくないかもしれません。

データを別の場所に移動する

パフォーマンスを向上させたり、データベースのサイズを縮小したりする最も簡単な方法は、データを別の場所に移動することです。一般的には、アーカイブテーブルを使用し、後でそれを圧縮してデータベースサイズを縮小することができます。しかし、最も一般的な選択肢は、データを非正規化された状態で別のストレージやデータウェアハウスに移動することです。また、誤って作成されたデータを削除するというのも有効な選択肢です。

ただし、テーブルにすでにいくつかのインデックスが設定されている場合、レコードを削除すると、事実上、断片化(フラグメンテーション)が増加することに注意してください。断片化を確認するには、sys.dm_db_index_physical_statsをクエリできます。断片化のレベルに応じて、それを修正するために2つの方法のいずれかを使用できます。

  • 断片化 <5%-10%; 30%) — 再編成 ALTER INDEX index_name REORGANIZE; (常にオンライン、Oracleでは利用不可)
  • 断片化 <30%; 100%) — 再構築 ALTER INDEX REBUILD [ONLINE]; 空間パーティションインデックスがある場合は、ALTER INDEX index_name REBUILD PARTITION partition_name; クエリを使用する必要があります。テーブルのインデックスを表示するには SELECT * FROM all_indexes; を呼び出し、パーティション名を確認するには SELECT * FROM ALL_TAB_PARTITIONS; を呼び出します。

Oracleのインデータベースアーカイブ

Oracleはより人気のあるデータベースの1つです。Oracle 12cでは、インデータベースアーカイブという機能が導入されました。これは非常に興味深い機能です。基本的には、選択したテーブルにこの機能を適用すると、Oracleは追加の列 ora_archive_state を作成し、値0で初期化します。この値は、行がアーカイブされていないことを意味します。この列を他の値に設定すると、事実上、行がアーカイブ済みとしてマークされます。アーカイブされた行は、本質的に次のようになります。

  • デフォルトでは表示されない ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;
  • セッション属性を設定した後に表示される ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
  • ora_archive_state 列は、可視性の値に応じて値が設定され、クエリに自動的に追加される。

インデータベースアーカイブの有効化と無効化は、2つのコマンドで行います。

ALTER TABLE table_name ROW ARCHIVAL;
ALTER TABLE table_name NO ROW ARCHIVAL;

アーカイブの有効化は非常に迅速ですが、無効化(アーカイブ状態の列を削除する)は、数億レコードを持つテーブルの場合、最大1時間かかることがあります。もう一つ、怖い話をさせてください。Oracleのインデータベースアーカイブは、外部キー制約を無視します。実際には、レコードは削除されないので、これは論理的な結果です。しかし、デフォルトでは、アーカイブされたデータはアプリケーションに表示されず、レコードへのリレーションがある場合、内部エラーが表示され始めて驚くかもしれません。

この時点で、テーブルがどのようにリンクされているかを分析し、それらを一緒にアーカイブする必要があるかもしれません。幸いにも、コアテーブルをアーカイブしようとしているわけではないでしょう。それらは恐らく他のすべてのテーブルにリンクされているからです。そのため、このプロセスは簡単に思えるかもしれませんが、実際には非常に複雑です。もちろん、セッション属性を使用してこのアーカイブデータにアクセスするオプションもあります。これにより、セッションを変更することで、選択した場所でアーカイブデータの可視性を維持することが可能です。

ただし、コネクションプールを使用している場合、接続を閉じた後、それは変更されたセッションとともにプールに戻り、事実上コネクションプールを汚染し、アーカイブを意味のないものにしてしまうことに注意してください。したがって、安全なアプローチは、接続を閉じる前にセッションを元に戻すこと(JDBC Connectionがスレッド間で共有されない限り、これは一般的に真であるはずです)、あるいはさらに安全なのは、アーカイブ可視性用途のために独自のコネクションプールを持つ別のデータソースを準備することです。

アーカイブ後のパフォーマンス向上

ここまでたどり着きましたか？データをアーカイブし、いくつかのパフォーマンステストを実行し、目に見えるパフォーマンスの向上がないことに気づきましたか？まあ、アーカイブ前後のデータ(インデータベースアーカイブ)の実行計画を確認すると、実質的な改善がないことがわかります。データは実際には削除されておらず、アーカイブされた行はまだフルスキャン中に考慮されています。また、アーカイブ状態の列をインデックスに追加してもいません。まあ、インデックスの数や追加の制約によっては、本当に面倒な作業になる可能性があるので、あなたを責めはしません。

パフォーマンスを向上させる別の方法があり、これは特にインデータベースアーカイブに適しています。それはテーブルパーティショニングです。この機能は諸刃の剣です。

パーティショニングは、正しく行われればテーブルのパフォーマンスを劇的に向上させることができますが、間違って行われたり、必要でない場合に行われたりすると、パフォーマンスを悪化させ、使い物にならなくすることさえあります。[severalnines.com]

その理由は、複数のパーティションにまたがるクエリは、単一のテーブルスペースで実行されるクエリよりも遅くなる傾向があるためです。間違った列でパーティション分割し、典型的なユースケースがあなたの周到な準備を無視した場合、システムは必然的にパフォーマンスを失います。それとは対照的に、テーブルのサイズが非常に大きい場合、インデックスのサイズも増加します。それらをRAMにロードするのが難しくなります。そのような場合、パーティショニングはインデックスのサイズを小さくして、メモリに収まりやすくするはずです。

インデータベースアーカイブの場合、ora_archive_state はパーティションキーの候補となります。ほとんどの場合、アクティブでアーカイブされていないデータをクエリすることになります。オプティマイザは、関連情報を持たないパーティションを検索しないはずです。アーカイブされたデータへのアクセスを必要とするシステムコンポーネントは、パフォーマンスが低下します。しかし、データを削除するのではなく、インターフェイスで古いデータへのアクセスを正しく示すことで、ユーザーはより寛容で理解を示すでしょう。「1バイトでも節約できれば、それは儲けものだ」という考え方に従うなら、パフォーマンスを気にしないのであれば、アーカイブされたレコードを持つパーティションをさらに圧縮することもできます。

アーカイブ状態の列でパーティション分割されたテーブルを作成するには、次のようなものを使用できます(別の列でパーティション分割する場合のサブパーティションの使用も可能です)。

CREATE TABLE table_name (
  --...
)
  ROW ARCHIVAL
  ENABLE ROW MOVEMENT
  PARTITION BY LIST ( ORA_ARCHIVE_STATE )
  (
    PARTITION p0 VALUES ('0'),
    PARTITION p1 VALUES ('1')
  );

すでにテーブルを作成したが、まだパーティション分割されていない場合は、パーティション化テーブルに変換することが可能です。

ALTER TABLE table_name MODIFY
  PARTITION BY LIST ( ORA_ARCHIVE_STATE )
  (
    PARTITION p0 VALUES ('0'),
    PARTITION p1 VALUES ('1')
  ) [ONLINE];

最も複雑なケースは、テーブルがすでにパーティション分割されている場合です。このような状況では、2つの選択肢があります。

  1. DBMS_REDEFINITIONを使用して既存のテーブルをパーティション分割する
  2. EXCHANGE PARTITIONを使用して既存のテーブルをパーティション分割する
< H2データベース — プロトタイピングJMeterによる負荷テスト >