Sensor Table Size Calculation

Sensor data can accumulate rapidly, especially in environments where multiple sensors are collecting data at high frequencies. Sensor data often varies in structure and content based on type of sensor. Calculating the size of a sensor table helps users in storage estimation. Calculating the size of row involves understanding the typical columns and their potential data types.

Note: The overall size used by sensor tables is totally dependent on how many sensors are activated by the user and the frequency at which those are executed.

Table structure:

Timestamp: Records the time when the sensor reading is taken.
Data column: It varies widely based on the type of sensor and the parameters being used to measure the data.

For example, in case of Chunk Writes sensor, parameters are chunkwrites_10K, chunkwrites_2K. Checkpoint sensor parameters are cp_time, ckpt_logid, physused, logused, etc.
Primary Key: It stores primary key (a few sensors include primary key).

Data types and size:

Timestamp: Type - BIGINT, Size - 8 bytes
Data: BSON documents up to 4 KB are stored in-row.

Type - BSON, Size - 4096 bytes + 4 bytes (4 KB) = 4100 bytes

It takes 4 bytes extra to store BSON data. JSON and BSON documents up to 4 KB are stored in-row. Documents that are greater than 4 KB in size, are stored in the sbspace that is associated with the table, or the default sbspace if the table does not have a designated sbspace. The maximum size of a JSON or BSON document is 32 KB.
Primary Key: Stores character strings of varying length (up to 255 bytes).

Type - VARCHAR, Size - 255 bytes

The maximum length of a VARCHAR column is the defined length (255) plus 1 byte to store the length of the actual value.

Calculate row size using SQL query:

Query to get row size of Checkpoint sensor:

SELECT rowsize FROM systables WHERE tabname = 's_1_checkpoint';
          4108 bytes

Query to get row size of Chunk Writes sensor:

SELECT rowsize FROM systables WHERE tabname = 's_1_chunkwrites’;
          4108 bytes

Note: The table name used to store sensor data is dynamically generated based on InformixHQ database server id.

Estimating row size:

Following example explains row size calculation:

If two sensors are added , "Chunk Writes" and "Checkpoint", for 1 hour (60 min) and the run interval is 15 minutes, database will have 4 records for each sensor after 60 minutes.

Row size calculation without primary key:
```
row size = timestamp 8 bytes + data (4096 bytes + 4 bytes) = 4108 bytes per row
```
Hence, Table size for 1 hr (60 min) = 4 * 4108 = 16432 bytes
Row size calculation with primary key:
```
Row size = timestamp 8 bytes + data (4096 bytes + 4 bytes) + primary key (255 bytes + 1 byte) = 4364 bytes per row
```
Table size for 1 hr (60 min) = 4 * 4364 = 17456 bytes (in case of primary key)

Note: In case of a primary key in the table, row size is 4364 bytes.