negative 'execution time (us)' for StorageUnion
I am toying with the idea of profiling queries using the execution_engine_profiles ( EEP) counters. When i see congestion on the cluster, I am automatically snapshotting EEP data.
While the counters are briefly displayed in
https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/MONITOR/EXECUTION_ENGINE_PROFILES.htm?Highlight=execution_engine_profiles
I would like to know which counters i should focus on in order to pinpoint performance problem queries around CPU , network and disk usage.
While working on this I noticed that for a given query Storage Union operator had a negative counter value for 'execution time (us)' . How can that be possible?
Here is what i have so far:
txid_stid_user | CPUtime(s) | clock time(s) | network wait (s) | K rows produced | encoded MiB recv
------------------------------+------------+---------------+------------------+-----------------+------------------
135107988853815273-5-user | -72.11 | 228.75 | 156.23 | 52418 | 0.00
select A.txid_stid_user,
sum( CASE counter_name WHEN 'execution time (us)' THEN sec_sum/1000/1000 ELSE 0 END)::numeric(14,2) as 'CPUtime(s)',
sum( CASE counter_name WHEN 'clock time (us)' THEN sec_sum/1000/1000 ELSE 0 END)::numeric(14,2) as 'clock time(s)',
sum( CASE counter_name WHEN 'network wait (us)' THEN sec_sum/1000/1000 ELSE 0 END)::numeric(14,2) as 'network wait (s)',
sum( CASE counter_name WHEN 'rows produced' THEN sec_sum/1000 ELSE 0 END)::numeric(14,0) as 'K rows produced',
sum( CASE counter_name WHEN 'encoded bytes received' THEN sec_sum/1024/1024 ELSE 0 END)::numeric(14,2) as 'encoded MiB recv'
FROM (
select transaction_id ||'-' || statement_id ||'-'|| user_name as txid_stid_user,
counter_name,
(sum(counter_value)/1000)::numeric(14,2) as sec_sum
FROM dc.eep --vs_execution_engine_profiles
WHERE counter_name IN ('execution time (us)' , 'clock time (us)','encoded bytes received', 'network wait (us)' , 'rows produced' )
and transaction_id = 135107988853815273
group by 1,2 ) A
GROUP BY 1 ORDER BY 2 DESC;
Any suggestions or explanation on why the counter is negative and what counters i should focus on and how to interpret them?
Thanks.
Comments
Read over the blog post "System Tables for Query Performance" here:
https://my.vertica.com/kb/System-Tables-For-Performance/Content/BestPractices/System-Tables-For-Performance.htm
At the bottom, check out the "Viewing Counter Details" section.