都市伝説 (Urban Legends) を読み解く - SQL Server が提供する文レベル読取り一貫性は不完全なのか!?

  • Article

マイクロソフトの北川です。

都市伝説には「ニュース性」「真実味」「オチ」という特徴があり、起源や根拠が全く不明なものが多いとされています。また、何かしらの根拠があるものに関しても、なんでもない事実に尾ひれがついて伝説化したとされています。この都市伝説の問題はそれが「真実」であるかのように語られる点にあります。特定の都市伝説が広く普及した原因は、広く信頼されているソースにより紹介されたことにあるとされています。

閑話休題

日本オラクル社のウェブサイトにおける「オラクル都市伝説」で揶揄されている「あのデータベース」ですが、今回は「PowerPivot と相性がいい」とされているようです。この PowerPivot は、提示されている UI を見るとマイクロソフトが5月1日より提供開始した SQL Server 2008 R2 PowerPivot for Excel (以降 PowerPivot) のようです。

「データの活用」という観点でエンジンでは競合する両社がともに「セルフサービス BI」の重要性を訴求するといういいお話では残念ながらないようです。では、今回はこの「都市伝説」を読み解いてみましょう。

以下オラクル都市伝説サイトより引用。

あのデータベースでも Oracle データベースの読取り一貫性に近い機能が追加されたらしいが、更新対象の行データを全て一時領域に書き込み大量のオーバーヘッドが発生してしまう。検証の段階で遅くて使い物にならないことが判明したため、この機能は使用しないことになっていた。あのデータベースはパフォーマンスを考慮してか、デフォルトでこの機能は OFF になっている。

 

SQL Server が提供する文レベル読取り一貫性は不完全なのか!?

「あのデータベース」が SQL Server のことを指しているのであれば、根拠のない非難と言わざるを得ないかと。まず「文レベル読取り一貫性」を実装している仕組みを理解する必要があります。

  • Oracle Database の場合
    1. データの更新が行われた場合、その更新前イメージを UNDO 領域に保存します。
    2. 他のトランザクションから排他ロックのかかったレコードの参照が行われた場合、この UNDO 領域の更新前イメージを参照することで、他のトランザクションに対して「トランザクションが開始された時点(過去)のコミット済みデータ」を返します。
  • SQL Server の場合 (文レベル読取り一貫性を有効にする "Read Committed Snapshot" ISOLATION LEVEL を選択した場合)
    1. データの更新が行われた場合、その更新前イメージを TempDB に保存します。
    2. 他のトランザクションから排他ロックのかかったレコードの参照が行われた場合、この TempDB の更新前イメージを参照することで、他のトランザクションに対して「トランザクションが開始された時点(過去)のコミット済みデータ」を返します。

上記を見比べていかがでしょうか。更新前イメージの格納先が異なる以外「文レベル読取り一貫性」の実現方法は同じであることが明らかです。つまり "UNDO 領域" と、"TempDB" という領域の違いにより、若干の更新前イメージ格納コストの差はあるかもしれませんが、両者を比較して SQL Server だけに大量のオーバーヘッドが発生する、とは言えないのではないでしょうか。

もちろん、SQL Server のデフォルトの ISOLATION LEVEL は Read Committed であり、この場合、上に挙げたような更新前イメージの格納は発生しません。ですので、SQL Server の ISOLATION LEVEL の差による比較なのであれば、更新前イメージの格納を「Read Committed と比較すると Read Committed Snapshot を使用する場合のオーバーヘッド」ということができるかもしれません。

では、このオーバーヘッドはどれくらいなのでしょうか。

以前マイクロソフトのパートナー様に、デフォルトインストールされた SQL Server で Isolation Level を Read Committed、Read Committed Snapshot に変更して TPC-C のトランザクション処理量を比較してもらう検証を実施いただきました。その結果下記結果を得ることができました。

  • Read Committed ではほとんど確認されなかった TempDB へのアクセス (I/O req per sec) が Read Committed Snapshot では増大していることが確認された (更新前イメージへのアクセスのため)。
    ※デフォルト インストールのため、TempDB は単一のデータファイルで構成されており、当該データファイルが格納されているディスクは単一ディスクで、RAID0 などのディスクアクセス高速化は図られていません。
  • Read Committed の場合の TPS (Transaction per Second) を 100 とした場合、Read Committed Snapshot の場合の TPS は 90 と若干劣化するものの、その差は 10% である。
    ※TempDB を構成するデータファイルへの I/O 効率化を図ればこの差はさらに縮まります。

同じディスク構成で、単一のディスクに UNDO 領域の構成ファイルを置いた場合、更新前イメージへのアクセスのための I/O による TPS の劣化は Oracle Database であれ、SQL Server であれ大きな差はありません。とすると、Isolation Level やそれぞれのデータベースが稼働するハードウェア構成 (サーバー、ストレージ) が共通であれば、TPS はほぼ同じような性能となると推測することが可能です。

上記をまとめて考察した場合、「SQL Server の Isolation Level を Read Committed Snapshot にした場合、オーバーヘッドが大きすぎて使い物にならない」のであれば、ハードウェア構成が同じだとすると、同じく Oracle Database で稼働しているシステムも使い物にならないということになります。もし、Oracle Database で稼働しているシステムには問題がないというのであれば、ハードウェア構成が SQL Server で稼働しているシステムとは異なる、つまり、データベースの違いによる性能差ではない、ということになります。

 

[結論] 「あのデータベース (SQL Server)」で読取り一貫性を有効にした場合、遅くて使い物にならないのであれば、同一ハードウェア構成であれば、Oracle Database であっても同じく遅くて使い物にならないでしょう。Oracle Database で稼働しているシステムで性能上の問題がないのであれば、おそらく「検証の段階で使用したハードウェア」がよほど低スペックであったのだといえます。決して SQL Server が実装している読取り一貫性のための機能が不完全なのではありません。

 

[本項目における突っ込みどころ]

  1. 「あのデータベースはパフォーマンスを考慮してか、デフォルトでこの機能は OFF になっている。 」って…。SQL Server が Read Committed を Default としているのは、Short Transaction を前提としているからであり、この前提では Read Committed が最も効率的な Isolation Level であるためです。