2016 CyberShake database migration - Revision history

Scottcal: /* Detailed Procedure for CyberShake DB Migration */

2018-01-23T22:45:08Z

Detailed Procedure for CyberShake DB Migration

← Older revision		Revision as of 22:45, 23 January 2018
Line 59:		Line 59:
	##Check the row count on all tables.		##Check the row count on all tables.
	##Ask Kevin to create a hazard curve using both the focal DB and the sqlite DB and compare results.		##Ask Kevin to create a hazard curve using both the focal DB and the sqlite DB and compare results.
	# Drop database on focal.		# <s>Drop database on focal.</s>
	# Update focal's software and configuration to be read-only database server, using the InnoDB engine. We originally planned to use the MyISAM engine because the read performance can be better, but Oracle is actually phasing out MyISAM and InnoDB performance continues to improve, so we will use InnoDB.		# <s>Update focal's software and configuration to be read-only database server, using the InnoDB engine. We originally planned to use the MyISAM engine because the read performance can be better, but Oracle is actually phasing out MyISAM and InnoDB performance continues to improve, so we will use InnoDB.</s>
	# Load input data, Study 15.12 runs+output data, Study 15.4 runs+output data, AR* tables, and curves for all runs onto focal for read-only access.		# <s>Load input data, Study 15.12 runs+output data, Study 15.4 runs+output data, AR* tables, and curves for all runs onto focal for read-only access.</s>

	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.		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.

Scottcal: /* Detailed Procedure for CyberShake DB Migration */

2018-01-12T17:10:24Z

Detailed Procedure for CyberShake DB Migration

← Older revision		Revision as of 17:10, 12 January 2018
Line 61:		Line 61:
	# Drop database on focal.		# Drop database on focal.
	# Update focal's software and configuration to be read-only database server, using the InnoDB engine. We originally planned to use the MyISAM engine because the read performance can be better, but Oracle is actually phasing out MyISAM and InnoDB performance continues to improve, so we will use InnoDB.		# Update focal's software and configuration to be read-only database server, using the InnoDB engine. We originally planned to use the MyISAM engine because the read performance can be better, but Oracle is actually phasing out MyISAM and InnoDB performance continues to improve, so we will use InnoDB.
	# Load input data, Study 15.12 runs+output data, Study 15.4 runs+output data, and curves for all runs onto focal for read-only access.		# Load input data, Study 15.12 runs+output data, Study 15.4 runs+output data, AR* tables, and curves for all runs onto focal for read-only access.

	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.		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.

Scottcal: /* Detailed Procedure for CyberShake DB Migration */

2018-01-11T21:47:26Z

Detailed Procedure for CyberShake DB Migration

← Older revision		Revision as of 21:47, 11 January 2018
Line 56:		Line 56:
	## Mysql date/time fields will be converted to integer fields.		## Mysql date/time fields will be converted to integer fields.
	# <s>For each study, load all the insert data, the runs data for that study, and the output data for that study into a DB. </s>		# <s>For each study, load all the insert data, the runs data for that study, and the output data for that study into a DB. </s>
	# Confirm the reloads into SQLite format were successful:		# <s>Confirm the reloads into SQLite format were successful:</s>
	##~~<s>~~Check the row count on all tables.~~</s>~~		##Check the row count on all tables.
	##Ask Kevin to create a hazard curve using both the focal DB and the sqlite DB and compare results.		##Ask Kevin to create a hazard curve using both the focal DB and the sqlite DB and compare results.
	# Drop database on focal.		# Drop database on focal.

Scottcal: /* Detailed Procedure for CyberShake DB Migration */

2018-01-11T21:41:00Z

Detailed Procedure for CyberShake DB Migration

← Older revision		Revision as of 21:41, 11 January 2018
Line 61:		Line 61:
	# Drop database on focal.		# Drop database on focal.
	# Update focal's software and configuration to be read-only database server, using the InnoDB engine. We originally planned to use the MyISAM engine because the read performance can be better, but Oracle is actually phasing out MyISAM and InnoDB performance continues to improve, so we will use InnoDB.		# Update focal's software and configuration to be read-only database server, using the InnoDB engine. We originally planned to use the MyISAM engine because the read performance can be better, but Oracle is actually phasing out MyISAM and InnoDB performance continues to improve, so we will use InnoDB.
	# Load input data, Study 15.12 runs+output data, ~~and~~ Study 15.4 runs+output data onto focal for read-only access~~. Each study is in a separate database~~.		# Load input data, Study 15.12 runs+output data, Study 15.4 runs+output data, and curves for all runs onto focal for read-only access.

	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.		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.

Scottcal: /* Detailed Procedure for CyberShake DB Migration */

2018-01-11T18:03:42Z

Detailed Procedure for CyberShake DB Migration

← Older revision		Revision as of 18:03, 11 January 2018
Line 60:		Line 60:
	##Ask Kevin to create a hazard curve using both the focal DB and the sqlite DB and compare results.		##Ask Kevin to create a hazard curve using both the focal DB and the sqlite DB and compare results.
	# Drop database on focal.		# Drop database on focal.
	# Update focal's software and configuration to be read-only database server, using the MyISAM engine.		# Update focal's software and configuration to be read-only database server, using the InnoDB engine. We originally planned to use the MyISAM engine because the read performance can be better, but Oracle is actually phasing out MyISAM and InnoDB performance continues to improve, so we will use InnoDB.
	# Load input data, Study 15.12 runs+output data, and Study 15.4 runs+output data onto focal for read-only access. Each study is in a separate database.		# Load input data, Study 15.12 runs+output data, and Study 15.4 runs+output data onto focal for read-only access. 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.		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.

Scottcal: /* Detailed Procedure for CyberShake DB Migration */

2018-01-11T17:59:52Z

Detailed Procedure for CyberShake DB Migration

← Older revision		Revision as of 17:59, 11 January 2018
Line 52:		Line 52:
	# <s>Confirm the reload on the production server has the correct number of rows in each table, as compared to the focal DB.</s>		# <s>Confirm the reload on the production server has the correct number of rows in each table, as compared to the focal DB.</s>
	# <s> 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. </s>		# <s> 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. </s>
	# Convert older study runs, output data, and all input data from MySQL dump file into SQLite format.		# <s>Convert older study runs, output data, and all input data from MySQL dump file into SQLite format.</s>
	## Conversion done with sqlite insert scripts at https://github.com/dumblob/mysql2sqlite .		## Conversion done with sqlite insert scripts at https://github.com/dumblob/mysql2sqlite .
	## Mysql date/time fields will be converted to integer fields.		## Mysql date/time fields will be converted to integer fields.

Scottcal: /* Detailed Procedure for CyberShake DB Migration */

2017-10-05T20:38:10Z

Detailed Procedure for CyberShake DB Migration

← Older revision		Revision as of 20:38, 5 October 2017
Line 55:		Line 55:
	## Conversion done with sqlite insert scripts at https://github.com/dumblob/mysql2sqlite .		## Conversion done with sqlite insert scripts at https://github.com/dumblob/mysql2sqlite .
	## Mysql date/time fields will be converted to integer fields.		## Mysql date/time fields will be converted to integer fields.
	# For each study, load all the insert data, the runs data for that study, and the output data for that study into a DB.		# <s>For each study, load all the insert data, the runs data for that study, and the output data for that study into a DB. </s>
	# Confirm the reloads into SQLite format were successful:		# Confirm the reloads into SQLite format were successful:
	##Check the row count on all tables.		##<s>Check the row count on all tables.</s>
	##Ask Kevin to create a hazard curve using both the focal DB and the sqlite DB and compare results.		##Ask Kevin to create a hazard curve using both the focal DB and the sqlite DB and compare results.
	# Drop database on focal.		# Drop database on focal.

Scottcal at 16:32, 6 October 2016

2016-10-06T16:32:46Z

← Older revision		Revision as of 16:32, 6 October 2016
Line 51:		Line 51:
	# <s>Add new index to speed up count queries, on (Run_ID/IM_Type_ID) in PeakAmplitudes table on production server.</s>		# <s>Add new index to speed up count queries, on (Run_ID/IM_Type_ID) in PeakAmplitudes table on production server.</s>
	# <s>Confirm the reload on the production server has the correct number of rows in each table, as compared to the focal DB.</s>		# <s>Confirm the reload on the production server has the correct number of rows in each table, as compared to the focal DB.</s>
	# 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.		# <s> 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. </s>
	# Convert older study runs, output data, and all input data from MySQL dump file into SQLite format.		# Convert older study runs, output data, and all input data from MySQL dump file into SQLite format.
	## Conversion done with sqlite insert scripts at https://github.com/dumblob/mysql2sqlite .		## Conversion done with sqlite insert scripts at https://github.com/dumblob/mysql2sqlite .

Scottcal: /* Detailed Procedure for CyberShake DB Migration */

2016-08-22T19:15:00Z

Detailed Procedure for CyberShake DB Migration

← Older revision		Revision as of 19:15, 22 August 2016
Line 29:		Line 29:

	== Detailed Procedure for CyberShake DB Migration ==		== Detailed Procedure for CyberShake DB Migration ==
	# <s>Back up read-only ~~and production databases~~</s>		# <s>Back up read-only</s> and production databases
	# <s>Upgrade CyberShake Production DB server OS (FC 24) and MariaDB (10.1.16) software </s>		# <s>Upgrade CyberShake Production DB server OS (FC 24) and MariaDB (10.1.16) software </s>
	# <s>Reconfigure DB on CyberShake Production DB Server:</s>		# <s>Reconfigure DB on CyberShake Production DB Server:</s>

Scottcal: /* Detailed Procedure for CyberShake DB Migration */

2016-08-22T19:13:40Z

Detailed Procedure for CyberShake DB Migration

← Older revision		Revision as of 19:13, 22 August 2016
Line 48:		Line 48:
	###the runs and output data which is not part of any study.		###the runs and output data which is not part of any study.
	## Create dump files on a USB3 drive for easier migration.		## Create dump files on a USB3 drive for easier migration.
	# Load Study 15.12 and non-study data into CyberShake Production Server using the InnoDB engine.		# <s>Load Study 15.12 and non-study data into CyberShake Production Server using the InnoDB engine.</s>
	# Add new index to speed up count queries, on (Run_ID/IM_Type_ID) in PeakAmplitudes table on production server.		# <s>Add new index to speed up count queries, on (Run_ID/IM_Type_ID) in PeakAmplitudes table on production server.</s>
	# Confirm the reload on the production server has the correct number of rows in each table, as compared to the focal DB.		# <s>Confirm the reload on the production server has the correct number of rows in each table, as compared to the focal DB.</s>
	# 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.		# 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.
	# Convert older study runs, output data, and all input data from MySQL dump file into SQLite format.		# Convert older study runs, output data, and all input data from MySQL dump file into SQLite format.