Database Maintenance
HSYCO stores some of its persistent data, like persistent variables and data loggers data, in an embedded HSQLDB database engine.
The database files are stored in the "data" directory.
Database Backup
If the database backup option is enabled, HSYCO performs a daily hot backup of the database, saving a copy of all data in the "data_backup" directory.
Manually copying or downloading the "data" directory while HSYCO is running is not recommended, because the resulting files could be corrupted and not useable to restore the database.
If the System Monitor I/O Server is enabled, the database.backup.elapsed and database.backup.time data points allow applications to monitor the backup process and eventually alert the system's administrator of potential issues. Check the System Monitor I/O Server documentation for additional information.
Database Backup Consolidation
If the database backup consolidation option is enabled, after the database backup is completed, HSYCO automatically starts a separate sub-process to check the integrity of the backup. Only if the check is successful, the backup files are zipped into a single hsyco.zip file in the data_backup directory.
The main advantage of the consolidation procedure is that, if the integrity check fails, the previous backup files would not be replaced with a corrupted version of the database.
Without backup consolidation, a running database that becomes corrupted could likely result in a corrupted backup (because some forms of corruption would still allow the regular backup to complete successfully), and this backup would overwrite the previous clean backup copy, effectively resulting in loss of data, unless a remote historical copy of the backup is available.
The main disadvantage of the backup consolidation option is that it has to keep multiple copies of the database during the integrity check, so it needs free file system space up to four times the size of the running database. Additionally, it is a processor and I/O intensive procedure.
If the System Monitor I/O Server is enabled, the database.backup.consolidation.elapsed, database.backup.consolidation.status and database.backup.consolidation.time data points allow applications to monitor the backup consolidation and integrity check process. Check the System Monitor I/O Server documentation for additional information.
Database Recovery
When the database recovery option is enabled, if an unrecoverable database error occurs at startup, HSYCO will rename the corrupted database directory to "data_original" and try to recover from last backup, if available.
When both database recovery and database backup consolidation options are enabled, and an integrity failure during the consolidation process is detected, HSYCO will be automatically restarted, in order to recover a clean database from the last consolidated backup.
Database Restore Procedure
It is possible to restore the database from a previous backup.
If HSYCO is running, you can conveniently execute the restore procedure from the File Manager.
Starting from the "data_backup" files you want to restore, rename or copy this directory to a directory named "data_restore_now", then select the hsyco.jar file and click the restart button.
HSYCO will restart and, finding the "data_restore_now" directory, will perform the following database restore process:
- rename the original "data" directory to "data_backup_before_restore". If a directory named "data_backup_before_restore" already exists, a unique number will be appended to the name, preserving the existing directory
- rename the "data_restore_now" to "data"
- start the database engine.
HSYCO should start with the database that was saved in "data_backup", with the last snapshot of the database saved in the "data_backup_before_restore" directory.
Database Compacting and Defragmentation
During normal use, when data is inserted, modified or deleted, the database storage files slowly become fragmented with areas of unused space, and the indexing becomes less optimized.
To avoid performance degradation over time, it is recommended to periodically re-optimize the database.
If the Database Compact procedure is enabled, setting the DatabaseCompactDay, DatabaseCompactHour and DatabaseCompactMinute configuration options, both the space and indexing of the database will be optimized at the scheduled time.
If the Database Compact procedure is not enabled, a defragmentation process that optimizes database space will still be executed automatically every 24 hours. The exact time of day when defragmentation is performed is not configurable, and the process may temporarily degrade the database performance while running. Because of this, if the database is heavily loaded, it is recommended to configure the Database Compact procedure to run at least once a month at a conveniently scheduled time.
If the SYSTEM I/O Server is enabled and the Database Compact procedure is not enabled, two data points are generated to report how long it takes to run the defragmentation (database.defrag.time) and when it was executed (database.defrag.elapsed).