Details
-
Type:
Story
-
Status: Done
-
Resolution: Done
-
Fix Version/s: None
-
Component/s: None
-
Labels:
-
Story Points:100
-
Epic Link:
-
Sprint:DB_S17_2, DB_S17_4, DB_S17_5
-
Team:Data Access and Database
Description
Goals
Download from IPAC, pre-reprocess (if needed), partition and load into PDAC object and forced source catalogs of various WISE missions.
References
Properties of the dataset are documented at:
A list of WISE catalogs to be loaded:
Tools and scripts developed for this efforts are found at the GitHub package:
Attachments
Issue Links
- has to be done before
-
- Done
- is blocked by
-
DM-9672 Query deployment to the cluster hangs with very large number of chunks.
- Done
-
- Won't Fix
-
- Done
- relates to
-
- Done
-
- Done
-
- Done
-
- Done
-
- Done
-
- Done
(5 relates to, 2 mentioned in)
Activity
Schema fixup
As a workaround to a problem explained in DM-9736 the table schema of all instances (and their chunk-specific derivatives) of tables Object and ForcedSource had been modified by replacing dec with decl as column names. This was done in 4 steps explaining below.
Fixing up table schema
scripts/run_fix_schema.bash [-n|--dry-run] |
Updating CSS
Deleting a database entry for CSS entry:
% source /datasets/gapon/stack/loadLSST.bash |
% setup -t qserv-dev qserv_distrib
|
% setup -j -r /datasets/gapon/development/qserv |
% qserv-admin.py "SHOW DATABASES;" |
...
|
wise_00
|
% qserv-admin.py "DROP DATABASE wise_00;" |
Then the schema and configuration files in the GitHub package were modified accordingly (to rename dec into decl as needed).
And finally the new configuration was loaded into CSS with:
scripts/setup_css.bash |
Testing
SELECT source_id,ra,decl FROM wise_00.Object WHERE qserv_areaspec_box(0,0,3,10) LIMIT 10; |
+----------------------+-----------+-----------+ |
| source_id | ra | decl |
|
+----------------------+-----------+-----------+ |
| 0000p015_ac51-041241 | 0.3591511 | 1.8563953 |
|
| 0000p015_ac51-050569 | 0.0401312 | 2.0203582 |
|
| 0000p015_ac51-049853 | 0.1542783 | 2.1076074 |
|
| 0000p015_ac51-038660 | 0.1149200 | 1.8393346 |
|
| 0000p015_ac51-051652 | 0.0792609 | 2.0639651 |
|
| 0000p015_ac51-052063 | 0.1071766 | 2.0572472 |
|
| 0000p015_ac51-026097 | 0.3453069 | 1.6647640 |
|
| 0000p015_ac51-048466 | 0.2439256 | 2.1099024 |
|
| 0000p015_ac51-044164 | 0.4954270 | 1.9122088 |
|
| 0000p015_ac51-038908 | 0.0102313 | 1.8636530 |
|
+----------------------+-----------+-----------+ |
10 rows in set (0.94 sec) |
SELECT source_id_mf,ra,decl FROM wise_00.ForcedSource WHERE qserv_areaspec_box(0,0,3,10) LIMIT 10; |
+----------------------+-----------+-----------+ |
| source_id_mf | ra | decl |
|
+----------------------+-----------+-----------+ |
| 0015p000_ac51-058064 | 1.3726421 | 0.5165190 |
|
| 0015p000_ac51-057359 | 1.5482223 | 0.5079978 |
|
| 0015p000_ac51-057594 | 1.3780350 | 0.4722597 |
|
| 0015p000_ac51-058064 | 1.3726421 | 0.5165190 |
|
| 0015p000_ac51-058064 | 1.3726421 | 0.5165190 |
|
| 0015p000_ac51-057093 | 1.3613006 | 0.5196325 |
|
| 0015p000_ac51-058064 | 1.3726421 | 0.5165190 |
|
| 0015p000_ac51-058064 | 1.3726421 | 0.5165190 |
|
| 0015p000_ac51-058064 | 1.3726421 | 0.5165190 |
|
| 0015p000_ac51-057096 | 1.5480032 | 0.5225815 |
|
+----------------------+-----------+-----------+ |
10 rows in set (4.79 sec) |
Renamed Object, Reject and ForcedSource tables back to the original names
JUSTIFICATION: retaining the original (adopted by the WISE project) naming convention would significantly simplify a documentation and table metadata support efforts for these catalogs on behalf of the LSST/PDAC team. This will also avoid a possible (and unnecessary) confusion which might arise among PDAC users because of the present LSST-way of naming these tables.
The procedure took the following steps
Making proper modifications to the tools, scripts and input data sets
The scripts, configurations and table schemas were updated and saved into GitHub (package):
Renaming the data tables
This was done by simply running on the PDAC master node the "fix-up" script added earlier into the toolset:
% hostname |
lsst-qserv-master01
|
|
|
% /bin/sudo -u qserv \ |
/datasets/gapon/development/db_pdac_wise/scripts/run_rename_tables.bash --verbose |
A similar operation was also performed for the Reject table of Qserv database wise_ext_00 which required a few extra steps of setting up a proper data set specification files (and cleaning up the configuration back to its default state):
% cd /datasets/gapon/development/db_pdac_wise/scripts/ |
% mv dataset.bash dataset.bash.wise_00 |
% mv dataset.bash.wise_ext_00 dataset.bash |
% /bin/sudo -u qserv run_rename_tables.bash --verbose |
% mv dataset.bash dataset.bash.wise_ext_00 |
% mv dataset.bash.wise_00 dataset.bash.wise |
The log files retain on the local file system of the master node has not revealed any problem. The log files were placed at:
% ls -l /tmp/wise_00/log/ |
-rw-r--r-- 1 qserv qserv 3312209 May 4 15:10 qserv-db01_rename_tables.log
|
-rw-r--r-- 1 qserv qserv 3312225 May 4 15:10 qserv-db02_rename_tables.log
|
...
|
-rw-r--r-- 1 qserv qserv 3311567 May 4 15:10 qserv-db29_rename_tables.log
|
-rw-r--r-- 1 qserv qserv 3311567 May 4 15:10 qserv-db30_rename_tables.log
|
-rw-r--r-- 1 qserv qserv 291 May 4 15:07 qserv-master01_rename_tables.log
|
Renaming the secondary index tables
The secondary index tables of both Object and Reject tables were manually renamed by running the following operations directly on the database server of the PDAC master:
MariaDB [qservMeta]> SHOW TABLES;
|
+---------------------------------------+ |
| Tables_in_qservMeta |
|
+---------------------------------------+ |
| wise_00__Object |
|
| wise_ext_00__Reject |
|
+---------------------------------------+ |
|
|
MariaDB [qservMeta]> RENAME TABLE qservMeta.wise_00__Object TO qservMeta.wise_00__allwise_cat; |
MariaDB [qservMeta]> RENAME TABLE qservMeta.wise_ext_00__Reject TO qservMeta.wise_ext_00__allwise_rej; |
|
|
MariaDB [qservMeta]> SHOW TABLES;
|
+---------------------------------------+ |
| Tables_in_qservMeta |
|
+---------------------------------------+ |
| wise_00__allwise_cat |
|
| wise_ext_00__allwise_rej |
|
+---------------------------------------+ |
Making changes to Qserv CSS
The current CSS has:
serv-admin.py "DUMP EVERYTHING" | grep wise_
|
/DBS/wise_00 READY
|
/DBS/wise_00/.packed.json {"partitioningId":"0000000018","releaseStatus":"RELEASED","storageClass":"L2"}
|
/DBS/wise_00/TABLES \N
|
/DBS/wise_00/TABLES/allwise_p3as READY
|
/DBS/wise_00/TABLES/allwise_p3as/schema (
|
/DBS/wise_00/TABLES/ForcedSource READY
|
/DBS/wise_00/TABLES/ForcedSource/partitioning \N
|
/DBS/wise_00/TABLES/ForcedSource/partitioning/.packed.json {"dirColName":"source_id_mf","dirDb":"wise_00","dirTable":"Object","latColName":"decl","lonColName":"ra","subChunks":"0"}
|
/DBS/wise_00/TABLES/ForcedSource/schema (
|
/DBS/wise_00/TABLES/ForcedSource/sharedScan \N
|
/DBS/wise_00/TABLES/ForcedSource/sharedScan/.packed.json {"lockInMem":"1"}
|
/DBS/wise_00/TABLES/Object READY
|
/DBS/wise_00/TABLES/Object/partitioning \N
|
/DBS/wise_00/TABLES/Object/partitioning/.packed.json {"dirColName":"source_id","dirDb":"wise_00","dirTable":"Object","latColName":"decl","lonColName":"ra","subChunks":"1"}
|
/DBS/wise_00/TABLES/Object/schema (
|
/DBS/wise_00/TABLES/Object/sharedScan \N
|
/DBS/wise_00/TABLES/Object/sharedScan/.packed.json {"lockInMem":"1"}
|
/DBS/wise_ext_00 READY
|
/DBS/wise_ext_00/.packed.json {"partitioningId":"0000000020","releaseStatus":"RELEASED","storageClass":"L2"}
|
/DBS/wise_ext_00/TABLES \N
|
/DBS/wise_ext_00/TABLES/Reject READY
|
/DBS/wise_ext_00/TABLES/Reject/partitioning \N
|
/DBS/wise_ext_00/TABLES/Reject/partitioning/.packed.json {"dirColName":"source_id","dirDb":"wise_ext_00","dirTable":"Reject","latColName":"decl","lonColName":"ra","subChunks":"1"}
|
/DBS/wise_ext_00/TABLES/Reject/schema (
|
/DBS/wise_ext_00/TABLES/Reject/sharedScan \N
|
/DBS/wise_ext_00/TABLES/Reject/sharedScan/.packed.json {"lockInMem":"1"}
|
The configuration was cleared with:
qserv-admin.py "DROP DATABASE wise_00"; |
qserv-admin.py "DROP DATABASE wise_ext_00"; |
And the new one reinstalled (for database wise_00 and wise_ext_00) with:
% cd /datasets/gapon/development/db_pdac_wise/scripts/ |
|
|
% /bin/sudo -u qserv setup_css.bash --verbose |
% /bin/sudo -u qserv setup_css_nonpart.bash --verbose |
|
|
% mv dataset.bash dataset.bash.wise_00 |
% mv dataset.bash.wise_ext_00 dataset.bash |
|
|
% /bin/sudo -u qserv setup_css.bash --verbose |
|
|
% mv dataset.bash dataset.bash.wise_ext_00 |
% mv dataset.bash.wise_00 dataset.bash.wise |
After that the new configuration has:
% qserv-admin.py "DUMP EVERYTHING" | grep wise_
|
/DBS/wise_00 READY
|
/DBS/wise_00/.packed.json {"partitioningId":"0000000022","releaseStatus":"RELEASED","storageClass":"L2"}
|
/DBS/wise_00/TABLES \N
|
/DBS/wise_00/TABLES/allwise_cat READY
|
/DBS/wise_00/TABLES/allwise_cat/partitioning \N
|
/DBS/wise_00/TABLES/allwise_cat/partitioning/.packed.json {"dirColName":"source_id","dirDb":"wise_00","dirTable":"allwise_cat","latColName":"decl","lonColName":"ra","subChunks":"1"}
|
/DBS/wise_00/TABLES/allwise_cat/schema (
|
/DBS/wise_00/TABLES/allwise_cat/sharedScan \N
|
/DBS/wise_00/TABLES/allwise_cat/sharedScan/.packed.json {"lockInMem":"1"}
|
/DBS/wise_00/TABLES/allwise_i3as_mep READY
|
/DBS/wise_00/TABLES/allwise_i3as_mep/partitioning \N
|
/DBS/wise_00/TABLES/allwise_i3as_mep/partitioning/.packed.json {"dirColName":"source_id_mf","dirDb":"wise_00","dirTable":"allwise_cat","latColName":"decl","lonColName":"ra","subChunks":"0"}
|
/DBS/wise_00/TABLES/allwise_i3as_mep/schema (
|
`source_id_mf` VARCHAR(28) NOT NULL, -- foreign key to the primary key of the allwise_cat table
|
/DBS/wise_00/TABLES/allwise_i3as_mep/sharedScan \N
|
/DBS/wise_00/TABLES/allwise_i3as_mep/sharedScan/.packed.json {"lockInMem":"1"}
|
/DBS/wise_00/TABLES/allwise_p3as READY
|
/DBS/wise_00/TABLES/allwise_p3as/schema (
|
/DBS/wise_ext_00 READY
|
/DBS/wise_ext_00/.packed.json {"partitioningId":"0000000021","releaseStatus":"RELEASED","storageClass":"L2"}
|
/DBS/wise_ext_00/TABLES \N
|
/DBS/wise_ext_00/TABLES/allwise_rej READY
|
/DBS/wise_ext_00/TABLES/allwise_rej/partitioning \N
|
/DBS/wise_ext_00/TABLES/allwise_rej/partitioning/.packed.json {"dirColName":"source_id","dirDb":"wise_ext_00","dirTable":"allwise_rej","latColName":"decl","lonColName":"ra","subChunks":"1"}
|
/DBS/wise_ext_00/TABLES/allwise_rej/schema (
|
/DBS/wise_ext_00/TABLES/allwise_rej/sharedScan \N
|
/DBS/wise_ext_00/TABLES/allwise_rej/sharedScan/.packed.json {"lockInMem":"1"}
|
Testing
No service restart was needed to enact the changes. All catalogs are now seen with their new names, and simple test queries produce sensible results:
USE wise_00; |
SHOW TABLES;
|
+-------------------+ |
| Tables_in_wise_00 |
|
+-------------------+ |
| allwise_cat |
|
| allwise_i3as_mep |
|
| allwise_p3as |
|
+-------------------+ |
|
|
USE wise_ext_00; |
SHOW TABLES;
|
+-----------------------+ |
| Tables_in_wise_ext_00 |
|
+-----------------------+ |
| allwise_rej |
|
+-----------------------+ |
All catalogs requested in a scope of the current ticket has been loaded and tested. Closing this ticket.
Note that there are 3 more relevant catalogs which will be loaded as a separate effort documented at DM-10740
Testing
Issues
In a course of the initial testing of the dataset blocking 3 issues with Qserv were discovered. The issues were triggered by a scale or table schemas of the dataset.
DM-9672- The cluster hangs with very large number of chunks due to a deadlock in an implementation of the worker componentDM-9706- The WISE object identifiers are strings and this is not supported by the Qserv implementationDM-9736- Double quotes around column names in SQL statements cause a parser error. This prevents users from quoting SQL reserved words like dec (for declination) when making queries against the Object or ForcedSource tables.After discovering these problems a work began to improve Qserv.
Performance
When the first two previously mentioned problems were resolved then a few simple queries were successfully launched against the new dataset in order to test the performance of the Qserv service:
| 747634026 || 41997706856 |The secondary index optimization
The next set of queries tested the secondary index:
| source_id || 0075m016_ac51-028478 || source_id || 0075m016_ac51-028478 || source_id || 0000m016_ac51-000001 || 0075m016_ac51-028478 |These can be compared with the poor performance of the full-table scan query yielding the same result:
| source_id || 0075m016_ac51-028478 |