The number of unused projections is not displayed

yamakawayamakawa Vertica Customer

Hi.

The number of unused projections is not displayed at Management Console.
Home -> Databases and Clusters -> MyDB -> Activity Table Utilization
Right pane - Projections Summary

For items other than unused projections, the values are displayed.
Once I restarted the MC, the situation did not change.

Answers

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited December 2020

    The count of unused projections comes from a Work Load Analyzer Rule. That rule has several parameters that are used in its underlying query.

    You can display the parameters like so:

    dbadmin=> SELECT parameter, current_value, default_value FROM vs_tuning_rules a JOIN vs_tuning_rule_parameters b ON b.tuning_rule = a.name WHERE observation_type = 'PROJ_UNUSED';
          parameter      | current_value | default_value
    ---------------------+---------------+---------------
     past_days           |            30 |            30
     table_used_at_least |            30 |            30
     min_used_bytes      |      10000000 |      10000000
    (3 rows)
    
    

    You can change the parameter values if you'd like.

    In fact, it's planned that in Vertica 10.1 we're lowering the defaults for this WLA rule to more appropriate values per client feedback. Plus, it's also planned that you will be able to tune WLA rule parameters from within MC.

  • yamakawayamakawa Vertica Customer

    @Jim_Knicely

    Thank you for your answer.

    I ran the query you gave as an example, and the settings were left at their default values.
    Is there any way I can use the query to get the unused projections if they do not show up on ManagementConsole?

  • moshegmosheg Vertica Employee Administrator

    To see which projections were loaded but not queried try this:

    SELECT anchor_table_schema,anchor_table_name ,projection_name ,max(query_start_timestamp) last_loaded 
    FROM projection_usage 
    WHERE io_type = 'output' AND projection_id NOT IN ( SELECT projection_id FROM projection_usage WHERE io_type = 'input' ) 
    AND anchor_table_id in (SELECT anchor_table_id FROM projections WHERE is_segmented group by 1 having count(*) > 2 ) 
    GROUP BY 1,2,3 
    ORDER BY 1,2,3;
    

    Last time a projection was loaded and queried:

    SELECT anchor_table_schema ,anchor_table_name ,projection_name
       ,max(DECODE(io_type, 'input', query_start_timestamp, NULL)) AS 'last_query'
       ,max(DECODE(io_type, 'output', query_start_timestamp, NULL)) AS 'last_ load'
    ,count(DECODE(io_type, 'input', query_start_timestamp, NULL)) AS 'count_query'
       ,count(DECODE(io_type, 'output', query_start_timestamp, NULL)) AS 'count_load'
    FROM projection_usage
    GROUP BY 1,2,3
    ORDER BY 4 DESC;
    
  • yamakawayamakawa Vertica Customer

    @mosheg

    Thank you for your answer.
    Unused projections could be checked.

    The question that remains is why the numbers are not displayed in the browser.
    I can see the unused projections in the individual tables, but not in the "Projections Summary".

    You can see them in MC in the development environment, which is accessible from the same terminal. I do not know the cause of the difference.

Leave a Comment

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