Profiling query

Hi kostek,

What is the retention policy for dc_execution_engine_profiles? This is one of the tables that provides data for the system tables you listed.

Can you post the results from running "select * from data_collector where table_name = 'dc_execution_engine_profiles';" in vsql? The part that's important is 

memory_buffer_size_kb | 2000
disk_size_kb | 50000
interval_set | f
interval_time | 0

Sometimes the DC table will have a retention policy set to 'store 0 time of data', which will prevent any records from being written to the table. The fix in that case would be to turn off the interval-limit for the table or increase the time/disk space allocated. 

Chris thanks for reply

Result of query below:

node_name                v_vertica_node0001
component                ExecutionEngineProfiles
table_name               dc_execution_engine_profiles
description              History of EE profiles
access_restricted        false
memory_buffer_size_kb    2000
disk_size_kb             50000
interval_set             false
interval_time            <Other>
record_too_big_errors    0
lost_buffers             0
lost_records             0
retired_files            2
retired_records          60722
current_memory_records   0
current_disk_records     99710
current_memory_bytes     0
current_disk_bytes       42006686
first_time               2015-12-14 13:24:29.311041
last_time                2015-12-17 12:15:13.344525
kb_per_day               10941.465013929

Hi kostek, 

It looks like that DC table is recording data, but I made a mistake in terms of which table to check. Apologies, I should have checked sooner. 

The tables that provides most of the data for query_profiles are [v_internal.]dc_requests_issued and dc_requests_completed. Is there any data there if you query them directly? If not, it would be helpful to see the configurations for those tables as well, just as you've provided for dc_execution_engine_profiles.

-Chris

Hi Chris,

Thanks for helping me.

I did some investigation

I see my old queries I've run 2 weeks ago in dc_requests_issued,dc_requests_completed, query_profiles tables. I've tried to reexecute these queries but unfortunately they don't apperar in these tables now. So it looks like something has changed in the configuration - I didn't make any changes but server/database was restarted few times because of power supply.

I've noticed one more thing - not all queries appear in Query_Events, running query which use e.g. PARTITIONS_ELIMINATED, NO GROUPBY PUSHDOWN, RLE_OVERRIDDEN etc.. causes that it goes to  Query_Events but when running just simple "select sum(FILED) from TABLE;" thee is no trace of it in Query_Events.

parameters in dc_requests_issued and dc_requests_completed are the same like in dc_execution_engine_profiles:

memory_buffer_size_kb 2000
disk_size_kb          50000
interval_set          false
interval_time         <Other>

Does the data_collector table indicate anything odd like a high amount of 'lost_records' for the dc tables which are not recording data anymore? I am stumpted as well as at point, as I can't think of what other configuration you would need to set to start recording records again. I am hoping someone more experienced in this area can step in at this point. In the meantime, I can point you to http://my.vertica.com/docs/7.2.x/HTML/index.htm#search-data_collector for more information on how to configure the data collector. 

As for Query_Events, that looks like the expected behavior. Query_Events will display information from the dc_execution_engine_events table, which will only record information for events like those you've listed. Queries that don't generate any events will not be listed, which is why you don't see your simple query there. 

Hope that helps,

Chris

Try this trick: 

1) Add fake guid to your query 

2) Catch that guid in monitoring view 

INSERT INTO /*e72dc6e5-b559-4deb-8082-d11d7bde196b*/ Facts.TestFieldpk_date8b58cdbcd0f1425f8b4c4277a0c707c8(pk_date100_, pk_date_, pk_date, TotalPrice)
SELECT pk_date100 AS pk_date100_, pk_date AS pk_date_, pk_date, quantity * Site_Price AS TotalPrice FROM Facts.Orders_User2
WHERE order_id % 3 = 1;

SELECT request_duration_ms, memory_acquired_mb, request
FROM QUERY_REQUESTS
WHERE
NOT is_executing AND
request LIKE '%e72dc6e5-b559-4deb-8082-d11d7bde196b%' AND
end_timestamp > CURRENT_TIMESTAMP - INTERVAL '20 minute' AND
request NOT LIKE '%v_internal%' AND
request NOT LIKE '%all_tables%'
LIMIT 1;

Hi ,

Very simple way to see PARTITION_ELIMINATED is trun exapln plan using the local verbose keyword , did you try that ?

exmple :

explain local verbose  select * from yourTb;

Thanks