Tips & Tricks

Clean out old projections

4

Spring officially starts tomorrow and it’s the perfect time to do some spring cleaning on old projections. This quick tip shows how to identify unused projections.

Since additional projections take up storage, it’s beneficial to periodically check the usage on projections. The following query will return the 50 oldest projections based on usage:

To see the size of the projection, this query will add data from storage_containers:

As this information is kept in a data collector, check for the retention and increase if desired:

About the author / 

Norbert Krupa

Norbert is the founder of vertica.tips and a Solutions Engineer at Talend. He is an HP Accredited Solutions Expert for Vertica Big Data Solutions. He has written the Vertica Diagnostic Queries which aim to cover monitoring, diagnostics and performance tuning. The views, opinions, and thoughts expressed here do not represent those of the user's employer.

4 Comments

  1. Gregory Williams March 19, 2015 at 8:36 AM -  Reply

    Small correction:
    SUM(sc.used_bytes) AS used_kb,
    should be:
    SUM(sc.used_bytes) / 1024 AS used_kb,

    • Norbert Krupa March 19, 2015 at 11:23 AM -  Reply

      Thank you, Gregory. I’ve fixed the error.

  2. Bryna Conlon August 13, 2015 at 4:09 PM -  Reply

    Does select analyze_statistcs(”) update the time on all projections in projection_usage? Should I be looking at query_request joined to projections to find which projections are being used?

    • Norbert Krupa August 14, 2015 at 9:35 AM -  Reply

      Indeed running ANALYZE_STATISTICS on a projection will make it appear as used. My caution with query_requests would be that retention is limited and it might not be reporting accurately due to underlying joins on this view. An alternative might be to run ANALYZE_STATISTICS only on recently used projections.

Leave a Reply

Upcoming Events

  • No upcoming events
AEC v1.0.4

Subscribe to Blog via Email

Enter your email address to subscribe and receive notifications of new posts by email.

Read more use cases here.

Notice

This site is not affiliated, endorsed or associated with HPE Vertica. This site makes no claims on ownership of trademark rights. The author contributions on this site are licensed under CC BY-SA 3.0 with attribution required.
%d bloggers like this: