Database Maintenance

Description of Databases

Initially we had a single database, eOVSA06, with many tables holding varioius system monitoring information at a 1-s cadence, tables with scan header information at a several-per-day cadence, and the calibration table (abin) with descriptors and data for various calibration types. However, that database, operating on the SQLserver Windows operating system, has grown too large and unwieldy (7.5 TB as of early 2022), and is impossible to use at other sites. The power outage associated with the Feb 2022 brush fire caused a major scare when the SQLserver machine could not be rebooted. We have largely recovered the database, but it is clearly dangerous to have only the one copy of this critical data.

In response, I have created a new database called eOVSA in the MySQL open standard, which contains only those columns from the 1-s stateframe needed for calibration (much smaller, at about 120 GB as of early 2022), and those columns needed from the scanheader, as well as the entire abin calibration database. This 125 GB database can be easily copied to other sites so that calibration of raw data can be done from anywhere. In concert with this, I have transitioned the EOVSA control and calibration software to Python3, creating two packages: eovsapy (routines that can be run from anywhere, and will form the main package for sharing with the world) and eovsactl (routines that are strictly for control of the EOVSA system and should only be run from the OVRO site). One issue, of course, is that this smaller database has to be kept up to date as more data are taken.

This wiki page is a convenient location for the specific commands needed to create and maintain these additional databases, as well as some documentation of how to back up the core part of the SQLserver (full) database.

SQLserver

The single eOVSA06 database has some disk errors that cause problems when trying to do a full backup, which is quite annoying. However, the way the complex table structure is filled out is to simply transmit binary data to three tables, abin, fbin, and hbin (calibration, stateframe, and scanheader tables, respectively). There are a number of "stored procedures" and "scalar-valued functions" (SPs and SVFs) that parse these binary records into the many other tables (and views) used in the database, but for saving the raw data it is enough to simply save these binary files and the corresponding code for the SPs and SVFs. The latter have been saved on the NAS4 RAID system under the folder /nas4/Gil/SP_and_SVF_code. The table structure has also been saved in the folder /nas4/Gil/Table_Structure. These binary files take about half the space of the full database. I have created a new database named test (not very original) in /nas4/SQL_LOG to which I have copies all of the recoverable abin, hbin, and fbin records through 2022-Feb-16 when the data stopped being recorded to the SQL database. This can serve as an emergency backup, but of course will need to be kept up to date as well.

Saved "Backup" Files

Contents of /nas4/SQL_LOG (backup "test" database containing tables abin, fbin, hbin)

-rwxr-xr-x 1 sched helios     117506048 Mar  3 00:09 eOVSA_xfer_2_GilInNJ
-rwxr-xr-x 1 sched helios  285778903040 Mar 17 00:20 test_log.ldf
-rwxr-xr-x 1 sched helios 3681028145152 Mar 17 00:20 test.mdf

Contents of /nas4/Gil/SP_and_SVF_code (stored procedures and single-valued functions needed to recreate database)

-rwxr-xr-x 1 sched helios   1179 Mar 13 06:13 SPs_AllOfEm.sql
-rwxr-xr-x 1 sched helios 527572 Mar 13 06:11 SPs_AllOfEm.txt
-rwxr-xr-x 1 sched helios   1503 Mar 14 04:46 SVFs_AllOfEm.sql
-rwxr-xr-x 1 sched helios   6173 Mar 14 04:45 SVFs_AllOfEm.txt

Contents of /nas4/Gil/Table_Structure (SQL script for recreating the tables)

-rwxr-xr-x 1 sched helios   8589 Mar 14 16:34 Tables_AllOfEm.sql
-rwxr-xr-x 1 sched helios 234161 Mar 14 16:32 Tables_AllOfEm.txt