SQL Serverのインスタンスを共用しているデータベースを分離するために考慮すべきこと

SQL Database SQL Server Database

ogp

こんにちは、技術本部SRE部ZOZOSREチームの斉藤です。普段はZOZOTOWNのオンプレミスとクラウドの構築・運用に携わっています。またDBREとしてZOZOTOWNのデータベース全般の運用・保守も兼務しております。

ZOZOTOWNではSQL Serverインスタンスが複数存在しており、サービスのメインデータベースとして稼働しています。その中で、1つのインスタンスを共用し、2つのデータベースが相乗りしている状態で運用されている環境が存在します。相乗りしているデータベースを検討したシステム構成の制限内で分離するには詳細な調査が必要でした。後述しておりますが、調査の過程で課題が見つかり、容易に分離はできませんでした。課題を解決し、分離を実現させるために日々邁進しております。

本記事では、SQL Serverインスタンスを共用し、2つのデータベースが相乗りしている環境からデータベースを分離させるための取り組みを紹介します。データベース分離に関して構成やスペック検討、課題として顕在化する項目の一例としてご参考になればと思います。

目次

データベース分離を検討した背景と課題

現在、1つのインスタンス上にショッピングカート関連の機能を持ったデータベース（以下、カートDB）と履歴関連のデータを持つデータベース（以下、履歴DB）が共存している状態です。どちらかのデータベースが起因で障害が発生した場合、もう一方のデータベースにも影響が波及し、障害の範囲が広がってしまいます。特にカートDBが影響を受け、ショッピングカート機能に影響を受けるとZOZOのサービス継続に多大なインパクトを発生させてしまいます。障害の影響範囲を限定的にするため、カートDBと履歴DBを分離したいと考えました。ワークロードやサービスの継続性を考慮し履歴DBをインスタンスから分離させることにしました。

構成検討

履歴DBは、ZOZOTOWN内の一部機能やサイト表示に影響があり、社内の業務システムでも使用されていることから、可用性を担保したまま移行できることを必須の条件としました。他にも、クラウドとオンプレの比較、費用や機能面、アプリケーション改修コストを主な検討項目としました。

ソリューション選定

3つのデータベースソリューションを検討しました。コストは各ソリューションを5年間運用した場合を想定して比較しました。結論としては、アプリケーションの改修コストとライセンスコストを押さえられるオンプレミスのSQL Serverを選定しました。クラウドはコストメリットが出ませんでした。ハードウェアの運用については、ノウハウが蓄積されているので運用コストの大幅な増加は無いと判断しました。

製品 アプリケーション改修コスト 運用費用（5年） ハードウェアの運用
SQL Server（オンプレミス） 低 低 有
Amazon RDS for SQL Server 低 高 無
Amazon Aurora MySQL 高 中 無

可用性

Windows Serverの標準機能として利用できるWindows Server Failover Clustering（以下、WSFC）でクラスター化し、可用性を担保することにしました。WSFCは既存環境で採用されており、安定して運用できることが選定理由です。

WSFCについての情報はWindows Server フェールオーバー クラスタリングの概要を参照してください。

概要図 data_replication

SQL Serverのエディション

既存のインスタンスはEnterprise Editionで稼働しています。データベースが独立するので、サーバスペックを縮小できると想定しました。Enterprise EditionとStandard Editionではコストが大幅に変わります。可能ならば、分離先のインスタンスはStandard Editionで稼働させたいと考えましたが、エディション毎に機能制限があります。Enterprise EditionとStandard Editionの主な違いは以下の通りです。

  • ライセンス価格
    • Enterprise EditionはStandard Editionに比べ、約4倍の価格。
      • Enterprise Edition：USD$13,748
      • Standard Edition：USD$3,586

ライセンス価格についての情報はSQL Server 2019 の価格を参照してください。

  • オンライン操作
    • Enterprise Editionはインデックスをオンラインで作成、再構築が可能だが、Standard Editionでは不可能。
  • リソース制限
    • Enterprise Editionはサーバに搭載されているリソースを最大限に使用できるが、Standard Editionでは制約がある。
      • CPU：4ソケットまたは、24コアのどちらか小さいほうに制限
      • バッファプール：最大サイズが128GB

エディション間の機能差についての詳細情報はSQL Server 2019 の各エディションとサポートされている機能を参照してください。

ライセンス価格の安いStandard Editionを選びたいところですが、各種制限の範囲で履歴DBを移行できるか懸念があります。次のステップとしてこれらの制限が移行にどの程度影響していくかを調査します。

必要なスペックを調査する

データベースを分離するにあたって難しいと感じたのが、スペック検討です。共有しているサーバリソースから履歴DB部分で使用しているリソースのみを抜き出す必要があります。メトリクスがサーバやインスタンス単位で取得されており、データベース単位で数値化しなければなりません。更にStandard Editionのリソース制限内で運用が可能かという観点でも調査が必要でした。

調査項目

  1. CPUコア数を見積もる
  2. 並列処理の最大限度（MAXDOP）を検証する
  3. バッファキャッシュサイズを見積もる

CPUコア数を見積もる

必要なCPUコア数を見積もります。まずDBのCPU使用率が24時間中で最も高かった1時間に絞りました。弊社では動的管理ビュー（Dynamic Management View:以下、DMV）の情報をロギングしています。対象時間帯で実行されている全クエリから履歴DBのCPU時間を抽出し、以下の式に当てはめて必要なコア数を算出しました。DMVのロギングについては以前のテックブログで紹介しています。

techblog.zozo.com

計算式

（履歴DBのCPU時間 ÷ 全体のCPU時間） × 全体のCPU使用率 × サーバの論理コア数

結果

(279 ÷ 557) × 0.5 × 64 = 16.02

以上の結果から、必要なコア数は約16となり、Standard Editionの制限内である24コアで稼働できる見込みとなりました。SQL Server以外のOSなどが使用するCPUも考慮し、32コアのハードウェアスペックがあれば問題ないと見積もりました。

並列処理の最大限度（MAXDOP）を検証する

SQL Serverは1つのステートメントで使用できるプロセッサの最大数を決めることができます。既存のインスタンスはMAXDOPが8で設定されており、Standard EditionでもMAXDOPを8に設定できます。しかし使用できるCPUコア数が24のため、MAXDOPが8のクエリが3セッションで実行されると、CPU使用率が100％に達してしまう可能性があります。Standard Editionの並列処理度を考慮するとMAXDOPを4程度に減らすことを検討する必要があります。並列処理の最大限度（MAXDOP）が半減した場合にどの程度の性能劣化が生じるのか検証しました。

並列処理の最大限度（MAXDOP）についての詳細情報はデータベース エンジンの構成 -MAXDOP ページを参照してください。

以下の調査クエリで履歴DBからMAXDOP8で実行されているクエリを抽出します。結果が比較しやすくなるので、調査クエリの結果から実行時間の長いクエリをピックアップしました。

select top 1000
     last_execution_time
    ,execution_count
    ,last_elapsed_time
    ,last_logical_reads
    ,last_physical_reads
    ,max_dop
    ,text
from
  sys.dm_exec_query_stats
      outer apply sys.dm_exec_query_plan (plan_handle) as qp
      outer apply sys.dm_exec_sql_text (sql_handle) as sql
where
  qp.dbid = db_id('履歴DB')
  and max_dop = 8

MAXDOP4に設定したStandard Editionの環境でピックアップしたクエリを実行し、結果を確認します。

  • 実行結果
    • 実行時間：20秒
    • 先行読み取り数：0
    • 論理読み取り数：8112285（100％キャッシュに載っている状態） data_replication

実行したクエリのMAXDOPを確認します。

  • 実行結果
    • MAXDOP4で実行されていることを確認 data_replication

同様のクエリに「option (maxdop 8)」を指定して再度実行します。「option (maxdop 8)」を指定すると強制的にMAXDOP8で実行されます。

  • 実行結果
    • 実行時間：10秒
    • 先行読み取り数：0
    • 論理読み取り数：8112285（100％キャッシュに載っている状態） data_replication

以上の検証結果から同時実行性を考慮し、MAXDOPを4にした場合、MAXDOP8で実行されているクエリは性能劣化が想定される結果となりました。業務要件を精査してどうしても性能劣化が許容できないクエリにのみ「option (maxdop 8)」を指定するなどの対策が必要という結論に至りました。

バッファキャッシュサイズを見積もる

必要なバッファサイズを見積もります。現状の各データベース毎のバッファキャッシュサイズを調査しました。

各データベース毎のバッファサイズ調査クエリ

select
    count(*)*8/1024/1024 as buffer_cache_size 
   ,case database_id  
        when 32767 then 'ResourceDb'  
        else db_name(database_id)  
        end as database_name
from
    sys.dm_os_buffer_descriptors with (nolock)
group by
    db_name(database_id) ,database_id 
order by
    buffer_cache_size desc;

各データベース毎のバッファサイズ

buffer_cache_size database_name
496GB 履歴DB
149GB カートDB

履歴DBで使用されているバッファキャッシュのサイズは496GBでした。履歴データを保持しているテーブルはデータ量が多く、バッファキャッシュを想像以上に使用しており、Standard Editionの制限である128GBの約4倍のサイズでした。バッファキャッシュに載っているデータの中でアクセスの無い余剰なデータがあると考え、調査する方法を検討しました。

  • テスト環境を作成する
    • 本番環境と同等のスペックを持つ周辺システムの構築とワークロードを再現することは現実的には難しい
  • 本番環境のバッファキャッシュサイズに使用上限を設定して影響調査する
    • 履歴DBのバッファキャッシュに限定して使用上限を設定できず、インスタンス全体に影響が波及してしまう

インスタンスを共用しているデメリットが現れてしまいました。残念ながらバッファキャッシュ内の余剰なデータを調査できませんでした。しかし、下記の結果の通りディスクを読み込んでいる処理が存在するのを確認できました。現状、問題になっているわけではありませんが、現時点でキャッシュアウトが発生していることが想定され、余剰なデータはバッファキャッシュに載っていないと判断しました。

物理読み込み発生有無の調査クエリ

select top 1000
     last_logical_reads
    ,last_physical_reads
    ,max_dop
    ,text
from
  sys.dm_exec_query_stats
    outer apply sys.dm_exec_query_plan (plan_handle) as qp
    outer apply sys.dm_exec_sql_text (sql_handle) as sql
where
  qp.dbid = db_id('履歴DB')
  and last_physical_reads > 0
order by
  max_physical_reads desc
  • 実行結果
    • last_physical_reads値が0ではないので、物理読み込みが発生していることが想定できる data_replication

キャッシュアウトが増加した場合に備え、どの程度の性能劣化が起きるのか検証することにしました。まずはメモリを大量に使っているクエリを調査します。

メモリを大量に使用している調査クエリ

select top 1
     last_execution_time 
    ,execution_count
    ,last_elapsed_time
    ,last_logical_reads
    ,last_physical_reads
    ,max_dop
    ,text
from
  sys.dm_exec_query_stats
    outer apply sys.dm_exec_query_plan (plan_handle) as qp
    outer apply sys.dm_exec_sql_text (sql_handle) as sql
where
  qp.dbid = db_id('履歴DB')
order by
  max_logical_reads desc
  • 該当したクエリ
    • 実行時間：11秒
    • last_logical_reads：58GBをデータキャッシュから読み込んでいる
      • 7494590ページx8KB=59956720KB=58GB
    • last_physical_reads：ディスク読み込みは発生していない data_replication

該当クエリをテスト環境で実行し性能差を比較します。キャッシュをクリアし、該当クエリを実行します。

dbcc dropcleanbuffers
  • 実行結果
    • 実行時間：4分22秒
    • 先行読み取り数：8112285
    • 論理読み取り数：8112285

SQL Serverの仕様上physical_readsの値には反映されず、先行と論理の読み取り数が同じ場合、物理I/Oが発生していると判断します。キャッシュクリアをしたので、物理ディスクからの読み込みになっており想定通り実行時間が増加しました。 data_replication

同様のクエリを再度実行します。

  • 実行結果
    • 実行時間：9秒
    • 先行読み取り数：0
    • 論理読み取り数：8112285

今回は100％キャッシュに載っている状態で実行され、本番と同じパフォーマンスで実行されました。バッファキャッシュに載っていればStandard Editionでも性能が出ることを確認できました。 data_replication

以上の調査結果からStandard Editionの制限内でバッファキャッシュを128GBにした場合、キャッシュアウトの増加が予想されます。クエリの性能劣化が想定されるため、メモリを大量に使用しているクエリの改善や業務要件の見直し、性能劣化の許容範囲などを調整していく必要があるという結論に至りました。

必要スペックの調査結果と改善案

現状のまま履歴DBをStandard Editionに分離した場合、著しい性能劣化が想定される結果になりました。分離する前にデータベースを最適化する必要があることを確認し、改善案を検討しました。

CPUコア数

履歴DBのCPU使用率が高い時間帯のCPU時間から計算し、16コアが必要となりました。Standard Editionの制限内に収まりますが、なるべくCPU使用を抑える施策をしておくのがよさそうです。

改善案

  • CPUを非効率に使用しているクエリのチューニング

並列処理の最大限度（MAXDOP）

Standard Editionは使用できるCPUコア数が24のため、MAXDOPを4程度に減らすことを検討する必要があります。クエリの性能劣化は避けられないので対策案を出しました。

対策案

  • MAXDOPを中間値の6に設定できるか検討する
  • 業務要件を精査して性能劣化が許容できないクエリをピックアップ
    • 対象のクエリにのみ「option (maxdop 8)」を指定する

バッファキャッシュサイズ

Standard Editionは使用できるバッファキャッシュサイズが128GBです。現状使用しているバッファキャッシュサイズの1/4程度になるので、キャッシュアウトが増加し、クエリの性能劣化が想定されます。メモリを大量に使用しているクエリを改善する必要があります。

改善案

  • 大量のデータを処理している処理の改修
    • 日時で全件selectをしている処理を改修する
    • メモリ使用が多いクエリのチューニング
  • 列ストアインデックスの検討
    • データを圧縮することで、ストレージの使用量が削減され、データの読み取り性能を向上させる
    • 大量のデータを一括で処理するバッチ処理の性能が向上する

列ストアインデックスについての情報は列ストア インデックス: 概要を参照してください。

改善の余地があるので、上記の各改善案をもとに現状の状態で改善をし、Standard Editionの制限内で分離が実現できるか取り組んでいきたいと思います。

まとめ

データベースの分離を検討した場合、スペックを縮小するのは難しい場合があります。コストや制限を考慮し、設けられた範囲内で分離するために施策を講じる必要があります。リソースを効率的に使用するために、1つのインスタンスに複数のデータベースを構築する場面は珍しくありません。しかし、分離の必要性が出てきた際にスペックを詳細に算出するのが難しいという課題があります。SQL Serverに関してはエディションによって制限があり、限られたリソースの範囲で分離を検討しなければならず、考慮するポイントが多いと感じました。「リソースに空きがある」という理由だけで1つのインスタンスを共用するのはリスクが高いと感じます。長期的な目線でデータベースの運用や障害リスクを考慮し、慎重に検討していくことが必要です。上記の通り、弊社では分離の前にリソースを最適化するという課題が現れました。今回考えた改善や改修案の実施結果については、また別の機会にお話できればと考えています。

おわりに

ZOZOでは、一緒にサービスを作り上げてくれる仲間を募集中です。ご興味のある方は、以下のリンクからぜひご応募ください！

corp.zozo.com

カテゴリー