Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Is there a monitoring table available with the last-touch timestamp for a table?

Is it possible to somehow find out the last-touch time for a table in vertica, possibly via some kind of a monitor table? The use case I am trying to solve for is when using some "cache" type tables for reports which I want to linger while their being active, but if no query's touched the table for X amount of time, I want to have a garbage-collection procedure drop the table.

Comments

  • If you're willing to do a little computation, Vertica does provide some usage information at the projection level. Once you know the projections, you can figure out what tables those projections belong to with some system table queries. You might want to take a look through the documentation of Vertica's monitoring APIs; they list all the tables that you can use to do this sort of thing: https://my.vertica.com/docs/CE/6.0.1/HTML/index.htm#9338.htm Let us know if you can figure out how to do what you'd like to do from there.
  • Hello: You may find the table query_requests monitoring table helpful. You could specify the request_type as QUERY, and possibly check the timestamp info. Here's the basic output with select * from query_requests: ---------------------------------------------------------------- node_name | v_vmart_node0003 user_name | dbadmin session_id | node03-6456:0x2e59 request_id | 1 transaction_id | 54043195528456874 statement_id | 1 request_type | QUERY request | select * from vs_nodes; request_label | search_path | "$user", public, v_catalog, v_monitor, v_internal memory_acquired_mb | 100 success | t error_count | start_timestamp | 2012-12-02 13:51:31.048956-05 end_timestamp | 2012-12-02 13:51:32.719739-05 request_duration_ms | 1671 is_executing | f
  • I like Adam's answer a bit better - the query_requests monitoring table would be useful if it actually had some information on used tables or projections in the columns - not having that makes it much less useful for this use case. Getting to the table name by first looking at the projection_usage monitoring table then looking up the table name via v_catalog.projections seems to be the ticket. Thanks
  • OK: glad Adam helped.
  • why not querying epoch value on the given table?

    select max(epoch) from <table_name>;

    But I am not sure if there is way to convert the epoch value to timestamp.
  • Hi,

    I am looking to see last used (if possible select/insert/update) tables.

     

    We have many Schemas and tables but alot of them are junk and not in used.

     

    Does anyone have a script to identify or Show activity on Tables?

     

    I am guessing I am not the first one :)

     

    thanks,

    Eyal

  • What I do to monitor something like this is i look at projection usage.

     I don`t care about the table, what i care more are projections, because to many projections will make your life a hell.

     Loading will be slow, purging will be slow, waste of space, how used is that projection in your workload and does it worth having an extra projection just to gain 1-2 sec on a 10 sec query ?! Also the TM is working to much when you have a lot of mouths to feed :).

     

     Here is SQL to make a report out of your Proj usage:

    with a1 as (
    SELECT p.table_schema,
    p.basename AS table_name,
    max(pu.last_used) AS last_used,
    date_part('day'::varchar(3), (((statement_timestamp())::timestamp - max(pu.last_used)))) AS days_last_used,
    round(sum((sc.used_bytes / 1048576::float)), 3) AS used_mb,
    round((sum(sc.used_bytes) / 1073741824::float), 3) AS used_gb
    FROM ((( SELECT dc_projections_used.projection_oid,
    max(dc_projections_used."time") AS last_used
    FROM v_internal.dc_projections_used
    WHERE (dc_projections_used.table_oid IN ( SELECT tables.table_id
    FROM v_catalog.tables
    WHERE (NOT tables.is_system_table)))
    GROUP BY dc_projections_used.projection_oid) pu JOIN ( SELECT vs_projections.oid,
    vs_projections.name,
    vs_projections.basename,
    vs_projections.schemaname AS table_schema
    FROM v_internal.vs_projections) p ON ((p.oid = pu.projection_oid))) JOIN ( SELECT storage_containers.projection_id,
    storage_containers.used_bytes
    FROM v_monitor.storage_containers) sc ON ((sc.projection_id = pu.projection_oid)))
    GROUP BY p.table_schema,
    p.basename
    ORDER BY max(pu.last_used)
    ),
    a2 as (
    SELECT innner.table_schema,
    innner.table_name,
    innner.last_used,
    innner.days_last_used,
    innner.used_mb,
    innner.used_gb
    FROM ( SELECT t.table_schema,
    t.table_name,
    t.create_time AS last_used,
    coalesce(date_part('day'::varchar(3), (((statement_timestamp())::timestamp - max(pu.query_start_timestamp)))), (-1)::numeric(18,0)) AS days_last_used,
    0::float AS used_mb,
    0::float AS used_gb
    FROM (v_catalog.tables t LEFT JOIN v_monitor.projection_usage pu ON ((pu.anchor_table_id = t.table_id)))
    GROUP BY t.table_schema,
    t.table_name,
    t.create_time) innner
    WHERE (innner.days_last_used = (-1)::numeric(18,0))
    ORDER BY innner.days_last_used DESC
    )
    SELECT a1.table_schema,
    a1.table_name,
    (a1.last_used)::date AS last_used,
    a1.days_last_used,
    a1.used_mb
    FROM a1 UNION ALL SELECT a2.table_schema,
    a2.table_name,
    (a2.last_used)::date AS last_used,
    a2.days_last_used,
    a2.used_mb
    FROM a2
    ORDER BY 4;

    - you can create some views based on those queryes ot make them fixed. 

    - hope this helped.

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.