Queries on sys.project.history.jobs are very slow and easily hit 32000 bytes limit

Dremio Cloud
1

A select * from sys.project.history.jobs gives us a Field exceeds the size limit of 32000 bytes.
error.

image

We could not understand which fields are the offending ones, in general.

Also, our Dremio Cloud test project is pretty recent and we didn’t run many jobs, however queries on this table our very slow:

image
image791×348 36.1 KB

Questions:

  • will it be possible to raise the 32k limit on dremio cloud, given that it is not even possible to query system views with that?
  • can we expect queries on this (small) table to become faster in the future? we’d like to rely on this table to set up some monitoring dashboards.
2

Hello there,

Thank you for contacting Dremio.
I understand that you are observing below error on running query select * from sys.project.history.jobs
Field exceeds the size limit of 32000 bytes.

Regarding your queries:

  • will it be possible to raise the 32k limit on dremio cloud, given that it is not even possible to query system views with that?

The default limit for the property limits.single_field_size_bytes is set to 32K. As of now, the Support Key can only be updated by Dremio internal team. To review the same from our end, I have sent a Private message regarding the above. Please reply to the same Private Message with your OrgID.

  • can we expect queries on this (small) table to become faster in the future? we’d like to rely on this table to set up some monitoring dashboards.

To assist you better on this, we would first need to review the current execution time. Once we review the above details, we will share the suggestions accordingly.

Feel free to update if you have any queries/concerns.

3

Thank you @Shantanu_Singh, I sent our OrganizationID in response to your private message.

Look forward to receiving your suggestions. :pray:

4

Hello,

Thank you for providing the requested OrgID details.
I have increased the limit for this Support Key limits.single_field_size_bytes to 65K from our end.
Further, please execute the same query and let us know if that helps.

We will further review the JobID for the same and analyze if there are any issues or if you observe any slowness.

Regards,

5

Hi Shantanu,

thank you very much raising for raising that limit: the same query now completes successfully.

It is still rather slow, though.

I defined a VDS as select * from sys.project.history.jobs and creating a reflection for it takes ~6 minutes.

Screenshot from 2022-08-31 22-07-10
Screenshot from 2022-08-31 22-07-10609×1129 80.6 KB

Screenshot from 2022-08-31 22-07-36
Screenshot from 2022-08-31 22-07-36824×618 36.8 KB

A ~303s “IO wait time” seems a bit large for retrieving ~25.60MB…

Please let us know whether this is expected to change. :pray:

In the meantime, we’ll try using the reflection for monitoring purpose, even though this will introduce a considerable latency.

Best,
Matteo

1 Like
6

Hi Matteo,

Thanks for your patience and for your update on the status.
On reviewing the Job-1cf09081-e186-1067-9291-7768f74c2900 from my end for the slow query. I see that the TABLE_FUNCTION operator 00-08 is taking most time Min Wait Time 3m25s and this time is taken for - IO Wait is 3.42m (98.26%)

I am checking further internal to identify this issue and will update our findings.

Meanwhile, to narrow down this issue could you also confirm if the slowness is observed for other tables in sys or any other dataset as well?

Regards,