Last modified 13 years ago Last modified on 06/19/2007 09:25:30 PM

Database and Nightly Processing in DC2

LSST Database


All Catalogs, including the Object Catalog, the DIASource Catalog and MOPS Catalogs will be managed by a relational database – MySQL. All key assumptions and requirements directly affecting the database at the Base Camp are documented in [2].

Database ingest will be controlled by the Catalog Ingest Service (CIS). Exact scope of responsibilities of CIS to be decided.

Expected size of the historical Object Catalog in DC2 is ~0.5 terabytes. In DC2 we will start with an empty DIASource Catalog. DC2 input data is described in detail in [1], and the sizes are discussed in more detail in [2] and [3].

3-phase Processing

At almost any given time there will be 3 phases running simultaneously: post-process for the “previous” FOV, real-time updates for the “current” FOV and prepare for the “next” FOV. The main reason for having 3 phases is to meet the real-time requirements: splitting processing into 3 phases reduces latency by avoiding disk I/O.

Will MOPS be integrated into the 3-phase approach? Specifically,
  • will prediction be done during prepare phase
  • will MOPSPred be in-memory which implies preloading (prepare phase) and flushing to disk (post-process phase)

Preparation For Nightly Processing

Start Catalog Ingest Service (CIS). CIS does:

  • Creates main Catalogs if they don't exist
  • Loads data to Object Catalog if it is empty
  • Creates Catalogs for storing nightly-specific information (or truncate if they exist)
  • Reloads some information to memory: all configuration parameters/policies such as match radius, stripe width, number of partitions, Source Classification, etc.

How much do we integrate MOPS schema with core schema? (e.g. do we put MOPS schema in EA? do we initialize MOPS schema in CIS?)

How to name databases and tables? How to pass these names to Detection Pipeline and Association Pipeline?

Note that the schema used at the Base Camp will be optimized for nightly processing, in particular:

  • it will be a subset of entire LSST schema (eg. VarObj Catalog will not be maintained)
  • indexes and partitioning will be tuned for Association Pipeline
  • the Object_photoZ and similar tables will be “merged” into the Object table

Preparation For Processing a Visit

30 sec before the first image from a given pair (visit) is taken, Catalog Ingest Service receives a signal from Online Control System with information about image coordinates. It will then truncate tables used for storing per-visit information (if the tables don't exist, it will create them) and load all necessary information from disk-based tables into in-memory tables.

How is the signal received in DC2?

MOPS Position Prediction

Reads information from MovingObject Catalog. Creates a set of predicted DIASources in MOPSPred Catalog.

Image Processing

At the end of Image Processing (IP), IP writes to CVS files the information about image, which includes persisting the following information:

  • information about the two exposures:
    • 1 row per ccd per exposure in CCD_Exposure table
    • 1 row per amplifier per exposure in AmpExposure table
    • 1 row per exposure in FPA_Exposure table
  • information about the visit:
    • 1 row in Visit table and 2 entries in _FPAExposure2Visit

This information is ingested into database during post-process phase by CIS.

  • What about other information like PhotoCal, WCS, PSF??? [need input from a scientist]
  • What about metadata?

Detection Pipeline

  • At the end of Detection Pipeline (DP) it writes newly detected sources into in-memory DIASourceNew table using INSERT.
  • DP sends information about classification (per DIA source) to AP

How is classification related information sent from DP to AP?

Will DP provide information about data source? (Note: this is DC2 specific)

Association Pipeline

The main tasks of the Association Pipeline (AP) are to:

  • detect variable objects by cross-matching the newly detected DIASources against the Objects Catalog
  • detect moving objects by cross-matching the newly detected DIASources not associated with any previously known variable objects with MOPS-pred catalog generated by MOPS.
  • decide where to send each source (MOPS? Alert Processing? Junk Collection?). To do that, it will read the in-memory SourceClassification table.
  • persist results

In practice, this will involve:

  • cross matching DIASourceNew Catalog with Object Catalog and persisting results:
    • updating corresponding existing objects
    • adding new objects
    • persisting information about all matches within searched radius (not only the closest). [BTW, we don't have schema for that yet]
  • cross matching MOPSPred with remaining DIASourceNew and persisting results:
    • updating corresponding moving objects
  • possibly rechecking cases where moving object and variable object overlap [still unclear]
  • persisting information about source classification [perhaps this belongs to DP?]

Note that we will not be deleting objects previously classified as variable objects which are now classified as moving objects. This will be done by MOPS during day time.

More About Persisting Results

  • Updating Object Catalog: in DC2 the worst case scenario will involve updating O(10,000) objects every 37 sec. [unclear how to update information in Object Catalog based on information in DIASource]
  • Adding new objects: in DC2 O(100) (newly detected variable objects)
  • Deleting re-classified objects: O(10) in DC2
    • Persisting information about source classification.

The new DIASources should be visible to the Alert Processing when current visit is processed, and the next time the same FOV is visited. The updated objects should be visible to AP the next time the same FOV is visited.

Several important measurements will be made in order to support efficient operation of the AP.

  • The Object and DIASource Catalogs will be partitioned.
  • The critical, real-time operations will be performed using an in-memory copy of the data.
  • The Association Pipeline will process data in parallel.


Both the Object and the DIASource Catalogs will be partitioned. Partition size and shape will be tuned to reduce and de-randomize disk I/O, and allow for easy parallelization. One of the most important factors in reducing disk I/O is avoiding indexes – rebuilding indexes for the large Object table in real time would require lots of I/O and computing power.


To meet real time requirements, all critical operations will be performed using an in-memory snapshot of the data. Clearly keeping the entire Object Catalog in memory is very cost-ineffective; however, it is sufficient to keep in memory only the data related to the currently-processed FOV. To achieve that, the processing of each FOV will be split into 3 phases: prepare, compare-and-update and post process. The entire real time phase phase will use in-memory tables.


Each FOV will be partitioned into multiple “stripes” by declination. A stripe is a natural unit of parallelism. Based on tests [4], the optimal number for DC2 is ~3-4. The number takes into the account data size, number of CPUs available on the DC2 database server, and the way the MySQL server is using multi-threading.

Alert Generation

Note: the part is beyond DC2 scope, it is added for completeness

Alert Generation will read the DIASourceNew Catalog, and will process these classified as “alertable”. For these sources it will perform any additional filtering using the in-memory historical DIASources.

Post-Process After Processing a Visit

The post-process phase will make sure all changes kept in in-memory tables are written back to appropriate partitions in the disk-based tables. Partitions needed for the next FOV that are already in memory will be kept; others will be dropped to make room for the prepare phase of the next-plus-one FOV.

Also, because of QA, we probably want to maintain a table with ids of all DIASources from the current night: "DIASourceIdTonight"

After Nightly Processing

CIS will be probably be shutdown as part of nightly processing shutdown [open question]

The database activities will continue. In particular, a table DIASourceTonight with all DIASources detected “tonight” will be prepared for QA based on the ids kept in DIASourceIdTonight. This table will be indexed.

Will QA write anything to database?

In real production (beyond DC2 scope) we will also:

  • trim old DIASources during the day
  • ...?

Database Interactions at a Glance

  Prep once Prep 4 nightly Prep 4 visit MOPS Pred IP DP AP Alerts Post visit Post nightly day time
Object ca   r       radu r w    
DIASource c   r         r aduw    
DIASourceNew     r     a r r rd r  
DIASourceIdTonight   c             a r  
DIASourceTonight                   cai r
MovingObject c     r     au        
MOPSDetections c     r              
MOPSPred c c   a     r        
Exposure related c       a           r
SourceClassification c r         r        


  • a – add new rows
  • c – create table (or truncate if exists)
  • r – read
  • u – update
  • d – delete
  • i – rebuild index
  • w – write to memory based to disk based
  • yellow background: disk based tables
  • green background: in-memory tables

Single-Server Architecture

Based on spreadsheet models [3] and extensive tests [4] we have run, a single database server with at least 8 cores, 16GB RAM and ~150-250 MB/sec sustained disk I/O bandwidth will be sufficient to meet the DC2 requirements. We believe that a similar architecture based on a single database server with ~10x more RAM, CPU power and disk I/O bandwidth will be sufficient for real LSST production; however it may be more cost-effective and scalable to use multiple servers to obtain the same resources. For reliability reasons, in real production we expect to mirror the database setup.

If we find reasons to go with multiple servers, there are no architectural obstacles preventing us from doing it. In fact, the architecture is already multi-server ready: e.g. even in DC2 we will load balance partitions across multiple databases, and moving some of these databases to a different server is well understood.


  1. DC2Plan: Input Data
  2. Nightly Processing – Database Related Requirements, Docushare Document-3591
  3. LSST Database I/O at Base Camp, Docushare Document-3594
  4. DC2 Database Partitioning Tests?