Keywords: DataAccWG
Attendees:
Ray Plante
Kem Cook
Arun Jagatheesan
Stuart Marshall
Ani Thakar
Maria Nieto-Santisteban
Russell Owen
Jacek Becla
Revisiting original requirements, partitioning
==============================================
Original requirement: 200 low volume users query 10 million
objects, returned data size: 1 GB / query
- 1 GB = 5 million objects: that is 50% of queried objects
- better to do full table scan and don't use indexes
- mysql will automatically do this
- not really what we want to estimate (200 full table scans)
- suggestion: 300 queries, return 0.5 GB (25% of rows)
user queries 10 million objects implies pre-partitioning
- 10 billion objects in catalog, so 1000 partitions,
10 million objects per partition
- 1000 partitions --> 2 square degrees per partition
(because ~2000 fields)
- why restriction: 10 million objects?
- user searches a small region of sky
- what is a reasonable region a low-volume user might search?
- 1 square degree or smaller
- so each query is likely to query a single partition
- it is reasonable to assume each low volume query searches
one partition
- BTW, searching eg 3 partitions would in practice increase
disk io roughly x3
- as long as number of accessed partitions does not grow
such that caching starts to take effect
- merging results might trigger extra disk io
Have spreadsheet where can easily change number of
partitions and size of returned data set (as simple as
changing value in 2 cells), so we can tweak it later
Asking for 50% of data from searched 1 square degrees
sounds like too much anyway, 25% better
---> for the purpose of the disk-io-estimate exercise
assume
1) each low-volume user will query 1 partition
2) 300 low volume queries, 0.5 GB result size
Super-high-volume
=================
- size of returned data set too low for LSST scale [per Ani]
- will raise with Jeff & Tim next week, will not have time
to work on super high volume this week anyway
(Still working on low volume)
How should we size catalog?
===========================
- (agreed before) deep db: 10 billion objects (stars and galaxies)
because of superhighvolume query
- that is DR2 based on db size estimate spreadsheet
- temp db in DR2: 600 billion detections
- ok, assume 600 billion detections for this exercise
- how to partition detections?
- for sizing spreadsheet we assumed 100 epocs per year per star,
so in dr2 each star's photometry has 100 epocs, want to keep
epocs together
--> slice based on sky: 2 sq deg per partition
that is 600 million detections per partition
- so each temporal query will search one partition
(600 million detections)
Some other assumptions
=======================
Assuming no caching, because 300 queries, 1000 partitions,
each query searches different partition
Assuming 70% of queries spatial, 30% temporal,
- don't know the "culture" in 2013, not sure how
correct this assumption is
Assuming precursor schema (merged llnl+uw)
For spatial queries assuming
- will assume ra, decl for all low-volume
- possibly htm for high-volume and super high volume
- have to understand disk io for htm
Should we keep variable objects in separate table?
==================================================
In current version of schema, variable objects together with
static objects, all in one Object table
- ~5% of all objects are var objects
- some queries need to join Object table with "VarObjects",
would be much more efficient to have var objects in separate table.
----> yes, let's separate var objects from Object table
Should we split Object table into different types?
==================================================
- Possible types: star, galaxy, moving objects
- if an object has more than 1 detection, it is very likely that
it is not a moving object, so either star or galaxy
- ~58% galaxies, ~42% stars
- many queries work with one type (where type="galaxy" and ...)
- because selectivity of index on "type" is very high,
this index is useless, so will have to go through all objects
even if really want just one type
- splitting stars and galaxies into separate tables would
immediately reduce io by almost 50% for queries that deal
with one type
- in some cases the same object can be classified as more than
one type (with different probability)
- BTW, that is not supported by our prototype schema, we have
only one column "type"
- if we split, and object is classified as more than one type,
it would appear in more than one table, so it is ok
- disk overhead due to duplication very small, very small number
of objects will be classified as multi-type
- keeping stars and galaxies together means wasting lots of
disk space:
- metadata for describing different types is different:
e.g. galaxies need different set of columns than stars,
but we will have to keep these columns for stars as well...
- maybe we can do this through views?
- but from disk space and io point of view it is the same
as if we were dealing with full table
- not sure if precomputed views could help
- some queries need both stars and galaxies (find all objects
near a given galaxy...)
- that is ok, could search in two tables
- danger of separate tables: if classification changes, need
to deal with that
- only things like reprocessing, deblender, could reclassify
- that will likely happen only between different releases,
so we are ok, we are not going to keep links between objects
from different releases
- worry: looking for neighbors will look only in one table:
find neighbors of a star will look in star table only.
- yes, will have to deal with that, but computing near neighbors
is a single query run in production once every few weeks or so,
and during this time we will have 1000s of queries looking
for one type of objects.
--> conclusion: good idea, split Object table into 3 tables:
stars, galaxies and moving objects. BUT...
- this is one of the most fundamental decisions about DB schema
(more on that below)
colors: precompute or compute on the fly?
=========================================
- At the moment our schema assumes that we don't store color
information in schema, and we always calculate it on the fly
by subtracting magnitudes (e.g gMag-rMag).
- that excludes indexes on xMag because difference
have to be calculated for each row
- yes, a lot of common queries want to do selection based on color
- usual searches involve increasing red (step by step) so we would
only need 5 colors
- sdss experience?
- sloan does not precompute colors, did not even think about it
- are indexes on magnitudes really used then? - most likely not
- normally when a user specifies color he/she also specifies
magnitude limit
--> conclusion: yes, precompute colors
Example query
=============
find extremely red galaxies:
SELECT objectId, ra, decl, uMag, gMag, rMag, iMag, zMag
FROM Object
WHERE type = "galaxy"
AND (iMag - zMag > 1.0)
Index on type not selective enough (58%), so can't use.
Index on iMag, zMag can't be used because of subtraction
So we have to do full table scan at the moment.
======
Select transients near a known galaxy:
SELECT v.ra, v.decl
FROM VarObj v, Object o
WHERE abs(v.ra-o.ra) < <DeltaRA>
AND abs(v.Decl-o.Decl) < <DeltaDec>
AND o.Type = "galaxy"
- the query is not right:
- there is missing ra, decl constraint, (need starting point)
summary of needed schema related changes
========================================
Discussed:
- split variable objects from Object table
- split Object table into Star, Galaxy, MovingObject tables
- precompute colors
Not discussed:
- add index on (ra, decl)
- need to add postage stamp support
- need to add template images support
Also, there is name mismatch between queries and schema:
eg "type" in queries, "objectType" in schema.
Fundamental schema decisions
============================
Several decisions made today are fundamental for
DB schema design (splitting Object table, extracting
var objects, precomputing colors)
- treat these decisions as very preliminary
- need to very well understand the science
point of view and implications
- suggesting to have a database telecon next week
and invite Jeff, Tim, Kirk, Kem (who else?) to rediscuss
- also check with Jim Gray
disk io estimate
================
- assume what was tentatively decided today for further
disk io studies
- there is a chance the low-volume part might be in a good
shape by the end of this week (the deadline Jeff gave us),
but definitely not everything...
Jacek
Jacek Becla wrote:
Keywords: DataAccWG
Hi all,
We will continue the discussion about Disk IO estimates
tomorrow (Wed) at 11:00 AM PDT.
Here are my current notes:
http://www.slac.stanford.edu/~becla/tmp/DiskIOEstimates.doc
I expect to discuss some of the "assumptions made".
Phone number: 866 330 1200
Pass code: 300 2363
Jacek
_______________________________________________
LSST-data mailing list
[email protected]
http://www.lsstmail.org/mailman/listinfo/lsst-data
_______________________________________________
LSST-data mailing list
[email protected]
http://www.lsstmail.org/mailman/listinfo/lsst-data
