Hi Jacek and Maria,
So I think we are more or less on the same page :). Some
thoughts/questions below.
1. Indexes on object table
In order to quickly locate particular rows to update, should we have some
kind of index on object id (or is leveraging the spatial index to find
objects fast enough)?
2. If generating the spatial index for the cross-match using the DBMS and
in-memory temp tables is fast enough, I'm still in favor of not having a
spatial index for the object table on-disk at all. As a compromise between
Maria's approach and Jaceks original proposal, I would make each region
significantly smaller than a FOV (say only 1 or 0.5 square deg) so that we
are still reading fairly large, complete partitions (for the very
efficient IO access patterns), but don't overshoot an actual FOV by huge
amounts.
3. Handling the problem of objects migrating across regions/partitions
Similar to the in-memory index I was proposing earlier, how about we
assign objects to regions based on their position at time-of-insert? The
region boundaries correspond to some disjoint spatial subdivision (used
as-is for inserts), but the query that reads in regions uses the region
boundaries padded by the max angular distance an object can "move" by to
determine whether or not the region overlaps the FOV. Actually, it's
probably easier to just pad the geometry corresponding to the FOV.
Once the regions are read in, build the spatial index and match using
actual positions. Alternatively (assuming there is a zone,ra cluster index
on disk) increase the match distance and perform further filtering to
acount for actual positions being slightly different to positions used for
index creation. This means the columns the spatial index is based on are
never updated and that objects will never move across regions, but would
require some extra columns in the schema (current as well as
time-of-insert position attributes).
Do we have any expectations on how much an object (that is not dealt with
via the MOP) can move in 6 months to 1 year?
4. Should the common part of the object table be further vertically
partitioned into position/everything else? My thinking is this should
translate to better in-memory locality for position attributes => speed up
spatial index creation time (if we do it on the fly).
5. Updates
At least using Informix the 100k updates would be abysmally slow if
the rows don't cluster well (even if the rows to update are in memory
already)... So how about this: inside of each region, split the original
object table into a regular object table and a table of objects likely to
produce a lot of DIA Sources (call it the high SNR variable object table).
Then update the tables individually. This should ensure good disk locality
for the majority of the updates (in the high SNR varobj table) and limit
the number of random writes. Maria's "replace the entire partition"
strategy should work extremely well on the varobj table.
6. The one thing I don't see addressed yet at all are deletes on the
object table (if I recall correctly the MOP is going to clean-up after
us). I don't really understand the performance implications of this (or
how/when it happens) too well. If it's a problem the only thing that comes
to mind is a small per-region "womb" table that new objects are inserted
into and that the MOP deletes from. Once an object has been in the womb
table for some length of time (enough to make deletion by MOP unlikely),
it is "born" and inserted into the big object table(s). As for the
updates, the idea is to localize the inserts/deletes to a relatively small
chunk of physical storage. We then tell the DBMS to maintain the entire
table in memory during the night, and deal with inserts into the big table
during the day.
Will try and send more comments tonight,
Serge
P.S. Any time 11am PST or later next Monday is fine with me.
On Thu, 09 Nov 2006 09:57:01 -0800, Becla, Jacek <[EMAIL PROTECTED]>
wrote:
Keywords: DataAccWG
-----Original Message-----
From: Becla, Jacek
Sent: Thursday, November 09, 2006 9:47 AM
To: 'Maria A. Nieto-Santisteban'
Cc: Serge Monkewitz; Ani Thakar; Tim Axelrod; Jeffrey P Kantor
Subject: RE: indexing/partitioning action item
Hi Maria
> 3) in the post-processing stage, we flush the updated rows
from the
> in-memory table into appropriate disk-based partitions.
Flushing 100K
> rows to disk (in sequential fashion) should not be too bad
Flushing 100 K rows to disk doesn't mean you have updated the original
tables.
Yes, sure, poor choice of words on my side. I meant replacing affected
rows in the original table
One alternative to avoid the updates(deletes/inserts) is reading the 4
neighbor regions, do whatever you have to do in the memory and then
replace the old partitions with the new ones. That is a common
technique.
This is very disk IO heavy. 1 entire partition = 10 million rows, and we
update ~1 % of rows (100K), in neighbor-partitions this fraction will be
even smaller. We should not attempt to write back all 10 million rows if
we can get away with writing << 1% (especially if these <<1% will be
clustered in a "good way")
> - you are not saying that we should keep these "temporary
tables" in
> memory, but I am guessing you do want it, right?
Not sure whether you refer to 1) keeping them in memory in the sense
of work with them in memory or 2) keeping them on memory the whole
night?
The former, per visit, then discard
I guess only if you can do all in memmory by your self. I don't know
if the DBMS will let you create the table only in memory and work with
that.
Is there any DBMS that let you say i want this table to be in memory
only?
Oh YES, in-memory database are very hot these days, the whole telecom
rides on them!, and now defense it starting. One example (recently in
the news):
http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9004516&source=NLT_AM&nlid=1
Oracle and mysql have good support for in-memory databases, postgres: no
but they are working on it. Don't know about db2. In mysql you just say
that the table type should be "in memory". You can trivially covert
regular table to in memory. Indexes are optimized for memory access, but
there are so restrictions too, e.g. var-length fields are converted to
non-var.
> We should try to talk on Monday, I am flying to Tucson Monday late
> afternoon, then in Tucson the rest of the week, practically
unavailable.
How about 2:00 ET?
11 PST? That would be during Data Mgmt telecon. 1 hour later might work
better (earlier there is techassess telecon, so we will not get the
telecon line)
Thanks,
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
