XML

Word

Printable

Details

Type: Story
Status: Done
Resolution: Done
Fix Version/s: None
Component/s: None
Labels:
- pdac

Story Points:
6
Epic Link:
S17 Support SUIT for Prototype DAC
Sprint:
DB_S17_2, DB_S17_4, DB_S17_5
Team:
Data Access and Database

Description

Currently, all catalog data for the NCSA/IN2P3 overlap region of the Stripe 82 processing in the PDAC has been taken from the NCSA side of the processing. However, the NCSA side of the processing does not include data for all patches on its "outer" boundary of the overlap region (and same is true for IN2P3 processing with respect to the the opposite overlap boundary).

The catalog merge will need to be redone, with patch division moved away from either of the overlap region boundaries in order to avoid "holes".

Based on a layout of patches (see a PDF document attached by this tickets) the new boundary should be laid between 375 and 376.

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

patch_layout_draft.pdf
157 kB
09/May/17 9:49 AM

Issue Links

is triggered by

DM-7053 Assemble a complete database with S13 DRP catalogs

Done

is triggering

DM-10596 PDAC: merge calexps and coadds for imgserv for database sdss_stripe82_01

Done

relates to

DM-8239 PDAC: objectId 3219370448785419 present in test database, but missing in PDAC Qserv

Done

mentioned in: Page Loading...

Activity

Descending order - Click to sort in ascending order

Hide

Permalink

Igor Gaponenko added a comment - 22/May/17 4:06 PM - edited

The database was merged on the right boundary. According to the tests the earlier reported problem (~~DM-8239~~) of the "hole" in patch=366,0 has been fixed.

Closing this issue.

Show

Igor Gaponenko added a comment - 22/May/17 4:06 PM - edited The database was merged on the right boundary. According to the tests the earlier reported problem ( DM-8239 ) of the "hole" in patch=366,0 has been fixed. Closing this issue.

Hide

Permalink

Igor Gaponenko added a comment - 22/May/17 3:49 PM

Testing if the problem of the mission objects in patch has been fixed

This test is meant to verify if the earlier reported issue (see ~~DM-8239~~) of the "hole" in patch=366,0 has been resolved. And indeed. it has been:

SELECT COUNT(*) FROM RunDeepForcedSource WHERE objectId=3219370448785419;

+----------------+

| SUM(QS1_COUNT) |

+----------------+

|            813 |

+----------------+

1 row in set (0.24 sec)

Show

Igor Gaponenko added a comment - 22/May/17 3:49 PM Testing if the problem of the mission objects in patch has been fixed This test is meant to verify if the earlier reported issue (see DM-8239 ) of the "hole" in patch=366,0 has been resolved. And indeed. it has been: SELECT COUNT (*) FROM RunDeepForcedSource WHERE objectId=3219370448785419; + ----------------+ | SUM (QS1_COUNT) | + ----------------+ | 813 | + ----------------+ 1 row in set (0.24 sec)

Hide

Permalink

Igor Gaponenko added a comment - 22/May/17 3:44 PM

Testing the database

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)

Compare the number of objects with the one found in the previous database:

SELECT COUNT(*) FROM sdss_stripe82_00.RunDeepSource;

+----------------+

| SUM(QS1_COUNT) |

+----------------+

|      186672557 |

+----------------+

The number of the forced sources in the new database comared with the older one:

SELECT COUNT(*) FROM sdss_stripe82_01.RunDeepForcedSource;

+----------------+

| SUM(QS1_COUNT) |

+----------------+

|    17536560743 |

+----------------+

1 row in set (4.23 sec)

SELECT COUNT(*) FROM sdss_stripe82_00.RunDeepForcedSource;

+----------------+

| SUM(QS1_COUNT) |

+----------------+

|    17529678071 |

+----------------+

1 row in set (4.57 sec)

Checking for the referential integrity

SELECT deepCoaddId FROM sdss_stripe82_01.DeepCoadd LIMIT 1;

+-------------+

| deepCoaddId |

+-------------+

|    10420224 |

+-------------+

1 row in set (0.21 sec)

SELECT COUNT(*) FROM sdss_stripe82_01.RunDeepSource WHERE coadd_id=10420224;

+----------------+

| SUM(QS1_COUNT) |

+----------------+

|           4590 |

+----------------+

1 row in set (4.64 sec)

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 |

+------------------+---------+------------+

10000 rows in set (0.02 sec)

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 |

+----------------+

1 row in set (0.26 sec)

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 |

+--------------------+-------------------+---------------------+------------------+-------------------+---------------------+

1 row in set (0.24 sec)

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            |

+-------------------------------+

Show

Igor Gaponenko added a comment - 22/May/17 3:44 PM Testing the database 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) Compare the number of objects with the one found in the previous database: SELECT COUNT (*) FROM sdss_stripe82_00.RunDeepSource; + ----------------+ | SUM (QS1_COUNT) | + ----------------+ | 186672557 | + ----------------+ The number of the forced sources in the new database comared with the older one: SELECT COUNT (*) FROM sdss_stripe82_01.RunDeepForcedSource; + ----------------+ | SUM (QS1_COUNT) | + ----------------+ | 17536560743 | + ----------------+ 1 row in set (4.23 sec) SELECT COUNT (*) FROM sdss_stripe82_00.RunDeepForcedSource; + ----------------+ | SUM (QS1_COUNT) | + ----------------+ | 17529678071 | + ----------------+ 1 row in set (4.57 sec) Checking for the referential integrity SELECT deepCoaddId FROM sdss_stripe82_01.DeepCoadd LIMIT 1; + -------------+ | deepCoaddId | + -------------+ | 10420224 | + -------------+ 1 row in set (0.21 sec) SELECT COUNT (*) FROM sdss_stripe82_01.RunDeepSource WHERE coadd_id=10420224; + ----------------+ | SUM (QS1_COUNT) | + ----------------+ | 4590 | + ----------------+ 1 row in set (4.64 sec) 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 | + ------------------+---------+------------+ 10000 rows in set (0.02 sec) 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 | + ----------------+ 1 row in set (0.26 sec) 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 | + --------------------+-------------------+---------------------+------------------+-------------------+---------------------+ 1 row in set (0.24 sec) 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 | + -------------------------------+

Hide

Permalink

Igor Gaponenko added a comment - 18/May/17 6:40 PM - edited

Loading catalogs into PDAC database sdss_stripe82_01

Using scripts and configurations from:

/datasets/gapon/development/db_pdac_stripe82

All operations are performed from the master node lsst-qserv-master01 of the PDAC cluster.

Set up 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	(

Create the database on all nodes of the cluster

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

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db03_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db04_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db05_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db06_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db07_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db08_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db09_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db10_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db11_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db12_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db13_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db14_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db15_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db16_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db17_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db18_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db19_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db20_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db21_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db22_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db23_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db24_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db25_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db26_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db27_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db28_create_database.log

-rw-r--r-- 1 qserv qserv  133 May 18 20:37 qserv-db29_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

CONCLUSION: all looks normal. Each log file reports:

deleting database: sdss_stripe82_01

creating database: sdss_stripe82_01

configuring access privileges for database: sdss_stripe82_01

Configuring Docker containers

IMPORTANT: this step ensures the containers will have sufficient temporary space to process multi-GB-size TSV files to be loaded into databases via the wmgr Web service.

Check the version of the container running on the nodes. This will be needed to configure the container management scripts before restarting the containers with the desired configuration parameters later on within this thread:

{coe:bash}

% docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
9f684092314d qserv/qserv:tickets_~~DM-10283~~_master "/bin/sh -c /qserv/sc" 3 weeks ago Up 3 weeks qserv

Check which mount points are configured:

{code:bash}

% docker inspect qserv

..

        "Mounts": [

                "Source": "/qserv/data",

                "Destination": "/qserv/data",

                "Mode": "",

                "RW": true,

                "Propagation": "rprivate"

},

                "Source": "/qserv/log",

                "Destination": "/qserv/run/var/log",

                "Mode": "",

                "RW": true,

                "Propagation": "rprivate"

],

..

PROBLEM FOUND: the mount point for /run/tmp is missing. Need to configure it as explained below.

First, edit the environment set up file to ensure the following parameters are set:

% cat/datasets/gapon/development/qserv/admin/tools/docker/deployment/ncsa/env.sh

..

BRANCH=tickets_DM-10283

# Data directory location on docker host, optional

HOST_DATA_DIR=/qserv/data

# Log directory location on docker host, optional

HOST_LOG_DIR=/qserv/log

# Temporary directory location on docker host, optional

HOST_TMP_DIR=/qserv/tmp

Then restart the container with these parameters:

% cd /datasets/gapon/development/qserv/admin/tools/docker/deployment/ncsa/

% ./stop.sh

% ./run.sh

Inspect the containers status:

% docker ps

CONTAINER ID        IMAGE                                 COMMAND                  CREATED             STATUS              PORTS               NAMES

511a591e000d        qserv/qserv:tickets_DM-10283_master   "/bin/sh -c /qserv/sc"   25 seconds ago      Up 22 seconds                           qserv

% docker inspect qserv

..

        "Mounts": [

                "Source": "/qserv/data",

                "Destination": "/qserv/data",

                "Mode": "",

                "RW": true,

                "Propagation": "rprivate"

},

                "Source": "/qserv/log",

                "Destination": "/qserv/run/var/log",

                "Mode": "",

                "RW": true,

                "Propagation": "rprivate"

},

                "Source": "/qserv/tmp",

                "Destination": "/qserv/run/tmp",

                "Mode": "",

                "RW": true,

                "Propagation": "rprivate"

],

..

Bump the number of MySQL connections

This will increased the num_connections limit at all MySQL/MariaDB servers beyond the default number of 512 which may be too low when loading many chunks in parallel.

Test the number of connections and the current limits on each node:

% /bin/sudo -u qserv /datasets/gapon/development/db_pdac_stripe82/scripts/run_show_processlist.bash --total

lsst-qserv-master01: 8 / 512

lsst-qserv-db01: 2 / 512

lsst-qserv-db02: 2 / 512

lsst-qserv-db03: 2 / 512

lsst-qserv-db04: 2 / 512

lsst-qserv-db05: 2 / 512

lsst-qserv-db06: 2 / 512

lsst-qserv-db07: 2 / 512

lsst-qserv-db08: 2 / 512

lsst-qserv-db09: 2 / 512

lsst-qserv-db10: 2 / 512

lsst-qserv-db11: 2 / 512

lsst-qserv-db12: 2 / 512

lsst-qserv-db13: 2 / 512

lsst-qserv-db14: 2 / 512

lsst-qserv-db15: 2 / 512

lsst-qserv-db16: 2 / 512

lsst-qserv-db17: 2 / 512

lsst-qserv-db18: 2 / 512

lsst-qserv-db19: 2 / 512

lsst-qserv-db20: 2 / 512

lsst-qserv-db21: 2 / 512

lsst-qserv-db22: 2 / 512

lsst-qserv-db23: 2 / 512

lsst-qserv-db24: 2 / 512

lsst-qserv-db25: 2 / 512

lsst-qserv-db26: 2 / 512

lsst-qserv-db27: 2 / 512

lsst-qserv-db28: 2 / 512

lsst-qserv-db29: 2 / 512

lsst-qserv-db30: 2 / 512

Bump the limit:

% /bin/sudo -u qserv /datasets/gapon/development/db_pdac_stripe82/scripts/run_set_max_connections.bash

Load partitioned table RunDeepSource

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:45:56 CDT 2017] ** Finished loading **

[Thu May 18 23:45:39 CDT 2017] ** Finished loading **

[Thu May 18 23:45:59 CDT 2017] ** Finished loading **

[Thu May 18 23:46:22 CDT 2017] ** Finished loading **

[Thu May 18 23:46:25 CDT 2017] ** Finished loading **

[Thu May 18 23:45:53 CDT 2017] ** Finished loading **

[Thu May 18 23:47:14 CDT 2017] ** Finished loading **

[Thu May 18 23:45:40 CDT 2017] ** Finished loading **

[Thu May 18 23:46:24 CDT 2017] ** Finished loading **

[Thu May 18 23:45:37 CDT 2017] ** Finished loading **

[Thu May 18 23:45:54 CDT 2017] ** Finished loading **

[Thu May 18 23:45:48 CDT 2017] ** Finished loading **

[Thu May 18 23:45:25 CDT 2017] ** Finished loading **

[Thu May 18 23:45:44 CDT 2017] ** Finished loading **

[Thu May 18 23:45:24 CDT 2017] ** Finished loading **

[Thu May 18 23:48:56 CDT 2017] ** Finished loading **

[Thu May 18 23:46:21 CDT 2017] ** Finished loading **

[Thu May 18 23:46:26 CDT 2017] ** Finished loading **

[Thu May 18 23:46:48 CDT 2017] ** Finished loading **

[Thu May 18 23:46:50 CDT 2017] ** Finished loading **

[Thu May 18 23:46:17 CDT 2017] ** Finished loading **

[Thu May 18 23:47:27 CDT 2017] ** Finished loading **

[Thu May 18 23:46:11 CDT 2017] ** Finished loading **

[Thu May 18 23:45:39 CDT 2017] ** Finished loading **

[Thu May 18 23:46:04 CDT 2017] ** Finished loading **

[Thu May 18 23:46:19 CDT 2017] ** Finished loading **

[Thu May 18 23:46:04 CDT 2017] ** Finished loading **

[Thu May 18 23:46:23 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

lsst-qserv-db03: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db04: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db05: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db06: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db07: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db08: 11G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db09: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db10: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db11: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db12: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db13: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db14: 11G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db15: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db16: 11G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db17: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db18: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db19: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db20: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db21: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db22: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db23: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db24: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db25: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db26: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db27: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db28: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db29: 12G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db30: 12G /qserv/data/mysql/sdss_stripe82_01

Building the secondary index

This operation has two phases:

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

Load partitioned table RunDeepForcedSource

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:22:32 CDT 2017] ** Finished loading **

[Sat May 20 12:20:07 CDT 2017] ** Finished loading **

[Sat May 20 13:10:17 CDT 2017] ** Finished loading **

[Sat May 20 13:07:19 CDT 2017] ** Finished loading **

[Sat May 20 12:46:31 CDT 2017] ** Finished loading **

[Sat May 20 13:20:50 CDT 2017] ** Finished loading **

[Sat May 20 12:36:40 CDT 2017] ** Finished loading **

[Sat May 20 13:00:53 CDT 2017] ** Finished loading **

[Sat May 20 13:17:21 CDT 2017] ** Finished loading **

[Sat May 20 12:03:53 CDT 2017] ** Finished loading **

[Sat May 20 12:29:24 CDT 2017] ** Finished loading **

[Sat May 20 14:17:38 CDT 2017] ** Finished loading **

[Sat May 20 12:22:56 CDT 2017] ** Finished loading **

[Sat May 20 12:47:27 CDT 2017] ** Finished loading **

[Sat May 20 11:48:24 CDT 2017] ** Finished loading **

[Sat May 20 12:51:53 CDT 2017] ** Finished loading **

[Sat May 20 13:03:10 CDT 2017] ** Finished loading **

[Sat May 20 12:25:56 CDT 2017] ** Finished loading **

[Sat May 20 13:42:33 CDT 2017] ** Finished loading **

[Sat May 20 13:08:54 CDT 2017] ** Finished loading **

[Sat May 20 12:55:21 CDT 2017] ** Finished loading **

[Sat May 20 13:25:40 CDT 2017] ** Finished loading **

[Sat May 20 12:22:02 CDT 2017] ** Finished loading **

[Sat May 20 13:00:17 CDT 2017] ** Finished loading **

[Sat May 20 12:07:51 CDT 2017] ** Finished loading **

[Sat May 20 12:21:10 CDT 2017] ** Finished loading **

[Sat May 20 12:46:56 CDT 2017] ** Finished loading **

[Sat May 20 12:55:06 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: 334G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db03: 331G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db04: 339G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db05: 344G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db06: 341G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db07: 346G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db08: 335G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db09: 336G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db10: 352G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db11: 323G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db12: 331G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db13: 342G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db14: 330G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db15: 334G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db16: 318G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db17: 335G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db18: 347G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db19: 334G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db20: 350G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db21: 347G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db22: 342G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db23: 343G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db24: 332G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db25: 332G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db26: 329G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db27: 330G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db28: 345G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db29: 341G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db30: 334G /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 **

[Fri May 19 19:41:06 CDT 2017] ** Finished loading **

[Fri May 19 19:42:02 CDT 2017] ** Finished loading **

[Fri May 19 19:41:54 CDT 2017] ** Finished loading **

[Fri May 19 19:42:45 CDT 2017] ** Finished loading **

[Fri May 19 19:42:10 CDT 2017] ** Finished loading **

[Fri May 19 19:42:14 CDT 2017] ** Finished loading **

[Fri May 19 19:43:03 CDT 2017] ** Finished loading **

[Fri May 19 19:40:32 CDT 2017] ** Finished loading **

[Fri May 19 19:41:18 CDT 2017] ** Finished loading **

[Fri May 19 19:42:40 CDT 2017] ** Finished loading **

[Fri May 19 19:42:20 CDT 2017] ** Finished loading **

[Fri May 19 19:42:47 CDT 2017] ** Finished loading **

[Fri May 19 19:40:02 CDT 2017] ** Finished loading **

[Fri May 19 19:40:41 CDT 2017] ** Finished loading **

[Fri May 19 19:41:58 CDT 2017] ** Finished loading **

[Fri May 19 19:41:56 CDT 2017] ** Finished loading **

[Fri May 19 19:41:34 CDT 2017] ** Finished loading **

[Fri May 19 19:40:36 CDT 2017] ** Finished loading **

[Fri May 19 19:43:17 CDT 2017] ** Finished loading **

[Fri May 19 19:43:10 CDT 2017] ** Finished loading **

[Fri May 19 19:41:37 CDT 2017] ** Finished loading **

[Fri May 19 19:41:47 CDT 2017] ** Finished loading **

[Fri May 19 19:43:11 CDT 2017] ** Finished loading **

[Fri May 19 19:41:31 CDT 2017] ** Finished loading **

[Fri May 19 19:42:13 CDT 2017] ** Finished loading **

[Fri May 19 19:42:01 CDT 2017] ** Finished loading **

[Fri May 19 19:40:23 CDT 2017] ** Finished loading **

[Fri May 19 19:41:56 CDT 2017] ** Finished loading **

[Fri May 19 19:40:38 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

lsst-qserv-db03: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db04: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db05: 33G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db06: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db07: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db08: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db09: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db10: 33G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db11: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db12: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db13: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db14: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db15: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db16: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db17: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db18: 33G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db19: 33G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db20: 33G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db21: 33G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db22: 33G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db23: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db24: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db25: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db26: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db27: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db28: 32G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db29: 33G /qserv/data/mysql/sdss_stripe82_01

lsst-qserv-db30: 32G /qserv/data/mysql/sdss_stripe82_01

Setting up the empty chunk list

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

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&

Restarting Qserv

Show

Igor Gaponenko added a comment - 18/May/17 6:40 PM - edited Loading catalogs into PDAC database sdss_stripe82_01 Using scripts and configurations from: /datasets/gapon/development/db_pdac_stripe82 All operations are performed from the master node lsst-qserv-master01 of the PDAC cluster. Set up 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 ( Create the database on all nodes of the cluster 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-db02_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db03_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db04_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db05_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db06_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db07_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db08_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db09_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db10_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db11_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db12_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db13_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db14_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db15_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db16_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db17_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db18_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db19_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db20_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db21_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db22_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db23_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db24_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db25_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db26_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db27_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db28_create_database.log -rw-r--r-- 1 qserv qserv 133 May 18 20:37 qserv-db29_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 CONCLUSION : all looks normal. Each log file reports: deleting database: sdss_stripe82_01 creating database: sdss_stripe82_01 configuring access privileges for database: sdss_stripe82_01 Configuring Docker containers IMPORTANT : this step ensures the containers will have sufficient temporary space to process multi-GB-size TSV files to be loaded into databases via the wmgr Web service. Check the version of the container running on the nodes. This will be needed to configure the container management scripts before restarting the containers with the desired configuration parameters later on within this thread: {coe:bash} % docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 9f684092314d qserv/qserv:tickets_ DM-10283 _master "/bin/sh -c /qserv/sc" 3 weeks ago Up 3 weeks qserv Check which mount points are configured: {code:bash} % docker inspect qserv .. "Mounts": [ { "Source": "/qserv/data", "Destination": "/qserv/data", "Mode": "", "RW": true, "Propagation": "rprivate" }, { "Source": "/qserv/log", "Destination": "/qserv/run/var/log", "Mode": "", "RW": true, "Propagation": "rprivate" } ], .. PROBLEM FOUND : the mount point for /run/tmp is missing. Need to configure it as explained below. First, edit the environment set up file to ensure the following parameters are set: % cat /datasets/gapon/development/qserv/admin/tools/docker/deployment/ncsa/env .sh .. BRANCH=tickets_DM-10283 # Data directory location on docker host, optional HOST_DATA_DIR= /qserv/data # Log directory location on docker host, optional HOST_LOG_DIR= /qserv/log # Temporary directory location on docker host, optional HOST_TMP_DIR= /qserv/tmp Then restart the container with these parameters: % cd /datasets/gapon/development/qserv/admin/tools/docker/deployment/ncsa/ % . /stop .sh % . /run .sh Inspect the containers status: % docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 511a591e000d qserv /qserv :tickets_DM-10283_master "/bin/sh -c /qserv/sc" 25 seconds ago Up 22 seconds qserv % docker inspect qserv .. "Mounts" : [ { "Source" : "/qserv/data" , "Destination" : "/qserv/data" , "Mode" : "" , "RW" : true , "Propagation" : "rprivate" }, { "Source" : "/qserv/log" , "Destination" : "/qserv/run/var/log" , "Mode" : "" , "RW" : true , "Propagation" : "rprivate" }, { "Source" : "/qserv/tmp" , "Destination" : "/qserv/run/tmp" , "Mode" : "" , "RW" : true , "Propagation" : "rprivate" } ], .. Bump the number of MySQL connections This will increased the num_connections limit at all MySQL/MariaDB servers beyond the default number of 512 which may be too low when loading many chunks in parallel. Test the number of connections and the current limits on each node: % /bin/sudo -u qserv /datasets/gapon/development/db_pdac_stripe82/scripts/run_show_processlist . bash --total lsst-qserv-master01: 8 / 512 lsst-qserv-db01: 2 / 512 lsst-qserv-db02: 2 / 512 lsst-qserv-db03: 2 / 512 lsst-qserv-db04: 2 / 512 lsst-qserv-db05: 2 / 512 lsst-qserv-db06: 2 / 512 lsst-qserv-db07: 2 / 512 lsst-qserv-db08: 2 / 512 lsst-qserv-db09: 2 / 512 lsst-qserv-db10: 2 / 512 lsst-qserv-db11: 2 / 512 lsst-qserv-db12: 2 / 512 lsst-qserv-db13: 2 / 512 lsst-qserv-db14: 2 / 512 lsst-qserv-db15: 2 / 512 lsst-qserv-db16: 2 / 512 lsst-qserv-db17: 2 / 512 lsst-qserv-db18: 2 / 512 lsst-qserv-db19: 2 / 512 lsst-qserv-db20: 2 / 512 lsst-qserv-db21: 2 / 512 lsst-qserv-db22: 2 / 512 lsst-qserv-db23: 2 / 512 lsst-qserv-db24: 2 / 512 lsst-qserv-db25: 2 / 512 lsst-qserv-db26: 2 / 512 lsst-qserv-db27: 2 / 512 lsst-qserv-db28: 2 / 512 lsst-qserv-db29: 2 / 512 lsst-qserv-db30: 2 / 512 Bump the limit: % /bin/sudo -u qserv /datasets/gapon/development/db_pdac_stripe82/scripts/run_set_max_connections . bash Load partitioned table RunDeepSource 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:45:56 CDT 2017] ** Finished loading ** [Thu May 18 23:45:39 CDT 2017] ** Finished loading ** [Thu May 18 23:45:59 CDT 2017] ** Finished loading ** [Thu May 18 23:46:22 CDT 2017] ** Finished loading ** [Thu May 18 23:46:25 CDT 2017] ** Finished loading ** [Thu May 18 23:45:53 CDT 2017] ** Finished loading ** [Thu May 18 23:47:14 CDT 2017] ** Finished loading ** [Thu May 18 23:45:40 CDT 2017] ** Finished loading ** [Thu May 18 23:46:24 CDT 2017] ** Finished loading ** [Thu May 18 23:45:37 CDT 2017] ** Finished loading ** [Thu May 18 23:45:54 CDT 2017] ** Finished loading ** [Thu May 18 23:45:48 CDT 2017] ** Finished loading ** [Thu May 18 23:45:25 CDT 2017] ** Finished loading ** [Thu May 18 23:45:44 CDT 2017] ** Finished loading ** [Thu May 18 23:45:24 CDT 2017] ** Finished loading ** [Thu May 18 23:48:56 CDT 2017] ** Finished loading ** [Thu May 18 23:46:21 CDT 2017] ** Finished loading ** [Thu May 18 23:46:26 CDT 2017] ** Finished loading ** [Thu May 18 23:46:48 CDT 2017] ** Finished loading ** [Thu May 18 23:46:50 CDT 2017] ** Finished loading ** [Thu May 18 23:46:17 CDT 2017] ** Finished loading ** [Thu May 18 23:47:27 CDT 2017] ** Finished loading ** [Thu May 18 23:46:11 CDT 2017] ** Finished loading ** [Thu May 18 23:45:39 CDT 2017] ** Finished loading ** [Thu May 18 23:46:04 CDT 2017] ** Finished loading ** [Thu May 18 23:46:19 CDT 2017] ** Finished loading ** [Thu May 18 23:46:04 CDT 2017] ** Finished loading ** [Thu May 18 23:46:23 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 lsst-qserv-db03: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db04: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db05: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db06: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db07: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db08: 11G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db09: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db10: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db11: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db12: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db13: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db14: 11G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db15: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db16: 11G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db17: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db18: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db19: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db20: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db21: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db22: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db23: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db24: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db25: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db26: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db27: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db28: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db29: 12G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db30: 12G /qserv/data/mysql/sdss_stripe82_01 Building the secondary index This operation has two phases: 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 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. Load partitioned table RunDeepForcedSource 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:22:32 CDT 2017] ** Finished loading ** [Sat May 20 12:20:07 CDT 2017] ** Finished loading ** [Sat May 20 13:10:17 CDT 2017] ** Finished loading ** [Sat May 20 13:07:19 CDT 2017] ** Finished loading ** [Sat May 20 12:46:31 CDT 2017] ** Finished loading ** [Sat May 20 13:20:50 CDT 2017] ** Finished loading ** [Sat May 20 12:36:40 CDT 2017] ** Finished loading ** [Sat May 20 13:00:53 CDT 2017] ** Finished loading ** [Sat May 20 13:17:21 CDT 2017] ** Finished loading ** [Sat May 20 12:03:53 CDT 2017] ** Finished loading ** [Sat May 20 12:29:24 CDT 2017] ** Finished loading ** [Sat May 20 14:17:38 CDT 2017] ** Finished loading ** [Sat May 20 12:22:56 CDT 2017] ** Finished loading ** [Sat May 20 12:47:27 CDT 2017] ** Finished loading ** [Sat May 20 11:48:24 CDT 2017] ** Finished loading ** [Sat May 20 12:51:53 CDT 2017] ** Finished loading ** [Sat May 20 13:03:10 CDT 2017] ** Finished loading ** [Sat May 20 12:25:56 CDT 2017] ** Finished loading ** [Sat May 20 13:42:33 CDT 2017] ** Finished loading ** [Sat May 20 13:08:54 CDT 2017] ** Finished loading ** [Sat May 20 12:55:21 CDT 2017] ** Finished loading ** [Sat May 20 13:25:40 CDT 2017] ** Finished loading ** [Sat May 20 12:22:02 CDT 2017] ** Finished loading ** [Sat May 20 13:00:17 CDT 2017] ** Finished loading ** [Sat May 20 12:07:51 CDT 2017] ** Finished loading ** [Sat May 20 12:21:10 CDT 2017] ** Finished loading ** [Sat May 20 12:46:56 CDT 2017] ** Finished loading ** [Sat May 20 12:55:06 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: 334G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db03: 331G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db04: 339G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db05: 344G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db06: 341G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db07: 346G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db08: 335G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db09: 336G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db10: 352G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db11: 323G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db12: 331G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db13: 342G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db14: 330G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db15: 334G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db16: 318G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db17: 335G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db18: 347G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db19: 334G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db20: 350G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db21: 347G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db22: 342G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db23: 343G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db24: 332G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db25: 332G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db26: 329G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db27: 330G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db28: 345G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db29: 341G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db30: 334G /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 ** [Fri May 19 19:41:06 CDT 2017] ** Finished loading ** [Fri May 19 19:42:02 CDT 2017] ** Finished loading ** [Fri May 19 19:41:54 CDT 2017] ** Finished loading ** [Fri May 19 19:42:45 CDT 2017] ** Finished loading ** [Fri May 19 19:42:10 CDT 2017] ** Finished loading ** [Fri May 19 19:42:14 CDT 2017] ** Finished loading ** [Fri May 19 19:43:03 CDT 2017] ** Finished loading ** [Fri May 19 19:40:32 CDT 2017] ** Finished loading ** [Fri May 19 19:41:18 CDT 2017] ** Finished loading ** [Fri May 19 19:42:40 CDT 2017] ** Finished loading ** [Fri May 19 19:42:20 CDT 2017] ** Finished loading ** [Fri May 19 19:42:47 CDT 2017] ** Finished loading ** [Fri May 19 19:40:02 CDT 2017] ** Finished loading ** [Fri May 19 19:40:41 CDT 2017] ** Finished loading ** [Fri May 19 19:41:58 CDT 2017] ** Finished loading ** [Fri May 19 19:41:56 CDT 2017] ** Finished loading ** [Fri May 19 19:41:34 CDT 2017] ** Finished loading ** [Fri May 19 19:40:36 CDT 2017] ** Finished loading ** [Fri May 19 19:43:17 CDT 2017] ** Finished loading ** [Fri May 19 19:43:10 CDT 2017] ** Finished loading ** [Fri May 19 19:41:37 CDT 2017] ** Finished loading ** [Fri May 19 19:41:47 CDT 2017] ** Finished loading ** [Fri May 19 19:43:11 CDT 2017] ** Finished loading ** [Fri May 19 19:41:31 CDT 2017] ** Finished loading ** [Fri May 19 19:42:13 CDT 2017] ** Finished loading ** [Fri May 19 19:42:01 CDT 2017] ** Finished loading ** [Fri May 19 19:40:23 CDT 2017] ** Finished loading ** [Fri May 19 19:41:56 CDT 2017] ** Finished loading ** [Fri May 19 19:40:38 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 lsst-qserv-db03: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db04: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db05: 33G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db06: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db07: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db08: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db09: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db10: 33G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db11: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db12: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db13: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db14: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db15: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db16: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db17: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db18: 33G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db19: 33G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db20: 33G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db21: 33G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db22: 33G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db23: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db24: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db25: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db26: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db27: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db28: 32G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db29: 33G /qserv/data/mysql/sdss_stripe82_01 lsst-qserv-db30: 32G /qserv/data/mysql/sdss_stripe82_01 Setting up the empty chunk list 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 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& Restarting Qserv

Hide

Permalink

Igor Gaponenko added a comment - 18/May/17 4:11 PM - edited

Preparing the input data folders for the loading

This is going to be an area which is formatted according to expectations of the catalog loading tools found in the GitHub package:

https://github.com/lsst-dm/db_pdac_stripe82

The are is prepared in a way which allows the parallel loading of chunks by scripts simultaneously run on all worker nodes. Note that the very same package also hosts tools which are used by the folder preparation steps explained hereafter.

The folder location is:

/datasets/gapon/production/stripe82_01_catalog_load/production_load

General set up

Create the catalog and its sub-folders:

% mkdir /datasets/gapon/production/stripe82_01_catalog_load/

% cd /datasets/gapon/production/stripe82_01_catalog_load/

% mkdir production_load

Set up the initial map of chunks to workers stored in the previously mentioned GitHub package. This map will be used by the map generator tools:

% ln -s /datasets/gapon/development/db_pdac_stripe82/worker2chunks  .

% wc -l worker2chunks/*

  36 worker2chunks/lsst-qserv-db01

  36 worker2chunks/lsst-qserv-db02

  36 worker2chunks/lsst-qserv-db03

  36 worker2chunks/lsst-qserv-db04

  36 worker2chunks/lsst-qserv-db05

  36 worker2chunks/lsst-qserv-db06

  36 worker2chunks/lsst-qserv-db07

  36 worker2chunks/lsst-qserv-db08

  36 worker2chunks/lsst-qserv-db09

  36 worker2chunks/lsst-qserv-db10

  36 worker2chunks/lsst-qserv-db11

  36 worker2chunks/lsst-qserv-db12

  36 worker2chunks/lsst-qserv-db13

  36 worker2chunks/lsst-qserv-db14

  36 worker2chunks/lsst-qserv-db15

  36 worker2chunks/lsst-qserv-db16

  36 worker2chunks/lsst-qserv-db17

  36 worker2chunks/lsst-qserv-db18

  36 worker2chunks/lsst-qserv-db19

  36 worker2chunks/lsst-qserv-db20

  36 worker2chunks/lsst-qserv-db21

  36 worker2chunks/lsst-qserv-db22

  36 worker2chunks/lsst-qserv-db23

  36 worker2chunks/lsst-qserv-db24

  36 worker2chunks/lsst-qserv-db25

  36 worker2chunks/lsst-qserv-db26

  36 worker2chunks/lsst-qserv-db27

  36 worker2chunks/lsst-qserv-db28

  36 worker2chunks/lsst-qserv-db29

  36 worker2chunks/lsst-qserv-db30

1080 total

RunDeepSource

Create the table-specific folder:

% cd /datasets/gapon/production/stripe82_01_catalog_load/

% mkdir production_load/RunDeepSource

Use the generator tools to populate the folder with the previously partitioned chunks:

% python /datasets/gapon/development/db_pdac_stripe82/tools/RunDeepSource_chunk2worker.py  mkdir \

>& RunDeepSource_chunk2worker_mkdir.source

% source RunDeepSource_chunk2worker_mkdir.source

% ls -1 production_load/RunDeepSource/

lsst-qserv-db01

lsst-qserv-db02

lsst-qserv-db03

lsst-qserv-db04

lsst-qserv-db05

lsst-qserv-db06

lsst-qserv-db07

lsst-qserv-db08

lsst-qserv-db09

lsst-qserv-db10

lsst-qserv-db11

lsst-qserv-db12

lsst-qserv-db13

lsst-qserv-db14

lsst-qserv-db15

lsst-qserv-db16

lsst-qserv-db17

lsst-qserv-db18

lsst-qserv-db19

lsst-qserv-db20

lsst-qserv-db21

lsst-qserv-db22

lsst-qserv-db23

lsst-qserv-db24

lsst-qserv-db25

lsst-qserv-db26

lsst-qserv-db27

lsst-qserv-db28

lsst-qserv-db29

lsst-qserv-db30

% python /datasets/gapon/development/db_pdac_stripe82/tools/RunDeepSource_chunk2worker.py ln \

>& RunDeepSource_chunk2worker_mkdir.source

% source RunDeepSource_chunk2worker_ln.source

% ls -1 production_load/RunDeepSource/lsst-qserv-db01/

chunk_113589_overlap.txt

chunk_113589.txt

chunk_113619_overlap.txt

chunk_113619.txt

chunk_113649_overlap.txt

..

chunk_117638_overlap.txt

chunk_117638.txt

chunk_index.bin

RunDeepForcedSource

Create the table-specific folder:

% cd /datasets/gapon/production/stripe82_01_catalog_load/

% mkdir production_load/RunDeepForcedSource

Use the generator tools to populate the folder with the previously partitioned chunks:

% python /datasets/gapon/development/db_pdac_stripe82/tools/RunDeepForcedSource_chunk2worker.py mkdir \

>& RunDeepForcedSource_chunk2worker_mkdir.source

% source RunDeepForcedSource_chunk2worker_mkdir.source

% ls -1 production_load/RunDeepForcedSource/

lsst-qserv-db01

lsst-qserv-db02

lsst-qserv-db03

lsst-qserv-db04

lsst-qserv-db05

lsst-qserv-db06

lsst-qserv-db07

lsst-qserv-db08

lsst-qserv-db09

lsst-qserv-db10

lsst-qserv-db11

lsst-qserv-db12

lsst-qserv-db13

lsst-qserv-db14

lsst-qserv-db15

lsst-qserv-db16

lsst-qserv-db17

lsst-qserv-db18

lsst-qserv-db19

lsst-qserv-db20

lsst-qserv-db21

lsst-qserv-db22

lsst-qserv-db23

lsst-qserv-db24

lsst-qserv-db25

lsst-qserv-db26

lsst-qserv-db27

lsst-qserv-db28

lsst-qserv-db29

lsst-qserv-db30

% python /datasets/gapon/development/db_pdac_stripe82/tools/RunDeepForcedSource_chunk2worker.py  ln \

>& RunDeepForcedSource_chunk2worker_ln.source

% source RunDeepForcedSource_chunk2worker_ln.source

% ls -1 production_load/RunDeepForcedSource/lsst-qserv-db01

out_daues_SDRP_dedupe_byfilter_0

out_daues_SDRP_dedupe_byfilter_1

out_daues_SDRP_dedupe_byfilter_2

out_daues_SDRP_dedupe_byfilter_3

out_daues_SDRP_dedupe_byfilter_4

out_lsst_prod_dedupe_byfilter_g

out_lsst_prod_dedupe_byfilter_i

out_lsst_prod_dedupe_byfilter_r

out_lsst_prod_dedupe_byfilter_u

out_lsst_prod_dedupe_byfilter_z

% ls -1 production_load/RunDeepForcedSource/lsst-qserv-db01/out_daues_SDRP_dedupe_byfilter_0/

chunk_114178.txt

chunk_114208.txt

chunk_114238.txt

chunk_114858.txt

chunk_114888.txt

chunk_114918.txt

chunk_115538.txt

chunk_115568.txt

chunk_115598.txt

chunk_116218.txt

chunk_116248.txt

chunk_116278.txt

chunk_116898.txt

chunk_116928.txt

chunk_116958.txt

chunk_117578.txt

chunk_117608.txt

chunk_117638.txt

chunk_index.bin

And the total number of partitions matches the expected number:

% ls -1 production_load/RunDeepForcedSource/*/*/ | grep .txt | ort -u | wc -l

Non-partitioned tables

Create the table-specific folder:

% cd /datasets/gapon/production/stripe82_01_catalog_load/

% mkdir production_load/non-part

Show

Igor Gaponenko added a comment - 18/May/17 4:11 PM - edited Preparing the input data folders for the loading This is going to be an area which is formatted according to expectations of the catalog loading tools found in the GitHub package: https://github.com/lsst-dm/db_pdac_stripe82 The are is prepared in a way which allows the parallel loading of chunks by scripts simultaneously run on all worker nodes. Note that the very same package also hosts tools which are used by the folder preparation steps explained hereafter. The folder location is: /datasets/gapon/production/stripe82_01_catalog_load/production_load General set up Create the catalog and its sub-folders: % mkdir /datasets/gapon/production/stripe82_01_catalog_load/ % cd /datasets/gapon/production/stripe82_01_catalog_load/ % mkdir production_load Set up the initial map of chunks to workers stored in the previously mentioned GitHub package. This map will be used by the map generator tools: % ln -s /datasets/gapon/development/db_pdac_stripe82/worker2chunks . % wc -l worker2chunks/* 36 worker2chunks /lsst-qserv-db01 36 worker2chunks /lsst-qserv-db02 36 worker2chunks /lsst-qserv-db03 36 worker2chunks /lsst-qserv-db04 36 worker2chunks /lsst-qserv-db05 36 worker2chunks /lsst-qserv-db06 36 worker2chunks /lsst-qserv-db07 36 worker2chunks /lsst-qserv-db08 36 worker2chunks /lsst-qserv-db09 36 worker2chunks /lsst-qserv-db10 36 worker2chunks /lsst-qserv-db11 36 worker2chunks /lsst-qserv-db12 36 worker2chunks /lsst-qserv-db13 36 worker2chunks /lsst-qserv-db14 36 worker2chunks /lsst-qserv-db15 36 worker2chunks /lsst-qserv-db16 36 worker2chunks /lsst-qserv-db17 36 worker2chunks /lsst-qserv-db18 36 worker2chunks /lsst-qserv-db19 36 worker2chunks /lsst-qserv-db20 36 worker2chunks /lsst-qserv-db21 36 worker2chunks /lsst-qserv-db22 36 worker2chunks /lsst-qserv-db23 36 worker2chunks /lsst-qserv-db24 36 worker2chunks /lsst-qserv-db25 36 worker2chunks /lsst-qserv-db26 36 worker2chunks /lsst-qserv-db27 36 worker2chunks /lsst-qserv-db28 36 worker2chunks /lsst-qserv-db29 36 worker2chunks /lsst-qserv-db30 1080 total RunDeepSource Create the table-specific folder: % cd /datasets/gapon/production/stripe82_01_catalog_load/ % mkdir production_load /RunDeepSource Use the generator tools to populate the folder with the previously partitioned chunks: % python /datasets/gapon/development/db_pdac_stripe82/tools/RunDeepSource_chunk2worker .py mkdir \ >& RunDeepSource_chunk2worker_mkdir. source % source RunDeepSource_chunk2worker_mkdir. source % ls -1 production_load /RunDeepSource/ lsst-qserv-db01 lsst-qserv-db02 lsst-qserv-db03 lsst-qserv-db04 lsst-qserv-db05 lsst-qserv-db06 lsst-qserv-db07 lsst-qserv-db08 lsst-qserv-db09 lsst-qserv-db10 lsst-qserv-db11 lsst-qserv-db12 lsst-qserv-db13 lsst-qserv-db14 lsst-qserv-db15 lsst-qserv-db16 lsst-qserv-db17 lsst-qserv-db18 lsst-qserv-db19 lsst-qserv-db20 lsst-qserv-db21 lsst-qserv-db22 lsst-qserv-db23 lsst-qserv-db24 lsst-qserv-db25 lsst-qserv-db26 lsst-qserv-db27 lsst-qserv-db28 lsst-qserv-db29 lsst-qserv-db30 % python /datasets/gapon/development/db_pdac_stripe82/tools/RunDeepSource_chunk2worker .py ln \ >& RunDeepSource_chunk2worker_mkdir. source % source RunDeepSource_chunk2worker_ln. source % ls -1 production_load /RunDeepSource/lsst-qserv-db01/ chunk_113589_overlap.txt chunk_113589.txt chunk_113619_overlap.txt chunk_113619.txt chunk_113649_overlap.txt .. chunk_117638_overlap.txt chunk_117638.txt chunk_index.bin RunDeepForcedSource Create the table-specific folder: % cd /datasets/gapon/production/stripe82_01_catalog_load/ % mkdir production_load /RunDeepForcedSource Use the generator tools to populate the folder with the previously partitioned chunks: % python /datasets/gapon/development/db_pdac_stripe82/tools/RunDeepForcedSource_chunk2worker .py mkdir \ >& RunDeepForcedSource_chunk2worker_mkdir. source % source RunDeepForcedSource_chunk2worker_mkdir. source % ls -1 production_load /RunDeepForcedSource/ lsst-qserv-db01 lsst-qserv-db02 lsst-qserv-db03 lsst-qserv-db04 lsst-qserv-db05 lsst-qserv-db06 lsst-qserv-db07 lsst-qserv-db08 lsst-qserv-db09 lsst-qserv-db10 lsst-qserv-db11 lsst-qserv-db12 lsst-qserv-db13 lsst-qserv-db14 lsst-qserv-db15 lsst-qserv-db16 lsst-qserv-db17 lsst-qserv-db18 lsst-qserv-db19 lsst-qserv-db20 lsst-qserv-db21 lsst-qserv-db22 lsst-qserv-db23 lsst-qserv-db24 lsst-qserv-db25 lsst-qserv-db26 lsst-qserv-db27 lsst-qserv-db28 lsst-qserv-db29 lsst-qserv-db30 % python /datasets/gapon/development/db_pdac_stripe82/tools/RunDeepForcedSource_chunk2worker .py ln \ >& RunDeepForcedSource_chunk2worker_ln. source % source RunDeepForcedSource_chunk2worker_ln. source % ls -1 production_load /RunDeepForcedSource/lsst-qserv-db01 out_daues_SDRP_dedupe_byfilter_0 out_daues_SDRP_dedupe_byfilter_1 out_daues_SDRP_dedupe_byfilter_2 out_daues_SDRP_dedupe_byfilter_3 out_daues_SDRP_dedupe_byfilter_4 out_lsst_prod_dedupe_byfilter_g out_lsst_prod_dedupe_byfilter_i out_lsst_prod_dedupe_byfilter_r out_lsst_prod_dedupe_byfilter_u out_lsst_prod_dedupe_byfilter_z % ls -1 production_load /RunDeepForcedSource/lsst-qserv-db01/out_daues_SDRP_dedupe_byfilter_0/ chunk_114178.txt chunk_114208.txt chunk_114238.txt chunk_114858.txt chunk_114888.txt chunk_114918.txt chunk_115538.txt chunk_115568.txt chunk_115598.txt chunk_116218.txt chunk_116248.txt chunk_116278.txt chunk_116898.txt chunk_116928.txt chunk_116958.txt chunk_117578.txt chunk_117608.txt chunk_117638.txt chunk_index.bin And the total number of partitions matches the expected number: % ls -1 production_load /RunDeepForcedSource/ */*/ | grep .txt | ort -u | wc -l 1080 Non-partitioned tables Create the table-specific folder: % cd /datasets/gapon/production/stripe82_01_catalog_load/ % mkdir production_load /non-part

9 older comments

People

Assignee:: Igor Gaponenko

Reporter:: Fritz Mueller

Watchers:: Fritz Mueller, Igor Gaponenko

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 09/Nov/16 7:14 PM

Updated:: 23/May/17 3:46 PM

Resolved:: 22/May/17 4:07 PM

Data Management

PDAC: patch "hole" in Stripe 82 catalog data

Details

Description

Attachments

Attachments

Issue Links

Activity

Testing if the problem of the mission objects in patch has been fixed

Testing the database

Sizes of tables

Checking for the referential integrity

Testing non-partitioned tables

Loading catalogs into PDAC database sdss_stripe82_01

Set up CSS

Create the database on all nodes of the cluster

Configuring Docker containers

Bump the number of MySQL connections

Load partitioned table RunDeepSource

Building the secondary index

Load partitioned table RunDeepForcedSource

Loading non-partitioned tables

Setting up the empty chunk list

Enabling the new database in Qserv

Restarting Qserv

Preparing the input data folders for the loading

General set up

RunDeepSource

RunDeepForcedSource

Non-partitioned tables

People

Dates