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

Comments

• Options

  [Deleted User]

  April 2013

  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.

  0
• Options

  [Deleted User]

  April 2013

  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

  0
• Options

  EugeneAbovsky

  April 2013

  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

  0
• Options

  [Deleted User]

  April 2013

  OK: glad Adam helped.

  0
• Options

  Rama_Rao_Samamo

  July 2014

  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.
  

  0
• Options

  eyalush

  October 2015

  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

  0
• Options

  Adrian_Oprea_1

  October 2015

  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.

  0