7,649
edits
Changes
Jump to navigation
Jump to search
Created page with "{{Project |Has title=Restoring vcdb3 |Has owner=Ed Egan, |Has project status=Complete }} ==Restore the dbase== Run the following: cd /bulk/mcnair/backups su researcher..."
{{Project
|Has title=Restoring vcdb3
|Has owner=Ed Egan,
|Has project status=Complete
}}
==Restore the dbase==
Run the following:
cd /bulk/mcnair/backups
su researcher
createdb vcdb3
pg_restore -Fc -d vcdb3 vcdb3_Fc_230818.dump
cd /bulk
mkdir vcdb3
cd vcdb3
psql vcdb3
==Reload the last changes==
#make vcdb3 in E:\projects
#copy C:\Users\ed\Dropbox\coauthoredprojects\NonMcNair\MatchingVCs\RevisedDbaseCode.sql to e:\projects\vcdb3
#Run that code!
1.6m queries running in <7s
index on 1.6m in <1s
Big table (99m) used to take ~2mins, now takes 2:15. Might want to reoptimize config.
Big reduction used to take 12mins. Now takes 55 secs!
In later queries (not as big), allocated cpu 97-100% but mem only 3.2%
Copy up to 3.6% mem usage. Max observed 4.3%.
'''All of the code ran correctly, with the right counts, etc.'''
==Consolidate code==
Code was in:
E:\mcnair\Projects\VentureXpert Database\vcdb3\LoadingScripts
The following files were copied to E:\projects\vcdb3\OriginalSQL
*Adding GDP.sql
*Agglomeration.SQL - this is the agglomeration work
*CohortPortCoMatch.sql
*GeoLoad.sql
*LoadingScriptsV1.sql - this does the load!
*MatchingEntrepsV3.sql - this is the main build, after the load
*MiscTasks.SQL
*Population.sql
*PortcoMatchMaster.SQL
*RoundRanking.sql - this is the city ranking script
*RoundRankingStates.sql - this is the state ranking script
Note that all code, including ranking code was run before backup.
|Has title=Restoring vcdb3
|Has owner=Ed Egan,
|Has project status=Complete
}}
==Restore the dbase==
Run the following:
cd /bulk/mcnair/backups
su researcher
createdb vcdb3
pg_restore -Fc -d vcdb3 vcdb3_Fc_230818.dump
cd /bulk
mkdir vcdb3
cd vcdb3
psql vcdb3
==Reload the last changes==
#make vcdb3 in E:\projects
#copy C:\Users\ed\Dropbox\coauthoredprojects\NonMcNair\MatchingVCs\RevisedDbaseCode.sql to e:\projects\vcdb3
#Run that code!
1.6m queries running in <7s
index on 1.6m in <1s
Big table (99m) used to take ~2mins, now takes 2:15. Might want to reoptimize config.
Big reduction used to take 12mins. Now takes 55 secs!
In later queries (not as big), allocated cpu 97-100% but mem only 3.2%
Copy up to 3.6% mem usage. Max observed 4.3%.
'''All of the code ran correctly, with the right counts, etc.'''
==Consolidate code==
Code was in:
E:\mcnair\Projects\VentureXpert Database\vcdb3\LoadingScripts
The following files were copied to E:\projects\vcdb3\OriginalSQL
*Adding GDP.sql
*Agglomeration.SQL - this is the agglomeration work
*CohortPortCoMatch.sql
*GeoLoad.sql
*LoadingScriptsV1.sql - this does the load!
*MatchingEntrepsV3.sql - this is the main build, after the load
*MiscTasks.SQL
*Population.sql
*PortcoMatchMaster.SQL
*RoundRanking.sql - this is the city ranking script
*RoundRankingStates.sql - this is the state ranking script
Note that all code, including ranking code was run before backup.
