Profiling query
Hi All,
Could You please tell me what else should be done to view queries in system tables such as Query_profiles, Query_Events. I would like analyze my queries execution in details like PARTITION_ELIMINATED etc...). EXPLAIN doesn't provide these information but as I've read I can find these in system tables. My problem is as I wrote before I cannot force vertica to log my queries in those tables.
I've used PROFILE keyword before select statement
I've enabled query profiling by execute SELECT ENABLE_PROFILING('query');
and still nothing.
vertica 7.2
0
Comments
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