（第2回）オンプレミスRDBでのビッグデータの蓄積【データ量見積り 編】
前回のおさらい(前提条件)
前回、検証を進めるにあたり、以下のような条件を想定しました。
- 製造業の企業
- 全国に10箇所の生産ライン工場を持つ
- 工場は24時間稼働
- 工場で稼働するセンサのデータを収集し、長期的(10～20年)にデータを蓄積し、効率的な稼動を分析するためにデータを利用したい
- 収集するセンサデータの生成頻度は１分とし、センサの数は3000個/1工場
- データは非構造化データ
- 工場のシステムと接続するため、セキュリティ面からオフライン環境（インターネット接続はしない）とする
今回は、これらの条件からどれくらいのデータ量となるかデータ量の見積りをしてみます。
検証環境について
本テーマでは検証するにあたって、RDBSをSQLServer2016 Developer Edition(Enterprise Editionと同等)を使用していきたいと思います。
オンプレミスRDBSはOracle、MySQL、PostgreSQL等・・・様々あるが、私自身がSQLServerを使う機会が多かったことがあり、今回はSQLServerを主軸として検証するが、他DBMSでも同等の機能などがあればピックアップして紹介していきます。
OS
Windows Server2016
DBMS
SQLServer2016 Developer Edition
テーブル構成について
センサデータを格納するテーブルは以下のような構成とします。
[sql]
CREATE TABLE DataTable (
plant_id as varchar(2) NOT NULL, --工場ID
sampling_at as smalldatetime NOT NULL, --取得日時
sensor_name as varchar(15) NOT NULL, --センサ名
valueasdecimal(15,3), --値
CONSTRAINT [DataTablePK] PRIMARYKEY CLUSTERED
(
plant_id,
sampling_at,
sensor_name
)
);
[/sql]
(テーブルイメージ)
plant_id
sampling_at
sensor_name
value
01
2018-12-01 00:00:00
Sensor_0001
12.345
01
2018-12-01 00:01:00
Sensor_0001
13.456
工場IDと時系列とセンサ名をキーにデータを蓄積していきます。
ヒープデータ量の推定
今回は、上記のテーブル構成でデータを蓄積すると考え、データ量がどれほどとなるのかを推定したいと思います。
まずはヒープのデータ量を推定します。
ヒープとは、クラスター化インデックスを使用しないテーブルのことを指します。
クラスター化インデックスを使用しないということは、テーブルに順不同で格納されていることになります。
上記のテーブル構成であればプライマリキーを使用していない状態になります。
つまりインデックス(索引)を含まない単純なデータ量を見積りたいと思います。
1. 想定レコード数
レコード数は、
- 生成頻度が１分
- 24時間稼働
- 10箇所の工場
- センサの数は3000個/1工場
- 少なくとも10年間のデータを蓄積
の条件から、
Num_Rows(レコード数) = ((24 * 60) × (365 × 10)) × (3000 × 10) = 157,680,000,000レコード (約1500億レコード)
2. NULL分の確保領域
各列のNULL分を確保する領域が予約されるため、以下の容量が必要となります。
NULL_Bitmap(NULL予約領域) = 2 + ((列(固定長および可変長)の総数) + 7) / 8)
= 2 + ((4 + 7) / 8) = 3.375
小数部は無視されるので、NULL_Bitmap = 3となります。
3. 行あたりのデータ量
固定長列の合計バイトサイズは以下となります。
plant_id : varchar(2) = 2 + 2 = 4 byte
sampling_at : smalldatetime = 4 byte
sensor_name : varchar(15) = 15 + 2 = 17 byte
Fixed_Data_Size(固定長列の合計バイトサイズ) = 4 + 4 + 17 = 25 byte
decimal型は可変長型なので、最大サイズを考慮して以下となります。
value : decimal(15,3) = 2 + (1(可変長列数) × 2) + 18 = 22 byte
Variable_Data_Size(可変長列の最大バイトサイズ) = 22 byte
Row_Size(1行あたりのデータ量) = Fixed_Data_Size + Variable_Data_Size + NULL_Bitmap + 行ヘッダオーバヘッド(4 byte)
= 25 + 22 + 3 + 4 = 54 byte
4. 1ページあたりの行数
ヒープのデータはページ単位で計算します。
1ページあたりに格納できるデータ量は8096 byteです。
また、行は複数のページにまたがることはできないので、小数以下は切り捨てます。
Row_Per_Page(1ページあたりの行数) = 8096 / (Row_Size + 2)
= 8096 / (54 + 2) = 144.57 = 144行/ページ
5. すべての行を格納するページ数
全データを格納するためのページ数を計算します。
Num_Pages(全ページ数) = Num_Rows / Row_Per_Page
= 157,680,000,000 / 144 = 1,095,000,000 ページ (約10億ページ)
6. ヒープにデータを格納するために必要な領域
1ページ当たりの総バイト数は8192 byteなので、
ヒープのサイズ (バイト) = 8192 × Num_Pages
= 8192 × 1,095,000,000 = 8,970,240,000,000 byte(約9兆byte)
= 8.97 Tbyte
以上で、ヒープのデータ量だけで9テラバイト程度になることがわかりました。
ヒープサイズの見積もりはこちら
データ量だけで9テラバイト程度と推定されたので、ビッグデータと呼べるくらいには多量なデータですね。
このデータ量は、インデックスを含んでいない純粋なデータ量なので、テーブル全体のデータ量はさらに倍近くに大きくなります。
また、チューニング等で非クラスター化インデックスを追加するなどするとさらに増加することが考えられます。
他RDBSでのデータ量推定
上記では、SQLServerでのデータ量を推定しましたが、他RDBSではどうなのか調べてみました。
Oracle
Oracle 8のWhitePaperに見積方法が記載されていましたが、現在のOracleの情報が見つかりませんでした。
内容は変わっていないのでしょうか。
(参考：Oracle WhitePaper 領域サイズの見積もり方法:http://otndnld.oracle.co.jp/deploy/maintenance/pdf/size_est.pdf)
MySQL
MySQLもテーブル列のデータ型で行当たりのデータ量を計算し、InnoDBのページ単位でデータ量を推定できるようです。
InnoDBのページサイズは16kbとSQLServerよりも大きいです。
https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html
PostgreSQL
いくつか推定する方法があるみたいですが、SQL関数を使用して、ディスクの使用量を調査して、推定する方法が簡単みたいですね。
https://www.postgresql.jp/document/10/html/disk-usage.html
おわりに
今回はデータ量を推定して、本テーマで取り扱うビッグデータの規模を見てみました。
クラウドを利用する場合には、ストレージ容量を拡大することは容易に行えますが、オンプレミスではストレージ領域は物理的なストレージの増加や、システムの停止を伴うことがあるため容易ではない場合があります。
そのため、データ量を推定して適切なストレージ容量を確保しておくことが重要となります。