Introduction

ATTENTION: please, make sure you've read the read the parent document to which this one is linked in order to understand the general concepts of the semi-parallel catalog loading protocol which is being utilized here!

This document explains specific steps which had to be taken in a context of loading a family of the SDSS/Stripe82 catalogs into the Qserv instance at NCSA/PDAC. There have been two loading efforts in this context. Each resulted in catalogs stored in a separate database, each listed below:

  • sdss_stripe82_00 : the initial version of the catalogs (see DM-7053) 
  • sdss_stripe82_01 : the improved version of the catalogs which fixes a problem with a hole in an overlap area of the merge between the NCSA and IN2P3 processed parts of the whole dataset (see DM-8241)

All examples shown below were taken from the second loading effort. All loading operations were performed from the master node lsst-qserv-master01 of the PDAC cluster. The rest of this document explains specific commands which had to be executed and methods used for monitoring the results of each stage.

Deploying and configuring the tools

Cloning then from GitHub to he GPFS file system at the head machine lsst-dev of the LSST Verification cluster:

% mkdir -p /datasets/gapon/development/
% cd /datasets/gapon/development/
% git clone https://github.com/lsst-dm/db_pdac_stripe82.git

The next step was to modify the dataset specification file to configuring the number of key parameters used by the loading tools. This included:

  • the path name to a location of the input data on the GPFS file system as it's seen by the master and worker nodes of the cluster
  • the name of the new database
  • the names of all tables which will be loaded

This is how the configuration file looked alike:

% cat /datasets/gapon/development/db_pdac_stripe82/scripts/dataset.bash
...
# Output database and tables

OUTPUT_DB="sdss_stripe82_01"

OUTPUT_OBJECT_TABLE="RunDeepSource"
OUTPUT_SOURCE_TABLE="RunDeepForcedSource"

OUTPUT_NONPART_TABLES="ZZZ_Db_Description LeapSeconds Filter Science_Ccd_Exposure Science_Ccd_Exposure_Metadata Science_Ccd_Exposure_To_Htm10 DeepCoadd DeepCoadd_Metadata DeepCoadd_To_Htm10 Science_Ccd_Exposure_NoFile"

# Data to be loaded into new database

INPUT_DATA_DIR="/datasets/gapon/production/stripe82_01_catalog_load/production_load"

All scripts of which are found in the scripts/ subfolder of the package relies upon these parameters.

Another important file which is not supposed to be modified under normal circumstances contains hard-wired parameters of the PDAC cluster and Qserv. This is the relevant fragment of the file:

% cat /datasets/gapon/development/db_pdac_stripe82/scripts/env.bash
..
# Custom versions of the LSST Stack and the latest version of the Qserv
# management scripts.

BASE_STACK="/datasets/gapon/stack"
QSERV_PKG="/datasets/gapon/development/qserv"

# Qserv deployment parameters (adjust accordingly)

MYSQL_PASSWORD="CHANGEME"

MASTER="lsst-qserv-master01"
SSH_MASTER="qserv-master01"

WORKERS=`seq --format 'lsst-qserv-db%02.0f' 1 30`
SSH_WORKERS=`seq --format 'qserv-db%02.0f' 1 30`

QSERV_MYSQL_DIR="/qserv/data/mysql"
QSERV_DUMPS_DIR="/qserv/data/dumps/$OUTPUT_DB"

# The default location for the log files created on Qserv node

LOCAL_TMP_DIR="/tmp/$OUTPUT_DB"
LOCAL_LOG_DIR="$LOCAL_TMP_DIR/log"


Creating the database

Script:

% /bin/sudo -u qserv /datasets/gapon/development/db_pdac_stripe82/scripts/run_create_database.bash \
--delete \
--verbose \
>& run_create_database.log&

NOTE: the --delete option which will delete the database (and all relevant data) if it existed.

Inspected log files (on the master node):

% ls -al /tmp/sdss_stripe82_01/log
-rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db01_create_database.log
...
-rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db30_create_database.log
-rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-master01_create_database.log

Each log file should report:

deleting database: sdss_stripe82_01
creating database: sdss_stripe82_01
configuring access privileges for database: sdss_stripe82_01


Configuring CSS

This stage requires running two scripts:

% /bin/sudo -u qserv /datasets/gapon/development/db_pdac_stripe82/scripts/setup_css.bash >& setup_css.log
% /bin/sudo -u qserv /datasets/gapon/development/db_pdac_stripe82/scripts/setup_css_nonpart.bash >& setup_css_nonpart.log

Verifying results:

% source /datasets/gapon/stack/loadLSST.bash
% setup -t qserv-dev qserv_distrib; setup -j -r /datasets/gapon/development/qserv
% qserv-admin.py "DUMP EVERYTHING;" | grep sdss_stripe82_01


/DBS/sdss_stripe82_01 READY
/DBS/sdss_stripe82_01/.packed.json {"partitioningId":"0000000026","releaseStatus":"RELEASED","storageClass":"L2"} 
/DBS/sdss_stripe82_01/TABLES \N
/DBS/sdss_stripe82_01/TABLES/DeepCoadd READY
/DBS/sdss_stripe82_01/TABLES/DeepCoadd/schema (
/DBS/sdss_stripe82_01/TABLES/DeepCoadd_Metadata READY
/DBS/sdss_stripe82_01/TABLES/DeepCoadd_Metadata/schema (
/DBS/sdss_stripe82_01/TABLES/DeepCoadd_To_Htm10 READY
/DBS/sdss_stripe82_01/TABLES/DeepCoadd_To_Htm10/schema (
/DBS/sdss_stripe82_01/TABLES/Filter READY
/DBS/sdss_stripe82_01/TABLES/Filter/schema (
/DBS/sdss_stripe82_01/TABLES/LeapSeconds READY
/DBS/sdss_stripe82_01/TABLES/LeapSeconds/schema (
/DBS/sdss_stripe82_01/TABLES/RunDeepForcedSource READY
/DBS/sdss_stripe82_01/TABLES/RunDeepForcedSource/partitioning \N
/DBS/sdss_stripe82_01/TABLES/RunDeepForcedSource/partitioning/.packed.json {"dirColName":"objectId","dirDb":"sdss_stripe82_01","dirTable":"RunDeepSource","latColName":"coord_decl","lonColName":"coord_ra","subChunks":"0"} 
/DBS/sdss_stripe82_01/TABLES/RunDeepForcedSource/schema (
/DBS/sdss_stripe82_01/TABLES/RunDeepForcedSource/sharedScan \N
/DBS/sdss_stripe82_01/TABLES/RunDeepForcedSource/sharedScan/.packed.json {"lockInMem":"1"} 
/DBS/sdss_stripe82_01/TABLES/RunDeepSource READY
/DBS/sdss_stripe82_01/TABLES/RunDeepSource/partitioning \N
/DBS/sdss_stripe82_01/TABLES/RunDeepSource/partitioning/.packed.json {"dirColName":"id","dirDb":"sdss_stripe82_01","dirTable":"RunDeepSource","latColName":"coord_decl","lonColName":"coord_ra","subChunks":"1"} 
/DBS/sdss_stripe82_01/TABLES/RunDeepSource/schema (
/DBS/sdss_stripe82_01/TABLES/RunDeepSource/sharedScan \N
/DBS/sdss_stripe82_01/TABLES/RunDeepSource/sharedScan/.packed.json {"lockInMem":"1"} 
/DBS/sdss_stripe82_01/TABLES/Science_Ccd_Exposure READY
/DBS/sdss_stripe82_01/TABLES/Science_Ccd_Exposure/schema (
/DBS/sdss_stripe82_01/TABLES/Science_Ccd_Exposure_Metadata READY
/DBS/sdss_stripe82_01/TABLES/Science_Ccd_Exposure_Metadata/schema (
/DBS/sdss_stripe82_01/TABLES/Science_Ccd_Exposure_NoFile READY
/DBS/sdss_stripe82_01/TABLES/Science_Ccd_Exposure_NoFile/schema (
/DBS/sdss_stripe82_01/TABLES/Science_Ccd_Exposure_To_Htm10 READY
/DBS/sdss_stripe82_01/TABLES/Science_Ccd_Exposure_To_Htm10/schema (
/DBS/sdss_stripe82_01/TABLES/ZZZ_Db_Description READY
/DBS/sdss_stripe82_01/TABLES/ZZZ_Db_Description/schema (

NOTE: the new CSS entries related to the database sdss_stripe82_01.

Loading the director (RunDeepSource) tables

Script:

% nohup /bin/sudo -u qserv \
  /datasets/gapon/development/db_pdac_stripe82/scripts/run_load_object.bash \
  --verbose \
  >& run_load_object.log&


Inspecting log files after the completion of the operation to make sure all went well (the log files are located on the master node):

% tail /tmp/sdss_stripe82_01/log/qserv-db*_load_object.log | grep 'Finished'
...
[Thu May 18 23:46:04 CDT 2017] ** Finished loading **
[Thu May 18 23:46:00 CDT 2017] ** Finished loading **
...

Also checking dis space utilization for the new database on the worker nodes:

% for worker in `seq --format="qserv-db%02g" 1 30`; do
ssh -n $worker 'echo `hostname`: `/bin/sudo -u qserv du -hs /qserv/data/mysql/sdss_stripe82_01 | grep qserv`'
done
lsst-qserv-db01: 12G /qserv/data/mysql/sdss_stripe82_01
lsst-qserv-db02: 12G /qserv/data/mysql/sdss_stripe82_01
...

Generating and loading the secondary index

This operation has two phases:

  1. harvesting triplets of the (objectId,chunkId,subChunkId) for all object (RunDeepSource) tables access all worker nodes. The results will be stored locally as TSV files (one file per object table) on each worker node
  2. transferring the TSV files from the worker nodes to the master node and loading them into the secondary index table

This scripts does the first stage:

% nohup \
  /bin/sudo -u qserv \
    /datasets/gapon/development/db_pdac_stripe82/scripts/run_dump_triplet.bash \
    --verbose \
    >& run_dump_triplet.log&

The second (loading) step is implemented by this script:

% nohup \
  /bin/sudo -u qserv \
    /datasets/gapon/development/db_pdac_stripe82/scripts/load_secondary_index.bash \
    --verbose \
    --get-remote-triplets \
    >& load_secondary_index.log&

IMPORTANT: option --get-remote-triplets is required to pull TSV files with the previously dumped triplets from the worker nodes. Otherwise a previously cached files will be assumed. And if no files were found in the local (master's cache) then the resulting secondary index table will be empty.

A simple test for the number of triplets after the completion of the file retrieval phase of the index loading operation:

% wc -l /qserv/data/dumps/sdss_stripe82_01/*.tsv
....
186671762 total

This number should also match the number of objects within the RunDeepSource table.

Loading the dependent (RunDeepForcedSource) tables

Script:

% nohup /bin/sudo -u qserv \
  /datasets/gapon/development/db_pdac_stripe82/scripts/run_load_source.bash \
  --verbose \
  >& run_load_source.log&

Inspecting log files after the completion of the operation to make sure all went well (the log files are located on the master node):

% tail /tmp/sdss_stripe82_01/log/qserv-db*_load_source.log | grep Finished
[Sat May 20 11:58:25 CDT 2017] ** Finished loading **
...
[Sat May 20 12:45:45 CDT 2017] ** Finished loading **

Also checking dis space utilization for the new database on the worker nodes:

% for worker in `seq --format="qserv-db%02g" 1 30`; do
    ssh -n $worker 'echo `hostname`: `/bin/sudo -u qserv du -hs /qserv/data/mysql/sdss_stripe82_01 | grep qserv`'
done
lsst-qserv-db01: 325G /qserv/data/mysql/sdss_stripe82_01
lsst-qserv-db02: 325G /qserv/data/mysql/sdss_stripe82_01
...

Loading non-partitioned tables

Running this script from the master node:

% nohup /bin/sudo -u qserv \ 
  /datasets/gapon/development/db_pdac_stripe82/scripts/run_load_nonpart.bash \
  --verbose \
  >& run_load_nonpart.log&

Checking the log files (on master) after the completion of the loading operation:

% tail /tmp/sdss_stripe82_01/log/qserv-db*_load_nonpart.log | grep 'Finished'
[Fri May 19 19:42:48 CDT 2017] ** Finished loading **
...

And the new disk space utilization across all worker nodes:

% for worker in `seq --format="qserv-db%02g" 1 30`; do \
    ssh -n $worker 'echo `hostname`: `/bin/sudo -u qserv du -hs /qserv/data/mysql/sdss_stripe82_01 | grep qserv`' \
done
lsst-qserv-db01: 32G /qserv/data/mysql/sdss_stripe82_01
lsst-qserv-db02: 32G /qserv/data/mysql/sdss_stripe82_01
...

Setting up the empty chunk list (TBC)

Since the new version of the database has the same set of chunks then it's possible to reusing the same empty chunk list file (doing this from the master node):

% /bin/sudo -u qserv ls -l /qserv/data/qserv/ | grep stripe82
-rw-r--r-- 1 qserv qserv 6881330 Nov 2 2016 empty_sdss_stripe82_00.txt

% /bin/sudo -u qserv cp \
/qserv/data/qserv/empty_sdss_stripe82_00.txt \
/qserv/data/qserv/empty_sdss_stripe82_01.txt

% /bin/sudo -u qserv ls -l /qserv/data/qserv/ | grep stripe82
-rw-r--r-- 1 qserv qserv 6881330 Nov 2 2016 empty_sdss_stripe82_00.txt
-rw-r--r-- 1 qserv qserv 6881330 May 19 11:50 empty_sdss_stripe82_01.txt

TODO: explain how to generate the list from scratch.

Enabling the new database in Qserv

This is the last stage of the database loading protocol which needs to be implemented before proceeding to the final tests. The database is enabled in *Qserv* by running this script (from the master node):

% /bin/sudo -u qserv \
  /datasets/gapon/development/db_pdac_stripe82/scripts/run_qserv_enable_database.bash \
  --verbose
  >& run_qserv_enable_database.log&

Testing results

Sizes of tables

Count objects in the secondary index table (via a direct connection to the master's database service):

SELECT COUNT(*) FROM qservMeta.sdss_stripe82_01__RunDeepSource;
+-----------+
| COUNT(*) |
+-----------+
| 186671762 |
+-----------+
1 row in set (1 min 22.79 sec)

Count objects in Qserv (via mysql-proxy):

SELECT COUNT(*) FROM sdss_stripe82_01.RunDeepSource;
+----------------+
| SUM(QS1_COUNT) |
+----------------+
| 186671762 |
+----------------+
1 row in set (4.12 sec)

The number of the forced sources in the database:

SELECT COUNT(*) FROM sdss_stripe82_01.RunDeepForcedSource;
+----------------+
| SUM(QS1_COUNT) |
+----------------+
| 17536560743 |
+----------------+
1 row in set (4.23 sec)

Checking the referential integrity

ATTENTION: this is just a sampling test (not the comprehensive one)!

Between DeepCoadd and RunDeepSource tables:

SELECT deepCoaddId FROM sdss_stripe82_01.DeepCoadd LIMIT 1;
+-------------+
| deepCoaddId |
+-------------+
| 10420224    |
+-------------+

SELECT COUNT(*) FROM sdss_stripe82_01.RunDeepSource WHERE coadd_id=10420224;
+----------------+
| SUM(QS1_COUNT) |
+----------------+
| 4590           |
+----------------+

Try the secondary index optimization for sample object identifiers:

SELECT * FROM qservMeta.sdss_stripe82_01__RunDeepSource LIMIT 10000;
...
| 1398579193184920 | 113663 | 561 |
| 1398579193184921 | 113663 | 559 |
| 1398579193184922 | 113663 | 559 |
| 1398579193184923 | 113663 | 559 |
| 1398579193184924 | 113663 | 563 |
| 1398579193184925 | 113663 | 559 |
| 1398579193184926 | 113663 | 560 |
| 1398579193184927 | 113663 | 561 |
| 1398579193184928 | 113663 | 559 |
+------------------+---------+----+
SELECT id,coord_ra,coord_decl FROM sdss_stripe82_01.RunDeepSource WHERE id=1398579193184928;
+------------------+-------------------+---------------------+
| id               | coord_ra          | coord_decl          |
+------------------+-------------------+---------------------+
| 1398579193184928 | 54.93634014802827 | -1.2062067585765894 |
+------------------+-------------------+---------------------+
SELECT COUNT(*) FROM sdss_stripe82_01.RunDeepForcedSource
WHERE objectId=1398579193184928;
+----------------+
| SUM(QS1_COUNT) |
+----------------+
| 419            |
+----------------+
SELECT id,coord_ra,coord_decl,objectId,object_coord_ra,object_coord_decl
FROM sdss_stripe82_01.RunDeepForcedSource WHERE objectId=1398579193184928 LIMIT 1;
+--------------------+-------------------+---------------------+------------------+-------------------+---------------------+
| id                 | coord_ra          | coord_decl          | objectId         | object_coord_ra   | object_coord_decl   |
+--------------------+-------------------+---------------------+------------------+-------------------+---------------------+
| 127104870174951919 | 54.93634014802832 | -1.2062067585762313 | 1398579193184928 | 54.93634014802827 | -1.2062067585765894 |
+--------------------+-------------------+---------------------+------------------+-------------------+---------------------+

Testing non-partitioned tables

USE sdss_stripe82_01;
SHOW TABLES;
+-------------------------------+
| Tables_in_sdss_stripe82_01    |
+-------------------------------+
| DeepCoadd                     |
| DeepCoadd_Metadata            |
| DeepCoadd_To_Htm10            |
| Filter                        |
| LeapSeconds                   |
| RunDeepForcedSource           |
| RunDeepSource                 |
| Science_Ccd_Exposure          |
| Science_Ccd_Exposure_Metadata |
| Science_Ccd_Exposure_NoFile   |
| Science_Ccd_Exposure_To_Htm10 |
| ZZZ_Db_Description            |
+-------------------------------+


  • No labels