データベーススペシャリスト 2022年 午後1 問02

　専門商社のB社では、見積業務で利用するシステム (以下、見積システムという)の、マスター保守に伴う調査業務を改善中である。 また、見積システムのパブリッククラウドへの移行を計画している。 　

〔パブリッククラウドが提供するサービスの主な仕様〕 1．オブジェクトストレージ 　オブジェクトストレージには、任意のファイルを保存することができる。 RDBMSとは独立して稼働し、RDBMSの障害時にも影響を受けずに、ファイルにアクセスすることができる。 2．RDBMS 　PaaSとして提供されるRDBMSは、インスタンスごとに割り当てられた仮想マシンで稼働する。 　(1) ログ 　　ログはログファイルに記録する。 ログファイルの切替え時に、切替え前に使用していたログファイル(以下、アーカイブログという)を、オブジェクトストレージに保存する。 ログ切替えの時間間隔は、任意に設定することができる。 　(2) バックアップ 　　① データベース全体のフルバックアップを、オブジェクトストレージに保存する。 バックアップは、データベースを停止して、オフラインで取得する。バックアップを取るタイミングは、任意に設定することができる。 　　② オブジェクトストレージに保存したフルバックアップとアーカイブログを使って、データベースを回復することができる。 　(3) レプリケーション 　　ログを使って、RDBMSのデータをほかのRDBMSに複製する。 複製元のテーブルに対する変更操作 (挿入・更新・削除)を複製先のテーブルに自動的に反映する。 　　レプリケーションには、同期型と非同期型がある。 　　① 同期型では、複製先でログをディスクに出力した後、複製元のトランザクションがコミットされる。 　　② 非同期型では、複製先へのログの到達を待たずに、複製元のトランザクションがコミットされる。 　(4) トリガー 　　テーブルに対する変更操作 (挿入・更新・削除)を契機に、あらかじめ定義した処理を実行する。 　　① 実行タイミングを定義することができる。 BEFOREトリガーは、テーブルに対する変更操作の前に実行され、更新中又は挿入中の値を実際の反映前に修正することができる。 AFTERトリガーは、変更操作の後に実行され、ほかのテーブルに対する変更操作を行うことができる。 　　② トリガーを実行する契機となった変更操作を行う前と後の行を参照することができる。 参照するには、操作前と操作後の行に対する相関名をそれぞれ定義し、相関名で列名を修飾する。 　

〔見積システムの概要〕 1．テーブル 主なテーブルのテーブル構造を図1に示す。

2．仕入先への見積依頼業務 　(1) B社の社員は、顧客からの引き合いを受けて、仕入先への見積依頼を入力する。 　　見積依頼番号を採番し、“見積依頼”、“見積依頼明細” テーブルに見積依頼の内容を登録する。 　(2) 仕入先に見積りを依頼し、回答を受け取る。 　(3) 仕入先からの回答を入力する。 対応する見積依頼の見積依頼番号を参照し、“見積回答”、“見積回答明細” テーブルに見積回答の内容を登録する。 商品のモデル名、定価が変更されたことが分かることがある。 この場合、当該商品は、“見積回答明細” テーブルに変更後の内容を登録する。 ただし、“商品” テーブルへの反映は後日行う。 　

〔“商品”テーブルの履歴管理〕 　モデル名又は定価のいずれかが変更されたが、変更が “商品” テーブルへ反映されていない商品を調べるため、図2に示すSQL文を定期的に実行している。

1．“商品” テーブルの設計変更 　“商品” テーブルを更新すると、過去の属性情報は失われてしまう。そこで、商品属性情報の変更を履歴として保存するために、“商品” テーブルの設計変更を行うことにした。 ただし、既存のアプリケーションプログラムには、極力影響を与えないようにする必要がある。 表1に示す2案を検討した結果、案2を採用した。

　案2の実装に当たり、“商品” テーブルへの列の追加、“商品履歴” テーブルの作成、及び主キーの追加を表2のSQL1に示すSQL文で行った。 　また、同一の適用開始日に同一の商品を複数回更新することはない前提で、“商品”テーブルの更新時に行う追加の処理を、表2のSQL2に示すトリガーで実装した。

2．データ移行 　“見積回答”、“見積回答明細” テーブルから “商品”、“商品履歴” テーブルへデータを移行するため、商品のモデル名又は定価のいずれかが変更されたことの履歴を図3のSQL文で調べた。 “見積回答”、“見積回答明細” テーブルの内容を表3, 表4に示す。 SQL文の結果を表5に示す。

　表5の内容を基に、“商品” テーブルを更新、又は “商品履歴” テーブルへ挿入することでデータを移行した。移行前の“商品” テーブルの状態によらず、変更があった全商品を更新した。 また、表2のSQL2に示すトリガーは未定義の状態で行った。

〔基盤設計〕 1．RPO, RTOの見積り 　見積システムをパブリッククラウドに移行した場合の、RDBMSのディスク障害時のRPO及びRTOを、次のように見積もった。 　(1) 利用するパブリッククラウドの仕様に基づいて、データベースのフルバックアップは1日に1回取得し、ログの切替えは5分に1回行い、回復時にはオブジェクトストレージに保存したフルバックアップとアーカイブログを使って回復する、という前提で見積もる。 　(2) RPOは、障害発生時に失われる（ア）に依存するので、最大（イ）分とみなせる。 　(3) RTOのうち、データベースの回復に掛かる時間は、フルバックアップからのリストア時間と、ログを適用するのに掛かる時間の合計である。 　(4) フルバックアップからのリストア時間は、データベース容量が180Gバイト、リストア時のディスク転送速度を100Mバイト/秒と仮定すると（ウ）秒である。ここで,1Gバイトは10^9バイト、1Mバイトは10^6バイトとする。 　(5) ログを適用する期間が最大になるのは、フルバックアップ取得後の経過時間が最大になる24時間である。 ログが毎秒10ページ出力されると仮定すると、適用するログの量は最大（エ）ページである。 ログを適用するのに掛かる時間は、バッファヒット率を0%、同期入出力時間がページ当たり2ミリ秒と仮定すると最大（オ）秒である。

2．参照専用インスタンス 　商品の変更履歴を調べるために実行するSQL文の負荷が大きく、見積システムへの影響が懸念された。 そこで、影響を最小化するために、参照専用インスタンスを本番インスタンスとは別に作成し、調査は参照専用インスタンスで行うことにした。 また、全テーブルについて、本番インスタンスから参照専用インスタンスへ非同期型のレプリケーションを行うことにした。

3．参照専用インスタンスへのフェイルオーバーによる業務継続 　RPO及びRTOを短くするために、本番インスタンスが障害になった場合、参照専用インスタンスにフェイルオーバーして、参照専用インスタンスを使用して業務を継続できるかを検討した。 検討の結果、非同期型のレプリケーションを行う前提だと、参照専用インスタンスでは、本番インスタンスでコミット済みの変更が失われる可能性があることが分かった。