Overview
This article provides an approach to leveraging multiple, dedicated engines for metadata refreshes in situations where your Dremio environment is set to use Iceberg metadata stored in the distributed store and where one engine is not sufficient for the volume and concurrency of metadata refreshes being performed in a Dremio platform.
Applies To
This article applies to all Dremio versions where it is possible to set up engines for workload isolation and where Iceberg metadata is enabled.
Details
Issue
When the use of Iceberg metadata is enabled in Dremio, It is always best practice to create a dedicated metadata refresh engine. Metadata refresh jobs can be routed to an engine using a simple workload management rule like the following:
query_type() IN ('Metadata Refresh')
As Dremio adoption increases within your organisation, you may find that one engine, even if it is configured with a large number of cores and a high amount of memory, may not be sufficient for the volume and concurrency of metadata refresh activity. The indicators for this are when metadata refreshes start to slow down, begin accruing wait time in the queue or indeed get cancelled due to hitting queue timeout limits.
Solution
One thing to understand is that when a user issues the ALTER PDS….REFRESH METADATA command (which gets issued with the user’s ID), Dremio spawns a second job that is viewable in the Jobs list which is a REFRESH DATASET job; the owner of this job is $dremio$ and it is this job that is sent to the query routing rules.
You can route the REFRESH DATASET queries that are executed by the $dremio$ user to different queues (and hence route them to different engines) by relying on simple maths. The below example shows how we can route several requests that are being executed one after another, just via clicks in the UI for simulation purposes, to different queues:
We are using 3 metadata refresh queues in this example, each associated with a distinct metadata refresh engine. You can, of course, create as many queues and engines as you like once you understand how this approach works.
The process is to take the current timestamp, extract the seconds value from it and then MOD it on the number 3 since this is how many metadata refresh queues we want. This will give us one of 3 values as output: 0, 1 or 2.
With that simple logic in mind, the routing rules we set up will route to our queues MR1, MR2 and MR3 are as follows. Note order is important:
Rule MR1 (routes to queue MR1) query_type() IN ('Metadata Refresh') AND MOD(SECOND(CURRENT_TIMESTAMP), 3) = 2
Rule MR2 (routes to queue MR2)
query_type() IN ('Metadata Refresh') AND MOD(SECOND(CURRENT_TIMESTAMP), 3) = 1
Rule MR3 (routes to queue MR3) -- the catch-all (which is the MOD = 0 but better to include all remaining)
query_type() IN ('Metadata Refresh')