XML

Word

Printable

Details

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

Story Points:
12
Epic Link:
F17 Support SUIT for PDAC
Sprint:
DB_S17_5, DB_S17_6, DB_S17_7, DB_S17_8, DB_S17_9, DB_S17_10
Team:
Data Access and Database

Description

This ticket documents data preparation and loading for the following WISE catalogs:

Catalog name	PDAC database	PDAC table	IRSA download page
WISE All-Sky Single Exposure (L1b) Source Table	wise_4band_00	allsky_4band_p1bs_psd	http://irsa.ipac.caltech.edu/data/download/wise-4band-psd/
WISE 3-Band Cryo Single Exposure (L1b) Source Table	wise_3band_00	allsky_3band_p1bs_psd	http://irsa.ipac.caltech.edu/data/download/wise-3band-psd/
WISE Post-Cryo Single Exposure (L1b) Source Table	wise_2band_00	allsky_2band_p1bs_psd	http://irsa.ipac.caltech.edu/data/download/wise-2band-psd/

Attachments

Issue Links

is triggering

DM-10988 Evaluating benefits of SSD and NVMe storage technologies for Qserv "secondary index"

Done

DM-11027 Investigate options for speeding up data ingestion into Qserv "secondary index"

Done

relates to

DM-9736 Quotes around column names in SQL statements cause a parser error.

Won't Fix

DM-9372 Load WISE catalog data in PDAC

Done

DM-12523 Load NEOWISE-R Year 1 Single Exposure (L1b) Source Table into PDAC

Done

DM-12910 Summary of WISE catalogs loaded into PDAC in 2017

Done

mentioned in: Page Loading...; Page Loading...

(1 relates to, 2 mentioned in)

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Igor Gaponenko added a comment - 02/Jun/17 3:35 PM - edited

Downloading WISE All-Sky Single Exposure (L1b) Source Table data

Table name	allsky_4band_p1bs_psd
Temporary data folder (NCSA)	/datasets/gapon/wise/allsky_4band_p1bs_psd/
Catalog data (IPAC)	https://irsa.ipac.caltech.edu/data/download/wise-4band-psd/
Schema file (IPAC)	https://irsa.ipac.caltech.edu/data/download/wise-4band-psd/wise_allsky_4band_p1bs_psd-schema.txt

Catalog schema

The original schema file was downloaded as:

/datasets/gapon/wise/allsky_4band_p1bs_psd/allsky_4band_p1bs_psd.txt

Then it was translated into MySQL schema definition (SQL DDL) file using this tool (available in GitHub package txt2schema.py):

% python \

  /datasets/gapon/development/db_pdac_wise/tools//txt2schema.py \

    allsky_4band_p1bs_psd.txt \

    > allsky_4band_p1bs_psd.sql

Made two modifications to thw original schema:

renaming column dec into decl as a workaround to an existing limitation of the Qserv SQL parser (see )
manual addition of the PRIMARY key

The relevant sections of the final schema file are presented below:

CREATE TABLE `allsky_4band_p1bs_psd` (

         `decl`  DECIMAL(9,7)  DEFAULT NULL,

    `source_id`  CHAR(16)      DEFAULT NULL,

    PRIMARY KEY(`source_id`)

) ENGINE=MyISAM;

Saved the schema file into the corresponding GitHub package at:

https://github.com/lsst-dm/db_pdac_wise/blob/master/sql/allsky_4band_p1bs_psd.sql

Downloaded files into the temporary location at NCSA

The files were placed at the GPFS folder:

/datasets/gapon/wise/allsky_4band_p1bs_psd/downloaded/

The operation succeeded with:

2304 compressed files (bz2)
2.5 TB of total data amount

Show

Igor Gaponenko added a comment - 02/Jun/17 3:35 PM - edited Downloading WISE All-Sky Single Exposure (L1b) Source Table data Table name allsky_4band_p1bs_psd Temporary data folder (NCSA) /datasets/gapon/wise/allsky_4band_p1bs_psd/ Catalog data (IPAC) https://irsa.ipac.caltech.edu/data/download/wise-4band-psd/ Schema file (IPAC) https://irsa.ipac.caltech.edu/data/download/wise-4band-psd/wise_allsky_4band_p1bs_psd-schema.txt Catalog schema The original schema file was downloaded as: /datasets/gapon/wise/allsky_4band_p1bs_psd/allsky_4band_p1bs_psd.txt Then it was translated into MySQL schema definition (SQL DDL) file using this tool (available in GitHub package txt2schema.py ): % python \ /datasets/gapon/development/db_pdac_wise/tools//txt2schema .py \ allsky_4band_p1bs_psd.txt \ > allsky_4band_p1bs_psd.sql Made two modifications to thw original schema: renaming column dec into decl as a workaround to an existing limitation of the Qserv SQL parser (see ) manual addition of the PRIMARY key The relevant sections of the final schema file are presented below: CREATE TABLE `allsky_4band_p1bs_psd` ( `decl` DECIMAL (9,7) DEFAULT NULL , `source_id` CHAR (16) DEFAULT NULL , PRIMARY KEY (`source_id`) ) ENGINE=MyISAM; Saved the schema file into the corresponding GitHub package at: https://github.com/lsst-dm/db_pdac_wise/blob/master/sql/allsky_4band_p1bs_psd.sql Downloaded files into the temporary location at NCSA The files were placed at the GPFS folder: /datasets/gapon/wise/allsky_4band_p1bs_psd/downloaded/ The operation succeeded with: 2304 compressed files (bz2) 2.5 TB of total data amount

Hide

Permalink

Igor Gaponenko added a comment - 02/Jun/17 6:03 PM

Processing downloaded files of catalog allsky_4band_p1bs_psd

Uncompressing the files

This stage took nearly 2 days of the wall-clock time using LSST Evaluation Cluster at NCSA. The resulted files were put into the same folder where the compressed ones were located. The original files were replaced with the uncompressed ones. The total amount of data in the folder is 10 TB:

/datasets/gapon/wise/allsky_4band_p1bs_psd/downloaded/data/

Translating files from the unl format into the TSV format

This stage is needed because TSV is the only input data format supported by the LSST DB catalog partitioning tools. The output are placed at:

/datasets/gapon/wise/allsky_4band_p1bs_psd/tsv

The translation tool unl2tsv is in GitHub at:

https://github.com/lsst-dm/db_pdac_wise/tree/master/tools

This operation finished with 11 TB of data within the output folder.

Show

Igor Gaponenko added a comment - 02/Jun/17 6:03 PM Processing downloaded files of catalog allsky_4band_p1bs_psd Uncompressing the files This stage took nearly 2 days of the wall-clock time using LSST Evaluation Cluster at NCSA. The resulted files were put into the same folder where the compressed ones were located. The original files were replaced with the uncompressed ones. The total amount of data in the folder is 10 TB: /datasets/gapon/wise/allsky_4band_p1bs_psd/downloaded/data/ Translating files from the unl format into the TSV format This stage is needed because TSV is the only input data format supported by the LSST DB catalog partitioning tools. The output are placed at: /datasets/gapon/wise/allsky_4band_p1bs_psd/tsv The translation tool unl2tsv is in GitHub at: https://github.com/lsst-dm/db_pdac_wise/tree/master/tools This operation finished with 11 TB of data within the output folder.

Hide

Permalink

Igor Gaponenko added a comment - 02/Jun/17 6:26 PM - edited

Partitioning files of catalog allsky_4band_p1bs_psd

The destination folder for the partitioned data is:

/datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/chunks

Partitioning by 32 parallel jobs

To speed up the operation all 2304 input TSV files were grouped into 32 file sets of 72 files in each. The file list are shown here:

% ls -l /datasets/gapon/wise/allsky_4band_p1bs_psd/files2partition/

-rw-r--r--  1 gapon grp_202 3229 Jun  2 15:20 00

-rw-r--r--  1 gapon grp_202 3320 Jun  2 15:20 01

-rw-r--r--  1 gapon grp_202 3373 Jun  2 15:20 02

-rw-r--r--  1 gapon grp_202 3368 Jun  2 15:20 03

-rw-r--r--  1 gapon grp_202 3301 Jun  2 15:20 04

-rw-r--r--  1 gapon grp_202 3440 Jun  2 15:20 05

-rw-r--r--  1 gapon grp_202 3301 Jun  2 15:20 06

-rw-r--r--  1 gapon grp_202 3296 Jun  2 15:20 07

-rw-r--r--  1 gapon grp_202 3397 Jun  2 15:20 08

-rw-r--r--  1 gapon grp_202 3392 Jun  2 15:20 09

-rw-r--r--  1 gapon grp_202 3301 Jun  2 15:20 10

-rw-r--r--  1 gapon grp_202 3344 Jun  2 15:20 11

-rw-r--r--  1 gapon grp_202 3397 Jun  2 15:20 12

-rw-r--r--  1 gapon grp_202 3392 Jun  2 15:20 13

-rw-r--r--  1 gapon grp_202 3349 Jun  2 15:20 14

-rw-r--r--  1 gapon grp_202 3248 Jun  2 15:20 15

-rw-r--r--  1 gapon grp_202 3253 Jun  2 15:20 16

-rw-r--r--  1 gapon grp_202 3248 Jun  2 15:20 17

-rw-r--r--  1 gapon grp_202 3397 Jun  2 15:20 18

-rw-r--r--  1 gapon grp_202 3392 Jun  2 15:20 19

-rw-r--r--  1 gapon grp_202 3349 Jun  2 15:20 20

-rw-r--r--  1 gapon grp_202 3296 Jun  2 15:20 21

-rw-r--r--  1 gapon grp_202 3373 Jun  2 15:20 22

-rw-r--r--  1 gapon grp_202 3368 Jun  2 15:20 23

-rw-r--r--  1 gapon grp_202 3349 Jun  2 15:20 24

-rw-r--r--  1 gapon grp_202 3344 Jun  2 15:20 25

-rw-r--r--  1 gapon grp_202 3445 Jun  2 15:20 26

-rw-r--r--  1 gapon grp_202 3296 Jun  2 15:20 27

-rw-r--r--  1 gapon grp_202 3253 Jun  2 15:20 28

-rw-r--r--  1 gapon grp_202 3152 Jun  2 15:20 29

-rw-r--r--  1 gapon grp_202 3373 Jun  2 15:20 30

-rw-r--r--  1 gapon grp_202 3128 Jun  2 15:20 31

Where each file has (as an example) the file names to be translated by a single batch job:

cat /datasets/gapon/wise/allsky_4band_p1bs_psd/files2partition/00

wise_allsky_4band_p1bs_psd_0.4727_4.5439_10

wise_allsky_4band_p1bs_psd_0.4727_4.5439_11

wise_allsky_4band_p1bs_psd_0.4727_4.5439_12

wise_allsky_4band_p1bs_psd_0.4727_4.5439_13

wise_allsky_4band_p1bs_psd_0.4727_4.5439_14

wise_allsky_4band_p1bs_psd_0.4727_4.5439_15

wise_allsky_4band_p1bs_psd_0.4727_4.5439_16

...

Then 32 parallel jobs were launched using srun command on the Verification Cluster (batch system):

% cd /datasets/gapon/wise/allsky_4band_p1bs_psd/

% for f in `ls files2partition/`; do \

  nohup srun /datasets/gapon/wise/allsky_4band_p1bs_psd/bin/partition.sh files2partition/${f} \

  >& /datasets/gapon/wise/allsky_4band_p1bs_psd/logs/${f}.log&

done

Where the script's code is shown below:

% cat cat /datasets/gapon/wise/allsky_4band_p1bs_psd/bin/partition.sh

#!/bin/bash

set -e

filelist=$1

if [ -z $filelist ]; then

    echo "usage: <filelist>"

    exit 1

fi

export HOME=/datasets/gapon

source ${HOME}/stack/loadLSST.bash

setup --nolocks -t qserv-dev qserv_distrib

cd ${HOME}/wise/allsky_4band_p1bs_psd/

for name in `cat ${filelist}`; do

    mkdir -p partitioned_x16/chunks/${name}/

    rm    -f partitioned_x16/chunks/${name}/*

    rm    -f partitioned_x16/log/${name}.log

    sph-partition \

      --verbose \

      -c partitioned_x16/allsky_4band_p1bs_psd.cfg \

      --out.dir=partitioned_x16/chunks/${name}/ \

      --in=tsv/${name}.tsv \

     >& partitioned_x16/log/${name}.log

done

ATTENTION: it was experimentally found that extra --nolocks option is very important when calling setup within scripts run in parallel. Otherwise some jobs may fail with:

setup: [Errno 2] No such file or directory: '/datasets/gapon/stack/.lockDir'

Analyzing results

First, checked the top level (for each file set) log files to make sure no errors were reported. The files are located at:

/datasets/gapon/wise/allsky_4band_p1bs_psd/logs

Then checked the log files produced for each of 2304 partitioned file as shown below to make sure no errors were reported and the files had large size (it's explained a bot later why):

% ls -l /datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/log

total 454656

-rw-r--r-- 1 gapon grp_202 243269 Jun  2 15:40 wise_allsky_4band_p1bs_psd_0.4727_4.5439_10.log

-rw-r--r-- 1 gapon grp_202 243319 Jun  2 15:44 wise_allsky_4band_p1bs_psd_0.4727_4.5439_11.log

-rw-r--r-- 1 gapon grp_202 243335 Jun  2 15:48 wise_allsky_4band_p1bs_psd_0.4727_4.5439_12.log

-rw-r--r-- 1 gapon grp_202 243321 Jun  2 15:51 wise_allsky_4band_p1bs_psd_0.4727_4.5439_13.log

-rw-r--r-- 1 gapon grp_202 243309 Jun  2 15:55 wise_allsky_4band_p1bs_psd_0.4727_4.5439_14.log

-rw-r--r-- 1 gapon grp_202 243316 Jun  2 15:58 wise_allsky_4band_p1bs_psd_0.4727_4.5439_15.log

-rw-r--r-- 1 gapon grp_202 243295 Jun  2 16:02 wise_allsky_4band_p1bs_psd_0.4727_4.5439_16.log

-rw-r--r-- 1 gapon grp_202 243277 Jun  2 16:06 wise_allsky_4band_p1bs_psd_0.4727_4.5439_17.log

-rw-r--r-- 1 gapon grp_202 243281 Jun  2 16:09 wise_allsky_4band_p1bs_psd_0.4727_4.5439_18.log

...

IMPORTANT: each of these files has the very valuable report from the partitioning tool, which includes (to mention just two most useful in the current context):

the total number of chunks and sub-chunks
identifiers of chunks

Collecting unique chunk identifiers from those reports:

% cd /datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16

% for f in `ls log/*.log`; do \

  cat ${f}; \

done | grep '"id"' | awk '{print substr($2,0,length($2)-1)}' \ > all_chunks.txt

% cat all_chunks.txt  | sort -n -u > all_chunks_unique_sorted.txt

% wc -l all_chunks_unique_sorted.txt

146332 all_chunks_unique_sorted.txt

OBSERVATION: this (146332) is very important as it matches the total number of chunks reported when partitioning other catalogs as per ~~DM-9372~~. Therefore no additional (new) chunk placement strategy will be needed for this catalog. The last test is just to make sure the chunks have the same number as the ones of the previously loaded partitioned catalogs. Using the Object-alike table for that:

% diff \

/datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted.txt \

/datasets/gapon/wise/allwise_p3as_psd/partitioned_x16/unique_chunks.txt

Show

Igor Gaponenko added a comment - 02/Jun/17 6:26 PM - edited Partitioning files of catalog allsky_4band_p1bs_psd The destination folder for the partitioned data is: /datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/chunks Partitioning by 32 parallel jobs To speed up the operation all 2304 input TSV files were grouped into 32 file sets of 72 files in each. The file list are shown here: % ls -l /datasets/gapon/wise/allsky_4band_p1bs_psd/files2partition/ -rw-r--r-- 1 gapon grp_202 3229 Jun 2 15:20 00 -rw-r--r-- 1 gapon grp_202 3320 Jun 2 15:20 01 -rw-r--r-- 1 gapon grp_202 3373 Jun 2 15:20 02 -rw-r--r-- 1 gapon grp_202 3368 Jun 2 15:20 03 -rw-r--r-- 1 gapon grp_202 3301 Jun 2 15:20 04 -rw-r--r-- 1 gapon grp_202 3440 Jun 2 15:20 05 -rw-r--r-- 1 gapon grp_202 3301 Jun 2 15:20 06 -rw-r--r-- 1 gapon grp_202 3296 Jun 2 15:20 07 -rw-r--r-- 1 gapon grp_202 3397 Jun 2 15:20 08 -rw-r--r-- 1 gapon grp_202 3392 Jun 2 15:20 09 -rw-r--r-- 1 gapon grp_202 3301 Jun 2 15:20 10 -rw-r--r-- 1 gapon grp_202 3344 Jun 2 15:20 11 -rw-r--r-- 1 gapon grp_202 3397 Jun 2 15:20 12 -rw-r--r-- 1 gapon grp_202 3392 Jun 2 15:20 13 -rw-r--r-- 1 gapon grp_202 3349 Jun 2 15:20 14 -rw-r--r-- 1 gapon grp_202 3248 Jun 2 15:20 15 -rw-r--r-- 1 gapon grp_202 3253 Jun 2 15:20 16 -rw-r--r-- 1 gapon grp_202 3248 Jun 2 15:20 17 -rw-r--r-- 1 gapon grp_202 3397 Jun 2 15:20 18 -rw-r--r-- 1 gapon grp_202 3392 Jun 2 15:20 19 -rw-r--r-- 1 gapon grp_202 3349 Jun 2 15:20 20 -rw-r--r-- 1 gapon grp_202 3296 Jun 2 15:20 21 -rw-r--r-- 1 gapon grp_202 3373 Jun 2 15:20 22 -rw-r--r-- 1 gapon grp_202 3368 Jun 2 15:20 23 -rw-r--r-- 1 gapon grp_202 3349 Jun 2 15:20 24 -rw-r--r-- 1 gapon grp_202 3344 Jun 2 15:20 25 -rw-r--r-- 1 gapon grp_202 3445 Jun 2 15:20 26 -rw-r--r-- 1 gapon grp_202 3296 Jun 2 15:20 27 -rw-r--r-- 1 gapon grp_202 3253 Jun 2 15:20 28 -rw-r--r-- 1 gapon grp_202 3152 Jun 2 15:20 29 -rw-r--r-- 1 gapon grp_202 3373 Jun 2 15:20 30 -rw-r--r-- 1 gapon grp_202 3128 Jun 2 15:20 31 Where each file has (as an example) the file names to be translated by a single batch job: cat /datasets/gapon/wise/allsky_4band_p1bs_psd/files2partition/00 wise_allsky_4band_p1bs_psd_0.4727_4.5439_10 wise_allsky_4band_p1bs_psd_0.4727_4.5439_11 wise_allsky_4band_p1bs_psd_0.4727_4.5439_12 wise_allsky_4band_p1bs_psd_0.4727_4.5439_13 wise_allsky_4band_p1bs_psd_0.4727_4.5439_14 wise_allsky_4band_p1bs_psd_0.4727_4.5439_15 wise_allsky_4band_p1bs_psd_0.4727_4.5439_16 ... Then 32 parallel jobs were launched using srun command on the Verification Cluster (batch system): % cd /datasets/gapon/wise/allsky_4band_p1bs_psd/ % for f in ` ls files2partition/`; do \ nohup srun /datasets/gapon/wise/allsky_4band_p1bs_psd/bin/partition .sh files2partition/${f} \ >& /datasets/gapon/wise/allsky_4band_p1bs_psd/logs/ ${f}.log& done Where the script's code is shown below: % cat cat /datasets/gapon/wise/allsky_4band_p1bs_psd/bin/partition .sh #!/bin/bash set -e filelist=$1 if [ -z $filelist ]; then echo "usage: <filelist>" exit 1 fi export HOME= /datasets/gapon source ${HOME} /stack/loadLSST . bash setup --nolocks -t qserv-dev qserv_distrib cd ${HOME} /wise/allsky_4band_p1bs_psd/ for name in ` cat ${filelist}`; do mkdir -p partitioned_x16 /chunks/ ${name}/ rm -f partitioned_x16 /chunks/ ${name}/* rm -f partitioned_x16 /log/ ${name}.log sph-partition \ --verbose \ -c partitioned_x16 /allsky_4band_p1bs_psd .cfg \ --out. dir =partitioned_x16 /chunks/ ${name}/ \ -- in =tsv/${name}.tsv \ >& partitioned_x16 /log/ ${name}.log done ATTENTION : it was experimentally found that extra --nolocks option is very important when calling setup within scripts run in parallel. Otherwise some jobs may fail with: setup: [Errno 2] No such file or directory: '/datasets/gapon/stack/.lockDir' Analyzing results First, checked the top level (for each file set) log files to make sure no errors were reported. The files are located at: /datasets/gapon/wise/allsky_4band_p1bs_psd/logs Then checked the log files produced for each of 2304 partitioned file as shown below to make sure no errors were reported and the files had large size (it's explained a bot later why): % ls -l /datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/log total 454656 -rw-r--r-- 1 gapon grp_202 243269 Jun 2 15:40 wise_allsky_4band_p1bs_psd_0.4727_4.5439_10.log -rw-r--r-- 1 gapon grp_202 243319 Jun 2 15:44 wise_allsky_4band_p1bs_psd_0.4727_4.5439_11.log -rw-r--r-- 1 gapon grp_202 243335 Jun 2 15:48 wise_allsky_4band_p1bs_psd_0.4727_4.5439_12.log -rw-r--r-- 1 gapon grp_202 243321 Jun 2 15:51 wise_allsky_4band_p1bs_psd_0.4727_4.5439_13.log -rw-r--r-- 1 gapon grp_202 243309 Jun 2 15:55 wise_allsky_4band_p1bs_psd_0.4727_4.5439_14.log -rw-r--r-- 1 gapon grp_202 243316 Jun 2 15:58 wise_allsky_4band_p1bs_psd_0.4727_4.5439_15.log -rw-r--r-- 1 gapon grp_202 243295 Jun 2 16:02 wise_allsky_4band_p1bs_psd_0.4727_4.5439_16.log -rw-r--r-- 1 gapon grp_202 243277 Jun 2 16:06 wise_allsky_4band_p1bs_psd_0.4727_4.5439_17.log -rw-r--r-- 1 gapon grp_202 243281 Jun 2 16:09 wise_allsky_4band_p1bs_psd_0.4727_4.5439_18.log ... IMPORTANT : each of these files has the very valuable report from the partitioning tool, which includes (to mention just two most useful in the current context): the total number of chunks and sub-chunks identifiers of chunks Collecting unique chunk identifiers from those reports: % cd /datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16 % for f in ` ls log/*.log`; do \ cat ${f}; \ done | grep '"id"' | awk '{print substr($2,0,length($2)-1)}' \ > all_chunks.txt % cat all_chunks.txt | sort -n -u > all_chunks_unique_sorted.txt % wc -l all_chunks_unique_sorted.txt 146332 all_chunks_unique_sorted.txt OBSERVATION : this ( 146332 ) is very important as it matches the total number of chunks reported when partitioning other catalogs as per DM-9372 . Therefore no additional (new) chunk placement strategy will be needed for this catalog. The last test is just to make sure the chunks have the same number as the ones of the previously loaded partitioned catalogs. Using the Object -alike table for that: % diff \ /datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted .txt \ /datasets/gapon/wise/allwise_p3as_psd/partitioned_x16/unique_chunks .txt

Hide

Permalink

Igor Gaponenko added a comment - 02/Jun/17 7:40 PM - edited

Preparing the staging area for chunks of catalog allsky_4band_p1bs_psd

General considerations and a setup

The goal of this step is to arrange chunks produced during the previously explained partitioning phase for parallel loading into PDAC. The staging area for the catalog is located at:

/datasets/gapon/production/wise_catalog_load/production_load/allsky_4band_p1bs_psd/

The area will contain symbolic links pointing to the corresponding chunks in the partitioned area. The links will be organized into two layers:

<host>/<folder>/<chunks>

Where the <folder> names are actually the names (w/o any extension) of the original files downloaded from IRSA/IPAC. This hierarchy is important for two reasons:

grouping by host name allows all worker to load their chunks in parallel with others.
grouping by folder solves two more problems:
1. disambiguates files having the same name (two original files may contribute into the same partition or into the overlap areas of the same partition)
2. allows (potentially) to further speedup the loading operation by having multiple loaders run within each worker node. Each such loaded would get its own set of folders.

The next requirement which needs to be addressed when generating the links is to ensure chunk collocation with the previously loaded WISE catalogs.

Obtaining existing worker-to-chunks map from PDAC

The following script located on the GPFS file system was launched on all worker nodes of the PDAC cluster simultaneously to harvest the maps of workers to the corresponding chunks:

#!/bin/bash

set -e

source /datasets/gapon/stack/loadLSST.bash

setup -N -t qserv-dev qserv_distrib

sql='SELECT SUBSTR(TABLE_NAME,18) FROM information_schema.tables WHERE TABLE_SCHEMA="wise_ext_00" AND TABLE_NAME LIKE "allwise_p3as_psr\_%" AND TABLE_NAME != "allwise_p3as_psr_1234567890"'

echo $sql | mysql -N -S/qserv/data/mysql/mysql.sock -P13306 -uroot -pCHANGEME -A

The script started from the master node of the cluster:

% ssh qserv-master01

% mkdir wise_worker2chunks

% for worker in `seq --format="qserv-db%02g" 1 30`; do \

    ssh -n $worker  /bin/sudo -u qserv /datasets/gapon/worker2chunks.bash

    > wise_worker2chunks/lsst-${worker}&

done

The map was transferred to lsst-dev01 and placed at:

% ls -l /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/

-rw-r--r-- 1 gapon grp_202 32197 Jun  4 19:04 lsst-qserv-db01

-rw-r--r-- 1 gapon grp_202 32199 Jun  4 19:04 lsst-qserv-db02

-rw-r--r-- 1 gapon grp_202 32199 Jun  4 19:04 lsst-qserv-db03

-rw-r--r-- 1 gapon grp_202 32199 Jun  4 19:04 lsst-qserv-db04

-rw-r--r-- 1 gapon grp_202 32199 Jun  4 19:04 lsst-qserv-db05

-rw-r--r-- 1 gapon grp_202 32199 Jun  4 19:04 lsst-qserv-db06

-rw-r--r-- 1 gapon grp_202 32200 Jun  4 19:04 lsst-qserv-db07

-rw-r--r-- 1 gapon grp_202 32200 Jun  4 19:04 lsst-qserv-db08

-rw-r--r-- 1 gapon grp_202 32200 Jun  4 19:04 lsst-qserv-db09

-rw-r--r-- 1 gapon grp_202 32200 Jun  4 19:04 lsst-qserv-db10

-rw-r--r-- 1 gapon grp_202 32200 Jun  4 19:04 lsst-qserv-db11

...

The number of entries matches the total number of chunks 146332:

% wc -l /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/*

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db01

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db02

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db03

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db04

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db05

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db06

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db07

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db08

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db09

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db10

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db11

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db12

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db13

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db14

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db15

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db16

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db17

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db18

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db19

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db20

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db21

  4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db22

  4877 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db23

  4877 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db24

  4877 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db25

  4877 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db26

  4877 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db27

  4877 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db28

  4877 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db29

  4877 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db30

146332 total

The link generator will be using the maps from that folder on the next stage.

Also save the mapping in GitHub for future references when partitioning other catalogs in this series at this sub-folder:

https://github.com/lsst-dm/db_pdac_wise/tree/master/wise_worker2chunks

Generating scripts to populate area

Generated a list of all files to be linked. This is a lengthy process due to the large number of chunk files created during the partitioning phase:

% cd /datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/

% for d in `ls chunks/`; do

  ls -l chunks/${d}/* | grep .txt; done > all_chunks_and_overlaps.txt

% wc -l all_chunks_and_overlaps.txt

16214880 all_chunks_and_overlaps.txt

ATTENTION: this is 16 million files!

Using a custom version of the generator which reads the above created list of chunk files and overlaps, and the earlier created worker-to-chunks map:

% scripts/chunk2folder.py folder > scripts/stage_folder.source

% wc -l  scripts/stage_folder.source

138304 scripts/stage_folder.source

% scripts/chunk2folder.py index > scripts/stage_index.source

138244 scripts/stage_index.source

% scripts/chunk2folder.py chunk > scripts/stage_chunk.source

32429761 scripts/stage_chunk.source

And here os the code of the generator script:

# Read the existing mapping of chunks to workers

# in the WISE catalog deployment in PDAC

import sys

from glob import glob

chunk2worker = {}

for f in glob('/datasets/gapon/development/db_pdac_wise/wise_worker2chunks/*'):

    worker = f[f.rfind('/')+1:]

    with open(f,'r') as files:

        for l in files:

            chunk = int(l[:-1])

            chunk2worker[chunk] = worker

# Sort a plain list of all files for chunks and the corresponding

# overlaps into a dictionary of:

#   [chunk][folder]

# Each value of the dictionary will represent a list of

# the files.

def split2components (p):

    folder_name = p[len('chunks/'):p.rfind("/")]

    file_name   = p[p.rfind("/")+1:-len('.txt')]

    if file_name[-len('_overlap'):] == '_overlap': chunk = int(file_name[len('chunk_'):-len('_overlap')])

    else:                                          chunk = int(file_name[len('chunk_'):])

    return {

        'file'  : "%s.txt" % file_name,

        'folder': folder_name,

        'chunk' : chunk}

chunk2folder2file = {}

with open('all_chunks_and_overlaps.txt','r') as files:

    for f in files:

        c = split2components(f[:-1])

        chunk = c['chunk']

        if chunk not in chunk2folder2file: chunk2folder2file[chunk] = {}

        folder = c['folder']

        if folder not in chunk2folder2file[chunk]: chunk2folder2file[chunk][folder] = []

        chunk2folder2file[chunk][folder].append(c['file'])

# Generate commands for populating the loading staging area with

# folders and links to the partitioned files

# Generate three sets of commands for preparing the staging area

# from which the catalog loading will be happening:

# - creating a tree of folders

# - creating symbolik links for special files 'chunk_index.bin' (one

#   such file per each final folder where chunk files will be linked

#   as well)

# - for setting up symbolic links pointing to the chunk files

#   and the overlaps

# NOTE: We should do these steps separately because of a latency

#       for creating folders in the GPFS file system which may

#       cause subseqeunt operations to fail.

source      = '/datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/chunks'

destination = '/datasets/gapon/production/wise_catalog_load/production_load/allsky_4band_p1bs_psd'

stage_folder = []

stage_index  = []

stage_chunk  = []

worker2folder = {}   ## the dictionary of [worker][folder] is used for bookkeeping

                     ## purposes to prevent issuing the same command for than once

chunks = chunk2folder2file.keys()

chunks.sort()

for chunk in chunks:

    if chunk not in chunk2worker: raise Exception("chunk %d is unknown" % chunk)

    worker = chunk2worker[chunk]

    if worker not in worker2folder:

        worker2folder[worker] = {}

        stage_folder.append("mkdir -p %s/%s" % (destination,worker,))

    for folder in chunk2folder2file[chunk]:

        if folder not in worker2folder[worker]:

            worker2folder[worker][folder] = 0

            stage_folder.append("mkdir -p %s/%s/%s" % (destination,worker,folder,))

            stage_index.append("ln -s %s/%s/chunk_index.bin %s/%s/%s/chunk_index.bin" % (source,folder,destination,worker,folder))

        for f in chunk2folder2file[chunk][folder]:

            stage_chunk.append("ln -s %s/%s/%s %s/%s/%s/%s" % (source,folder,f,destination,worker,folder,f))

# Print commands from the specified stage

stage2print = {

    'folder' : stage_folder,

    'index'  : stage_index,

    'chunk'  : stage_chunk}

help_ = "usage: %s {%s}" % (sys.argv[0], "|".join([stage for stage in stage2print.keys()]),)

if len(sys.argv) != 2:

    print help_

    sys.exit(1)

stage = sys.argv[1]

if stage not in stage2print.keys():

    print help_

    sys.exit(1)

print "#!/bin/bash"

for op in stage2print[stage]:

    print "echo %s" % op

    print op

TODO: put it to GitHub.

Populating the area

Run all 3 scripts sequentially. It's very IMPORTANT to create folders before setting up the links. There is an undesired latency in the GPGS metadata service which may delay catalog stats reporting thus causing the subsequent (link and index) stages to fail if the second stages are mixed together with the folder operations.

% nohup ./scripts/stage_folder.source >& log/stage_folder.log

% nohup ./scripts/stage_index.source >& log/stage_index.log

% nohup ./scripts/stage_chunk.source >& log/stage_chunk.log

Show

Igor Gaponenko added a comment - 02/Jun/17 7:40 PM - edited Preparing the staging area for chunks of catalog allsky_4band_p1bs_psd General considerations and a setup The goal of this step is to arrange chunks produced during the previously explained partitioning phase for parallel loading into PDAC . The staging area for the catalog is located at: /datasets/gapon/production/wise_catalog_load/production_load/allsky_4band_p1bs_psd/ The area will contain symbolic links pointing to the corresponding chunks in the partitioned area. The links will be organized into two layers: <host>/<folder>/<chunks> Where the <folder> names are actually the names (w/o any extension) of the original files downloaded from IRSA/IPAC. This hierarchy is important for two reasons: grouping by host name allows all worker to load their chunks in parallel with others. grouping by folder solves two more problems: disambiguates files having the same name (two original files may contribute into the same partition or into the overlap areas of the same partition) allows (potentially) to further speedup the loading operation by having multiple loaders run within each worker node. Each such loaded would get its own set of folders. The next requirement which needs to be addressed when generating the links is to ensure chunk collocation with the previously loaded WISE catalogs. Obtaining existing worker-to-chunks map from PDAC The following script located on the GPFS file system was launched on all worker nodes of the PDAC cluster simultaneously to harvest the maps of workers to the corresponding chunks: #!/bin/bash set -e source /datasets/gapon/stack/loadLSST . bash setup -N -t qserv-dev qserv_distrib sql= 'SELECT SUBSTR(TABLE_NAME,18) FROM information_schema.tables WHERE TABLE_SCHEMA="wise_ext_00" AND TABLE_NAME LIKE "allwise_p3as_psr\_%" AND TABLE_NAME != "allwise_p3as_psr_1234567890"' echo $sql | mysql -N -S /qserv/data/mysql/mysql .sock -P13306 -uroot -pCHANGEME -A The script started from the master node of the cluster: % ssh qserv-master01 % mkdir wise_worker2chunks % for worker in ` seq -- format = "qserv-db%02g" 1 30`; do \ ssh -n $worker /bin/sudo -u qserv /datasets/gapon/worker2chunks . bash > wise_worker2chunks /lsst- ${worker}& done The map was transferred to lsst-dev01 and placed at: % ls -l /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/ -rw-r--r-- 1 gapon grp_202 32197 Jun 4 19:04 lsst-qserv-db01 -rw-r--r-- 1 gapon grp_202 32199 Jun 4 19:04 lsst-qserv-db02 -rw-r--r-- 1 gapon grp_202 32199 Jun 4 19:04 lsst-qserv-db03 -rw-r--r-- 1 gapon grp_202 32199 Jun 4 19:04 lsst-qserv-db04 -rw-r--r-- 1 gapon grp_202 32199 Jun 4 19:04 lsst-qserv-db05 -rw-r--r-- 1 gapon grp_202 32199 Jun 4 19:04 lsst-qserv-db06 -rw-r--r-- 1 gapon grp_202 32200 Jun 4 19:04 lsst-qserv-db07 -rw-r--r-- 1 gapon grp_202 32200 Jun 4 19:04 lsst-qserv-db08 -rw-r--r-- 1 gapon grp_202 32200 Jun 4 19:04 lsst-qserv-db09 -rw-r--r-- 1 gapon grp_202 32200 Jun 4 19:04 lsst-qserv-db10 -rw-r--r-- 1 gapon grp_202 32200 Jun 4 19:04 lsst-qserv-db11 ... The number of entries matches the total number of chunks 146332 : % wc -l /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/ * 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db01 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db02 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db03 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db04 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db05 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db06 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db07 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db08 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db09 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db10 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db11 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db12 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db13 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db14 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db15 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db16 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db17 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db18 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db19 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db20 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db21 4878 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db22 4877 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db23 4877 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db24 4877 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db25 4877 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db26 4877 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db27 4877 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db28 4877 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db29 4877 /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/lsst-qserv-db30 146332 total The link generator will be using the maps from that folder on the next stage. Also save the mapping in GitHub for future references when partitioning other catalogs in this series at this sub-folder: https://github.com/lsst-dm/db_pdac_wise/tree/master/wise_worker2chunks Generating scripts to populate area Generated a list of all files to be linked. This is a lengthy process due to the large number of chunk files created during the partitioning phase: % cd /datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/ % for d in ` ls chunks/`; do ls -l chunks/${d}/* | grep .txt; done > all_chunks_and_overlaps.txt % wc -l all_chunks_and_overlaps.txt 16214880 all_chunks_and_overlaps.txt ATTENTION : this is 16 million files! Using a custom version of the generator which reads the above created list of chunk files and overlaps, and the earlier created worker-to-chunks map: % scripts /chunk2folder .py folder > scripts /stage_folder . source % wc -l scripts /stage_folder . source 138304 scripts /stage_folder . source % scripts /chunk2folder .py index > scripts /stage_index . source 138244 scripts /stage_index . source % scripts /chunk2folder .py chunk > scripts /stage_chunk . source 32429761 scripts /stage_chunk . source And here os the code of the generator script: # Read the existing mapping of chunks to workers # in the WISE catalog deployment in PDAC import sys from glob import glob chunk2worker = {} for f in glob( '/datasets/gapon/development/db_pdac_wise/wise_worker2chunks/*' ): worker = f[f.rfind( '/' ) + 1 :] with open (f, 'r' ) as files: for l in files: chunk = int (l[: - 1 ]) chunk2worker[chunk] = worker # Sort a plain list of all files for chunks and the corresponding # overlaps into a dictionary of: # # [chunk][folder] # # Each value of the dictionary will represent a list of # the files. def split2components (p): folder_name = p[ len ( 'chunks/' ):p.rfind( "/" )] file_name = p[p.rfind( "/" ) + 1 : - len ( '.txt' )] if file_name[ - len ( '_overlap' ):] = = '_overlap' : chunk = int (file_name[ len ( 'chunk_' ): - len ( '_overlap' )]) else : chunk = int (file_name[ len ( 'chunk_' ):]) return { 'file' : "%s.txt" % file_name, 'folder' : folder_name, 'chunk' : chunk} chunk2folder2file = {} with open ( 'all_chunks_and_overlaps.txt' , 'r' ) as files: for f in files: c = split2components(f[: - 1 ]) chunk = c[ 'chunk' ] if chunk not in chunk2folder2file: chunk2folder2file[chunk] = {} folder = c[ 'folder' ] if folder not in chunk2folder2file[chunk]: chunk2folder2file[chunk][folder] = [] chunk2folder2file[chunk][folder].append(c[ 'file' ]) # Generate commands for populating the loading staging area with # folders and links to the partitioned files # Generate three sets of commands for preparing the staging area # from which the catalog loading will be happening: # - creating a tree of folders # # - creating symbolik links for special files 'chunk_index.bin' (one # such file per each final folder where chunk files will be linked # as well) # # - for setting up symbolic links pointing to the chunk files # and the overlaps # # NOTE: We should do these steps separately because of a latency # for creating folders in the GPFS file system which may # cause subseqeunt operations to fail. source = '/datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/chunks' destination = '/datasets/gapon/production/wise_catalog_load/production_load/allsky_4band_p1bs_psd' stage_folder = [] stage_index = [] stage_chunk = [] worker2folder = {} ## the dictionary of [worker][folder] is used for bookkeeping ## purposes to prevent issuing the same command for than once chunks = chunk2folder2file.keys() chunks.sort() for chunk in chunks: if chunk not in chunk2worker: raise Exception( "chunk %d is unknown" % chunk) worker = chunk2worker[chunk] if worker not in worker2folder: worker2folder[worker] = {} stage_folder.append( "mkdir -p %s/%s" % (destination,worker,)) for folder in chunk2folder2file[chunk]: if folder not in worker2folder[worker]: worker2folder[worker][folder] = 0 stage_folder.append( "mkdir -p %s/%s/%s" % (destination,worker,folder,)) stage_index.append( "ln -s %s/%s/chunk_index.bin %s/%s/%s/chunk_index.bin" % (source,folder,destination,worker,folder)) for f in chunk2folder2file[chunk][folder]: stage_chunk.append( "ln -s %s/%s/%s %s/%s/%s/%s" % (source,folder,f,destination,worker,folder,f)) # Print commands from the specified stage stage2print = { 'folder' : stage_folder, 'index' : stage_index, 'chunk' : stage_chunk} help_ = "usage: %s {%s}" % (sys.argv[ 0 ], "|" .join([stage for stage in stage2print.keys()]),) if len (sys.argv) ! = 2 : print help_ sys.exit( 1 ) stage = sys.argv[ 1 ] if stage not in stage2print.keys(): print help_ sys.exit( 1 ) print "#!/bin/bash" for op in stage2print[stage]: print "echo %s" % op print op TODO : put it to GitHub . Populating the area Run all 3 scripts sequentially. It's very IMPORTANT to create folders before setting up the links. There is an undesired latency in the GPGS metadata service which may delay catalog stats reporting thus causing the subsequent (link and index) stages to fail if the second stages are mixed together with the folder operations. % nohup . /scripts/stage_folder . source >& log /stage_folder .log % nohup . /scripts/stage_index . source >& log /stage_index .log % nohup . /scripts/stage_chunk . source >& log /stage_chunk .log

Hide

Permalink

Igor Gaponenko added a comment - 06/Jun/17 1:52 PM - edited

Downloading WISE 3-Band Cryo Single Exposure (L1b) Source Table data

Table name	allsky_3band_p1bs_psd
Temporary data folder (NCSA)	/datasets/gapon/wise/allsky_3band_p1bs_psd/
Catalog data (IPAC)	https://irsa.ipac.caltech.edu/data/download/wise-3band-psd/
Schema file (IPAC)	https://irsa.ipac.caltech.edu/data/download/wise-3band-psd/wise_allsky_3band_p1bs_psd-schema.txt

Catalog schema

The original schema file was downloaded as:

/datasets/gapon/wise/allsky_3band_p1bs_psd/allsky_3band_p1bs_psd.txt

Then it was translated into MySQL schema definition (SQL DDL) file using this tool (available in GitHub package txt2schema.py):

% python \

  /datasets/gapon/development/db_pdac_wise/tools//txt2schema.py \

    allsky_3band_p1bs_psd.txt \

    > allsky_3band_p1bs_psd.schema

Made two modifications to thO original schema:

renaming column *dec *into *decl *as a workaround to an existing limitation of the *Qserv *SQL parser (see ~~DM-9736~~)
manual addition of the PRIMARY key

The relevant sections of the final schema file are presented below:

CREATE TABLE `allsky_3band_p1bs_psd` (

         `decl`    DECIMAL(9,7)  DEFAULT NULL,

    `source_id`    CHAR(16)      DEFAULT NULL,

    PRIMARY KEY(`source_id`)

) ENGINE=MyISAM;

Saved the schema file into the corresponding GitHub package at:

https://github.com/lsst-dm/db_pdac_wise/blob/master/sql/allsky_3band_p1bs_psd.sql

Catalog data

Started the download script from lsst-xfer. The files will be placed at the temporary data folder at:

/datasets/gapon/wise/allsky_3band_p1bs_psd/downloaded/

The operation has succeeded with:

2304 compressed files (bz2)
1 TB of total data amount

Show

Igor Gaponenko added a comment - 06/Jun/17 1:52 PM - edited Downloading WISE 3-Band Cryo Single Exposure (L1b) Source Table data Table name allsky_3band_p1bs_psd Temporary data folder (NCSA) /datasets/gapon/wise/allsky_3band_p1bs_psd/ Catalog data (IPAC) https://irsa.ipac.caltech.edu/data/download/wise-3band-psd/ Schema file (IPAC) https://irsa.ipac.caltech.edu/data/download/wise-3band-psd/wise_allsky_3band_p1bs_psd-schema.txt Catalog schema The original schema file was downloaded as: /datasets/gapon/wise/allsky_3band_p1bs_psd/allsky_3band_p1bs_psd.txt Then it was translated into MySQL schema definition (SQL DDL) file using this tool (available in GitHub package txt2schema.py ): % python \ /datasets/gapon/development/db_pdac_wise/tools//txt2schema .py \ allsky_3band_p1bs_psd.txt \ > allsky_3band_p1bs_psd.schema Made two modifications to thO original schema: renaming column *dec *into *decl *as a workaround to an existing limitation of the *Qserv *SQL parser (see DM-9736 ) manual addition of the PRIMARY key The relevant sections of the final schema file are presented below: CREATE TABLE `allsky_3band_p1bs_psd` ( `decl` DECIMAL (9,7) DEFAULT NULL , `source_id` CHAR (16) DEFAULT NULL , PRIMARY KEY (`source_id`) ) ENGINE=MyISAM; Saved the schema file into the corresponding GitHub package at: https://github.com/lsst-dm/db_pdac_wise/blob/master/sql/allsky_3band_p1bs_psd.sql Catalog data Started the download script from lsst-xfer. The files will be placed at the temporary data folder at: /datasets/gapon/wise/allsky_3band_p1bs_psd/downloaded/ The operation has succeeded with: 2304 compressed files ( bz2 ) 1 TB of total data amount

Hide

Permalink

Igor Gaponenko added a comment - 06/Jun/17 2:11 PM

Processing downloaded files of catalog allsky_3band_p1bs_psd

Uncompressing the files

The resulted files were put into the same folder where the compressed ones were located. The original files were replaced with the uncompressed ones. The total amount of data in the folder is 3.3 TB:

/datasets/gapon/wise/allsky_3band_p1bs_psd/downloaded/data/

Translating files from the unl format into the TSV format

This stage is needed because TSV is the only input data format supported by the LSST DB catalog partitioning tools. The output are placed at:

/datasets/gapon/wise/allsky_3band_p1bs_psd/tsv

The translation tool unl2tsv is in GitHub at:

https://github.com/lsst-dm/db_pdac_wise/tree/master/tools

This operation finished with 3.4 TB of data within the output folder.

Show

Igor Gaponenko added a comment - 06/Jun/17 2:11 PM Processing downloaded files of catalog allsky_3band_p1bs_psd Uncompressing the files The resulted files were put into the same folder where the compressed ones were located. The original files were replaced with the uncompressed ones. The total amount of data in the folder is 3.3 TB : /datasets/gapon/wise/allsky_3band_p1bs_psd/downloaded/data/ Translating files from the unl format into the TSV format This stage is needed because TSV is the only input data format supported by the LSST DB catalog partitioning tools. The output are placed at: /datasets/gapon/wise/allsky_3band_p1bs_psd/tsv The translation tool unl2tsv is in GitHub at: https://github.com/lsst-dm/db_pdac_wise/tree/master/tools This operation finished with 3.4 TB of data within the output folder.

Hide

Permalink

Igor Gaponenko added a comment - 06/Jun/17 2:39 PM - edited

Partitioning files of catalog allsky_3band_p1bs_psd

NOTE: this catalog was partitioned similarity to the earlier processed allsky_4band_p1bs_psd. Hence many repeating details are omitted here.

The destination folder for the partitioned data is:

/datasets/gapon/wise/allsky_3band_p1bs_psd/partitioned_x16/chunks

The customized partitioning script:

datasets/gapon/wise/allsky_3band_p1bs_psd/bin/partition.sh

Collected unique chunk identifiers from the corresponding log files:

% cd /datasets/gapon/wise/allsky_3band_p1bs_psd/partitioned_x16

% for f in `ls log/*.log`; do

  cat ${f};

done | grep '"id"' | awk '{print substr($2,0,length($2)-1)}' \ > all_chunks.txt

% cat all_chunks.txt  | sort -n -u > all_chunks_unique_sorted.txt

% wc -l all_chunks_unique_sorted.txt

45490 all_chunks_unique_sorted.txt

OBSERVATION: this number of 45490 chunks is a few times smaller than the total number of chunks (146332) in the 4-band mission's catalog allsky_4band_p1bs_psd. This is consistent with the differences in the Sky area coverage of WISE missions explained at:

http://irsa.ipac.caltech.edu/Missions/wise.html

The following test is also very important as it indicates that no new chunks were added in the 3-band catalog as compared with the 4-band one. This one has a true subset of chunks:

% diff \

  /datasets/gapon/wise/allsky_3band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted.txt \

  /datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted.txt \

  | grep '<' | wc -l

% diff \

  /datasets/gapon/wise/allsky_3band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted.txt \

  /datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted.txt \

  | grep '>' | wc -l

Hence no additional (new) chunk placement strategy will be needed for this catalog.

Show

Igor Gaponenko added a comment - 06/Jun/17 2:39 PM - edited Partitioning files of catalog allsky_3band_p1bs_psd NOTE : this catalog was partitioned similarity to the earlier processed allsky_4band_p1bs_psd . Hence many repeating details are omitted here. The destination folder for the partitioned data is: /datasets/gapon/wise/allsky_3band_p1bs_psd/partitioned_x16/chunks The customized partitioning script: datasets/gapon/wise/allsky_3band_p1bs_psd/bin/partition.sh Collected unique chunk identifiers from the corresponding log files: % cd /datasets/gapon/wise/allsky_3band_p1bs_psd/partitioned_x16 % for f in ` ls log/*.log`; do cat ${f}; done | grep '"id"' | awk '{print substr($2,0,length($2)-1)}' \ > all_chunks.txt % cat all_chunks.txt | sort -n -u > all_chunks_unique_sorted.txt % wc -l all_chunks_unique_sorted.txt 45490 all_chunks_unique_sorted.txt OBSERVATION : this number of 45490 chunks is a few times smaller than the total number of chunks ( 146332 ) in the 4-band mission's catalog allsky_4band_p1bs_psd . This is consistent with the differences in the Sky area coverage of WISE missions explained at: http://irsa.ipac.caltech.edu/Missions/wise.html The following test is also very important as it indicates that no new chunks were added in the 3-band catalog as compared with the 4-band one. This one has a true subset of chunks: % diff \ /datasets/gapon/wise/allsky_3band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted .txt \ /datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted .txt \ | grep '<' | wc -l 0 % diff \ /datasets/gapon/wise/allsky_3band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted .txt \ /datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted .txt \ | grep '>' | wc -l 100842 Hence no additional (new) chunk placement strategy will be needed for this catalog.

Hide

Permalink

Igor Gaponenko added a comment - 06/Jun/17 3:06 PM - edited

Preparing the staging area for chunks of catalog allsky_3band_p1bs_psd

NOTE: operations explained in this section are very similar to the ones for catalog allsky_4band_p1bs_psd. Specific differences are mentioned in this section.

General considerations and a setup

The staging area for the catalog is located at:

/datasets/gapon/production/wise_catalog_load/production_load/allsky_3band_p1bs_psd/

The chunk collocation requirement for this catalog should already be satisfied because a set of chunks of the catalog is a true subset of the ones of the 4-bad one. See the partitioning section for more details.

The worker-to-chunks map (for the link generator)

Using the previously deployed map at:

/datasets/gapon/development/db_pdac_wise/wise_worker2chunks/

Generating scripts to populate area

Generated a list of all files to be linked. This is a lengthy process due to the large number of chunk files created during the partitioning phase:

% cd /datasets/gapon/wise/allsky_3band_p1bs_psd/partitioned_x16/

% for d in `ls chunks/`; do

  ls -l chunks/${d}/* | grep .txt; done > all_chunks_and_overlaps.txt

% wc -l all_chunks_and_overlaps.txt

4997903 all_chunks_and_overlaps.txt

ATTENTION: this is 5 million files!

Using a custom version of the generator which reads the above created list of chunk files and overlaps, and the earlier created worker-to-chunks map:

% scripts/chunk2folder.py folder > scripts/stage_folder.source

% wc -l  scripts/stage_folder.source

138301 scripts/stage_folder.source

% scripts/chunk2folder.py index > scripts/stage_index.source

138241 scripts/stage_index.source

% scripts/chunk2folder.py chunk > scripts/stage_chunk.source

9995807 scripts/stage_chunk.source

And here is the code of the generator script:

# Read the existing mapping of chunks to workers

# in the WISE catalog deployment in PDAC

import sys

from glob import glob

chunk2worker = {}

for f in glob('/datasets/gapon/development/db_pdac_wise/wise_worker2chunks/*'):

    worker = f[f.rfind('/')+1:]

    with open(f,'r') as files:

        for l in files:

            chunk = int(l[:-1])

            chunk2worker[chunk] = worker

# Sort a plain list of all files for chunks and the corresponding

# overlaps into a dictionary of:

#   [chunk][folder]

# Each value of the dictionary will represent a list of

# the files.

def split2components (p):

    folder_name = p[len('chunks/'):p.rfind("/")]

    file_name   = p[p.rfind("/")+1:-len('.txt')]

    if file_name[-len('_overlap'):] == '_overlap': chunk = int(file_name[len('chunk_'):-len('_overlap')])

    else:                                          chunk = int(file_name[len('chunk_'):])

    return {

        'file'  : "%s.txt" % file_name,

        'folder': folder_name,

        'chunk' : chunk}

chunk2folder2file = {}

with open('all_chunks_and_overlaps.txt','r') as files:

    for f in files:

        c = split2components(f[:-1])

        chunk = c['chunk']

        if chunk not in chunk2folder2file: chunk2folder2file[chunk] = {}

        folder = c['folder']

        if folder not in chunk2folder2file[chunk]: chunk2folder2file[chunk][folder] = []

        chunk2folder2file[chunk][folder].append(c['file'])

# Generate commands for populating the loading staging area with

# folders and links to the partitioned files

# Generate three sets of commands for preparing the staging area

# from which the catalog loading will be happening:

# - creating a tree of folders

# - creating symbolik links for special files 'chunk_index.bin' (one

#   such file per each final folder where chunk files will be linked

#   as well)

# - for setting up symbolic links pointing to the chunk files

#   and the overlaps

# NOTE: We should do these steps separately because of a latency

#       for creating folders in the GPFS file system which may

#       cause subseqeunt operations to fail.

source      = '/datasets/gapon/wise/allsky_3band_p1bs_psd/partitioned_x16/chunks'

destination = '/datasets/gapon/production/wise_catalog_load/production_load/allsky_3band_p1bs_psd'

stage_folder = []

stage_index  = []

stage_chunk  = []

worker2folder = {}   ## the dictionary of [worker][folder] is used for bookkeeping

                     ## purposes to prevent issuing the same command for than once

chunks = chunk2folder2file.keys()

chunks.sort()

for chunk in chunks:

    if chunk not in chunk2worker: raise Exception("chunk %d is unknown" % chunk)

    worker = chunk2worker[chunk]

    if worker not in worker2folder:

        worker2folder[worker] = {}

        stage_folder.append("mkdir -p %s/%s" % (destination,worker,))

    for folder in chunk2folder2file[chunk]:

        if folder not in worker2folder[worker]:

            worker2folder[worker][folder] = 0

            stage_folder.append("mkdir -p %s/%s/%s" % (destination,worker,folder,))

            stage_index.append("ln -s %s/%s/chunk_index.bin %s/%s/%s/chunk_index.bin" % (source,folder,destination,worker,folder))

        for f in chunk2folder2file[chunk][folder]:

            stage_chunk.append("ln -s %s/%s/%s %s/%s/%s/%s" % (source,folder,f,destination,worker,folder,f))

# Print commands from the specified stage

stage2print = {

    'folder' : stage_folder,

    'index'  : stage_index,

    'chunk'  : stage_chunk}

help_ = "usage: %s {%s}" % (sys.argv[0], "|".join([stage for stage in stage2print.keys()]),)

if len(sys.argv) != 2:

    print help_

    sys.exit(1)

stage = sys.argv[1]

if stage not in stage2print.keys():

    print help_

    sys.exit(1)

print "#!/bin/bash"

for op in stage2print[stage]:

    print "echo %s" % op

    print op

TODO: put it to GitHub.

Populating the area

Run all 3 scripts sequentially. It's very IMPORTANT to create folders before setting up the links. There is an undesired latency in the GPFS metadata service which may delay catalog stats reporting thus causing the subsequent (link and index) stages to fail if the second stages are mixed together with the folder operations.

% nohup ./scripts/stage_folder.source >& log/stage_folder.log

% nohup ./scripts/stage_index.source >& log/stage_index.log

% nohup ./scripts/stage_chunk.source >& log/stage_chunk.log

Show

Igor Gaponenko added a comment - 06/Jun/17 3:06 PM - edited Preparing the staging area for chunks of catalog allsky_3band_p1bs_psd NOTE : operations explained in this section are very similar to the ones for catalog allsky_4band_p1bs_psd . Specific differences are mentioned in this section. General considerations and a setup The staging area for the catalog is located at: /datasets/gapon/production/wise_catalog_load/production_load/allsky_3band_p1bs_psd/ The chunk collocation requirement for this catalog should already be satisfied because a set of chunks of the catalog is a true subset of the ones of the 4-bad one. See the partitioning section for more details. The worker-to-chunks map (for the link generator) Using the previously deployed map at: /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/ Generating scripts to populate area Generated a list of all files to be linked. This is a lengthy process due to the large number of chunk files created during the partitioning phase: % cd /datasets/gapon/wise/allsky_3band_p1bs_psd/partitioned_x16/ % for d in ` ls chunks/`; do ls -l chunks/${d}/* | grep .txt; done > all_chunks_and_overlaps.txt % wc -l all_chunks_and_overlaps.txt 4997903 all_chunks_and_overlaps.txt ATTENTION : this is 5 million files! Using a custom version of the generator which reads the above created list of chunk files and overlaps, and the earlier created worker-to-chunks map: % scripts /chunk2folder .py folder > scripts /stage_folder . source % wc -l scripts /stage_folder . source 138301 scripts /stage_folder . source % scripts /chunk2folder .py index > scripts /stage_index . source 138241 scripts /stage_index . source % scripts /chunk2folder .py chunk > scripts /stage_chunk . source 9995807 scripts /stage_chunk . source And here is the code of the generator script: # Read the existing mapping of chunks to workers # in the WISE catalog deployment in PDAC import sys from glob import glob chunk2worker = {} for f in glob( '/datasets/gapon/development/db_pdac_wise/wise_worker2chunks/*' ): worker = f[f.rfind( '/' ) + 1 :] with open (f, 'r' ) as files: for l in files: chunk = int (l[: - 1 ]) chunk2worker[chunk] = worker # Sort a plain list of all files for chunks and the corresponding # overlaps into a dictionary of: # # [chunk][folder] # # Each value of the dictionary will represent a list of # the files. def split2components (p): folder_name = p[ len ( 'chunks/' ):p.rfind( "/" )] file_name = p[p.rfind( "/" ) + 1 : - len ( '.txt' )] if file_name[ - len ( '_overlap' ):] = = '_overlap' : chunk = int (file_name[ len ( 'chunk_' ): - len ( '_overlap' )]) else : chunk = int (file_name[ len ( 'chunk_' ):]) return { 'file' : "%s.txt" % file_name, 'folder' : folder_name, 'chunk' : chunk} chunk2folder2file = {} with open ( 'all_chunks_and_overlaps.txt' , 'r' ) as files: for f in files: c = split2components(f[: - 1 ]) chunk = c[ 'chunk' ] if chunk not in chunk2folder2file: chunk2folder2file[chunk] = {} folder = c[ 'folder' ] if folder not in chunk2folder2file[chunk]: chunk2folder2file[chunk][folder] = [] chunk2folder2file[chunk][folder].append(c[ 'file' ]) # Generate commands for populating the loading staging area with # folders and links to the partitioned files # Generate three sets of commands for preparing the staging area # from which the catalog loading will be happening: # - creating a tree of folders # # - creating symbolik links for special files 'chunk_index.bin' (one # such file per each final folder where chunk files will be linked # as well) # # - for setting up symbolic links pointing to the chunk files # and the overlaps # # NOTE: We should do these steps separately because of a latency # for creating folders in the GPFS file system which may # cause subseqeunt operations to fail. source = '/datasets/gapon/wise/allsky_3band_p1bs_psd/partitioned_x16/chunks' destination = '/datasets/gapon/production/wise_catalog_load/production_load/allsky_3band_p1bs_psd' stage_folder = [] stage_index = [] stage_chunk = [] worker2folder = {} ## the dictionary of [worker][folder] is used for bookkeeping ## purposes to prevent issuing the same command for than once chunks = chunk2folder2file.keys() chunks.sort() for chunk in chunks: if chunk not in chunk2worker: raise Exception( "chunk %d is unknown" % chunk) worker = chunk2worker[chunk] if worker not in worker2folder: worker2folder[worker] = {} stage_folder.append( "mkdir -p %s/%s" % (destination,worker,)) for folder in chunk2folder2file[chunk]: if folder not in worker2folder[worker]: worker2folder[worker][folder] = 0 stage_folder.append( "mkdir -p %s/%s/%s" % (destination,worker,folder,)) stage_index.append( "ln -s %s/%s/chunk_index.bin %s/%s/%s/chunk_index.bin" % (source,folder,destination,worker,folder)) for f in chunk2folder2file[chunk][folder]: stage_chunk.append( "ln -s %s/%s/%s %s/%s/%s/%s" % (source,folder,f,destination,worker,folder,f)) # Print commands from the specified stage stage2print = { 'folder' : stage_folder, 'index' : stage_index, 'chunk' : stage_chunk} help_ = "usage: %s {%s}" % (sys.argv[ 0 ], "|" .join([stage for stage in stage2print.keys()]),) if len (sys.argv) ! = 2 : print help_ sys.exit( 1 ) stage = sys.argv[ 1 ] if stage not in stage2print.keys(): print help_ sys.exit( 1 ) print "#!/bin/bash" for op in stage2print[stage]: print "echo %s" % op print op TODO : put it to GitHub . Populating the area Run all 3 scripts sequentially. It's very IMPORTANT to create folders before setting up the links. There is an undesired latency in the GPFS metadata service which may delay catalog stats reporting thus causing the subsequent (link and index) stages to fail if the second stages are mixed together with the folder operations. % nohup . /scripts/stage_folder . source >& log /stage_folder .log % nohup . /scripts/stage_index . source >& log /stage_index .log % nohup . /scripts/stage_chunk . source >& log /stage_chunk .log

Hide

Permalink

Igor Gaponenko added a comment - 06/Jun/17 6:26 PM - edited

Downloading WISE Post-Cryo Single Exposure (L1b) Source Source Table data

Table name	allsky_2band_p1bs_psd
Temporary data folder (NCSA)	/datasets/gapon/wise/allsky_2band_p1bs_psd/
Catalog data (IPAC)	https://irsa.ipac.caltech.edu/data/download/wise-2band-psd/
Schema file (IPAC)	https://irsa.ipac.caltech.edu/data/download/wise-2band-psd/allsky_2band_p1bs_psd-schema.txt

Catalog schema

The original schema file was downloaded as:

/datasets/gapon/wise/allsky_2band_p1bs_psd/allsky_2band_p1bs_psd.txt

Then it was translated into MySQL schema definition (SQL DDL) file using this tool (available in GitHub package txt2schema.py):

% python \

  /datasets/gapon/development/db_pdac_wise/tools//txt2schema.py \

    allsky_2band_p1bs_psd.txt \

    > allsky_2band_p1bs_psd.sql

Made two modifications to the original schema:

renaming column *dec *into *decl *as a workaround to an existing limitation of the *Qserv *SQL parser (see ~~DM-9736~~)
manual addition of the PRIMARY key

The relevant sections of the final schema file are presented below:

CREATE TABLE `allsky_2band_p1bs_psd` (

    `SOURCE_ID`    VARCHAR(16)   DEFAULT NULL,

          `DECL`   DECIMAL(9,7)  DEFAULT NULL,

    PRIMARY KEY(`SOURCE_ID`)

) ENGINE=MyISAM;

Saved the schema file into the corresponding GitHub package at:

https://github.com/lsst-dm/db_pdac_wise/blob/master/sql/allsky_2band_p1bs_psd.sql

Catalog data

Started the download script from lsst-xfer. The files will be placed at the temporary data folder at:

/datasets/gapon/wise/allsky_2band_p1bs_psd/downloaded/

The operation has succeeded with:

1152 compressed files (bz2)
1.6 TB of total data amount

Show

Igor Gaponenko added a comment - 06/Jun/17 6:26 PM - edited Downloading WISE Post-Cryo Single Exposure (L1b) Source Source Table data Table name allsky_2band_p1bs_psd Temporary data folder (NCSA) /datasets/gapon/wise/allsky_2band_p1bs_psd/ Catalog data (IPAC) https://irsa.ipac.caltech.edu/data/download/wise-2band-psd/ Schema file (IPAC) https://irsa.ipac.caltech.edu/data/download/wise-2band-psd/allsky_2band_p1bs_psd-schema.txt Catalog schema The original schema file was downloaded as: /datasets/gapon/wise/allsky_2band_p1bs_psd/allsky_2band_p1bs_psd.txt Then it was translated into MySQL schema definition (SQL DDL) file using this tool (available in GitHub package txt2schema.py ): % python \ /datasets/gapon/development/db_pdac_wise/tools//txt2schema .py \ allsky_2band_p1bs_psd.txt \ > allsky_2band_p1bs_psd.sql Made two modifications to the original schema: renaming column *dec *into *decl *as a workaround to an existing limitation of the *Qserv *SQL parser (see DM-9736 ) manual addition of the PRIMARY key The relevant sections of the final schema file are presented below: CREATE TABLE `allsky_2band_p1bs_psd` ( `SOURCE_ID` VARCHAR (16) DEFAULT NULL , `DECL` DECIMAL (9,7) DEFAULT NULL , PRIMARY KEY (`SOURCE_ID`) ) ENGINE=MyISAM; Saved the schema file into the corresponding GitHub package at: https://github.com/lsst-dm/db_pdac_wise/blob/master/sql/allsky_2band_p1bs_psd.sql Catalog data Started the download script from lsst-xfer. The files will be placed at the temporary data folder at: /datasets/gapon/wise/allsky_2band_p1bs_psd/downloaded/ The operation has succeeded with: 1152 compressed files ( bz2 ) 1.6 TB of total data amount

Hide

Permalink

Igor Gaponenko added a comment - 07/Jun/17 1:28 PM - edited

Processing downloaded files of catalog allsky_2band_p1bs_psd

Uncompressing the files

/datasets/gapon/wise/allsky_2band_p1bs_psd/downloaded/data/

Translating files from the unl format into the TSV format

This stage is needed because TSV is the only input data format supported by the LSST DB catalog partitioning tools. Meanwhile the uncompressed files have bar ('|') separated fields. For example:

% head -1 data/allsky_2band_p1bs_psd.P1152_1000.csv

09192a024-005472|24|09192a|5472|172.7979845|81.2987903|0.9951000000000001|0.9689000000000001|0.3967|126.5754611|35.2543209|110.0790643|64.0764644|857.956|402.791|855.21|400.58500000000004|8.465|3.1140000000000003|0.0|24.594|4.157|1.0290000000000001|3.4|-0.4|47.0670013|13.713000300000001|-6.24910021|16.7849998|16.603|0.316|1.29799998|15.879000000000001||0.922900021|1.05499995|1|0|0.0|0.0|0.0|0.0|0000|15.848||32|0.28500000000000003|15.096||32|0.34|16.534000000000002||32|15.92||32|16.133||32|15.436||32|15.875||32|15.048||32|15.609||32|14.93||32|16.076|0.537|3|14.824||32|15.864|0.523|3|14.646||32|15.723|0.528|3|14.483||32|15.448|0.468|3|14.328000000000001||32|16.6023|15.8817|-0.0007|0.0027|15.8473|15.098700000000001|0.015700000000000002|-0.001|0||0||0000|10|1.0|88.257|999999.0|0000|1|BUXX||0|0||||0|||||||55483.562573|903|919201024005472|-0.150088122141745|0.0189659031314817|0.988490692981159|221002011|15676971526579

Also note misleading file extensions of .csv in the above shown files.

The output are placed at:

/datasets/gapon/wise/allsky_2band_p1bs_psd/tsv

The translation tool unl2tsv is in GitHub at:

https://github.com/lsst-dm/db_pdac_wise/tree/master/tools

This operation finished with 7.4 TB of data within the output folder.

Show

Igor Gaponenko added a comment - 07/Jun/17 1:28 PM - edited Processing downloaded files of catalog allsky_2band_p1bs_psd Uncompressing the files The resulted files were put into the same folder where the compressed ones were located. The original files were replaced with the uncompressed ones. The total amount of data in the folder is 7.2 TB : /datasets/gapon/wise/allsky_2band_p1bs_psd/downloaded/data/ Translating files from the unl format into the TSV format This stage is needed because TSV is the only input data format supported by the LSST DB catalog partitioning tools. Meanwhile the uncompressed files have bar ('|') separated fields. For example: % head -1 data /allsky_2band_p1bs_psd .P1152_1000.csv 09192a024-005472|24|09192a|5472|172.7979845|81.2987903|0.9951000000000001|0.9689000000000001|0.3967|126.5754611|35.2543209|110.0790643|64.0764644|857.956|402.791|855.21|400.58500000000004|8.465|3.1140000000000003|0.0|24.594|4.157|1.0290000000000001|3.4|-0.4|47.0670013|13.713000300000001|-6.24910021|16.7849998|16.603|0.316|1.29799998|15.879000000000001||0.922900021|1.05499995|1|0|0.0|0.0|0.0|0.0|0000|15.848||32|0.28500000000000003|15.096||32|0.34|16.534000000000002||32|15.92||32|16.133||32|15.436||32|15.875||32|15.048||32|15.609||32|14.93||32|16.076|0.537|3|14.824||32|15.864|0.523|3|14.646||32|15.723|0.528|3|14.483||32|15.448|0.468|3|14.328000000000001||32|16.6023|15.8817|-0.0007|0.0027|15.8473|15.098700000000001|0.015700000000000002|-0.001|0||0||0000|10|1.0|88.257|999999.0|0000|1|BUXX||0|0||||0|||||||55483.562573|903|919201024005472|-0.150088122141745|0.0189659031314817|0.988490692981159|221002011|15676971526579 Also note misleading file extensions of .csv in the above shown files. The output are placed at: /datasets/gapon/wise/allsky_2band_p1bs_psd/tsv The translation tool unl2tsv is in GitHub at: https://github.com/lsst-dm/db_pdac_wise/tree/master/tools This operation finished with 7.4 TB of data within the output folder.

Hide

Permalink

Igor Gaponenko added a comment - 08/Jun/17 6:25 PM - edited

Partitioning files of catalog allsky_2band_p1bs_psd

NOTE: this catalog was partitioned similarity to the earlier processed allsky_3band_p1bs_psd. Hence many repeating details are omitted here.

The destination folder for the partitioned data is:

/datasets/gapon/wise/allsky_2band_p1bs_psd/partitioned_x16/chunks

The customized partitioning script:

/datasets/gapon/wise/allsky_2band_p1bs_psd/bin/partition.sh

Launching the jobs on the SLURM cluster:

% cd /datasets/gapon/wise/allsky_2band_p1bs_psd/

% for f in `ls files2partition/`; do

    sleep 10

    nohup srun /datasets/gapon/wise/allsky_2band_p1bs_psd/bin/partition.sh files2partition/${f} \

    >& /datasets/gapon/wise/allsky_2band_p1bs_psd/logs/${f}.log&

done

NOTE: the extra delay of 10 seconds before each step. This is meant to eliminate a chance of the race condition on a lock when executing the LSST Stack's setup.

Collected unique chunk identifiers from the corresponding log files:

% cd /datasets/gapon/wise/allsky_2band_p1bs_psd/partitioned_x16

% for f in `ls log/*.log`; do

  cat ${f};

done | grep '"id"' | awk '{print substr($2,0,length($2)-1)}' \ > all_chunks.txt

% cat all_chunks.txt  | sort -n -u > all_chunks_unique_sorted.txt

% wc -l all_chunks_unique_sorted.txt

106941 all_chunks_unique_sorted.txt

OBSERVATION: this number of 106941 chunks is a few times smaller than the total number of chunks (146332) in the 4-band mission's catalog allsky_4band_p1bs_psd. This is consistent with the differences in the Sky area coverage of WISE missions explained at:

http://irsa.ipac.caltech.edu/Missions/wise.html

The following test is also very important as it indicates that no new chunks were added in the 2-band catalog as compared with the 4-band one. This one has a true subset of chunks:

% diff \

  /datasets/gapon/wise/allsky_2band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted.txt \

  /datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted.txt \

  | grep '<' | wc -l

% diff \

  /datasets/gapon/wise/allsky_2band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted.txt \

  /datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted.txt \

  | grep '>' | wc -l

Hence no additional (new) chunk placement strategy will be needed for this catalog.

Show

Igor Gaponenko added a comment - 08/Jun/17 6:25 PM - edited Partitioning files of catalog allsky_2band_p1bs_psd NOTE : this catalog was partitioned similarity to the earlier processed allsky_3band_p1bs_psd . Hence many repeating details are omitted here. The destination folder for the partitioned data is: /datasets/gapon/wise/allsky_2band_p1bs_psd/partitioned_x16/chunks The customized partitioning script: /datasets/gapon/wise/allsky_2band_p1bs_psd/bin/partition.sh Launching the jobs on the SLURM cluster: % cd /datasets/gapon/wise/allsky_2band_p1bs_psd/ % for f in ` ls files2partition/`; do sleep 10 nohup srun /datasets/gapon/wise/allsky_2band_p1bs_psd/bin/partition .sh files2partition/${f} \ >& /datasets/gapon/wise/allsky_2band_p1bs_psd/logs/ ${f}.log& done NOTE : the extra delay of 10 seconds before each step. This is meant to eliminate a chance of the race condition on a lock when executing the LSST Stack's setup . Collected unique chunk identifiers from the corresponding log files: % cd /datasets/gapon/wise/allsky_2band_p1bs_psd/partitioned_x16 % for f in ` ls log/*.log`; do cat ${f}; done | grep '"id"' | awk '{print substr($2,0,length($2)-1)}' \ > all_chunks.txt % cat all_chunks.txt | sort -n -u > all_chunks_unique_sorted.txt % wc -l all_chunks_unique_sorted.txt 106941 all_chunks_unique_sorted.txt OBSERVATION : this number of 106941 chunks is a few times smaller than the total number of chunks ( 146332 ) in the 4-band mission's catalog allsky_4band_p1bs_psd . This is consistent with the differences in the Sky area coverage of WISE missions explained at: http://irsa.ipac.caltech.edu/Missions/wise.html The following test is also very important as it indicates that no new chunks were added in the 2-band catalog as compared with the 4-band one. This one has a true subset of chunks: % diff \ /datasets/gapon/wise/allsky_2band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted .txt \ /datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted .txt \ | grep '<' | wc -l 0 % diff \ /datasets/gapon/wise/allsky_2band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted .txt \ /datasets/gapon/wise/allsky_4band_p1bs_psd/partitioned_x16/all_chunks_unique_sorted .txt \ | grep '>' | wc -l 39391 Hence no additional (new) chunk placement strategy will be needed for this catalog.

Hide

Permalink

Igor Gaponenko added a comment - 09/Jun/17 6:07 PM

Preparing the staging area for chunks of catalog allsky_2band_p1bs_psd

NOTE: operations explained in this section are very similar to the ones for catalogs allsky_4band_p1bs_psd and allsky_3band_p1bs_psd. Specific differences are mentioned in this section.

General considerations and a setup

The staging area for the catalog is located at:

/datasets/gapon/production/wise_catalog_load/production_load/allsky_2band_p1bs_psd/

The worker-to-chunks map (for the link generator)

Using the previously deployed map at:

/datasets/gapon/development/db_pdac_wise/wise_worker2chunks/

Generating scripts to populate area

Collecting the names of all chunk files to be linked:

% cd /datasets/gapon/wise/allsky_2band_p1bs_psd/partitioned_x16/

% for d in `ls chunks/`; do

    ls -1 chunks/${d}/* | grep .txt

done > all_chunks_and_overlaps.txt

% wc -l all_chunks_and_overlaps.txt

324645 all_chunks_and_overlaps.txt

Using a custom version of the generator which reads the above created list of chunk files and overlaps, and the earlier created worker-to-chunks map:

% python scripts/chunk2folder.py folder > scripts/stage_folder.source

% wc -l  scripts/stage_folder.source

66133 scripts/stage_folder.source

% python scripts/chunk2folder.py index > scripts/stage_index.source

% wc -l  scripts/stage_index.source

66073 scripts/stage_index.source

% python scripts/chunk2folder.py chunk > scripts/stage_chunk.source

% wc -l  scripts/stage_chunk.source

649291 scripts/stage_chunk.source

Populating the area

% nohup ./scripts/stage_folder.source >& log/stage_folder.log

% nohup ./scripts/stage_index.source >& log/stage_index.log

% nohup ./scripts/stage_chunk.source >& log/stage_chunk.log

Show

Igor Gaponenko added a comment - 09/Jun/17 6:07 PM Preparing the staging area for chunks of catalog allsky_2band_p1bs_psd NOTE : operations explained in this section are very similar to the ones for catalogs allsky_4band_p1bs_psd and allsky_3band_p1bs_psd . Specific differences are mentioned in this section. General considerations and a setup The staging area for the catalog is located at: /datasets/gapon/production/wise_catalog_load/production_load/allsky_2band_p1bs_psd/ The chunk collocation requirement for this catalog should already be satisfied because a set of chunks of the catalog is a true subset of the ones of the 4-bad one. See the partitioning section for more details. The worker-to-chunks map (for the link generator) Using the previously deployed map at: /datasets/gapon/development/db_pdac_wise/wise_worker2chunks/ Generating scripts to populate area Collecting the names of all chunk files to be linked: % cd /datasets/gapon/wise/allsky_2band_p1bs_psd/partitioned_x16/ % for d in ` ls chunks/`; do ls -1 chunks/${d}/* | grep .txt done > all_chunks_and_overlaps.txt % wc -l all_chunks_and_overlaps.txt 324645 all_chunks_and_overlaps.txt Using a custom version of the generator which reads the above created list of chunk files and overlaps, and the earlier created worker-to-chunks map: % python scripts /chunk2folder .py folder > scripts /stage_folder . source % wc -l scripts /stage_folder . source 66133 scripts /stage_folder . source % python scripts /chunk2folder .py index > scripts /stage_index . source % wc -l scripts /stage_index . source 66073 scripts /stage_index . source % python scripts /chunk2folder .py chunk > scripts /stage_chunk . source % wc -l scripts /stage_chunk . source 649291 scripts /stage_chunk . source Populating the area Run all 3 scripts sequentially. It's very IMPORTANT to create folders before setting up the links. There is an undesired latency in the GPFS metadata service which may delay catalog stats reporting thus causing the subsequent (link and index) stages to fail if the second stages are mixed together with the folder operations. % nohup . /scripts/stage_folder . source >& log /stage_folder .log % nohup . /scripts/stage_index . source >& log /stage_index .log % nohup . /scripts/stage_chunk . source >& log /stage_chunk .log

Hide

Permalink

Igor Gaponenko added a comment - 09/Jun/17 6:08 PM - edited

Loading catalog allsky_2band_p1bs_psd into PDAC

The loading protocol is explained in details at:

https://confluence.lsstcorp.org/display/DM/Loading+WISE+catalogs+into+PDAC

The rest of this section presents a short summary of actions and tests taken during this loading.

Dataset configuration

Database name: wise_2band_00

Do this at lsst-dev:

% cd /datasets/gapon/development/db_pdac_wise/scripts

% ln -s dataset.bash.wise_2band_00  dataset.bash

Creating database and initializing CSS

All these steps were conducted on the master node of the cluster lsst-qserv-master01:

% /bin/sudo -u qserv \

  /datasets/gapon/development/db_pdac_wise/scripts/run_create_database.bash \

  --delete --verbose >& run_create_database.log&

% /bin/sudo -u qserv \

  /datasets/gapon/development/db_pdac_wise/scripts/setup_css.bash \

  >& setup_css.log

% 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 wise_2band_00

/DBS/wise_2band_00	READY

/DBS/wise_2band_00/.packed.json	{"partitioningId":"0000000027","releaseStatus":"RELEASED","storageClass":"L2"}

/DBS/wise_2band_00/TABLES	\N

/DBS/wise_2band_00/TABLES/allsky_2band_p1bs_psd	READY

/DBS/wise_2band_00/TABLES/allsky_2band_p1bs_psd/partitioning	\N

/DBS/wise_2band_00/TABLES/allsky_2band_p1bs_psd/partitioning/.packed.json	{"dirColName":"source_id","dirDb":"wise_2band_00","dirTable":"allsky_2band_p1bs_psd","latColName":"decl","lonColName":"ra","subChunks":"1"}

/DBS/wise_2band_00/TABLES/allsky_2band_p1bs_psd/schema	(

/DBS/wise_2band_00/TABLES/allsky_2band_p1bs_psd/sharedScan	\N

/DBS/wise_2band_00/TABLES/allsky_2band_p1bs_psd/sharedScan/.packed.json	{"lockInMem":"1"}

Configuring MySQL server and Docker container

/bin/sudo -u qserv \

  /datasets/gapon/development/db_pdac_wise/scripts/run_show_processlist.bash --total

% /bin/sudo -u qserv \

  /datasets/gapon/development/db_pdac_wise/scripts/run_set_max_connections.bash

% /bin/sudo -u qserv \

  /datasets/gapon/development/db_pdac_wise/scripts/run_show_processlist.bash --total

sst-qserv-master01: 14 / 16384

lsst-qserv-db01: 2 / 16384

lsst-qserv-db02: 2 / 16384

lsst-qserv-db03: 2 / 16384

...

% docker ps

CONTAINER ID        IMAGE                                 COMMAND                  CREATED             STATUS              PORTS               NAMES

44d15b27eac2        qserv/qserv:tickets_DM-10283_master   "/bin/sh -c /qserv/sc"   2 weeks ago         Up 2 weeks                              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"

],

Loading partitioned tables

% nohup /bin/sudo -u qserv \

  /datasets/gapon/development/db_pdac_wise/scripts/run_load_object.bash \

  --verbose \

  >& run_load_object.log&

Testing results:

% tail /tmp/wise_2band_00/log/qserv-db*_load_object.log | grep 'Finished' | wc -l

% tail /tmp/wise_2band_00/log/qserv-db*_load_object.log | grep 'Finished'

[Sat Jun 10 13:59:28 CDT 2017] ** Finished loading **

[Sat Jun 10 13:50:58 CDT 2017] ** Finished loading **

[Sat Jun 10 13:39:20 CDT 2017] ** Finished loading **

[Sat Jun 10 13:33:27 CDT 2017] ** Finished loading **

[Sat Jun 10 13:28:02 CDT 2017] ** Finished loading **

[Sat Jun 10 13:28:10 CDT 2017] ** Finished loading **

[Sat Jun 10 13:24:06 CDT 2017] ** Finished loading **

[Sat Jun 10 13:22:07 CDT 2017] ** Finished loading **

[Sat Jun 10 13:12:26 CDT 2017] ** Finished loading **

[Sat Jun 10 13:12:15 CDT 2017] ** Finished loading **

[Sat Jun 10 13:16:00 CDT 2017] ** Finished loading **

[Sat Jun 10 13:14:34 CDT 2017] ** Finished loading **

[Sat Jun 10 13:18:59 CDT 2017] ** Finished loading **

[Sat Jun 10 13:11:46 CDT 2017] ** Finished loading **

[Sat Jun 10 13:13:28 CDT 2017] ** Finished loading **

[Sat Jun 10 13:25:58 CDT 2017] ** Finished loading **

[Sat Jun 10 13:30:49 CDT 2017] ** Finished loading **

[Sat Jun 10 13:22:43 CDT 2017] ** Finished loading **

[Sat Jun 10 13:21:04 CDT 2017] ** Finished loading **

[Sat Jun 10 13:23:58 CDT 2017] ** Finished loading **

[Sat Jun 10 13:25:21 CDT 2017] ** Finished loading **

[Sat Jun 10 13:38:21 CDT 2017] ** Finished loading **

[Sat Jun 10 13:37:53 CDT 2017] ** Finished loading **

[Sat Jun 10 13:31:47 CDT 2017] ** Finished loading **

[Sat Jun 10 13:25:03 CDT 2017] ** Finished loading **

[Sat Jun 10 13:27:11 CDT 2017] ** Finished loading **

[Sat Jun 10 13:25:20 CDT 2017] ** Finished loading **

[Sat Jun 10 13:34:24 CDT 2017] ** Finished loading **

[Sat Jun 10 13:43:53 CDT 2017] ** Finished loading **

[Sat Jun 10 13:57:27 CDT 2017] ** Finished loading **

Generating the secondary index (FIRST ATTEMPT)

Harvesting triplets:

% nohup \

  /bin/sudo -u qserv \

    /datasets/gapon/development/db_pdac_wise/scripts/run_dump_triplet.bash \

    --verbose \

    >& run_dump_triplet.log&

Loading triplets into the index:

% nohup \

  /bin/sudo -u qserv \

    /datasets/gapon/development/db_pdac_wise/scripts/load_secondary_index.bash \

    --verbose \

    --get-remote-triplets \

    >& load_secondary_index.log&

Performance degradation after loading 60% of TSV files

The number of files which have been loaded:

% cat load_secondary_index.log | grep 'loading tri'  | wc -l

SHOW PROCESSLIST;

| 64107 | root     | localhost       | NULL             | Query   |  9519 | reading file | LOAD DATA INFILE '/qserv/data/dumps/wise_2band_00/idx_object_200800.tsv' INTO TABLE qservMeta.wise_2 |   36.303 |

Actually, the gradual performance degradation was seen much earlier. Later it become more obvious before it literally stalled while loading file idx_object_200800.tsv as per the log file analysis:

% cat load_secondary_index.log | grep 'loading tri'  | tail -3

loading triplest from file: idx_object_200798.tsv

loading triplest from file: idx_object_200799.tsv

loading triplest from file: idx_object_200800.tsv

The last file is being loaded for the last 2 hours (at least). The file is noticeable larger than the previous ones:

% sudo ls -al /qserv/data/dumps/wise_2band_00/ | grep -B 4 -A 4 idx_object_200800.tsv

-rw-r--r-- 1 qserv qserv  23608289 Jun 10 20:54 idx_object_200796.tsv

-rw-r--r-- 1 qserv qserv  30216240 Jun 10 21:11 idx_object_200797.tsv

-rw-r--r-- 1 qserv qserv  44797664 Jun 10 21:28 idx_object_200798.tsv

-rw-r--r-- 1 qserv qserv  73420288 Jun 10 21:44 idx_object_200799.tsv

-rw-r--r-- 1 qserv qserv 148547683 Jun 10 22:02 idx_object_200800.tsv

-rw-r--r-- 1 qserv qserv 156758880 Jun 10 22:19 idx_object_200801.tsv

-rw-r--r-- 1 qserv qserv  75832749 Jun 10 22:35 idx_object_200802.tsv

-rw-r--r-- 1 qserv qserv  42320964 Jun 10 22:53 idx_object_200803.tsv

-rw-r--r-- 1 qserv qserv  30272316 Jun 10 23:10 idx_object_200804.tsv

Reconfiguring InnoDB

Further analysis of the database service configuration showed that the server runs with the default configuration of the InnoDB storage engine. This configuration seems to be way to suboptimal for the large bulk inserts. This is seen with:

SHOW VARIABLES LIKE 'innodb_buffer_pool_%';

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

| Variable_name                       | Value          |

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

| innodb_buffer_pool_dump_at_shutdown | OFF            |

| innodb_buffer_pool_dump_now         | OFF            |

| innodb_buffer_pool_dump_pct         | 100            |

| innodb_buffer_pool_filename         | ib_buffer_pool |

| innodb_buffer_pool_instances        | 8              |

| innodb_buffer_pool_load_abort       | OFF            |

| innodb_buffer_pool_load_at_startup  | OFF            |

| innodb_buffer_pool_load_now         | OFF            |

| innodb_buffer_pool_size             | 134217728      |

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

Among those the most problematic one is the last parameter innodb_buffer_pool_size which is set to 128 MB only MySQL suggest that it should be set up to 80% of the machine's RAM.

Unfortunately this parameter is NOT DYNAMIC for a version of the MariaDB service run inside the Qserv container:

% mysql ...

Server version: 10.1.21-MariaDB Source distribution

The documentation suggests that the parameter is DYNAMIC as of verision 10.2.2 or higher. Therefore the only solution is to abort the index loading, reconfigure the service and resume the loading process from a file which takes to long to load.

Aborting the loading:

% ps -ef | grep load_

root      6142     1  0 Jun14 ?        00:00:00 /bin/sudo -u qserv /datasets/gapon/development/db_pdac_wise/scripts/load_secondary_index.bash --verbose

qserv     6144  6142  0 Jun14 ?        00:01:23 /bin/bash /datasets/gapon/development/db_pdac_wise/scripts/load_secondary_index.bash --verbose

gapon    18007 16017  0 16:16 pts/0    00:00:00 grep --color=auto load_

% sudo kill 6142 6144

Though, this didn't really kill the loading thread within the service as per:

SHOW PROCESSLIST;

| 64107 | root     | localhost       | NULL             | Query   | 10187 | reading file | LOAD DATA INFILE '/qserv/data/dumps/wise_2band_00/idx_object_200800.tsv' INTO TABLE qservMeta.wise_2 |   38.176 |

Besides, the server is still running in then same node as earlier:

% top

top - 16:28:00 up 2 days,  6:17,  1 user,  load average: 1.33, 1.39, 1.40

Tasks: 485 total,   1 running, 484 sleeping,   0 stopped,   0 zombie

%Cpu(s):  0.2 us,  0.1 sy,  0.0 ni, 97.7 id,  2.1 wa,  0.0 hi,  0.0 si,  0.0 st

KiB Mem : 13128352+total,   440624 free,  2054096 used, 12878880+buff/cache

KiB Swap:  4194300 total,  4103904 free,    90396 used. 12876192+avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND

 4806 qserv     20   0 3242864 255704   4808 S   9.6  0.2 732:49.71 mysqld

...

Reconfiguring and restarting the service by logging into the Docker container:

[gapon@lsst-qserv-master01 ~]$ docker exec -it qserv bash

% export TERM=xterm

% run/etc/init.d/mysqld help

Usage: run/etc/init.d/mysqld  {start|stop|restart|reload|force-reload|status}  [ MySQL server options ]

% mv run/etc/my.cnf run/etc/my.cnf.saved

# added two parameters

% diff run/etc/my.cnf run/etc/my.cnf.saved

18,20d17

< innodb_buffer_pool_size=96G

< innodb_buffer_pool_instances=1

% run/etc/init.d/mysqld force-reload

[ ok ] Reloading service MySQL.

# UNFORTUNATELY this did NOT stop the server

stop

# The previous operation was stuck as well. Killing the server process in the brute force way:

% ps -ef

qserv       90     1  0 Jun14 ?        00:00:00 /bin/sh /qserv/stack/Linux64/mariadb/10.1.21.lsst1/bin/mysqld_safe --defaults-file=/qserv/run/etc/my.cnf --datadir=/qserv/d

qserv      210    90 23 Jun14 ?        12:14:16 /qserv/stack/Linux64/mariadb/10.1.21.lsst1/bin/mysqld --defaults-file=/qserv/run/etc/my.cnf --basedir=/qserv/stack/Linux64/

% kill -9 90 210

# Cleaning up the PID because of

% run/etc/init.d/mysqld status

[ ok ] MySQL running (27236).

% ls -al run/var/run/mysqld/

total 4

drwxr-xr-x 2 qserv qserv 23 Jun 16 16:47 .

drwxr-xr-x 4 qserv qserv 99 Jun 14 11:39 ..

-rw-rw---- 1 qserv qserv  6 Jun 16 16:47 mysqld.pid

% rm run/var/run/mysqld/mysqld.pid

% run/etc/init.d/mysqld status

[FAIL] MySQL is not running, but lock exists ... failed!

% ls -al run/var/lock/subsys/

total 0

drwxr-xr-x 2 qserv qserv 97 Jun 14 11:39 .

drwxr-xr-x 3 qserv qserv 19 Apr 27 17:41 ..

-rw-r--r-- 1 qserv qserv  0 Jun 14 11:39 cmsd

-rw-r--r-- 1 qserv qserv  0 Jun 14 11:38 mysql

-rw-r--r-- 1 qserv qserv  0 Jun 14 11:39 mysql-proxy

-rw-r--r-- 1 qserv qserv  0 Jun 14 11:39 qserv-watcher

-rw-r--r-- 1 qserv qserv  0 Jun 14 11:39 qserv-wmgr

-rw-r--r-- 1 qserv qserv  0 Jun 14 11:39 xrootd

% rm run/var/lock/subsys/mysql

% run/etc/init.d/mysqld status

[FAIL] MySQL is not running ... failed!

% run/etc/init.d/mysqld start

Starting MySQL

............................................................................................................................................[....] Manager of pid-file quit[FAILout updating file. ... failed!

# However the database daemon seems to be back:

% ps -ef

UID        PID  PPID  C STIME TTY          TIME CMD

...

qserv    27236     1  8 16:46 ?        00:01:53 /qserv/stack/Linux64/mariadb/10.1.21.lsst1/bin/mysqld --defaults-file=/qserv/run/etc/my.cnf --basedir=/qserv/stack/Linux64/

...

Testing the service and checking the new configuration from the host machine's shell:

% top

top - 17:10:49 up 2 days,  6:59,  1 user,  load average: 0.00, 0.10, 0.55

Tasks: 484 total,   1 running, 483 sleeping,   0 stopped,   0 zombie

%Cpu(s):  0.0 us,  0.0 sy,  0.0 ni,100.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st

KiB Mem : 13128352+total, 10918868 free, 11064664 used, 10929998+buff/cache

KiB Swap:  4194300 total,  4107444 free,    86856 used. 11975208+avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND

19440 qserv     20   0  0.103t 8.846g  10148 S   0.3  7.1   1:53.85 mysqld

NOTE: the new virtual size of the process.
Checking the configuration parameters:

SHOW PROCESSLIST;

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

| Id | User     | Host            | db               | Command | Time | State | Info             | Progress |

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

|  2 | qsmaster | localhost       | qservCssData     | Sleep   |    1 |       | NULL             |    0.000 |

|  4 | qsmaster | localhost:38936 | sdss_stripe82_00 | Sleep   | 1573 |       | NULL             |    0.000 |

|  6 | qsmaster | localhost:38944 | sdss_stripe82_00 | Sleep   |  193 |       | NULL             |    0.000 |

| 10 | qsmaster | localhost:38954 | sdss_stripe82_00 | Sleep   |  493 |       | NULL             |    0.000 |

| 12 | root     | localhost       | NULL             | Query   |    0 | init  | SHOW PROCESSLIST |    0.000 |

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

SHOW VARIABLES LIKE 'innodb_buffer_pool_%';

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

| Variable_name                       | Value          |

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

| innodb_buffer_pool_dump_at_shutdown | OFF            |

| innodb_buffer_pool_dump_now         | OFF            |

| innodb_buffer_pool_dump_pct         | 100            |

| innodb_buffer_pool_filename         | ib_buffer_pool |

| innodb_buffer_pool_instances        | 1              |

| innodb_buffer_pool_load_abort       | OFF            |

| innodb_buffer_pool_load_at_startup  | OFF            |

| innodb_buffer_pool_load_now         | OFF            |

| innodb_buffer_pool_size             | 103079215104   |

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

Resuming the index loading

Test the number of objects in the secondary index table:

Checking the index loading status:

% ls -al /tmp/wise_2band_00/log/ | grep load_idx | tail -4

-rw-r--r-- 1 qserv qserv        0 Jun 16 11:35 load_idx_object_200797.tsv.log

-rw-r--r-- 1 qserv qserv        0 Jun 16 11:47 load_idx_object_200798.tsv.log

-rw-r--r-- 1 qserv qserv        0 Jun 16 12:18 load_idx_object_200799.tsv.log

-rw-r--r-- 1 qserv qserv       58 Jun 16 16:33 load_idx_object_200800.tsv.log

% cat /tmp/wise_2band_00/log/load_idx_object_200800.tsv.log

ERROR 1053 (08S01) at line 1: Server shutdown in progress

This is the DESIRED result meaning that loading of the last TSV file was aborted and trasaction was rolled back which is also supported by this message in the server's log file:

% sudo cat /qserv/log/mysqld.log

...

170616 16:56:43 mysqld_safe mysqld from pid file /qserv/run/var/run/mysqld/mysqld.pid ended

 94 95 96 97 98 99 1002017-06-16 16:57:18 140284848981760 [Note] InnoDB: Rollback of trx with id 118618240 completed

2017-06-16 16:57:18 7f969c974700  InnoDB: Rollback of non-prepared transactions completed

170616 17:03:25 mysqld_safe Starting mysqld daemon with databases from /qserv/data/mysql

...

Making sure the new loading won't destroy the previously loaded content. This is the SQL DML statement which create the table:

% cat /tmp/wise_2band_00/create_secondary_index.sql

CREATE TABLE IF NOT EXISTS `qservMeta`.`wise_2band_00__allsky_2band_p1bs_psd` (

  `source_id` CHAR(28) NOT NULL,

  `chunkId` int(11) DEFAULT NULL,

  `subChunkId` int(11) DEFAULT NULL,

  PRIMARY KEY (`source_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Putting away TSV files which were successfully loaded as per the master log file:

% /bin/sudo -u qserv mkdir /qserv/data/dumps/wise_2band_00/loaded

% for f in `cat load_secondary_index.log | grep loading | awk '{print $5}'`; do

  echo mv \

    /qserv/data/dumps/wise_2band_00/${f} \

    /qserv/data/dumps/wise_2band_00/loaded/${f}

done | wc -l

% for f in `cat load_secondary_index.log | grep loading | awk '{print $5}'`; do

  echo mv \

    /qserv/data/dumps/wise_2band_00/${f} \

    /qserv/data/dumps/wise_2band_00/loaded/${f}

done | tail -3

mv /qserv/data/dumps/wise_2band_00/idx_object_200798.tsv /qserv/data/dumps/wise_2band_00/loaded/idx_object_200798.tsv

mv /qserv/data/dumps/wise_2band_00/idx_object_200799.tsv /qserv/data/dumps/wise_2band_00/loaded/idx_object_200799.tsv

mv /qserv/data/dumps/wise_2band_00/idx_object_200800.tsv /qserv/data/dumps/wise_2band_00/loaded/idx_object_200800.tsv

And now move the files for real (NOTE we should put the last file idx_object_200800.tsv back because it was not properly loaded while the process was aborted):

% for f in `cat load_secondary_index.log | grep loading | awk '{print $5}'`; do

  echo $f

  /bin/sudo -u qserv

    mv /qserv/data/dumps/wise_2band_00/${f} \

       /qserv/data/dumps/wise_2band_00/loaded/${f}

done

% /bin/sudo -u qserv \

  mv /qserv/data/dumps/wise_2band_00/loaded/idx_object_200800.tsv \

     /qserv/data/dumps/wise_2band_00/

% /bin/sudo -u qserv \

  ls -1 /qserv/data/dumps/wise_2band_00/loaded | grep .tsv | wc -l

%/bin/sudo -u qserv \

  ls -1 /qserv/data/dumps/wise_2band_00/ | grep .tsv | wc -l

Saved the previous log file and restarted the operation:

% mv load_secondary_index.log load_secondary_index.log.loaded

% nohup /bin/sudo -u qserv \

  /datasets/gapon/development/db_pdac_wise/scripts/load_secondary_index.bash \

  --verbose >& load_secondary_index.log&

Table size:

% sudo ls -alh /qserv/data/mysql/qservMeta/ | grep wise_2band_00

-rw-rw----  1 qserv qserv 1014 Jun 14 12:02 wise_2band_00__allsky_2band_p1bs_psd.frm

-rw-rw----  1 qserv qserv 334G Jun 16 18:36 wise_2band_00__allsky_2band_p1bs_psd.ibd

Disk space status:

% df -h /

Filesystem               Size  Used Avail Use% Mounted on

/dev/mapper/system-root  926G  733G  194G  80% /

ATTENTION: The previously loaded files may need to be removed to free some space of needed:

% /bin/sudo -u qserv \

  du -hs /qserv/data/dumps/wise_2band_00/loaded

94G	/qserv/data/dumps/wise_2band_00/loaded

% /bin/sudo -u qserv \

  du -hs /qserv/data/dumps/wise_2band_00/

187G	/qserv/data/dumps/wise_2band_00/

Monitoring the loading process to see if the new configuration has allowed to speed it up:

% top

top - 18:32:22 up 2 days,  8:21,  1 user,  load average: 1.23, 0.61, 0.34

Tasks: 487 total,   1 running, 486 sleeping,   0 stopped,   0 zombie

%Cpu(s):  0.2 us,  0.1 sy,  0.0 ni, 97.7 id,  2.0 wa,  0.0 hi,  0.0 si,  0.0 st

KiB Mem : 13128352+total, 10661044 free, 11181256 used, 10944121+buff/cache

KiB Swap:  4194300 total,  4107444 free,    86856 used. 11955263+avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND

19440 qserv     20   0  0.103t 8.945g  10412 S  11.0  7.1   2:36.26 mysqld

% iostat -k 10

...

avg-cpu:  %user   %nice %system %iowait  %steal   %idle

           0.23    0.00    0.06    1.39    0.00   98.33

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn

sda             184.10         0.00     10396.05          0     103960

dm-0            183.60         0.00     10378.45          0     103784

avg-cpu:  %user   %nice %system %iowait  %steal   %idle

           0.22    0.00    0.06    1.44    0.00   98.28

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn

sda             189.00         0.00     11197.50          0     111975

dm-0            189.10         0.00     11200.70          0     112007

...

The last operation HAS FAILED due to disk space limitation. At this point the loading was stopped and a major upgrade of the master node hardware ordered as per JIRA [IHS-378].

The second attempt to generate the secondary index after the hardware upgrade

NOTE: this attempt will also involve an additional step of presorting the contents of the TSV triplet files before loading them into the table. The main benefit of this is a significant table size reduction as demonstrated in ~~DM-11027~~ (jump straight to the Conclusions section).

Verifying the status of the secondary index generation and cleaning it up

Compare the total number of loaded Objects versus the number of entries in the incomplete secondary index.

Obtaining the total number of loaded objects via Qserv proxy:

SELECT COUNT(*) FROM wise_2band_00.allsky_2band_p1bs_psd;

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

| SUM(QS1_COUNT) |

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

|     7337642955 |

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

1 row in set (8 min 55.40 sec)

Obtaining the number of the secondary index entries via a direct connection to the master's MySQL service:

SELECT COUNT(*) FROM qservMeta.wise_2band_00__allsky_2band_p1bs_psd;

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

|  COUNT(*)  |

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

| 6738261333 |

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

NOTE:' it took about 3 hours to get the result of the query.

Wipe out the secondary index:

DROP TABLE qservMeta.wise_2band_00__allsky_2band_p1bs_psd;

Query OK, 0 rows affected (40.67 sec)

Get rid of the local (master) cache of the intermediate TSV files:

% /bin/sudo -u qserv rm -rvf /qserv/data/dumps/wise_2band_00

% /bin/sudo -u qserv mkdir /qserv/data/dumps/wise_2band_00

Pulling triplets from the worker nodes to master

ATTENTION: this operation is based on an assumption that all triplets which were created during the first (the failed) attempt were still available. And indeed this was verified prior to proceed with this stage. Details are not going to be reflected here.

Harvesting triplets:

% nohup \

  /bin/sudo -u qserv \

    /datasets/gapon/development/db_pdac_wise/scripts/run_dump_triplet.bash \

    --verbose \

    >& run_dump_triplet.log&

Verify if the dump completed on all worker nodes:

Loading triplets into the index:

% nohup \

  /bin/sudo -u qserv \

    /datasets/gapon/development/db_pdac_wise/scripts/load_secondary_index.bash \

    --verbose \

    --get-remote-triplets \

    --no-triplets-loading \

    >& pull_triplets.log&

Verifying a status of triplets after the file migration operation finished. Verifying if the total number of TSV fils matched the number of partitions:

% ls -1 /qserv/data/dumps/wise_2band_00 | grep tsv | wc -l

Verifying if the total number of objects matches expectations (the actual counting was a bit more complex because the shell interpreter was not able to expand the list of 106891 files):

% wc -l /qserv/data/dumps/wise_2band_00/*.tsv

7337642955

CONCLUSION: bot numbers match the expectations.

Pre-sorting and merging TSV files

NOTE: This stage is required due to the 30% reduction of the table size demonstrated in ~~DM-11027~~. Also, the sort-merge has no significant effect on the total duration of the procedure.

Creating 64 groups of TSV files which will be processed in parallel (because the second soft-merge procedure is CPU intensive):

% /bin/sudo -u qserv -i

% cd /qserv/data/dumps/

%  mkdir wise_2band_00_tsv64

% ls wise_2band_00 | grep tsv > wise_2band_00_tsv64.txt

% wc -l wise_2band_00_tsv64.txt

106891 wise_2band_00_tsv64.txt

% cd wise_2band_00_tsv64

% split -d -l 1674 ../wise_2band_00_tsv64.txt ''

% ls -l

% ls -al | head -4

-rw-rw-r-- 1 qserv qserv 36809 Oct 27 15:42 00

...

-rw-rw-r-- 1 qserv qserv 35141 Oct 27 15:42 62

-rw-rw-r-- 1 qserv qserv 30009 Oct 27 15:42 63

% wc -l * | tail -1

 106891 total

Sort in 64 streams and store the sorted files in a separate folder:

% cd /qserv/data/dumps/

% mkdir wise_2band_00_sorted

% ate

Fri Oct 27 15:46:06 CDT 2017

% for c in `ls wise_2band_00_tsv64`; do

    for f in `cat wise_2band_00_tsv64/${c}`; do

        sort wise_2band_00/${f} > wise_2band_00_sorted/${f}

    done&

done

Analyzing results after all 64 jobs finished:

% date

Fri Oct 27 16:25:52 CDT 2017

% ls -1 wise_2band_00_sorted | wc -l

% du -hs wise_2band_00_sorted

187G	wise_2band_00_sorted

Total run time of the stage: 40 min

The next step is to do the merge-sort. Due to a large number of input files this operation is split into 2 steps:

sort-merging files within each stream (as defined on the previous step). All 64 streams are processed simulnateously.
final merge of the 64 files (produced by the previous stage) into a single file

Parallel merge of 64 streams:

% cd wise_2band_00_sorted

% mkdir ../wise_2band_00_sorted_merged

% for f in `ls ../wise_2band_00_tsv64`; do

    mkdir -p ../wise_2band_00_tmp_${f}

    cat ../wise_2band_00_tsv64/${f} | tr '\n' '\0' | sort -m -s -k1 -T ../wise_2band_00_tmp_${f} -o ../wise_2band_00_sorted_merged/${f}.tsv --files0-from=- &

done

% date

Fri Oct 27 17:54:27 CDT 2017

NOTE: the translation of newlines to NULL is required to feed null-terminated file (input) names into the sort tool. Also note that each stream uses its own temporary directory on the current disk instead of relying on a small /tmp file system.

Analyzing a status after it finished

% date

Fri Oct 27 18:16:33 CDT 2017

% ls -1 wise_2band_00_sorted_merged/ | wc -l

% du -hs wise_2band_00_sorted_merged/

187G	wise_2band_00_sorted_merged/

% wc -l wise_2band_00_sorted_merged/*.tsv | tail -1

  7337642955 total

Total run time of the stage: 22 minutes

Final merge of 64 steam files into the large one:

% mkdir wise_2band_00_tmp

% nohup sort -m -s -k1 -T wise_2band_00_tmp/  -o wise_2band_00_sorted_merged.tsv wise_2band_00_sorted_merged/*&

% date

Fri Oct 27 18:42:34 CDT 2017

Analyzing a status after it finished

% date

Fri Oct 27 20:47:04 CDT 2017

% ls -alh wise_2band_00_sorted_merged.tsv

-rw-rw-r-- 1 qserv qserv 187G Oct 27 20:41 wise_2band_00_sorted_merged.tsv

% wc -l wise_2band_00_sorted_merged.tsv

7337642955 wise_2band_00_sorted_merged.tsv

Total run time of the stage: 2 hours

Splitting the large file into smaller chunks

% mkdir wise_2band_00_sorted_merged_split

% cd wise_2band_00_sorted_merged_split

% date

Fri Oct 27 20:52:56 CDT 2017

% split -d -l 80663508 ../wise_2band_00_sorted_merged.tsv ''

% date

Fri Oct 27 20:59:36 CDT 2017

Analyzing a status after it finished

% ls -1 | wc -l

% du -hs .

187G	.

% wc -l * | tail -1

  7337642955 total

Total number of files: 91
Average file size: 2.2 GB
Lines per file: 80663508
Total run time of the stage: 7 minutes

Rename folders and those lastly created 91 TSV files to allow the previously developed tool to work:

% mv wise_2band_00 wise_2band_00_imported

% ln -s wise_2band_00_sorted_merged_split wise_2band_00

% cd wise_2band_00_sorted_merged_split

% for f in `ls`; do mv ${f} ${f}.tsv; done

Loading:

{code:bash}

% cd /home/gapon

% date

Fri Oct 27 21:27:06 CDT 2017

% nohup /bin/sudo -u qserv \

/datasets/gapon/development/db_pdac_wise/scripts/load_secondary_index.bash \

--verbose \

>& load_secondary_index_load_only.log&

Monitoring the progress:

% iostat -m 1 | grep sdb

sdb             123.12         5.44         5.93    1444284    1573500

sdb             489.00         6.75        93.35          6         93

sdb             583.00         7.75       110.45          7        110

sdb             395.00         8.00        74.53          8         74

sdb             554.00         4.50       104.20          4        104

sdb             626.00         8.50       120.70          8        120

sdb             794.00         7.62       165.61          7        165

sdb             575.00        10.00       109.70         10        109

sdb             755.00         9.62       143.21          9        143

...

% sudo ls -alh /qserv/data/mysql/qservMeta | grep wise_2band_00

-rw-rw----  1 qserv qserv 1014 Oct 27 03:40 wise_2band_00__allsky_2band_p1bs_psd.frm

-rw-rw----  1 qserv qserv 6.3G Oct 27 21:33 wise_2band_00__allsky_2band_p1bs_psd.ibd

The operation finished within less than 7 hours.
The log files are unremarkable.
The row count matches the number of objects in tables:

SELECT COUNT(*) FROM qservMeta.wise_2band_00__allsky_2band_p1bs_psd;

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

|  COUNT(*)  |

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

| 7337642955 |

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

The size of the table files is:

% sudo ls -alh /qserv/data/mysql/qservMeta | grep wise_2band_00

-rw-rw----  1 qserv qserv 1014 Oct 27 03:40 wise_2band_00__allsky_2band_p1bs_psd.frm

-rw-rw----  1 qserv qserv 416G Oct 28 04:22 wise_2band_00__allsky_2band_p1bs_psd.ibd

Cleaning up:

% rm wise_2band_00_tsv64.txt

% rm -rf wise_2band_00_tsv64/

% rm -rf wise_2band_00

% rm -rf wise_2band_00_sorted/

% rm -rf wise_2band_00_sorted_merged/

% rm -rf wise_2band_00_tmp*/

% rm wise_2band_00_sorted_merged.tsv

% rm -rf wise_2band_00_sorted_merged_split/

% rm wise_2band_00

% rm -rf wise_2band_00 wise_2band_00_imported/

% mkdir wise_2band_00

% df -h /data

Filesystem      Size  Used Avail Use% Mounted on

/dev/sdb1       2.7T  617G  2.1T  24% /data

Setting up the empty chunk list file

The file was generated using the trivial Python script based on early produced list of unique chunks:

chunks = {}

with open("all_chunks_unique_sorted.txt", "r") as f:

    for chunk in f:

        chunks[int(chunk[:-1])] = True

minChunk = 0

maxChunk = 1000000

for chunk in range(minChunk,maxChunk):

    if chunk not in chunks:

        print chunk

The generator was used like this:

% cd /datasets/gapon/wise/allsky_2band_p1bs_psd/partitioned_x16

% python generate_empty_chunk_list.py > empty_wise_2band_00.txt

% wc -l empty_wise_2band_00.txt

893059 empty_wise_2band_00.txt

% wc -l all_chunks_unique_sorted.txt

106941 all_chunks_unique_sorted.txt

Then installed the resulting files on the master node:

% /bin/sudo -u qserv \

  cp /datasets/gapon/wise/allsky_2band_p1bs_psd/partitioned_x16/empty_wise_2band_00.txt \

       /qserv/data/qserv/

Enabling the database in Qserv

% /bin/sudo -u qserv \

  /datasets/gapon/development/db_pdac_wise/scripts/run_qserv_enable_database.bash \

  --verbose \

  >& run_qserv_enable_database.log&

ATTENTION: Qserv needs to be restarted to recognize the database

Show

Igor Gaponenko added a comment - 09/Jun/17 6:08 PM - edited Loading catalog allsky_2band_p1bs_psd into PDAC The loading protocol is explained in details at: https://confluence.lsstcorp.org/display/DM/Loading+WISE+catalogs+into+PDAC The rest of this section presents a short summary of actions and tests taken during this loading. Dataset configuration Database name: wise_2band_00 Do this at lsst-dev : % cd /datasets/gapon/development/db_pdac_wise/scripts % ln -s dataset. bash .wise_2band_00 dataset. bash Creating database and initializing CSS All these steps were conducted on the master node of the cluster lsst-qserv-master01 : % /bin/sudo -u qserv \ /datasets/gapon/development/db_pdac_wise/scripts/run_create_database . bash \ --delete --verbose >& run_create_database.log& % /bin/sudo -u qserv \ /datasets/gapon/development/db_pdac_wise/scripts/setup_css . bash \ >& setup_css.log % 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 wise_2band_00 /DBS/wise_2band_00 READY /DBS/wise_2band_00/ .packed.json { "partitioningId" : "0000000027" , "releaseStatus" : "RELEASED" , "storageClass" : "L2" } /DBS/wise_2band_00/TABLES \N /DBS/wise_2band_00/TABLES/allsky_2band_p1bs_psd READY /DBS/wise_2band_00/TABLES/allsky_2band_p1bs_psd/partitioning \N /DBS/wise_2band_00/TABLES/allsky_2band_p1bs_psd/partitioning/ .packed.json { "dirColName" : "source_id" , "dirDb" : "wise_2band_00" , "dirTable" : "allsky_2band_p1bs_psd" , "latColName" : "decl" , "lonColName" : "ra" , "subChunks" : "1" } /DBS/wise_2band_00/TABLES/allsky_2band_p1bs_psd/schema ( /DBS/wise_2band_00/TABLES/allsky_2band_p1bs_psd/sharedScan \N /DBS/wise_2band_00/TABLES/allsky_2band_p1bs_psd/sharedScan/ .packed.json { "lockInMem" : "1" } Configuring MySQL server and Docker container /bin/sudo -u qserv \ /datasets/gapon/development/db_pdac_wise/scripts/run_show_processlist . bash --total % /bin/sudo -u qserv \ /datasets/gapon/development/db_pdac_wise/scripts/run_set_max_connections . bash % /bin/sudo -u qserv \ /datasets/gapon/development/db_pdac_wise/scripts/run_show_processlist . bash --total sst-qserv-master01: 14 / 16384 lsst-qserv-db01: 2 / 16384 lsst-qserv-db02: 2 / 16384 lsst-qserv-db03: 2 / 16384 ... % docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 44d15b27eac2 qserv /qserv :tickets_DM-10283_master "/bin/sh -c /qserv/sc" 2 weeks ago Up 2 weeks 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" } ], Loading partitioned tables % nohup /bin/sudo -u qserv \ /datasets/gapon/development/db_pdac_wise/scripts/run_load_object . bash \ --verbose \ >& run_load_object.log& Testing results: % tail /tmp/wise_2band_00/log/qserv-db *_load_object.log | grep 'Finished' | wc -l 30 % tail /tmp/wise_2band_00/log/qserv-db *_load_object.log | grep 'Finished' [Sat Jun 10 13:59:28 CDT 2017] ** Finished loading ** [Sat Jun 10 13:50:58 CDT 2017] ** Finished loading ** [Sat Jun 10 13:39:20 CDT 2017] ** Finished loading ** [Sat Jun 10 13:33:27 CDT 2017] ** Finished loading ** [Sat Jun 10 13:28:02 CDT 2017] ** Finished loading ** [Sat Jun 10 13:28:10 CDT 2017] ** Finished loading ** [Sat Jun 10 13:24:06 CDT 2017] ** Finished loading ** [Sat Jun 10 13:22:07 CDT 2017] ** Finished loading ** [Sat Jun 10 13:12:26 CDT 2017] ** Finished loading ** [Sat Jun 10 13:12:15 CDT 2017] ** Finished loading ** [Sat Jun 10 13:16:00 CDT 2017] ** Finished loading ** [Sat Jun 10 13:14:34 CDT 2017] ** Finished loading ** [Sat Jun 10 13:18:59 CDT 2017] ** Finished loading ** [Sat Jun 10 13:11:46 CDT 2017] ** Finished loading ** [Sat Jun 10 13:13:28 CDT 2017] ** Finished loading ** [Sat Jun 10 13:25:58 CDT 2017] ** Finished loading ** [Sat Jun 10 13:30:49 CDT 2017] ** Finished loading ** [Sat Jun 10 13:22:43 CDT 2017] ** Finished loading ** [Sat Jun 10 13:21:04 CDT 2017] ** Finished loading ** [Sat Jun 10 13:23:58 CDT 2017] ** Finished loading ** [Sat Jun 10 13:25:21 CDT 2017] ** Finished loading ** [Sat Jun 10 13:38:21 CDT 2017] ** Finished loading ** [Sat Jun 10 13:37:53 CDT 2017] ** Finished loading ** [Sat Jun 10 13:31:47 CDT 2017] ** Finished loading ** [Sat Jun 10 13:25:03 CDT 2017] ** Finished loading ** [Sat Jun 10 13:27:11 CDT 2017] ** Finished loading ** [Sat Jun 10 13:25:20 CDT 2017] ** Finished loading ** [Sat Jun 10 13:34:24 CDT 2017] ** Finished loading ** [Sat Jun 10 13:43:53 CDT 2017] ** Finished loading ** [Sat Jun 10 13:57:27 CDT 2017] ** Finished loading ** Generating the secondary index (FIRST ATTEMPT) Harvesting triplets: % nohup \ /bin/sudo -u qserv \ /datasets/gapon/development/db_pdac_wise/scripts/run_dump_triplet . bash \ --verbose \ >& run_dump_triplet.log& Loading triplets into the index: % nohup \ /bin/sudo -u qserv \ /datasets/gapon/development/db_pdac_wise/scripts/load_secondary_index . bash \ --verbose \ --get-remote-triplets \ >& load_secondary_index.log& Performance degradation after loading 60% of TSV files The number of files which have been loaded: % cat load_secondary_index.log | grep 'loading tri' | wc -l 63104 SHOW PROCESSLIST; | 64107 | root | localhost | NULL | Query | 9519 | reading file | LOAD DATA INFILE '/qserv/data/dumps/wise_2band_00/idx_object_200800.tsv' INTO TABLE qservMeta.wise_2 | 36.303 | Actually, the gradual performance degradation was seen much earlier. Later it become more obvious before it literally stalled while loading file idx_object_200800.tsv as per the log file analysis: % cat load_secondary_index.log | grep 'loading tri' | tail -3 loading triplest from file : idx_object_200798.tsv loading triplest from file : idx_object_200799.tsv loading triplest from file : idx_object_200800.tsv The last file is being loaded for the last 2 hours (at least). The file is noticeable larger than the previous ones: % sudo ls -al /qserv/data/dumps/wise_2band_00/ | grep -B 4 -A 4 idx_object_200800.tsv -rw-r--r-- 1 qserv qserv 23608289 Jun 10 20:54 idx_object_200796.tsv -rw-r--r-- 1 qserv qserv 30216240 Jun 10 21:11 idx_object_200797.tsv -rw-r--r-- 1 qserv qserv 44797664 Jun 10 21:28 idx_object_200798.tsv -rw-r--r-- 1 qserv qserv 73420288 Jun 10 21:44 idx_object_200799.tsv -rw-r--r-- 1 qserv qserv 148547683 Jun 10 22:02 idx_object_200800.tsv -rw-r--r-- 1 qserv qserv 156758880 Jun 10 22:19 idx_object_200801.tsv -rw-r--r-- 1 qserv qserv 75832749 Jun 10 22:35 idx_object_200802.tsv -rw-r--r-- 1 qserv qserv 42320964 Jun 10 22:53 idx_object_200803.tsv -rw-r--r-- 1 qserv qserv 30272316 Jun 10 23:10 idx_object_200804.tsv Reconfiguring InnoDB Further analysis of the database service configuration showed that the server runs with the default configuration of the InnoDB storage engine. This configuration seems to be way to suboptimal for the large bulk inserts. This is seen with: SHOW VARIABLES LIKE 'innodb_buffer_pool_%' ; + -------------------------------------+----------------+ | Variable_name | Value | + -------------------------------------+----------------+ | innodb_buffer_pool_dump_at_shutdown | OFF | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_dump_pct | 100 | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_instances | 8 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | OFF | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 134217728 | + -------------------------------------+----------------+ Among those the most problematic one is the last parameter innodb_buffer_pool_size which is set to 128 MB only MySQL suggest that it should be set up to 80% of the machine's RAM. Unfortunately this parameter is NOT DYNAMIC for a version of the MariaDB service run inside the Qserv container: % mysql ... Server version: 10.1.21-MariaDB Source distribution The documentation suggests that the parameter is DYNAMIC as of verision 10.2.2 or higher. Therefore the only solution is to abort the index loading, reconfigure the service and resume the loading process from a file which takes to long to load. Aborting the loading: % ps -ef | grep load_ root 6142 1 0 Jun14 ? 00:00:00 /bin/sudo -u qserv /datasets/gapon/development/db_pdac_wise/scripts/load_secondary_index . bash --verbose qserv 6144 6142 0 Jun14 ? 00:01:23 /bin/bash /datasets/gapon/development/db_pdac_wise/scripts/load_secondary_index . bash --verbose gapon 18007 16017 0 16:16 pts /0 00:00:00 grep --color=auto load_ % sudo kill 6142 6144 Though, this didn't really kill the loading thread within the service as per: SHOW PROCESSLIST; | 64107 | root | localhost | NULL | Query | 10187 | reading file | LOAD DATA INFILE '/qserv/data/dumps/wise_2band_00/idx_object_200800.tsv' INTO TABLE qservMeta.wise_2 | 38.176 | Besides, the server is still running in then same node as earlier: % top top - 16:28:00 up 2 days, 6:17, 1 user, load average: 1.33, 1.39, 1.40 Tasks: 485 total, 1 running, 484 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.2 us, 0.1 sy, 0.0 ni, 97.7 id , 2.1 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 13128352+total, 440624 free , 2054096 used, 12878880+buff /cache KiB Swap: 4194300 total, 4103904 free , 90396 used. 12876192+avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 4806 qserv 20 0 3242864 255704 4808 S 9.6 0.2 732:49.71 mysqld ... Reconfiguring and restarting the service by logging into the Docker container: [gapon@lsst-qserv-master01 ~]$ docker exec -it qserv bash % export TERM=xterm % run /etc/init .d /mysqld help Usage: run /etc/init .d /mysqld {start|stop|restart|reload|force-reload|status} [ MySQL server options ] % mv run /etc/my .cnf run /etc/my .cnf.saved # added two parameters % diff run /etc/my .cnf run /etc/my .cnf.saved 18,20d17 < innodb_buffer_pool_size=96G < innodb_buffer_pool_instances=1 % run /etc/init .d /mysqld force-reload [ ok ] Reloading service MySQL. # UNFORTUNATELY this did NOT stop the server stop # The previous operation was stuck as well. Killing the server process in the brute force way: % ps -ef qserv 90 1 0 Jun14 ? 00:00:00 /bin/sh /qserv/stack/Linux64/mariadb/10 .1.21.lsst1 /bin/mysqld_safe --defaults- file = /qserv/run/etc/my .cnf --datadir= /qserv/d qserv 210 90 23 Jun14 ? 12:14:16 /qserv/stack/Linux64/mariadb/10 .1.21.lsst1 /bin/mysqld --defaults- file = /qserv/run/etc/my .cnf --basedir= /qserv/stack/Linux64/ % kill -9 90 210 # Cleaning up the PID because of % run /etc/init .d /mysqld status [ ok ] MySQL running (27236). % ls -al run /var/run/mysqld/ total 4 drwxr-xr-x 2 qserv qserv 23 Jun 16 16:47 . drwxr-xr-x 4 qserv qserv 99 Jun 14 11:39 .. -rw-rw---- 1 qserv qserv 6 Jun 16 16:47 mysqld.pid % rm run /var/run/mysqld/mysqld .pid % run /etc/init .d /mysqld status [FAIL] MySQL is not running, but lock exists ... failed! % ls -al run /var/lock/subsys/ total 0 drwxr-xr-x 2 qserv qserv 97 Jun 14 11:39 . drwxr-xr-x 3 qserv qserv 19 Apr 27 17:41 .. -rw-r--r-- 1 qserv qserv 0 Jun 14 11:39 cmsd -rw-r--r-- 1 qserv qserv 0 Jun 14 11:38 mysql -rw-r--r-- 1 qserv qserv 0 Jun 14 11:39 mysql-proxy -rw-r--r-- 1 qserv qserv 0 Jun 14 11:39 qserv-watcher -rw-r--r-- 1 qserv qserv 0 Jun 14 11:39 qserv-wmgr -rw-r--r-- 1 qserv qserv 0 Jun 14 11:39 xrootd % rm run /var/lock/subsys/mysql % run /etc/init .d /mysqld status [FAIL] MySQL is not running ... failed! % run /etc/init .d /mysqld start Starting MySQL ............................................................................................................................................[....] Manager of pid- file quit[FAILout updating file . ... failed! # However the database daemon seems to be back: % ps -ef UID PID PPID C STIME TTY TIME CMD ... qserv 27236 1 8 16:46 ? 00:01:53 /qserv/stack/Linux64/mariadb/10 .1.21.lsst1 /bin/mysqld --defaults- file = /qserv/run/etc/my .cnf --basedir= /qserv/stack/Linux64/ ... Testing the service and checking the new configuration from the host machine's shell: % top top - 17:10:49 up 2 days, 6:59, 1 user, load average: 0.00, 0.10, 0.55 Tasks: 484 total, 1 running, 483 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.0 us, 0.0 sy, 0.0 ni,100.0 id , 0.0 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 13128352+total, 10918868 free , 11064664 used, 10929998+buff /cache KiB Swap: 4194300 total, 4107444 free , 86856 used. 11975208+avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 19440 qserv 20 0 0.103t 8.846g 10148 S 0.3 7.1 1:53.85 mysqld NOTE : the new virtual size of the process. Checking the configuration parameters: SHOW PROCESSLIST; + ----+----------+-----------------+------------------+---------+------+-------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | + ----+----------+-----------------+------------------+---------+------+-------+------------------+----------+ | 2 | qsmaster | localhost | qservCssData | Sleep | 1 | | NULL | 0.000 | | 4 | qsmaster | localhost:38936 | sdss_stripe82_00 | Sleep | 1573 | | NULL | 0.000 | | 6 | qsmaster | localhost:38944 | sdss_stripe82_00 | Sleep | 193 | | NULL | 0.000 | | 10 | qsmaster | localhost:38954 | sdss_stripe82_00 | Sleep | 493 | | NULL | 0.000 | | 12 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | 0.000 | + ----+----------+-----------------+------------------+---------+------+-------+------------------+----------+ SHOW VARIABLES LIKE 'innodb_buffer_pool_%' ; + -------------------------------------+----------------+ | Variable_name | Value | + -------------------------------------+----------------+ | innodb_buffer_pool_dump_at_shutdown | OFF | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_dump_pct | 100 | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | OFF | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 103079215104 | + -------------------------------------+----------------+ Resuming the index loading Test the number of objects in the secondary index table: Checking the index loading status: % ls -al /tmp/wise_2band_00/log/ | grep load_idx | tail -4 -rw-r--r-- 1 qserv qserv 0 Jun 16 11:35 load_idx_object_200797.tsv.log -rw-r--r-- 1 qserv qserv 0 Jun 16 11:47 load_idx_object_200798.tsv.log -rw-r--r-- 1 qserv qserv 0 Jun 16 12:18 load_idx_object_200799.tsv.log -rw-r--r-- 1 qserv qserv 58 Jun 16 16:33 load_idx_object_200800.tsv.log % cat /tmp/wise_2band_00/log/load_idx_object_200800 .tsv.log ERROR 1053 (08S01) at line 1: Server shutdown in progress This is the DESIRED result meaning that loading of the last TSV file was aborted and trasaction was rolled back which is also supported by this message in the server's log file: % sudo cat /qserv/log/mysqld .log ... 170616 16:56:43 mysqld_safe mysqld from pid file /qserv/run/var/run/mysqld/mysqld .pid ended 94 95 96 97 98 99 1002017-06-16 16:57:18 140284848981760 [Note] InnoDB: Rollback of trx with id 118618240 completed 2017-06-16 16:57:18 7f969c974700 InnoDB: Rollback of non-prepared transactions completed 170616 17:03:25 mysqld_safe Starting mysqld daemon with databases from /qserv/data/mysql ... Making sure the new loading won't destroy the previously loaded content. This is the SQL DML statement which create the table: % cat /tmp/wise_2band_00/create_secondary_index .sql CREATE TABLE IF NOT EXISTS `qservMeta`.`wise_2band_00__allsky_2band_p1bs_psd` ( `source_id` CHAR (28) NOT NULL , `chunkId` int (11) DEFAULT NULL , `subChunkId` int (11) DEFAULT NULL , PRIMARY KEY (`source_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Putting away TSV files which were successfully loaded as per the master log file: % /bin/sudo -u qserv mkdir /qserv/data/dumps/wise_2band_00/loaded % for f in ` cat load_secondary_index.log | grep loading | awk '{print $5}' `; do echo mv \ /qserv/data/dumps/wise_2band_00/ ${f} \ /qserv/data/dumps/wise_2band_00/loaded/ ${f} done | wc -l 63104 % for f in ` cat load_secondary_index.log | grep loading | awk '{print $5}' `; do echo mv \ /qserv/data/dumps/wise_2band_00/ ${f} \ /qserv/data/dumps/wise_2band_00/loaded/ ${f} done | tail -3 mv /qserv/data/dumps/wise_2band_00/idx_object_200798 .tsv /qserv/data/dumps/wise_2band_00/loaded/idx_object_200798 .tsv mv /qserv/data/dumps/wise_2band_00/idx_object_200799 .tsv /qserv/data/dumps/wise_2band_00/loaded/idx_object_200799 .tsv mv /qserv/data/dumps/wise_2band_00/idx_object_200800 .tsv /qserv/data/dumps/wise_2band_00/loaded/idx_object_200800 .tsv And now move the files for real ( NOTE we should put the last file idx_object_200800.tsv back because it was not properly loaded while the process was aborted): % for f in ` cat load_secondary_index.log | grep loading | awk '{print $5}' `; do echo $f /bin/sudo -u qserv mv /qserv/data/dumps/wise_2band_00/ ${f} \ /qserv/data/dumps/wise_2band_00/loaded/ ${f} done % /bin/sudo -u qserv \ mv /qserv/data/dumps/wise_2band_00/loaded/idx_object_200800 .tsv \ /qserv/data/dumps/wise_2band_00/ % /bin/sudo -u qserv \ ls -1 /qserv/data/dumps/wise_2band_00/loaded | grep .tsv | wc -l 63103 % /bin/sudo -u qserv \ ls -1 /qserv/data/dumps/wise_2band_00/ | grep .tsv | wc -l 43788 Saved the previous log file and restarted the operation: % mv load_secondary_index.log load_secondary_index.log.loaded % nohup /bin/sudo -u qserv \ /datasets/gapon/development/db_pdac_wise/scripts/load_secondary_index . bash \ --verbose >& load_secondary_index.log& Table size: % sudo ls -alh /qserv/data/mysql/qservMeta/ | grep wise_2band_00 -rw-rw---- 1 qserv qserv 1014 Jun 14 12:02 wise_2band_00__allsky_2band_p1bs_psd.frm -rw-rw---- 1 qserv qserv 334G Jun 16 18:36 wise_2band_00__allsky_2band_p1bs_psd.ibd Disk space status: % df -h / Filesystem Size Used Avail Use% Mounted on /dev/mapper/system-root 926G 733G 194G 80% / ATTENTION : The previously loaded files may need to be removed to free some space of needed: % /bin/sudo -u qserv \ du -hs /qserv/data/dumps/wise_2band_00/loaded 94G /qserv/data/dumps/wise_2band_00/loaded % /bin/sudo -u qserv \ du -hs /qserv/data/dumps/wise_2band_00/ 187G /qserv/data/dumps/wise_2band_00/ Monitoring the loading process to see if the new configuration has allowed to speed it up: % top top - 18:32:22 up 2 days, 8:21, 1 user, load average: 1.23, 0.61, 0.34 Tasks: 487 total, 1 running, 486 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.2 us, 0.1 sy, 0.0 ni, 97.7 id , 2.0 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 13128352+total, 10661044 free , 11181256 used, 10944121+buff /cache KiB Swap: 4194300 total, 4107444 free , 86856 used. 11955263+avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 19440 qserv 20 0 0.103t 8.945g 10412 S 11.0 7.1 2:36.26 mysqld % iostat -k 10 ... avg-cpu: %user % nice %system %iowait %steal %idle 0.23 0.00 0.06 1.39 0.00 98.33 Device: tps kB_read /s kB_wrtn /s kB_read kB_wrtn sda 184.10 0.00 10396.05 0 103960 dm-0 183.60 0.00 10378.45 0 103784 avg-cpu: %user % nice %system %iowait %steal %idle 0.22 0.00 0.06 1.44 0.00 98.28 Device: tps kB_read /s kB_wrtn /s kB_read kB_wrtn sda 189.00 0.00 11197.50 0 111975 dm-0 189.10 0.00 11200.70 0 112007 ... The last operation HAS FAILED due to disk space limitation. At this point the loading was stopped and a major upgrade of the master node hardware ordered as per JIRA [IHS-378] . The second attempt to generate the secondary index after the hardware upgrade NOTE : this attempt will also involve an additional step of presorting the contents of the TSV triplet files before loading them into the table. The main benefit of this is a significant table size reduction as demonstrated in DM-11027 (jump straight to the Conclusions section). Verifying the status of the secondary index generation and cleaning it up Compare the total number of loaded Objects versus the number of entries in the incomplete secondary index. Obtaining the total number of loaded objects via Qserv proxy : SELECT COUNT (*) FROM wise_2band_00.allsky_2band_p1bs_psd; + ----------------+ | SUM (QS1_COUNT) | + ----------------+ | 7337642955 | + ----------------+ 1 row in set (8 min 55.40 sec) Obtaining the number of the secondary index entries via a direct connection to the master's MySQL service: SELECT COUNT (*) FROM qservMeta.wise_2band_00__allsky_2band_p1bs_psd; + ------------+ | COUNT (*) | + ------------+ | 6738261333 | + ------------+ NOTE :' it took about 3 hours to get the result of the query. Wipe out the secondary index: DROP TABLE qservMeta.wise_2band_00__allsky_2band_p1bs_psd; Query OK, 0 rows affected (40.67 sec) Get rid of the local ( master ) cache of the intermediate TSV files: % /bin/sudo -u qserv rm -rvf /qserv/data/dumps/wise_2band_00 % /bin/sudo -u qserv mkdir /qserv/data/dumps/wise_2band_00 Pulling triplets from the worker nodes to master ATTENTION : this operation is based on an assumption that all triplets which were created during the first (the failed) attempt were still available. And indeed this was verified prior to proceed with this stage. Details are not going to be reflected here. Harvesting triplets: % nohup \ /bin/sudo -u qserv \ /datasets/gapon/development/db_pdac_wise/scripts/run_dump_triplet . bash \ --verbose \ >& run_dump_triplet.log& Verify if the dump completed on all worker nodes: Loading triplets into the index: % nohup \ /bin/sudo -u qserv \ /datasets/gapon/development/db_pdac_wise/scripts/load_secondary_index . bash \ --verbose \ --get-remote-triplets \ --no-triplets-loading \ >& pull_triplets.log& Verifying a status of triplets after the file migration operation finished. Verifying if the total number of TSV fils matched the number of partitions: % ls -1 /qserv/data/dumps/wise_2band_00 | grep tsv | wc -l 106891 Verifying if the total number of objects matches expectations (the actual counting was a bit more complex because the shell interpreter was not able to expand the list of 106891 files): % wc -l /qserv/data/dumps/wise_2band_00/ *.tsv 7337642955 CONCLUSION : bot numbers match the expectations. Pre-sorting and merging TSV files NOTE : This stage is required due to the 30% reduction of the table size demonstrated in DM-11027 . Also, the sort-merge has no significant effect on the total duration of the procedure. Creating 64 groups of TSV files which will be processed in parallel (because the second soft-merge procedure is CPU intensive): % /bin/sudo -u qserv -i % cd /qserv/data/dumps/ % mkdir wise_2band_00_tsv64 % ls wise_2band_00 | grep tsv > wise_2band_00_tsv64.txt % wc -l wise_2band_00_tsv64.txt 106891 wise_2band_00_tsv64.txt % cd wise_2band_00_tsv64 % split -d -l 1674 .. /wise_2band_00_tsv64 .txt '' % ls -l % ls -al | head -4 -rw-rw-r-- 1 qserv qserv 36809 Oct 27 15:42 00 ... -rw-rw-r-- 1 qserv qserv 35141 Oct 27 15:42 62 -rw-rw-r-- 1 qserv qserv 30009 Oct 27 15:42 63 % wc -l * | tail -1 106891 total Sort in 64 streams and store the sorted files in a separate folder: % cd /qserv/data/dumps/ % mkdir wise_2band_00_sorted % ate Fri Oct 27 15:46:06 CDT 2017 % for c in ` ls wise_2band_00_tsv64`; do for f in ` cat wise_2band_00_tsv64/${c}`; do sort wise_2band_00/${f} > wise_2band_00_sorted/${f} done & done Analyzing results after all 64 jobs finished: % date Fri Oct 27 16:25:52 CDT 2017 % ls -1 wise_2band_00_sorted | wc -l 106891 % du -hs wise_2band_00_sorted 187G wise_2band_00_sorted Total run time of the stage: 40 min The next step is to do the merge-sort. Due to a large number of input files this operation is split into 2 steps: sort-merging files within each stream (as defined on the previous step). All 64 streams are processed simulnateously. final merge of the 64 files (produced by the previous stage) into a single file Parallel merge of 64 streams: % cd wise_2band_00_sorted % mkdir .. /wise_2band_00_sorted_merged % for f in ` ls .. /wise_2band_00_tsv64 `; do mkdir -p .. /wise_2band_00_tmp_ ${f} cat .. /wise_2band_00_tsv64/ ${f} | tr '\n' '\0' | sort -m -s -k1 -T .. /wise_2band_00_tmp_ ${f} -o .. /wise_2band_00_sorted_merged/ ${f}.tsv --files0-from=- & done % date Fri Oct 27 17:54:27 CDT 2017 NOTE: the translation of newlines to NULL is required to feed null-terminated file (input) names into the sort tool. Also note that each stream uses its own temporary directory on the current disk instead of relying on a small /tmp file system. Analyzing a status after it finished % date Fri Oct 27 18:16:33 CDT 2017 % ls -1 wise_2band_00_sorted_merged/ | wc -l 64 % du -hs wise_2band_00_sorted_merged/ 187G wise_2band_00_sorted_merged/ % wc -l wise_2band_00_sorted_merged/*.tsv | tail -1 7337642955 total Total run time of the stage: 22 minutes Final merge of 64 steam files into the large one: % mkdir wise_2band_00_tmp % nohup sort -m -s -k1 -T wise_2band_00_tmp/ -o wise_2band_00_sorted_merged.tsv wise_2band_00_sorted_merged/*& % date Fri Oct 27 18:42:34 CDT 2017 Analyzing a status after it finished % date Fri Oct 27 20:47:04 CDT 2017 % ls -alh wise_2band_00_sorted_merged.tsv -rw-rw-r-- 1 qserv qserv 187G Oct 27 20:41 wise_2band_00_sorted_merged.tsv % wc -l wise_2band_00_sorted_merged.tsv 7337642955 wise_2band_00_sorted_merged.tsv Total run time of the stage: 2 hours Splitting the large file into smaller chunks % mkdir wise_2band_00_sorted_merged_split % cd wise_2band_00_sorted_merged_split % date Fri Oct 27 20:52:56 CDT 2017 % split -d -l 80663508 .. /wise_2band_00_sorted_merged .tsv '' % date Fri Oct 27 20:59:36 CDT 2017 Analyzing a status after it finished % ls -1 | wc -l 91 % du -hs . 187G . % wc -l * | tail -1 7337642955 total Total number of files: 91 Average file size: 2.2 GB Lines per file: 80663508 Total run time of the stage: 7 minutes Rename folders and those lastly created 91 TSV files to allow the previously developed tool to work: % mv wise_2band_00 wise_2band_00_imported % ln -s wise_2band_00_sorted_merged_split wise_2band_00 % cd wise_2band_00_sorted_merged_split % for f in ` ls `; do mv ${f} ${f}.tsv; done Loading: {code: bash } % cd /home/gapon % date Fri Oct 27 21:27:06 CDT 2017 % nohup /bin/sudo -u qserv \ /datasets/gapon/development/db_pdac_wise/scripts/load_secondary_index . bash \ --verbose \ >& load_secondary_index_load_only.log& Monitoring the progress: % iostat -m 1 | grep sdb sdb 123.12 5.44 5.93 1444284 1573500 sdb 489.00 6.75 93.35 6 93 sdb 583.00 7.75 110.45 7 110 sdb 395.00 8.00 74.53 8 74 sdb 554.00 4.50 104.20 4 104 sdb 626.00 8.50 120.70 8 120 sdb 794.00 7.62 165.61 7 165 sdb 575.00 10.00 109.70 10 109 sdb 755.00 9.62 143.21 9 143 ... % sudo ls -alh /qserv/data/mysql/qservMeta | grep wise_2band_00 -rw-rw---- 1 qserv qserv 1014 Oct 27 03:40 wise_2band_00__allsky_2band_p1bs_psd.frm -rw-rw---- 1 qserv qserv 6.3G Oct 27 21:33 wise_2band_00__allsky_2band_p1bs_psd.ibd The operation finished within less than 7 hours . The log files are unremarkable. The row count matches the number of objects in tables: SELECT COUNT (*) FROM qservMeta.wise_2band_00__allsky_2band_p1bs_psd; + ------------+ | COUNT (*) | + ------------+ | 7337642955 | + ------------+ The size of the table files is: % sudo ls -alh /qserv/data/mysql/qservMeta | grep wise_2band_00 -rw-rw---- 1 qserv qserv 1014 Oct 27 03:40 wise_2band_00__allsky_2band_p1bs_psd.frm -rw-rw---- 1 qserv qserv 416G Oct 28 04:22 wise_2band_00__allsky_2band_p1bs_psd.ibd Cleaning up: % rm wise_2band_00_tsv64.txt % rm -rf wise_2band_00_tsv64/ % rm -rf wise_2band_00 % rm -rf wise_2band_00_sorted/ % rm -rf wise_2band_00_sorted_merged/ % rm -rf wise_2band_00_tmp*/ % rm wise_2band_00_sorted_merged.tsv % rm -rf wise_2band_00_sorted_merged_split/ % rm wise_2band_00 % rm -rf wise_2band_00 wise_2band_00_imported/ % mkdir wise_2band_00 % df -h /data Filesystem Size Used Avail Use% Mounted on /dev/sdb1 2.7T 617G 2.1T 24% /data Setting up the empty chunk list file The file was generated using the trivial Python script based on early produced list of unique chunks: chunks = {} with open ( "all_chunks_unique_sorted.txt" , "r" ) as f: for chunk in f: chunks[ int (chunk[: - 1 ])] = True minChunk = 0 maxChunk = 1000000 for chunk in range (minChunk,maxChunk): if chunk not in chunks: print chunk The generator was used like this: % cd /datasets/gapon/wise/allsky_2band_p1bs_psd/partitioned_x16 % python generate_empty_chunk_list.py > empty_wise_2band_00.txt % wc -l empty_wise_2band_00.txt 893059 empty_wise_2band_00.txt % wc -l all_chunks_unique_sorted.txt 106941 all_chunks_unique_sorted.txt Then installed the resulting files on the master node: % /bin/sudo -u qserv \ cp /datasets/gapon/wise/allsky_2band_p1bs_psd/partitioned_x16/empty_wise_2band_00 .txt \ /qserv/data/qserv/ Enabling the database in Qserv % /bin/sudo -u qserv \ /datasets/gapon/development/db_pdac_wise/scripts/run_qserv_enable_database . bash \ --verbose \ >& run_qserv_enable_database.log& ATTENTION : Qserv needs to be restarted to recognize the database

Hide

Permalink

Igor Gaponenko added a comment - 30/Oct/17 2:56 PM - edited

Loading catalog allsky_3band_p1bs_psd into PDAC

The loading protocol is explained in details at:

https://confluence.lsstcorp.org/display/DM/Loading+WISE+catalogs+into+PDAC

The rest of this section presents a short summary of actions and tests taken during this loading.

Dataset configuration
Database name: wise_3band_00
Do this at lsat-dev:

% cd /datasets/gapon/development/db_pdac_wise/scripts

% ln -s dataset.bash.wise_3band_00  dataset.bash

Creating database and initializing CSS

All these steps were conducted on the master node of the cluster lsst-qserv-master01:

% /bin/sudo -u qserv \

  /datasets/gapon/development/db_pdac_wise/scripts/run_create_database.bash \

  --delete --verbose >& run_create_database.log&

% /bin/sudo -u qserv \

  /datasets/gapon/development/db_pdac_wise/scripts/setup_css.bash \

  >& setup_css.log

% 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 wise_3band_00

/DBS/wise_3band_00	READY

/DBS/wise_3band_00/.packed.json	{"partitioningId":"0000000028",

Data Management

Details

Description

Attachments

Issue Links

Activity

Downloading WISE All-Sky Single Exposure (L1b) Source Table data

Catalog schema

Downloaded files into the temporary location at NCSA

Processing downloaded files of catalog allsky_4band_p1bs_psd

Uncompressing the files

Translating files from the unl format into the TSV format

Partitioning files of catalog allsky_4band_p1bs_psd

Partitioning by 32 parallel jobs

Analyzing results

Preparing the staging area for chunks of catalog allsky_4band_p1bs_psd

General considerations and a setup

Obtaining existing worker-to-chunks map from PDAC

Generating scripts to populate area

Populating the area

Downloading WISE 3-Band Cryo Single Exposure (L1b) Source Table data

Catalog schema

Catalog data

Processing downloaded files of catalog allsky_3band_p1bs_psd

Uncompressing the files

Translating files from the unl format into the TSV format

Partitioning files of catalog allsky_3band_p1bs_psd

Preparing the staging area for chunks of catalog allsky_3band_p1bs_psd

General considerations and a setup

The worker-to-chunks map (for the link generator)

Generating scripts to populate area

Populating the area

Downloading WISE Post-Cryo Single Exposure (L1b) Source Source Table data

Catalog schema

Catalog data

Processing downloaded files of catalog allsky_2band_p1bs_psd

Uncompressing the files

Translating files from the unl format into the TSV format

Partitioning files of catalog allsky_2band_p1bs_psd

Preparing the staging area for chunks of catalog allsky_2band_p1bs_psd

General considerations and a setup

The worker-to-chunks map (for the link generator)

Generating scripts to populate area

Populating the area

Loading catalog allsky_2band_p1bs_psd into PDAC

Dataset configuration

Creating database and initializing CSS

Configuring MySQL server and Docker container

Loading partitioned tables

Generating the secondary index (FIRST ATTEMPT)

Performance degradation after loading 60% of TSV files

Reconfiguring InnoDB

Resuming the index loading

The second attempt to generate the secondary index after the hardware upgrade

Verifying the status of the secondary index generation and cleaning it up

Pulling triplets from the worker nodes to master

Pre-sorting and merging TSV files

Setting up the empty chunk list file

Enabling the database in Qserv

Loading catalog allsky_3band_p1bs_psd into PDAC

Creating database and initializing CSS