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
