2

Our ODC instance is deployed on AWS Aurora RDS and is populated with approximately 200 million datasets. The Aurora RDS is configured as Serverless v2 (64 - 128 ACUs) and has 4 replicas (1 writer + 3 readers) in total.

datacube version = 1.8.12

I am facing issues with its performance, especially when I'm attempting to find_datasets that would return more than a few 10s of thousands datasets.

As an example, a find_datasets search generates the following query:

SELECT agdc.dataset.id, agdc.dataset.metadata_type_ref, agdc.dataset.dataset_type_ref, agdc.dataset.metadata, agdc.dataset.archived, agdc.dataset.added, agdc.dataset.added_by, array((SELECT selected_dataset_location.uri_scheme || ':' || selected_dataset_location.uri_body AS anon_1 
FROM agdc.dataset_location AS selected_dataset_location 
WHERE selected_dataset_location.dataset_ref = agdc.dataset.id AND selected_dataset_location.archived IS NULL ORDER BY selected_dataset_location.added DESC, selected_dataset_location.id DESC)) AS uris 
FROM agdc.dataset 
WHERE agdc.dataset.archived IS NULL AND (tstzrange(least(agdc.common_timestamp((agdc.dataset.metadata #>> '{properties, dtr:start_datetime}')), agdc.common_timestamp((agdc.dataset.metadata #>> '{properties, datetime}'))), greatest(agdc.common_timestamp((agdc.dataset.metadata #>> '{properties, dtr:end_datetime}')), agdc.common_timestamp((agdc.dataset.metadata #>> '{properties, datetime}'))), '[]') && tstzrange('2017-01-01T00:00:00+00:00'::timestamptz, '2023-05-31T23:59:59.999999+00:00'::timestamptz, '[]')) AND agdc.dataset.dataset_type_ref = 72

A SELECT COUNT (*) ... relevant query (same search parameters) will show about 8 million records returned.

The related database session gets a IO: DataFileRead wait event and stays there for a long time. I don't know if it completes since either datacube gets killed or I simply kill the process after a lot of time (maybe more than 1 h).

If I copy-paste the generated query to pgAdmin and execute it directly from my computer, I'm getting a ClientWrite event.

I'm not sure if this is a datacube/sqlalchemy issue or a postgres issue.

1 Answer 1

0

It's a pretty unusual use case to select 8 million datasets.

I'd rethink your architecture a bit, and break your analysis down, or if you really want to interrogate that many datasets, perhaps go direct to Postgres or dump to a bulk format to explore. Something like Parquet would work.

What I expect is happening is that this query is happily churning away and slowly returning the 8 million rows, but it's timing out because the code that has been written in Datacube expects something in seconds, not minutes or an hour!

You could explore the bottleneck, whether it's the database, the network or the Python code, but I reckon re-thinking what you're doing is a better idea.

1
  • 1
    My use case does NOT involve returning 8 million datasets. The issue above is regarding a limitation when querying my ODC instance where the returned Dataset objects exceed a few tens of thousands. I'm not sure how to determine how unusual this use case is but I doesn't seem like an exceptional one. EDIT: It's been a while since this issue was posted. We have decided to drop ODC because of its poor performance and its high costs, in favor of a custom solution involving NoSQL.
    – Sotosoul
    Oct 31, 2023 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.