Hi Tim,
I reworked the disk io spreadsheet: removed "variable star near hot
star", changed number of other high-volume queries to keep the total
number = 20, and changed the query plan for "find new transients
for given epoch" as discussed. The CPU requirements went down
by ~80% (5329 --> 972). Disk IO went down 5%.
The spreadsheet:
http://www.slac.stanford.edu/~becla/tmp/lsst_diskIO_estimates_v15b.xls
Jacek
Tim Axelrod wrote:
Hi Jacek,
I think the main issue regarding the "variable star near hot star" query
is that we have it in the "common, high volume category". I think this
is the wrong choice. While I can understand the rationale for the
query, I think it should not be executed with any frequency. I have
two reasons for thinking this. The first is that I am confident that
the size of the community interested in the results of this query is
quite small. The second is that the results delivered by the query
should evolve only slowly as the survey proceeds. It is a good
candidate to execute prior to every DR, generating a specialized catalog
that can itself be queried by those interested. So, without worrying
yet about redesigning the query itself, I'd suggest we run it once per DR.
As a more general point, I think we have occasionally discussed the need
to limit the resource consumption of a query. This is clearly
necessary, and we need to be sure that we have that limiter in mind when
we project our query cpu requirements.
As a second general point, I suspect we need to do some more thinking
about the whole category of queries that involve "find something near
something else". Spatial indices are clearly key, as pointed out by
Jim Gray.
Cheers,
Tim
Tim Axelrod wrote:
Hi Jacek,
Yes, I thought about keeping alertId in the Object table. The
complication is that it is quite possible that an Object will have
more than one alert sent over its history. So, if the alertId is in
the Object table, you must still search somehow the Alert table to
find the full set of alerts.
To be useful, this query needs to make use of the alert table. The
query without the alerts is simpler, as you say, but it will return
great volumes of data that would usually be of no interest to anyone.
Thanks for the "variable star near hot star" query. I am pondering
it...
BTW, I am copying this to the mailing list, because I think it will be
of more general interest.
Cheers,
Tim
Jacek Becla wrote:
Hi Tim,
OK, sounds good.
I suggest instead of a bit flag indicating whether an alert
was generated, we should keep alertId. NULL value will
indicate there was no alert.
So assuming Object table has the following rows:
- firstTAI DATETIME ## earliest observation time
- latestTAI DATETIME ## most recent observation
- alertId INT DEFAULT NULL
(feel free to suggest better names for firstTAI and latestTAI),
the query will look like:
SELECT objectId
FROM Object
JOIN Alert USING (alertId)
WHERE Object.latestTAI > t0
AND Alert.timeGenerated > t0
BTW, which query do you mean by "your form of query":
- the one with alerts or without?
The query without alerts will be trivial:
SELECT objectId
FROM Object
WHERE Object.latestTAI > t0
Do we want to sort results?
> What are the other top cpu consumers of the query set?
The other one (actually it is the top one, I missed it
the first time) is "find variable stars close to hot stars"
It is responsible for close to 60% (so you can see that
if we rewrite these two queries, the cpu usage will go down
dramatically).
SELECT v.objectId, v.RA, v.Decl,
o2.objectId, o.RA, o.Decl
FROM VarObj v, Object o
WHERE v.objectId <> o.objectId
AND abs(v.RA - o.RA) < 0.003 / cos(radians(o.decl)
AND abs(v.Decl - o.Decl) < 0.003
AND v.type = "star"
AND o.type = "star"
AND ((o.g - o.r < 0.0)
OR (o.r - o.i < 0.0)
OR (o.u - o.g < 1.0))
For that query I assumed we have precomputed colors:
grColor, riColor, ugColor and have index for each of them
(you will not find it in precursor schema, but it is
included in the spreadsheet "schema" and it is on my
pending-schema-changes list)
So the query does the following: for each partition:
- use index on grColor and select 0.002% of all rows
- use index on riColor and select 0.002% of all rows
- use index on ugColor and select 0.002% of all rows
- now we have 0.006% of all rows from Object table
(sum because it is OR)
- use index on "type" and select 95% of rows (95% is
derived based on star:galaxy ratio). This gives us
620 rows
- use objectId index to find objectIds for selected rows
- now comes the "heavy" part: we join 620 rows with
all rows (except one) from VarObj table.
The join is through objectId index.
VarObj table has almost 1 billion rows. After join
we end up with 617 billion rows. This intermediate
result will definitely NOT fit into memory, so we
have to keep in on disk
- then we have to look at the distance for each row from
the intermediate result (the v.RA-oRA...part). That
requires full index scan for VarObj table, and
fetching intermediate result from disk
I am assuming 0.000003% of rows is selected, and this is
is the result.
Everything described above is repeated 2,000 times
(that is how many partitions we assume).
The numbers 0.002% and 0.000003% were picked "by hand"
such that the returned result set is ~ 1GB
Any suggestions how to improve this?
Jacek
Tim Axelrod wrote:
Hi Jacek,
I would like to go with my form of the query for the purposes of cpu
sizing. Again, the way I am interpreting the goal here is to
return all Objects that have had a transient identified after some
input time t0. This result is of scientific interest. Asking
about "all detections of a given Source after t0" makes no sense.
A Source is only a single detection at a particular time. Only
the Object relates sources to each other and says "this set of
Sources come from a particular astronomical object".
In any case, the way I have formulated the query requires only
reference to the Object table, which is a big win over scanning
through the entire Source table! We now need to have a reasonable
estimate of how much this approach helps the cpu.
What are the other top cpu consumers of the query set?
Thanks,
Tim
Jacek Becla wrote:
Hello,
I am not clear on the difference between Tim's and Jacek's
interpretation
of the meaning of the query.
The only difference I can see is that my query returns one row
(a single Source), while Tim's query returns many "Objects".
The fact that Tim is talking about "Objects" confuses me a little.
Are we talking about all detections of given Source after t0,
or we are talking about all Objects associated with the very
first detection of given source after t0?
I'm also waiting for a final decision whether we want to go
with "my" query, "Tim's" query, or whether we should involve
alerts here.
Pondering this one has led to a couple of thoughts about the
Object table:
1. The row should include the time of the earliest
observation of
the object, and the time of the most recent observation.
2. The row should include at least a one bit flag that
indicates
whether an alert was ever generated for this object.
I added that to the list of pending schema changes that
I maintain (in my private area).
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
