Server Implementers - Feature Request "Latest Datastream Observation Cache"?

[justinschembri]

Hello Topic #1 folk, @hylkevds @Kattouw -

Something of a feature request that is not strictly speaking part of STA.

Is there a performative solution where an additional table or an in memory cache storing the last phenomenon time per datastream? Useful for health-checking, although I guess that might mean an extra write per datastream.

My health-checking now looks like this query:

https://<frost-service>/v1.1/Things?
$select=id
&$expand=Datastreams(
  $select=id;
  $expand=Observations(
    $select=phenomenonTime;
    $top=1;
    $orderby=phenomenonTime desc
    )
  )
&$top=1000
&$skip=<int>
&$orderby=@iot.id asc

I run three queries in parallel until I exhaust the pages.

The infrastructure where the (Fraunhofer) FROST server is hosted must really affect performance because the timing is:

~ 13.5s for https://airquality-frost.k8s.ilt-dmz.iosb.fraunhofer.de/FROST-Server/v1.1 with ~5600 Things

~ 35.91s for https://multicare.bk.tudelft.nl/FROST-Server with 24 Things! Note: same credentials as the Brabantse server.

[hylkevds]

The performance difference between servers can have many reasons. For this query the indices on the Observations table are the most important. Also, fast disks. The airquality service runs on a node with a local SSD.

But, your phenomenonTime cache already exists! The Datastream class has phenomenonTime, resultTime and observedArea for exactly these queries. You can also filter on them, for example all Datastreams that have not been updated in the last 12 hours:

https://airquality-frost.k8s.ilt-dmz.iosb.fraunhofer.de/v1.1/Datastreams?$filter=phenomenonTime/end lt now() sub duration'PT12H'&$count=true

Note that lt and gt are a bit funky for time intervals. But you can also filter on phenomenonTime/end

[justinschembri]

🤦‍♂️ And here I've been making crazy convoluted queries. That is great, thanks I'll reimplement.

[justinschembri]

Reduced to <500ms :)

[justinschembri]

@hylkevds - the $filter=phenemonTime/end doesn't work on V1 FROST servers, e.g., the BRGM server was there some other syntax back then, or should I just resort to parsing the string?

[hylkevds]

That BRGM server is just really old, they really should update it at some point.
Without the /end the query should return the same data:
https://sensorthings-wq.brgm-rec.fr/FROST-Server/v1.0/Datastreams?$filter=phenomenonTime%20lt%20now()%20sub%20duration%27PT12H%27&$count=true

You do have to be patient though, almost 10 million Datastreams :)

[hylkevds]

Also, water quality data is updated very infrequently. Sampling campaigns are expensive, so most sampling locations are only visited once a month at maximum.

[hylkevds]

Also, manually using skip for pagination is very inefficient, especially for large result sets. At least for FROST its better to use the server-provided nextLink.

[justinschembri]

I figured firing things off requests in parallel could be performative? Isn't the next link using a skip anyway?

[hylkevds]

Nope, FROST does not use the $skip in nextLinks, it only adds it for user readability. It uses the $skipFilter.

The reason skip-pagination is bad, is that the database still has to find each item that is being skipped, and ensure it is visible in the current transaction. Especially that second part is important, since indices are shared across transactions, the database can't just use the index to jump to the starting point. It has to validate each and every entry before skipping it. So if you skip the first 100 entries, it first has to search these 100 entries. And if you then skip the first 200, it has to search these same first 100 entries again, before skipping them.
When using a filter to find the first entry, it can use the index to quickly find the first matching, visible entry, and directly start work on the entries that actually need to be returned.