RDBMSの制約、データ所要量、性能見積に関する話
・以下2つの条件が制約がある時、
- 申請種別は、'1'(立替経費精算), '2'(経費支払依頼)のいずれか
- 支払先は、申請登録時の指定は、経費支払依頼では必須(NOT NULL), 立替経費精算では任意
検査制約は以下のようになる。
CHECK(申請種別='1' OR 申請種別='2' AND 支払先 IS NOT NULL)
この時、意味としては以下のようになるはず。
CHECK(申請種別='1' OR (申請種別='2' AND 支払先 IS NOT NULL))
しかし、括弧は全体を閉じる括弧しか付与しない。一般的な数学の考え方と少し異なるので、注意が必要。
・テーブルのデータ所要量を計算する時、最初に次の3つを確認する。
1. 見積行数(格納する最大のデータ件数)
2. ページサイズ
3. 平均行長
RDBMSが、ストレージとデータの入出力を行う単位を、データページという。
データページの単位のことを、ページサイズと呼ぶ。
平均行長(バイト)とは、平均の行の長さ、つまりテーブルの中の1行(1データ)の容量(バイト)を示す。
計算で求めるとすれば、テーブル定義表の格納長(バイト)の合計値となる。
・1データページ当たりの平均行数(行)
1データページ当たりの平均行数は、計算で求めることが多い。
ページサイズ(1データページの大きさ、4,000バイト等)を、平均行長(1行の大きさ)で割って、求める。
1データページ当たりの平均行数は、以下の計算式で求める。
= ページサイズ(バイト) * (1 - 空き領域率) / 平均行長(バイト)
※小数第1位を切り捨てる。特に問題文に記載は無いが、基本的に整数で記述することにする。
・必要データページ数(ページ)
見積行数が1,500,000行、1データページ当たりの平均行数が15行
1,500,000(行) / 15(行/データページ) = 100,000ページ
・データ所要量
ページサイズ(バイト) * 必要データページ数(ページ)
= 4,000 * 100,000
= 400(MB)
・性能見積の問題では、何の業務、処理に対する性能見積であるかを問題文から必ず判断する必要がある。
今回の場合は、一般経費申請における、性能見積。
つまり、1秒間に200トランザクションを処理する必要がある。
・1トランザクション当たりのI/O時間を求める問題がある。
1データページ当たりのストレージへのI/O時間は20ミリsである。
バッファヒット率とは、ディスクアクセスを行うことなく、バッファキャッシュ内で要求されたブロックが
検出された頻度のこと。
つまり、バッファヒット率が0%の時は、キャッシュからはデータの取得ができず、
全てディスクアクセスで必要なデータを取得する。
バッファヒット率が0%の時、毎回I/Oが発生する形で、1トランザクション当たり、平均50データページにアクセスする。
50(データページ/トランザクション) * 20(ミリs/データページ)
= 1000(ミリs/トランザクション)
= 1(s/トランザクション)
・1トランザクション当たりのCPU処理時間は、10ミリs
1トランザクション = 50データページ
50データページ * 0.6(ヒット率60%) = 30データページ
この30データページは、キャッシュからデータを取得するので、
I/Oは発生しない。I/O時間は発生しない。
残りの20データページはI/Oが発生するので、処理時間を計算する。
問題文にある通り、1データページにかかるI/O処理時間は20ミリsなので、
20(ミリs/データページ) * 20(データページ) = 400ミリs(I/Oに必要な時間)
CPUの1データページ当たりの処理時間は、0.2ミリsである。
0.2(ミリs/データページ) * 50(データページ) = 10ミリs(CPU処理に必要な時間)
※CPU処理は、I/Oは関係無いため、元々の50データページの処理が必要な点に注意。
400 + 10 = 410ミリs
= 0.41s
・バッチ処理の処理件数の問題
1. まず、処理の前提条件を確認する。
- 共通マスタのテーブル参照は、試算の対象にしない。
→当該テーブルの件数のみを対象にすれば良い
- "旅費申請"、"交通費申請"、"一般経費申請"、"経費伝票"の各年月には、
それぞれ処理年月をキーとする索引があるものとする。
→これらのテーブルは、処理年月により、範囲が選択できる。
- 申請数は、各事業部、各科目、各処理年月に均一に分布する。
→処理年月で均等
2. 詳細な処理内容を確認する。
まず、各処理と、処理によって操作されるテーブルを整理する。
- 経費伝票作成
"旅費申請"、"交通費申請"、"一般経費申請"、"旅費申請明細"、
"交通費申請明細"、"経費伝票"
- 会計データ作成
"経費伝票"
- 経費分析表作成
"経費予算"、"経費伝票"
3. テーブルの行数
問題文及び表に記載有。
4. テーブル行数の絞り込みに必要な情報を確認する
前提条件には、以下の記載がある。
"探索対象行数"は、参照する全てのテーブルについて、各テーブルの選択条件に一致する行数の合計とする。
この説明から、探索条件にキーが設定されていて、対象となる行数が絞り込まれているのではないかと考える。
会計データ作成処理では、"経費伝票"テーブルを操作する。
"経費伝票"テーブルの見積行数は6,600,000、探索対象行数は110,000となっている。
6,600,000 / 60 = 110,000
上記の計算(/60)が何らかの理由で実行されているが、その理由は現時点では不明。解説にも記載は無い。
探索対象行数は、参照するテーブルの選択条件に一致する行数の合計、との記載がある。
バッチPGMの"会計データ作成"処理の内容は、"経費伝票"テーブルの処理年月が、当月に一致する1か月分の
行ごとに、会計データ1行を編集して、ファイルに出力する、とある。
上記文章は日本語的におかしいが、以下の意味と考えられる。
"経費伝票"テーブルの内のデータを'処理年月'を1か月毎に集計して、ファイルに出力する。
(12か月分のデータがあれば、12行に集約して、出力)
問題文の[システム概要]、1. 現行システム、③には、以下の記述がある。
60か月前から、現在までのデータが全て保存されている。
つまり、バッチPGMの処理により、会計データは、60行に集約されて、出力される。
・経費伝票作成処理の処理行数を確認する。
「処理年月が当月に一致する行を対象に」という記述から、これらのテーブルの"①保存期間"を確認する。
↑なぜ、'処理年月'ではなく、'保存期間'?
そして'保存期間'という列が存在することをどこから判断できる?
・経費伝票作成処理のサーバ間通信対象行数を求める。
サーバ間通信対象行数は、「処理ごとに、行の参照、追加のために、DBサーバとAPサーバ間で転送される行数とする。」
- DBサーバ上でバッチPGMを実行することができないので、バッチPGMはAPサーバ上で実行する。
- 会計データ作成処理は、110,000件になっている。
- 「専用サーバでは、利用者はサーバにログインできない。このため、バッチPGMの実行、ファイルシステムへのアクセスを行えないが、
Webベースの管理画面を通じて、次の管理機能を利用できる。」
上記3点の情報から、ファイルシステムはAPサーバ側にある、と判断する必要がある。
(正直これは問題文からは判断できない。一般的な業務アプリケーションの仕様から、想定する必要がある。)
結局のところ、「表4 バッチPGMの処理内容」から、以下を判断する必要がある。
- それぞれの処理が、APサーバ、DBサーバ、どちらで処理されるのか。
- それぞれの処理が、どのテーブルをどのくらいの量(行数)処理するのか。
一番簡単な会計データ作成処理の場合は以下の通りである。
"経費伝票"テーブルから、データをSELECT及び集計して、ファイルに出力する。
- "経費伝票"テーブルは当然DBサーバにある。
- 見積行数は60か月分で、660万行なので、1か月分は、11万行。
DBサーバから、APサーバに11万行を転送する。
これは既に問題文に書かれている値と一致する。
次に、経費伝票作成処理について考える。
ちなみに、[クラウドサービスの選定・評価]の1. サービスの選定、の(4)に以下の記述があった。
(4)DBサーバ上でバッチPGMを実行することができないので、バッチPGMはAPサーバ上で実行する。
つまり、現行システムではDBサーバ上でバッチPGMを実行するが、クラウドサービスではそれができないので、
APサーバ上で実行すると言っている。
具体的なサービス名は明記されていないが、おそらくAWS RDS等を想定したシステム構成になっていると考えられる。
・サーバ間通信対象行数を求める。
会計データ作成処理の、結果行数と、サーバ間通信対象行数は同じ値になっている。
110,000。
これは、バッチPGMの処理内容に記載がある通り、"経費伝票"テーブルからデータを取得して、
月毎に集約し、会計データ1行を編集してファイルに出力する、という処理から、
データ抽出(DBサーバ)→ファイル出力(APサーバ)といった流れで、サーバ間通信を
行っていると考えられる。
よって経費伝票作成処理も、同様の手順で考えることができる。
バッチPGMの処理内容から、対象行をバッチPGM中に読み込む、読み込んだ行毎に
"経費伝票"テーブルの各列に値を設定して1行を追加する、という内容から、
110,000 * 2 = 220,000だと考えられる。
・サーバ間通信対象行数を削減するために、ストアドプロシージャを使う。
実行可能なSQL文の処理手続きを予め、データベースに格納し、クライアントからの
命令で実行できるようにしたものをストアドプロシージャと呼ぶ。
利点は主に、以下の2点がある。
ネットワーク負荷の低減：通信回数・通信量が少なくなる。(実行命令と結果のみ)
セキュリティの向上：クライアントから直接データにアクセスできなくなる。
・クラスタリング
検索対象の表に用いられる演算を分析し、演算結果として得られるレコード群を近くの
領域に配置することで、検索速度を高速化する。
・キーレンジ分割方式
大量のデータをテーブルに格納する場合、あらかじめテーブルを構成する特定の属性を
キーとして設定し、そのキー値の範囲によってデータを格納するディスクを決め、分割して
格納することをキーレンジ分割方式と呼ぶ。データ格納先を分割することで、データの検索処理を
並列化し、高速化が期待できる。
