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

EugeneAbovsky

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.

[Deleted User]

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.

[Deleted User]

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

EugeneAbovsky

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

[Deleted User]

OK: glad Adam helped.

Rama_Rao_Samamo

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.

eyalush

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

Adrian_Oprea_1

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.