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