How do I check the last updated time stamp on tables in my vertica cluster?
Hi,
I am looking at a a vertica database of about 80 tables, most of which are not used. I want to figure out which ones have not been used in a while so I can rename and delete them. Is there a script I can run to figure out when all tables in the database were last updated?
Thanks.
C
0
Comments
Hi ,
This a sql i use to look at unused projections.(this will show all the projections that have quried at a point).
- remove unsed projections since they will slowdown loading and refreshing, the TM will do more work then necessary.
You also might have empty tables (only the DDL is there on projections for them).
- remove them since is creating to much confusion/hard to manage.
See if you have views pointing to this tables.
Also you might have projections that have never been used:
Note:
- this will depend on the retention policy on the dc_projections_used table.
-this query will tell you how long back can you look for query requests info.
- this can be increased also but you need to considerthe space usage of the table(remember all DC table are stored on disk, so quering this table is perf killer).
Ohh almost forgot !!!
This is something that might trasah your cleanup strategy as did with mine a while back.
If you have a table that was created , populated and queried a while back , let`s say 60 days. This table should be considered for cleanup ! right !
But as a good DBA you want to have statistics for your table(all of them), so when you run statistics on this table guess what happens ? the table wil no longer appear as unused, it looks like the analyze_stat will 'query' the table and it will appaer in the used_projections table.
This can be misleading when you try to identify the real unused tables.
So as tactict to avoid this, now i query the table dc_projection_used table and run my stats only on used table.
Hi. Thanks for the reply. The queries you provided preved very helpful.
Is there a way to see the when the tables themselves were last modified (INSERT/UPDATE)?
Thanks.
C
Does anyone have solution for this?
You can use the PROJECTION_USAGE system table, io_type = 'output'. Review the current effective retention policy for the corresponding Data Collector tables since those tables control the timeframe covered by the system table.
--Sharon