The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

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

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 

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
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.