Storing Reference Catalog

LSST Database

Assumptions (based on DataAccWG telecon discussions Oct 23, Oct 6, Oct 2):

• data will come from several sources (USNO-B, simCat, maybe SDSS, maybe 2MASS [TBD])
• some fields will go over the pole
• we should keep reference catalogs for simCat and all-the-rest separate (almost nothing will match)
• size of the master reference catalog (MRC) will be ~1 billion rows (USNO-B), augmented by some columns (maybe from SDSS, maybe 2MASS). For now, assuming 100 bytes/row, that is 100GB [need to come up with schema]
• we will need to extract a subset of rows to create individual reference catalogs:
  • astrometric reference catalog (simCat, non-simCat)
  • photometric reference catalog (simCat, non-simCat)
  • SDQA astrometric reference catalog (simCat, non-simCat). It will need only bright, isolate stars [open question: maybe we don't need separate SDQA cat]
  • SDQA photometric reference catalog (simCat, non-simCat) - this is beyond DC3b [see open question above]
• each reference catalog will be ~ 1% of the entire MRC, so ~1GB
• some objects will be shared by multiple reference catalogs
• we do not need to worry about updating reference catalogs (e.g., the input source catalogs are frozen) - this is true for at least USNOB and SDSS.
• typical access pattern "give me all objects for a given CCD" - this implies a cone search with a ccd radius in ccd center, then would need to clip the edges.

Building the Reference Catalogs

ObjectIds from different data sources will not match, and we will need to run association (a la Association Pipeline) to synchronize them (this is a heavy operation).

Given the assumptions above and the results of the tests below, the best option seems to be:

• build a MRC (join data from all sources into one large table)
• extract reference catalogs and store separately (they are small, ~1 GB each in DC3b)
• could push the MRC to tapes if we need to recover some disk space
• should we ever need to introduce a new version of a source catalog - we would create a new MRC.

SDQA Catalogs:

• want bright stars: need to apply filter on magnitude
• want isolated stars: need to identify object without near neighbors
• see SdqaWcsFailureCheckStage for more details

The former is easy. The latter will require some thinking. It is a one-time operation, and we are talking about few million rows (1% of 1 billion = 10 millions, cut on magnitude will further reduce it). Options:

• maybe use custom C++ code, generate on the fly overlapping declination zones and stream by zone
• maybe use DIF-spatial-index-assisted join

[This needs investigation]

Spatial index: we are considering using ​DIF. Todo:

• write up DIF capabilities
• test it
• also try InnoDB with cluster index on healpixId

Description of the Tests

The tests were done on lsst-dev01 (2 CPUs), using /u1/ file system which can do ~45 MB/sec sequential read (tested using "dd if=/u1/bigFile of=/dev/zero")

Input data: USNO-B catalog, 1 billion rows (65GB in csv form). Schema:

CREATE TABLE XX (
  # objectId  BIGINT NOT NULL PRIMARY KEY, -- added later
  ra        REAL NOT NULL,
  decl      REAL NOT NULL,
  muRA      FLOAT NOT NULL,
  muRAErr   FLOAT NOT NULL,
  muDecl    FLOAT NOT NULL,
  muDeclErr FLOAT NOT NULL,
  epoch     FLOAT NOT NULL,
  bMag      FLOAT NOT NULL,
  bFlag     FLOAT NOT NULL,
  rMag      FLOAT NOT NULL,
  rFlag     FLOAT NOT NULL,
  b2Mag     FLOAT NOT NULL,
  b2Flag    FLOAT NOT NULL,
  r2Mag     FLOAT NOT NULL,
  r2Flag    FLOAT NOT NULL  
);

Quick Summary

• Speed of loading is reasonable (~1 h 20 min)
• Speed of building indexes is acceptable (~5 h)
• Selecting small number of rows via index is very fast
• Speed of full table scan is at ~85% of raw disk speed (30 min)
• Selecting large number of rows via index (full index scan) is unacceptably slow (8 h)
• Speed of join is relatively slow: at 15% of raw disk speed (~5 h)

Details are given below.

Data ingest

Loading data done via

LOAD DATA INFILE 'x.csv' INTO TABLE XX FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'"

took 1h 19 min.

iostat showed steady 27 MB/sec write I/O, disk was 18% busy, CPU 90% busy.

The db file size (MYD): 60 GB

Building key

Command:

alter table add key(bMag)

CPU ~30% busy. Disk ~75% busy doing 41 MB/sec write and 21 MB/sec read

Took 5h 11min

MYI file size: 12 GB

Adding primary key

(This is not a typical operation - we won't be doing it in LSST)

Command:

ALTER TABLE XX 
ADD COLUMN objectId BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Took 15h 36min

MYD grew to 68 GB, MYI grew to 26 GB

Index for objectId takes 14 GB. (theoretically, 1 billion x 8 bytes is 8 GB, so 43% overhead)

Full table scan

Command:

-- note, there is no index on r2Mag
select count(*) from XX where r2Mag > 5.6

Takes 30 min

iostat shows between 36-42 MB/sec read I/O, which makes sense (68 GB MYD file in 30 min = 38 MB/sec). This is 84% of the best this disk can do.

Selecting through index

Small # rows

Selecting a small number of rows through index takes no time

Large # rows (full index scan)

-- there is an index on bMag
select count(*) from XX where  bMag > 4;

Took 7h 55 min

The result = 370 milion rows (37% of all rows)

iostat shows there a lot of small io (1MB/sec), which is consistent with elapsed time: IDX file size = 27.4 GB --> 1MB/sec. It looks like mysql is inefficiently walking through the index (tree), fetching pieces from disk in small chunks.

Note that a similar query but selecting small number of rows

-- this returns 0 rows
select count(*) from XX where  bMag > 40;

Takes no time.

As expected, disabling indexes and rerunning query takes 29 min 14 sec (full table scan)

Speed of join

We may want to join USNO-B objects with eg SDSS objects on the fly (assuming objectId are synchronized)...

-- create dummy sdss table
CREATE TABLE sdss (
  objectId  BIGINT NOT NULL PRIMARY KEY,
  sdss1     FLOAT NOT NULL,
  sdss2     FLOAT NOT NULL,
  sdss3     FLOAT NOT NULL,
  sdss4     FLOAT NOT NULL,
  sdss5     FLOAT NOT NULL,
  sdss6     FLOAT NOT NULL
);

-- in this case it is 25% of usno rows
INSERT INTO sdss 
SELECT objectId, bMag, bFlag, rMag, rFlag,b2Mag, b2Flag 
FROM XX
WHERE objectId % 4 = 0;

The loading took 1 h 21 min.

Doing the join:

CREATE TABLE refCat1 
SELECT * FROM XX
JOIN sdss using (objectId);

Takes 3 h 54 min

It involved reading 68+8 GB and writing 23 GB. That gives speed 7 MB/sec, which is ~15% of the raw disk speed.

Speed of spatial join

TODO (Serge?)

Full USNOB-1 Schema

In Informix format (easy to map to MySQL)

create table "informix".usno_b1 
  (
    usno_b1 char(12) not null ,
    tycho2 char(12),
    ra decimal(9,6) not null ,
    dec decimal(8,6) not null ,
    e_ra smallint not null ,
    e_dec smallint not null ,
    epoch decimal(5,1) not null ,
    pm_ra integer not null ,
    pm_dec integer not null ,
    pm_prob smallint,
    e_pm_ra smallint not null ,
    e_pm_dec smallint not null ,
    e_fit_ra smallint not null ,
    e_fit_dec smallint not null ,
    ndet smallint not null ,
    flags char(3) not null ,
    b1_mag decimal(4,2),
    b1_cal smallint,
    b1_survey smallint,
    b1_field smallint,
    b1_class smallint,
    b1_xi decimal(4,2),
    b1_eta decimal(4,2),
    r1_mag decimal(4,2),
    r1_cal smallint,
    r1_survey smallint,
    r1_field smallint,
    r1_class smallint,
    r1_xi decimal(4,2),
    r1_eta decimal(4,2),
    b2_mag decimal(4,2),
    b2_cal smallint,
    b2_survey smallint,
    b2_field smallint,
    b2_class smallint,
    b2_xi decimal(4,2),
    b2_eta decimal(4,2),
    r2_mag decimal(4,2),
    r2_cal smallint,
    r2_survey smallint,
    r2_field smallint,
    r2_class smallint,
    r2_xi decimal(4,2),
    r2_eta decimal(4,2),
    i_mag decimal(4,2),
    i_cal smallint,
    i_survey smallint,
    i_field smallint,
    i_class smallint,
    i_xi decimal(4,2),
    i_eta decimal(4,2),
    x decimal(17,16) not null ,
    y decimal(17,16) not null ,
    z decimal(17,16) not null ,
    spt_ind integer not null ,
    cntr serial not null 
  );

revoke all on "informix".usno_b1 from "public" as "informix";

create index "informix".usno_b1_cntr on "informix".usno_b1 (cntr)  using btree ;
create index "informix".usno_b1_dec on "informix".usno_b1 (dec)    using btree ;
create index "informix".usno_b1_spt_ind on "informix".usno_b1     (spt_ind) using btree ;

Proposed Reference Catalog Schema

Work in progress

    objectId BIGINT NOT NULL PRIMARY KEY,
    ra DOUBLE,      -- ra
    decl DOUBLE,    -- del
    muRa DOUBLE,    -- pm_ra
    muDecl DOUBLE,  -- pm_dec
    parallax FLOAT, -- ???
    b1Mag FLOAT,    -- b1_mag
    r1Mag FLOAT,    -- r1_mag
    b2Mag FLOAT,    -- b2_mag
    r2Mag FLOAT,    -- r2_mag
    iMag FLOAT      -- i_Mag
    -- what about errors for all the above???