2016 CyberShake database migration

Overview of CyberShake data products

CyberShake is a multi-layered seismic hazard model. The CyberShake system is designed with two primary interfaces to external programs, both of which operate through an MySQL database. The MySQL database schema is maintained in the CyberShake SVN repository. A recent version is posted online. CyberShake has an input interface for UCERF rupture forecasts. OpenSHA uses this interface to populate CyberShake database with fault models and rupture probabilities. Internal CyberShake programs populate additional DB Tables with information about sites, and velocity models, and other hazard model specific information. Then, the CyberShake system run on HPC systems. Much of the CyberShake processing is conducted using scientific workflow tools. The end point of the CyberShake workflow processing are output Database tables, on the CyberShake production database server. These tables represent the CyberShake output interface. The CyberShake output external interface provides study information, site information, hazard curves, and peak amplitude information. Associated output data, not currently in the database, includes SRF files, and seismogram files. These output external data are access through file-based lookups, often depending on metadata encoded in the output file names.

To clarify terminology:

• "Input data": Rupture data, ERF-related data, sites data. This data is shared between studies.
• "Run data": What parameters are used with each run, timestamps, systems, study membership. A run is only part of a single study.
• "Output DB data": Peak amplitudes data, point on hazard curves, frequencies for which hazard curves are calculated.
• "Output External data": SRF files, seismogram files

Current CyberShake Database server hardware and software

• Production CyberShake DB version (focal): Server version: 5.1.73 Source distribution
• Read Only CyberShake DB version (moment): Server version: 10.0.23-MariaDB MariaDB Server

Goals of DB Migration

• Improve performance of production CyberShake runs. Indications are that database write performance is slowing down our CyberShake production runs.
• Separate production data from completed studies to reduce the possibility that new production runs will affect completed studies.
• Provide improved read performance for users of CyberShake external output interface, such as programs that plot CyberShake hazard maps, hazard curves, and peak amplitudes.
• Build CyberShake data access mechanisms and infrastructure that will support planned UGMS CyberShake MCER web site

Status of DB resources following migration

• Production mysql software upgraded to current version of mysql software.
• Production Mysql database server running on upgraded computer hardware.
• Read-only Mysql database server running on adequate computer hardware and storage.
• CyberShake production server contains: 1 database with all input data, the runs and output data for Study 15.12 and 15.4, and the runs and output data for runs which are not associated with any study.
• CyberShake read-only database server: 2 databases: 1 with Study 15.4, and 1 with Study 15.12 data.
• Sqlite v3+ database containing complete copy of all CyberShake database data (as it contained on July 15, 2016)

Detailed Procedure for CyberShake DB Migration

1. Check current size of production CyberShake DB (focal).
2. Confirm storage space on CyberShake production server to confirm that file system has room for dump files.
3. Create a backup of the full database to ensure we can recreate the CyberShake production database if there are problems during the dump procedure.
4. Run mysqldump on entire CyberShake DB on focal using (row, buffering) options. Use mysqldump (sql, delimited) output format.
   1. The proposed mysqldump commands are:
5. Although we are dumping the entire CyberShake DB, we will generate dumpfiles for
   1. all the input data,
   2. each study's output and runs data, and
   3. the runs and output data which is not part of any study.
6. Confirm all Production database dumps completed successfully.

1. Backup read-only database (moment)

1. Upgrade CyberShake Production DB server hardware/OS/Mysql software and required workflow tools, certs, etc
2. Reconfigure DB on CyberShake Production DB Server (single file per table - Please note other specific changes here)
3. Either Load Study 15.12, 15.4, non-study data into CyberShake Production Server using the InnoDB engine ,or
4. Or Load CyberShake Input data into CyberShake production server.
5. Confirm the reload into CyberShake Production Server was successful by running workflows and populating CyberShake production server with new hazard curves using workflows from Blue Waters.

1. Convert older study runs, output data, and all input data from MySQL dump file into SQLite format. Create a different DB for each study.
   1. Conversion done with sqlite insert scripts posted here ....
   2. Mysql non-primary key fields that are autoincrement are converted this way...
   3. Mysql date/time fields that where sub-seconds are important are converted this way...
   4. mysql date/time files where integer time is okay are converted this way....
2. Confirm the reloads into SQLite format were successful with the following acceptance tests:
   1. Study 15.4 hazard curve query and result ...
   2. Study 15.12 hazard curve query and result ..
   3. Other tests

1. Possibly Re-establish Mysql CyberShake read-only database so that ...
2. Load input data, Study 15.12 runs+output data, and Study 15.4 runs+output data onto focal for read-only access, using the MyISAM engine. Each study is in a separate database.

Since the input data is much smaller (~100x) than the output data, we will keep a full copy of it with each study. It would be much more time intensive to identify which subset of input data applies just to the study and the extra space needed to keep it all is trivial. However, for each study, we will only keep the runs data for runs which are associated with that study.