jim,
i think jacek is referring to the cost of duplicating the object tables,
if we have *both* a common table as well as separate tables for
different object types (albeit with fewer parameters more specific to
each type).
in fact we wanted to get your opinion on whether we should *just* have
the separate tables per object type instead of a common table and views
for each object type (like we do in SDSS). what would it mean
performance and maintenance wise to have just the separate tables? we
know it may mean some non-trivial queries for users who want to search
all or multiple object types, but what else from a DB point of view?
ani
On Fri, 23 Jun 2006, Jim Gray wrote:
> (1) let the record show that SDSS has a common star/galaxy table in the
> DB, so I am unclear what cost is prohibitive for LSST.
>
> (2) covering indices are your friend.
> All but one index is non-clustered but the index can carry lots of
> data fields.
> This is a way of having multiple data organizations.
>
> But.. Of course maintaining these indices is expensive (inserts are
> expensive).
> You need a good read/write ratio (>>1)
>
> Jim Gray
> Microsoft Research, Suite 1690, 455 Market, SF CA 94105, tel: 415 778
> 8222 fax: 425 706 7329 [EMAIL PROTECTED]
> http://research.Microsoft.com/~gray
>
>
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Jacek Becla
> Sent: Friday, June 23, 2006 2:00 PM
> To: LSST Data Management
> Subject: [LSST-data] Notes from Database telecon Fri Jun 23
>
> Keywords: DataAccWG
>
> Attendees:
> Ray Plante
> Russell Owen
> Kem Cook
> Sergei Nikolaev
> Zeljko Ivezic [part of telecon]
> Jacek Becla
>
>
>
> review proposed changes to the schema
> =====================================
>
> Proposed changes ok
> - have "extended object" table in schema, perhaps could use it BUT
> Zeljko: splitting Object table into Stars and Galaxies can be a
> nightmare to deal from science perspective
>
>
> Would be nice to duplicate data to have at the same time
> 1) all objects in one table and
> 2) stars and galaxies separate
> - but at this point that can not be considered as a realistic
> option because of the cost constraints we have to assume
>
>
> Conclusion:
> - write a short (1-2 pages) high level summary write-up
> about proposed changes, then discuss at Science Council
> meeting
> - would be useful to show through spreadsheet the cost depending
> on whether we split Object table or not
> (relatively simple for disk space estimate, much more work
> for disk io estimate)
>
>
> At some point soon (when Jacek finishes the disk io spreadsheet),
> someone should help out with checking it to ensure the assumptions are
> correctly reflected there.
>
>
> Jacek will work primarily with Kem and Sergei on the write up, and will
> keep Ray in the loop
>
>
>
> another controversial topic....
> ===============================
>
> retrieving data through non-clustered index can easily be worse than
> full table scan
> - what is a clustered index? Index, which is used to sort the
> data rows on disk. Any other index is non-clustered.
> So if we sort (cluster) data based on spatial locality (eg ra,decl),
> index on ra,decl becomes "clustered", and index on "color"
> is not-clustered.
> - fetching data rows through a non-clustered index means
> reading data very sparsely (selected rows are scattered
> across all blocks randomly), to make things worse, blocks
> are read in random order
> - if number of data rows fetched through index = ~ number
> of data blocks, every block will likely be fetched
> (in random order).
> That is much worse than full sequential table scan...
> - in practice, for the numbers we have in the spreadsheet:
> (DR2 data set, 1000 partitions) selecting more than 2.5%
> of rows from a single partition through a non-cluster index
> turns out to be worse than full table scan of the partition
> - fetching the same number of rows (not the same percentage)
> from non-partitioned data set: non-clustered index
> better
>
>
> Proposing to consider the following approach:
> - pick one index and rely on it. Most likely this would be
> spatial index (even most temporal queries use ra,decl...)
> - sort the data based on that index (make it a clustered index)
> - keep the whole index in memory
> Based on current db size estimate, we would need for
> ra,decl index (worst case):
> - 117 GB for DR1
> - 250 GB for DR2
> - 420 GB for DR20
> which is very reasonable.
> - rely on that index as much as possible: force its use first
> in every query that has spatial constraints.
>
>
> Jacek
>
>
>
>
>
>
> Jacek Becla wrote:
> > Keywords: DataAccWG
> >
> > Hi all,
> >
> > We will have a Database telecon tomorrow (Fri) at 11:00 AM PDT.
> > (yes, it will be much more Database-oriented than DataAccess)
> >
> > The main topics
> > - proposed changes to schema
> > - update on disk io estimates
> >
> > Relevant readings:
> >
> > 1) email I sent earlier today about proposed schema changes
> >
> > 2)
> > http://www.slac.stanford.edu/~becla/tmp/lsst_diskIO_estimates_v01.doc
> > [the "Per Query Breakdown" starting on page 3 is probably worth
> > re-reading if you already saw the previous version]
> >
> > 3)
> > http://www.slac.stanford.edu/~becla/tmp/lsst_diskIO_estimates_v01.xls
> > [do keep in mind it is under construction, any comments or bug
> > reports would be appreciated]
> >
> >
> > 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
>
> _______________________________________________
> LSST-data mailing list
> [email protected]
> http://www.lsstmail.org/mailman/listinfo/lsst-data
>
--
Aniruddha R. Thakar, Research Scientist
Center for Astrophysical Sciences, JHU, Bloomberg 375
3701 San Martin Drive, Baltimore MD 21218-2695
410-516-4850, Fax: 410-516-5096
[EMAIL PROTECTED], http://www.sdss.jhu.edu/~thakar
-----------------------------------------------------------------------
He who throws mud loses ground.
_______________________________________________
LSST-data mailing list
[email protected]
http://www.lsstmail.org/mailman/listinfo/lsst-data
