Database Maintenance: Difference between revisions
Line 10: | Line 10: | ||
== Saved "Backup" Files == | == Saved "Backup" Files == | ||
Contents of /nas4/SQL_LOG (backup "test" database containing tables abin, fbin, hbin) | Contents of '''/nas4/SQL_LOG''' (backup "test" database containing tables abin, fbin, hbin) | ||
<pre> | <pre> | ||
-rwxr-xr-x 1 sched helios 117506048 Mar 3 00:09 eOVSA_xfer_2_GilInNJ | -rwxr-xr-x 1 sched helios 117506048 Mar 3 00:09 eOVSA_xfer_2_GilInNJ | ||
Line 17: | Line 17: | ||
</pre> | </pre> | ||
Contents of /nas4/Gil/SP_and_SVF_code (stored procedures and single-valued functions needed to recreate database) | Contents of '''/nas4/Gil/SP_and_SVF_code''' (stored procedures and single-valued functions needed to recreate database) | ||
<pre> | <pre> | ||
-rwxr-xr-x 1 sched helios 1179 Mar 13 06:13 SPs_AllOfEm.sql | -rwxr-xr-x 1 sched helios 1179 Mar 13 06:13 SPs_AllOfEm.sql | ||
Line 25: | Line 25: | ||
</pre> | </pre> | ||
Contents of /nas4/Gil/Table_Structure (SQL script for recreating the tables) | Contents of '''/nas4/Gil/Table_Structure''' (SQL script for recreating the tables) | ||
<pre> | <pre> | ||
-rwxr-xr-x 1 sched helios 8589 Mar 14 16:34 Tables_AllOfEm.sql | -rwxr-xr-x 1 sched helios 8589 Mar 14 16:34 Tables_AllOfEm.sql |
Revision as of 12:41, 4 April 2022
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