How much disk space do I need for my SQL database?
It is difficult to make an exact estimation of the required disk space for the SQL database. It depends on various factors – some of them are dynamic and variable.
Since an exact calculation of the required disk space for SQL is not possible, this article just gives some hints for a rough estimation – without any warranty!
In general, only the tables that contain values (trending values, alarm logs, LaMPS test results) are important. Since all other tables contain only definitions, they can be neglected.
Table "trending_value"
Here the following factors are important:
number of data points that are selected for trending
number of data changes of the data points that are selected for trending
trending type (All, Sampling, COV, ...)
required disk space for a single database record
For a rough estimation without any warranty, the following equation can be used for a single trending:
space = changes * age * space_rowspace : required space for a single trending
changes : data changes per day
age: max data age in days
space_row: required space for single data row
Table "alarm_logs"
Here the following factors are important:
number of alarm definitions
number of alarm state changes for all defined alarms
length user comments
required disk space for a single database record
Calculating the required size for alarm logs is more complicated since the occurrence for alarms is normally unpredictable. In addition, the user comments are also stored within SQL and their length is variable. For a single alarm definition, the following equation can be used:
space = changes * age * space_rowspace : required space for a single alarm
changes : alarm state changes per day
age: max data age in days
space_row: required space for single data row - the user comment length is variable and should be considered
Table "lamps_test" and "lamps_test_results"
Here the following factors are important:
number of KNX/DALI gateways and number of used DALI ballasts
number of DALI test executions
required disk space for a single database record
space = number_tests * age * (space_row_test + number_ballasts * space_row_test_result)space: required space for a DALI gateway
number_tests: number of tests per month
age: max data age in months
space_row_test: required space for single data row in table "lamps_test"
space_row_test_result: required space for single data row in table "lamps_test_results"
number_ballasts: number of DALI ballast behind gateway
Disk space for a single database record
table name | MS SQL | MariaDB |
|---|---|---|
trending_value | at least 53 bytes for numeric values (for calculation 250 bytes can be used) <string length> * 2 + (at least 53) for string values (for calculation 250 + <string length> * 2 bytes can be used) | TBD |
alarm_logs | at least 82 bytes (exact value depends on the length of user comments, for empty comment 300 bytes can be used) | TBD |
lamps_test | On average 220 bytes per entry. | On average 220 bytes per entry. |
lamps_test_results | On average 140 bytes per entry. | On average 150bytes per entry. |
Approximate database sizes for trending values
The following values are based on tests.
Setup:
160 trendings of type "Sampling" with interval of two minutes.
Values range from 0 to 100.
Trending time: one year.
Result:
~ 40 million trending values
MariaDB 10.6: ~4.5 gb total database size.
SQL Server 2022 Express edition: ~5.5 gb total database size (Usage of express edition is not recommended for this database size because of performance limitations.)
Approximate database sizes for LaMPS test data
The following values are based on tests.
Setup:
1,000 DALI tests for a single gateway with 64 DALI ballasts
Result:
MariaDB 10.6: ~9.5 MB total database size.
SQL Server 2022 Express edition: 144 MB total database size (lamps_test table: ~0.2 MB, lamps_test_result table: ~ 8.7 MB)