(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
