none
DBServer-AとDBServer-Bにおけるパフォーマンス差異の原因について RRS feed

  • 質問

  • はじめて投稿します。

    よろしくお願い申し上げます。

    現在、下記のような環境でDBServerが構築されています。

    ****
    DBServer-A
    ・AWS-EC2(m3.large)内にて構築
    ・CPU Intel(R)Xeon(R) CPU E5-2670 v2 @2.50GHz (仮想プロセッサx2)
    ・メモリ7.50GB
    ・SSD
    ・Windows Server 2012 R2 Standard(x64)
    ・SQL Server 2014 Web Edition
    ・System Center Endpoint Protection
    ****
    DBServer-B
    ・社内物理Server内のHyper-Vにて構築
    ・CPU Intel(R)Xeon(R) CPU E5-2470 v2 @2.40GHz (仮想プロセッサx4)
    ・メモリ30.0GB
    ・SSD
    ・Windows Server 2012 Standard(x64)
    ・SQL Server 2014 Standard Edition
    ・ウイルスバスターコーポレートエディション
    ****
    DBServer-AとDBServer-Bにそれぞれで、全く同じDatabaceをセットしています。

    具体的には、DBServer-Aで作成したDBをデタッチし、DBServer-Bにアタッチし
    て構築しています。


    一見、あらゆる面において、「DBServer-B」の方がパフォーマンスが良いはずですが、
    SQL Managiment Stadioで各種クエリを実行すると、全てにおいて、
    「DBServer-A」の方が遥かに早い結果を齎せます。

    例1)ViewをJoin参照したSELECT分(若干複雑なクエリ)
    →DBServer-Aでは、2秒
    →DBServer-Bでは、90秒

    例2)55万件のDELETE文発行
    →DBServer-Aでは、25秒
    →DBServer-Bでは、52秒

    単純SELECTにおいても、INSERT、UPDATE、DELETE、全てにおいて、
    DBServer-Bでのパフォーマンスが著しく悪い状況です。


    *********************
    これまでに、確認した内容
    *********************
    (1)tempdbは仮想CPUにあわせて作成済み
     DBServer-Aは2つ
     DBServer-Bは4つ

    (2)SQL Server 最大サーバーメモリは仮想メモリの75%で設定済み

    (3)CrystalDiskMarkによるSSDのベンチマーク比較
      【DBServer-A】
      ・Seq R[61.15MB/s] W[86.61MB/s]
      ・512k R[62.48MB/s] W[86.62MB/s]
      ・4k R[12.56MB/s] W[8.102MB/s]
      ・4kQD32 R[12.53MB/s] W[12.53MB/s]
      【DBServer-B】
      ・Seq R[797.2MB/s] W[330.0MB/s]
      ・512k R[267.4MB/s] W[149.4MB/s]
      ・4k R[19.37MB/s] W[4.546MB/s]
      ・4kQD32 R[152.6MB/s] W[3.974MB/s]

    (4)SQL Managiment Stadioで各種クエリを実行のパフォーマンスモニタ監視
      MemoryAvailable Mbytes
      MemoryPages/Sec
      Paging File%Usage
      Paging File%Usage Peak
      Processor%Processor Time
      Processor%User Time
      Processor%Privileged Time
      ProcessorInterrupts/Sec
      SystemProcessor Queue Length
      Physical Disk%Disk Time
      Physical DiskCurrent Disk Queue Length
      Physical DiskDisk Writes/Sec
      Network InterfaceBytes Total/Sec
      各種負荷率はDBServer-A,B共に同じような曲線となっており
      ボトルネックとなる箇所を見つけられず

    (5)リクエストに対するレスポンスが1000ミリ秒未満のクエリでは
      パフォーマンス差を確認出来ず

    (6)共にIndexの最適化、統計情報の更新は実行済み


    ***
    ハードウエア環境もリソース環境も異なり、
    ウイルスソフトの種類も違い、
    OSもWin2012R2とWin2012と違い、
    SQLも、WEB Edtionとstandard Edtionとの違いがあるため、
    DB構造及びデータ量が同じとはいえ、
    単純比較出来ない事は承知していますが、
    とはいえ、インフラのリソース差は歴然であり、
    DBServer-AよりDBServer-Bが劣るというのは考えにくい症状です。

    ただ1点、SSDの4k(4kQD32)Writeパフォーマンスが劣っているのが気になりますが、これが原因でここまでの差が出ますでしょうか?

    他に何か考えられる事がありますでしょうか?

    何をどう調べるべきか、アドバイス頂けたら幸いです。


    2015年6月26日 10:00

すべての返信

  • あと着目すべきは物理サーバーのメモリ帯域幅かな。

    E5-2670のメモリ帯域幅は4ch合わせて51.2GB/s、E5-2470は3ch合わせて38.4GB/sです。

    またメモリの種類はDDR3 800/1066/1333/1600のいずれかを使えるわけですが、DDR3 1333とか使ってた場合はもっと差が開くので。


    甕星

    • 回答としてマーク 2015年6月29日 6:22
    • 回答としてマークされていない 2015年6月29日 6:23
    • 回答としてマーク 2015年6月29日 6:32
    • 回答としてマークされていない 2015年7月1日 3:08
    2015年6月26日 21:56
  • その線で言うと、

    (仮想サーバーでなく)物理サーバーの搭載メモリ量によってはメモリのクロックダウンも発生しますね。せっかくDDR3 1600を挿していても800MHz動作もあり得ます。

    • 回答としてマーク 2015年6月29日 6:22
    • 回答としてマークされていない 2015年6月29日 6:23
    • 回答としてマーク 2015年6月29日 6:32
    • 回答としてマークされていない 2015年7月1日 3:08
    2015年6月27日 0:13
  • ご回答ありがとうございます。

    メモリ帯域幅のよる差はやはり大きいようですね。

    大変参考になりました。

    ありがとうございました。

    2015年6月29日 1:55
  • ご回答ありがとうございます。

    物理サーバーの搭載メモリ量によるクロックダウン....盲点でした。

    大変参考になりました。

    ありがとうございました。

    2015年6月29日 2:13
  • >ただ1点、SSDの4k(4kQD32)Writeパフォーマンスが劣っているのが気になりますが、これが原因でここまでの差が出ますでしょうか?
    単純なDELETE文であれば、その実行速度はストレージの4k書き込み性能に大きく依存するはずです。
    ですから *例2)55万件のDELETE文発行* については妥当な結果だと思います。

    それよりも *例1)ViewをJoin参照したSELECT分(若干複雑なクエリ)* が顕著な違いを見せているので、その実行プランとにらめっこしながら地道にボトルネックを探しては如何でしょう。
    ボトルネックが見つからなかった時は、ストレージの強化が堅実にパフォーマンスアップにつながると思います。
    CrystalDiskMarkの結果をみると今のSSDはエンタープライズ向けではなさそうな感じですので、エンタープライズ向け製品を導入するかハードウェアRAIDボードを導入するなど検討されてはどうでしう。
    • 回答としてマーク 2015年6月29日 6:22
    • 回答としてマークされていない 2015年6月29日 6:23
    • 回答としてマーク 2015年6月29日 6:32
    • 回答としてマークされていない 2015年7月1日 3:08
    2015年6月29日 3:05
  • ご回答ありがとうございます。

    やはり、4K書き込みの影響は顕著に出ますね。
    ありがとうございます。


    View参照のSELECTは、謎のままです。

    実行プランとにらめっこしてはみたものの、ボトルネックが見つからず状態です。

    サブクエリを多様化してる若干複雑なクエリでありますが、例に記したとおり、

    →DBServer-Aでは、2秒
    →DBServer-Bでは、90秒

    というパフォーマンスです。

    しかし、Selectで返す結果のViewのカラムを参照除外すると、DBServer-Aと変わらぬパフォーマンスでレスポンスされます。
    SQLを書くと長くなるので、例えば簡易的に示すと

    ***
    select
      a.col as col1
     ,b.col as col2
    from 
     TBL_A as a
     left join
     View_B as b /* VIEW */
     on
     a.key = b.key
    ***
    とすると

    →DBServer-Aでは、2秒
    →DBServer-Bでは、90秒

    ***
    select
      a.col as col1
    -- ,b.col as col2
    from 
     TBL_A as a
     left join
     View_B as b /* VIEW */
     on
     a.key = b.key
    ***
    とすると

    →DBServer-Aでは、2秒
    →DBServer-Bでは、2秒

    といった具合です。

    ※Viewはリンクサーバーを使ったOracleのデータだったので、ソコにボトルネックがあると当初は判断していたのですが、リンクサーバーを削除し、Oracleではなく、自身のテーブル(同一インスタンス内の同一データベース内に同じ形式で作成したテーブル)に変えても結果は同じで、リンクサーバーが原因ではないと判断し、SQL Serverに何か設定(チューニングオプション)があるのか?と迷った事が発端となっております。

    ※Viewの作りとしては簡単に示すと
    with SampleView(Col1)
    as
    (
    SELECT Col1 FROM TBL1 WITH (NOLOCK) where 条件

    UNION ALL

    SELECT Col1 FROM TBL2 
    JOIN 
    SampleView
    ON 条件
    where 条件
    )
    SELECT  * FROM SampleView

    といった、with union all の再帰クエリとなっています。

    ※View単体の呼び出しは一切ストレスありません。

    ※また、あまり関係はないと思いますが追加情報として。。。
    利用しているのは、SQL Server 2014ですが、データベースオプションの互換性レベルをSQL Server 2012(110)に切り替えて使用しています。
    マスタ系のデータベースがSQL Server 2012で作成されたデータベース(mdf)で、そのmdfをSQL Server 2014にアタッチして利用しなければならない前提があり、マスタとJOINした際に、Version差異によるパフォーマンス低下が確認できたため、やむを得ず、データベースオプションの互換性レベルをSQL Server 2012(110)に切り替えて使用しています。

    このあたりの追加情報で、引き続き、CPU(最大メモリー帯域幅)による性能差、及び、SSD4kWriteによる性能差以外で何か考えられる点があれば、ご教授願えれば幸いです。
    2015年6月29日 6:20
  • View_Bのクエリをそのままストアドプロシージャにして、それと入れ替えてみたらでんな結果になるでしょうか?

    追伸:未解決のようなので、回答としてマークは解除されたほうがいいのでは?

    • 編集済み hihijiji 2015年6月30日 10:15 追伸
    2015年6月30日 10:06
  • 実行プランはどちらでも同じだったということですかね?

    個人的な感覚では、マシンのパフォーマンスによる直接的な影響による差は、2倍とかその程度までで、それ以上差が出る場合は、単純なパフォーマンス差ではなく何か特別なことが起こっているという感覚です。

    ※微妙なパフォーマンス差が、偶然大きい結果の差を引き起こすことももちろんありますが

    数十倍となると、うーん、どうでしょうね…

    ちなみに、サーバBのCPUのハイパースレッディングは有効になっていますか?
    無効にすると何か変わったりするでしょうか?

    あと、並列クエリの設定がどうなっているかとかですかね…

    2015年7月1日 4:35