how I can see database size correctly (raw and compressed)

Hello, I can't understand how I can see database size correctly (raw and compressed)
I see compressed database size:
select sum(used_bytes) from projection_storage;
I get output 1139051427 bytes or 1139 Мb

I see raw database size:
select audit ('');
I get output 229751617 bytes or 229 Мb

Why raw database size in 5 times less than compressed?
It has to be vice versa or do I see database size not correctly?

Tagged:

Answers

  • Jim_KnicelyJim_Knicely Administrator
    edited June 22

    Are you running Eon or Enterprise?

    Do you have a lot of projections per anchor table? Or a lot of replicated projections?

    SELECT anchor_table_name, COUNT(*) FROM projections GROUP BY anchor_table_name ORDER BY 2 DESC LIMIT 5;
    SELECT COUNT(*) FROM projections WHERE NOT is_segmented;
    

    Note: Data in TEMP tables is included in PROJECTION_STORAGE but not an AUDIT().

  • I have an enterprise mode (3 nodes in cluster)
    This is the output of the commands above

    SELECT anchor_table_name, COUNT(*) FROM projections GROUP BY anchor_table_name ORDER BY 2 DESC LIMIT 5;
         anchor_table_name     | COUNT
    ---------------------------+-------
     stream_sources            |     7
     stream_microbatches       |     7
     stream_microbatch_history |     6
     stream_targets            |     5
     stream_clusters           |     4
    (5 rows)
    
     SELECT COUNT(*) FROM projections WHERE NOT is_segmented;
     COUNT
    -------
        21
    (1 row)
    
  • LenoyJLenoyJ Employee
    edited June 26

    @aleksss55 said:
    Hello, I can't understand how I can see database size correctly (raw and compressed)
    I see compressed database size:
    select sum(used_bytes) from projection_storage;
    I get output 1139051427 bytes or 1139 Мb

    Just querying projection_storage won't be accurate when comparing to the results from the audit. Projection_storage includes buddy projections and other type of projections which are all not included in the license audit. Most types of 'duplicated' data aren't considered for license audit (IMO, one of the cool perks of using Vertica :)). That's why you see license audit size drastically smaller than projection_storage.
     
    According to https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/Licensing/CalculatingTheDatabaseSize.htm:

    Unaudited Data
    Table data that appears in multiple projections is counted only once. An audit also excludes the following data:

    • Temporary table data.
    • Data in SET USING columns.
    • Non-columnar data accessible through external table definitions. Data in columnar formats such as ORC and Parquet count against your totals.
    • Data that was deleted but not yet purged.
    • Data stored in system and work tables such as monitoring tables, Data Collector tables, and Database Designer tables.
    • Delimiter characters.

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.