Checklist for Inconsistent Execution Time of Same Query in Eon Mode

In Eon Mode, when a query is executed and if the ROS containers associated to the tables in the query are not present in depot, it will fetch results from the communal storage bucket. In the next immediate run, the query should provide results to the user from the files present in the depot. However, there are scenarios where high count of queries against various tables run in parallel and depot is flushed to make space for new ROS containers needed to serve other queries. This is totally acceptable.

What should you do when you observe inconsistent execution times for multiple runs of the same query?

Step 1: Run explain local with the problematic query.

explain local {problematic query}

This fetches all the ROS containers (.gt files) needed into the depot.

Step 2: Run the following query using the transaction id associated with Step 1.

select count (*) from vs_depot_fetch_queue where transaction_id =

You’ll see the required files being downloaded to depot. Monitor the output until the result of the query is 0. This indicates that all files are downloaded to depot.

Step 3: Run your query using profile keyword and wait for it to complete.

Profile {problematic query}

Capture the transaction_id and statement_id of the query from the hint in the output.

Step 4: Run the following query using transaction_id and statement_id from Step 3.

select sum(bytes_read), node_name,storage_location from dc_file_reads where transaction_id=  and statement_id=  group by node_name,storage_location;

Take a note of storage_location id.

Step 5: Run the following query using the list of storage_location ids from Step 5.

select location_id, location_path from storage_locations where location_id in {comma separated list of location id’s from step 4}

Now check the location_path in the output and confirm if the query has read all the files from depot or not. If it has read from depot, then everything is good. If not, go to Step 6

Step 6: Get the Vertica version using the follopwing query.

select version ();

If it is less than 12.0.4-6, then it is possible that you are running into a known bug, VER‑86935 which is fixed in 12.0.4-6. Please upgrade your cluster.

Step 7: Vertica version is higher than 12.0.4-6.
Raise a support ticket with Vertica Technical Support.