Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

empty node_name in execution_engine_profiles in vertica 7

in vertica 7, the node_name column in execution_engine_profiles is returned as an empty string (i.e., length of zero) if another query is executing concurrently. however, the other column values in execution_engine_profiles appear to be correct. once the other query has finished, node_name has correct values. this problem occurs with both single node vertica 7 running on a vm and a four node vertica instance on redhat. 

assuming this is a bug (and not a mistake on my part), it means one cannot really profile a long-running query while it is executing because the node_name information is missing. so, a quick fix or work-around would be appreciated.

to reproduce the bug, install single-node vertica on a VM (we used ubuntu) and install the demo database, vmmart.

create a large fact table to ensure a long-running query. we duplicated the data in the table online_sales_fact using the following query (executed 5 times so the table will end with 80 million rows):

 insert into online_sales.online_sales_fact (select * from online_sales.online_sales_fact);

then, in one screen, execute the sample query 9 from the Vertica vmart documentation: 

PROFILE SELECT sales_quantity, sales_dollar_amount, transaction_type, cc_name
FROM online_sales.online_sales_fact
INNER JOIN online_sales.call_center_dimension 
ON (online_sales.online_sales_fact.call_center_key
      = online_sales.call_center_dimension.call_center_key AND sale_date_key = 156)
ORDER BY sales_dollar_amount DESC;

record the transaction id and statement id. then, while the profiled query is executing, query execution_engine_profiles, e.g., (need to define tid and sid)

select ep.path_id as pid, ep.node_name, ep.baseplan_id as bid, ep.localplan_id as lpid, ep.operator_id as opid, ep.activity_id as aid, ep.resource_id as rid, ep.operator_name, ep.counter_name, ep.counter_tag, ep.counter_value
from execution_engine_profiles as ep 
where transaction_id = :tid and statement_id = :sid
order by path_id, node_name, baseplan_id, localplan_id, operator_id;

if the profiled query is still running, node_name will appear empty. then, when the profiled query completes, rerun the execution_engine_profiles query and node_name will appear.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.